Home How to add dfs with different number of column axis levels, but sharing same axis names?

Questions

How to add dfs with different number of column axis levels, but sharing same axis names?

December 14, 2023

I have two multi-level column dataframes:

data = {
    'A': {
        'X': {'Value1': 2, 'Value2': 1},
        'Y': {'Value1': 2, 'Value2': 3}
    },
    'B': {
        'X': {'Value1': 10, 'Value2': 11},
        'Y': {'Value1': 10, 'Value2': 11}
    }
}

df = pd.DataFrame(data)

Which looks like this…

Group          A                           B                                   
Subgroup        X            Y             X             Y                        
Metric         Value1 Value2 Value1 Value2 Value1 Value2 Value1 Value2
2023-01-01        2     1     2     3     10     11     10     11
2023-01-02        2     1     2     3     10     11     10     11
2023-01-03        2     1     2     3     10     11     10     11
2023-01-04        2     1     2     3     10     11     10     11
2023-01-05        2     1     2     3     10     11     10     11

df2:

data = {
    'A': {'Value1': [3, 3, 1, 3, 3], 'Value2': [5, 2, 2, 2, 2]},
    'B': {'Value1': [3, 4, 7, 3, 3], 'Value2': [2, 2, 7, 2, 2]}
}

df_2 = pd.DataFrame(data, index=pd.to_datetime(['2023-01-01', 
'2023-01-02', '2023-01-03', '2023-01-04', '2023-01-05']))

# Convert to constructor
df2 = df_2.unstack().unstack()

Which looks like this…

Group          A             B                                                 
Metric         Value1 Value2 Value1 Value2 
2023-01-01        3      5     3     2    
2023-01-02        3      2     4     2      
2023-01-03        1      2     7     7    
2023-01-04        3      2     3     2       
2023-01-05        3      2     3     2

and would like to add df2 to df1, but for each combination of Group and Metric, add across each Subgroup that match to look like this…

Group          A                           B                                   
Subgroup        X            Y             X             Y                        
Metric         Value1 Value2 Value1 Value2 Value1 Value2 Value1 Value2
2023-01-01        5     6     5     5     13     13     13     13
2023-01-02        5     3     6     5     14     15     14     13
2023-01-03        3     3     9     10    17     18     17     18
2023-01-04        5     3     5     5     13     14     13     13
2023-01-05        5     3     5     5     13     14     13     13

Any help would be appreciated.

Some ideas like merging but I think i lose the middle subgroup level above but could have been doing it incorrectly.

>Solution :

I think the easiest might be to droplevel on df1, then add, finally recreate the DataFrame:

pd.DataFrame(df1.droplevel('Subgroup', axis=1).add(df2).to_numpy(),
             index=df1.index, columns=df1.columns)

Alternatively, reindex df2 and convert to numpy:

df1 += df2.reindex_like(df1.droplevel('Subgroup', axis=1)).to_numpy()

Output:

Group           A                           B                     
Subgroup        X             Y             X             Y       
Metric     Value1 Value2 Value1 Value2 Value1 Value2 Value1 Value2
2023-01-01      5      5      6      8     13     13     13     13
2023-01-02      5      5      3      5     14     14     13     13
2023-01-03      3      3      3      5     17     17     18     18
2023-01-04      5      5      3      5     13     13     13     13
2023-01-05      5      5      3      5     13     13     13     13