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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With