Sum column values of two dataframe

Ig_Ferr 0
Viewed 26

I have a dataframe called 'a' which contains the geographic weights of funds with isin code in column 'isin' (index column):

              U.S.  Canada  U.K.
isin
LU1739554647  0.08   0.210  0.11
LU0599946893  0.14   0.026  0.07
LU1460782227  0.25   0.050  0.14

Then I have another dataframe called 'b' with other instrument (that are not funds) and contains only one column with country code ('country') and the weight ('weight). asset_ID is the isin code of the instrument:

       asset_ID country  weight
0  US1912161007    U.S.   0.005
1  GB0007188757     U.K.   0.100

My goal is to calculate the geographical allocation of the entire portfolio by aggregating and summing up the weights of countries in the two dataframes. To do this, first I sum the column values for every column in dataframe 'a' with this line of code a.sum(axis = 0, skipna = True) and this output:

U.S.      0.470
Canada    0.286
U.K.      0.320

At this point I don't know how to aggregate and sum the values of the above output with dataframe 'b'. The desidered output result is like this:

U.S.      0.475
Canada    0.286
U.K.      0.420

What's the most efficient way to do this? I specify that the dataframe 'a' can have a variable number of columns as many as there are countries and the isin codes are always unique as well as the country codes. Thank you

dataframe pandas python
1 Answers

Use DataFrame.pivot with multiple values by DataFrame.mul and then sum:

s = df1.mul(df2.pivot('asset_ID','country','weight'), fill_value=1).sum()
print (s)
Canada    0.286
U.K.      0.420
U.S.      0.475
dtype: float64
jezrael0
Related