CODEWITHSUNDEEP
python-3.xpandasdataframe
Thanasis
Thanasis

Reputation: 725

Add the elements of a column in one dataframe to the elements of another column in another dataframe

Hi I have the following dataframes

enter image description here

And I would like to add the elements of the M/O column from Df2 to the elements of the A/O column in Df1. The same thing for Df2 M/L to Df1 A/L.

Both of these dataframes have 2 levels for column names.

enter image description here

I am trying the following but it doesn't add the 5th row of Df2 to Df1:

Df1 = {('A','O'): [5, 6, 7, 8], ('A','L'): [9, 10, 11, 12], ('G','O'): [2, 2, 2, 2], ('G','L'): [3, 3, 3, 3]}
index1 =[1,2,3,4]
Df1 = pd.DataFrame(data=Df1, index=index1)
Df2 = {('M','O'): [1, 2, 3, 4], ('M','L'): [5, 6, 7, 8], ('S','O'): [1, 1, 1, 1], ('S','L'): [1, 1, 1, 1]}
index2 =[1,2,3,5]
Df2 = pd.DataFrame(data=Df2, index=index2)

    for profile in ["O", "L"]:
    Df1[("A", profile)] = Df1[("A", profile)].add(Df2[("M", profile)], fill_value=0)

Do you know why?

Thanks

Upvotes: 0

Views: 132

Answers (1)

jezrael
jezrael

Reputation: 862471

You can use xs with drop_level=False for return MultiIndex DataFrame, then rename top level for align with level A of Df1:

df = Df2.xs('M', axis=1, level=0, drop_level=False).rename(columns={'M':'A'}, level=0)
Df1 = Df1.add(df, fill_value=0)
print (Df1)
      A          G     
      L     O    L    O
1  14.0   6.0  3.0  2.0
2  16.0   8.0  3.0  2.0
3  18.0  10.0  3.0  2.0
4  12.0   8.0  3.0  2.0
5   8.0   4.0  NaN  NaN

Upvotes: 1

Related Questions