How to merge and sum multi index dataframes

Asked Sep 22 '21 at 00:53

Active Sep 22 '21 at 00:53

Viewed 95 times

I would like to add multiple multi index data frames together, with differing columns, but if there are similar columns/cells, then the values will be added together.

Constraints:

There are up to n dataframes to be merged and summed
Within the code, you cannot call the multi index columns and index (should be general)

Example:

df1

Category   | A | B | C | 
Region     | AC| AD| AK|
0-5 years  | 2 | 3 | 4 |
5-10 years | 1 | 2 | 5 |
10-12 years| 2 | 0 | 2 |

df 2
Category   | A | B | D | 
Region     | AC| AD| AM|
0-5 years  | 1 | 4 | 1 |
5-10 years | 2 | 5 | 1 |
10-12 years| 3 | 6 | 0 |

Desired outcome:

Category   | A | B | C | D |
Region     | AC| AD| AK| AM|
0-5 years  | 3 | 7 | 4 | 1 |
5-10 years | 3 | 7 | 5 | 1 |
10-12 years| 5 | 6 | 2 | 0 |

Any help would be greatly appreciated, thanks in advance!

asked Sep 22 '21 at 00:53

chrisC

1

Is this just `df1.add(df2, fill_value=0)`? – Henry Ecker Sep 22 '21 at 00:58
@HenryEcker When doing df1.add(df2) column D will return as NA values – chrisC Sep 22 '21 at 01:02
Add `fill_value=0` – Henry Ecker Sep 22 '21 at 01:02
@HenryEcker yes, but I'd like the values to be filled with the original D column values – chrisC Sep 22 '21 at 01:04
No, `df1.add(df2, fill_value=0)` will fill the missing spaces with 0 _instead of_ NaN _before_ adding and since 0 is the additive identity you'll end up with the correct sum since `x + 0 = x` [Adding two pandas dataframes](https://stackoverflow.com/q/11106823/15497888) – Henry Ecker Sep 22 '21 at 01:04
1. concat, 2.groupby().sum(). – lazy Sep 22 '21 at 01:31
@HenryEcker That did the trick, thanks! – chrisC Sep 23 '21 at 07:29

How to merge and sum multi index dataframes

0 Answers0