merge few pivot tables in pandas

Question

How I can merge two pandas pivot tables? When I try run my code I have error: keyerror

data_pivot= pandas.DataFrame(data.pivot_table(values = 'NR_ACTIONS', index=["HOUR", "OPID", "NAME"], columns='CONTACTED_PERSON_NEW', aggfunc='sum'))
data_pivot.fillna(0, inplace=True)
data2_pivot= pandas.DataFrame(data2.pivot_table(values = 'AMOUNT_PA', index=["HOUR", "OPID", "NAME"], columns='PA_TYPE', aggfunc='sum'))
data2_pivot.fillna(0, inplace=True)
all_data = pandas.merge(data_pivot, data2_pivot, 'left', on = ["HOUR", "OPID", "NAME"] )

Kostiantyn Palianychka · Accepted Answer · 2015-08-19 10:08:56Z

8

answer for my question is :

data_pivot= pandas.DataFrame(data.pivot_table(values = 'NR_ACTIONS', index=["HOUR", "OPID", "NAME"], columns='CONTACTED_PERSON_NEW', aggfunc='sum'))
data_pivot.fillna(0, inplace=True)
data_pivot.reset_index( inplace=True)
data2_pivot= pandas.DataFrame(data2.pivot_table(values = 'AMOUNT_PA', index=["HOUR", "OPID", "NAME"], columns='PA_TYPE', aggfunc='sum'))
data2_pivot.fillna(0, inplace=True)
data2_pivot.reset_index( inplace=True)
all_data = pandas.merge(data_pivot, data2_pivot, 'left', on = ["HOUR", "OPID", "NAME"] )

answered Aug 19, 2015 at 10:08

Kostiantyn Palianychka

5951 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jeangelj Over a year ago

that was great, what does the reset_index do that it fixes the key error? thank you

Arthur D. Howland Over a year ago

Had same 'key error' problem after trying to manipulate a df after a pivot_table procedure. df.reset_index(inplace=true) works! Although doing a match function ".isin" comparing two df's resulted in a "NoneType" object error, the solution was to leave out the "inplace=true." So df.reset_index() was the final answer.

el Josso · Accepted Answer · 2021-07-12 14:02:29Z

The best solution here, if you want to keep the index, would be to tell pandas to merge on the index on the left and right tables. By doing this, you don't need to reset the index.

data_pivot= pandas.DataFrame(data.pivot_table(values = 'NR_ACTIONS', index=["HOUR", "OPID", "NAME"], columns='CONTACTED_PERSON_NEW', aggfunc='sum'))
data_pivot.fillna(0, inplace=True)
data2_pivot= pandas.DataFrame(data2.pivot_table(values = 'AMOUNT_PA', index=["HOUR", "OPID", "NAME"], columns='PA_TYPE', aggfunc='sum'))
data2_pivot.fillna(0, inplace=True)

all_data = pandas.merge(data_pivot, data2_pivot, left_index=True,right_index=True )

You can always go to Pandas' doc to see the different arguments

PS: If you use multi index, it works, but you need to be careful, the number of keys in the other DataFrame (either the index or a number of columns) must match the number of levels.

Collectives™ on Stack Overflow

merge few pivot tables in pandas

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related