Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pandas Dataframes merge column names with column values

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

like image 257
Crovish Avatar asked Dec 04 '25 03:12

Crovish


1 Answers

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
like image 108
BENY Avatar answered Dec 05 '25 18:12

BENY



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!