pandas Dataframes merge column names with column values

Question

I have 2 dataframes

df = pd.DataFrame({'Location': [ 'Hawai', 'Torino', 'Paris'],
                  'Time': [2000, 2001,2002],
                    'Value': [1.2, 2.2,3.4]
                   })
df.set_index(['Location','Time'],inplace=True)


df2 = pd.DataFrame({'Country': [ 'US', 'IT', 'FR'],
                'Unit': [ 'USD', 'EUR', 'EUR'],
                  'Location': [ 'Hawai', 'Torino', 'Paris'],
                  '2000': [666, 888,777],
                    '2002': [44,55,66]
                   })
df2.set_index(['Country','Unit','Location'],inplace=True)

It produces this :

               Value
Location Time       
Hawai    2000    1.2
Torino   2001    2.2
Paris    2002    3.4
                       2000  2002
Country Unit Location            
US      USD  Hawai      666    44
IT      EUR  Torino     888    55
FR      EUR  Paris      777    66

I need to merge them, such as for each country/unit/Location, each column is multiplied by the corresponding value from the first dataframe(given Location and Time)
So the result should look like

                       2000  2002
Country Unit Location            
US      USD  Hawai      799.2    149.6
IT      EUR  Torino     1065.6    187
FR      EUR  Paris      932.4    224.4

I'm stuck here, thanks for your help

So you want to merge the dataframes and multiply the Values in dataframe 1 to the corresponding year columns in dataframe 2? — Error - Syntactical Remorse
– Error - Syntactical Remorse, Commented Feb 22, 2019 at 19:50
yes exactly. The challenge here is to merge based on the column names — Crovish
– Crovish, Commented Feb 22, 2019 at 19:51

BENY · Accepted Answer · 2019-02-22 20:04:30Z

2

Doing with unstack then mul

df2.columns=df2.columns.astype(int)
s=df.Value.unstack(fill_value=1)
df2.mul(s)
Out[675]: 
                        2000  2001   2002
Country Unit Location                    
US      USD  Hawai     799.2   NaN   44.0
IT      EUR  Torino    888.0   NaN   55.0
FR      EUR  Paris     777.0   NaN  224.4

Base on the comment below

df2.mul(df.Value.reset_index('Location',drop=True))
Out[683]: 
                         2000  2001   2002
Country Unit Location                     
US      USD  Hawai      799.2   NaN  149.6
IT      EUR  Torino    1065.6   NaN  187.0
FR      EUR  Paris      932.4   NaN  224.4

edited Feb 22, 2019 at 20:04

answered Feb 22, 2019 at 19:52

BENY

324k22 gold badges176 silver badges250 bronze badges

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

10 Comments

Error - Syntactical Remorse Over a year ago

Wrong results for year 2000 and 2002

BENY Over a year ago

@Error-SyntacticalRemorse he did not show the whole data of df, that is why , and what do you think that number should be ?

BENY Over a year ago

@Error-SyntacticalRemorse my number show 799.2 and he do not have data for 2000 for Torino so should we keep it same ?

Error - Syntactical Remorse Over a year ago

Based on his example he is using the 1.2 value from year 2000 for Torino and Paris as well. (1.2)x(888)=1065.6 and (1.2)x(777)=932.4

Error - Syntactical Remorse Over a year ago

So my understanding is that location from data frame 1 is irrelevant. Only the year and the value is used. Is this right @Crovish

|

Collectives™ on Stack Overflow

pandas Dataframes merge column names with column values

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related