Combining DataFrame on Datetime index, summing values in other rows if same index

Question

I have two DataFrames with the same columns and I want to have them combined so the final dataframe only has a unique set of dates, but the Count column gets summed for any matching dates across the dataframes. Here is an example:

DataFrame1

     Date        Count
0    2020-01-01    5
1    2020-01-02    10

DataFrame2

     Date        Count
0    2020-01-01    10
1    2020-01-03    20

Result

     Date        Count
0    2020-01-01    15
1    2020-01-02    10
3    2020-01-03    20

What is the best way to do this?

sophocles · Accepted Answer · 2021-01-26 14:32:01Z

2

Try, using groupby, and pd.concat:

res = (pd.concat([df1,df2],axis=0)).groupby('Date')['Count'].sum().reset_index()

Will get you:

         Date  Count
0  2020-01-01     15
1  2020-01-02     10
2  2020-01-03     20

edited Jan 26, 2021 at 14:32

answered Jan 26, 2021 at 14:04

sophocles

13.9k3 gold badges18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining DataFrame on Datetime index, summing values in other rows if same index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related