Adding DataFrames with partially overlapping indices-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Assuming "Month"s are indexes (if not set_index('Month') first), we could use reindex each DataFrame with the union of the indexes, fillna and add:

idx = df1.index.union(df2.index)
out = df1.reindex(idx).fillna(0).add(df2.reindex(idx).fillna(0)).astype(int)

Output:

            Val 1  Val 2
Month                   
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