CODEWITHSUNDEEP
pythonpandasdataframemergepivot-table
Tony
Tony

Reputation: 241

Merge two dataframes (one df created from pivottable) based on index

I have two dataframes:

DF1: 

Dept  Produce  
Acct   
111 Fruit  
222 Vegetable

DF2:

Acct Spent LastIn  
111 50 Monday  
222 75 Tuesday

I want my new dataframe DF3 to look like this:

DF3:

Acct Produce Spent LastIn  
111 Fruit 50 Monday  
222 Vegetable 75 Tuesday

I have been trying to use: pd.merge(DF1,DF2, on='Acct'), but am getting a long-winded error that ends with:

KeyError: 'Acct'

I believe this is because DF1 is a converted pivottable and python thinks that DEPT is the actual index. So I try to remove 'Acct' row from DF1 using .drop but can't because I get this error:

ValueError: labels ['Acct'] not contained in axis.

How do I get to my desired endstate DF3?

Upvotes: 1

Views: 262

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210882

IIUC:

Sample DFs:

In [57]: d1
Out[57]:
Dept    Produce
Acct
111       Fruit
222   Vegetable

DF's index:

In [60]: d1.index
Out[60]: Int64Index([111, 222], dtype='int64', name='Acct')

Columns:

In [61]: d1.columns
Out[61]: Index(['Produce'], dtype='object', name='Dept')

DF2:

In [58]: d2
Out[58]:
   Acct  Spent   LastIn
0   111     50   Monday
1   222     75  Tuesday

Solution:

In [59]: d2.merge(d1, left_on='Acct', right_index=True)
Out[59]:
   Acct  Spent   LastIn    Produce
0   111     50   Monday      Fruit
1   222     75  Tuesday  Vegetable

Upvotes: 1

Related Questions