I have two Pandas DataFrames I’d like to add together, with a datetime index, and a set of common columns.
The datetime indices will have 95% common values, but some of the rows in df2 may not be in df1 and vice versa.
I’d like to add the two DataFrames together, and when one of the DataFrames do not have the index the other does just treat is as 0 (or take the one with a value, whichever is better).
The result should not drop any indices, i.e. something like an outer join, rather than an inner.
I have tried pd.add, but that appears to drop NaN results where both DataFrames do not have an entry.
pd.concat works where they don’t have common indices, but where they do I get duplicates instead of adding together. Do I have to do a second groupby sum step? I thought there’d be a simpler way to do this.
For example:
FRAME 1
| Month | Val 1 | Val 2 |
|---|---|---|
| 2022-01-01 | 1 | 2 |
| 2022-02-01 | 3 | 4 |
| 2022-03-01 | 5 | 6 |
FRAME 2
| Month | Val 1 | Val 2 |
|---|---|---|
| 2022-03-01 | 101 | 102 |
| 2022-04-01 | 103 | 104 |
| 2024-01-01 | 105 | 106 |
| 2025-01-01 | 107 | 108 |
DESIRED RESULT
| Month | Val 1 | Val 2 |
|---|---|---|
| 2022-01-01 | 1 | 2 |
| 2022-02-01 | 3 | 4 |
| 2022-03-01 | 106 | 108 |
| 2022-04-01 | 103 | 104 |
| 2024-01-01 | 105 | 106 |
| 2025-01-01 | 107 | 108 |
>Solution :
Hope this works for you 🙂
pd.concat([df1,df2]).groupby(["Month"]).sum().reset_index()
Output
Month Val1 Val2
0 2022-01-01 1 2
1 2022-02-01 3 4
2 2022-03-01 106 108
3 2022-04-01 103 104
4 2024-01-01 105 106
5 2025-01-01 107 108