Adding rows that have the same column value in a pandas dataframe

Question

I have a pandas dataframe with dates and hours as columns. Now I want to add the hours of the same dates. For example to make this:

7-1-2016 | 4
7-1-2016 | 2
4-1-2016 | 5

Into this:

7-1-2016 | 6
4-1-2016 | 5

Is there a quick way to do this on big files?

Can you post raw data, code to reproduce your df, you code lacks column names. Anyway I think this would work: df.groupby('date')['hours'].sum() — EdChum
– EdChum, Commented Jan 7, 2016 at 14:08
I now see that I forgot to include some code, but this seems to be working. Thank you very much! — maxmijn
– maxmijn, Commented Jan 7, 2016 at 14:20

Sylhare · Accepted Answer · 2019-11-22 22:07:08Z

8

Here GroupBy can be used to provide the desired output.

DataFrame.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False)

Group series using mapper (dict or key function, apply given function to group, return result as series) or by a series of columns.

Try:

df.groupby('date')['hours'].sum()

edited Nov 22, 2019 at 22:07

Sylhare

7,2898 gold badges75 silver badges88 bronze badges

answered Jan 7, 2016 at 14:15

Kapil Bhalla

1163 bronze badges

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

2 Comments

maxmijn Over a year ago

This seemed to be working, but it is not adding up to the right values everywhere. I think this has to do with the fact that the hours are DateTime objects. This is an output I get for example: 2013-12-30 0 days 08:00:00

maxmijn Over a year ago

Nevermind, there was an error in my data. It works, thank you very much!

Chris · Accepted Answer · 2016-01-07 14:14:24Z

0

Setting the index to the date allows you to use the resample method (as well as lots of other time series functionality). If you have other analysis to do, I suggest:

df.index = pd.to_datetime(df['datecol'].astype(str) + ' ' + df['Hourcol'].astype(str), format='%Y-%m-%d %H')
df = df.resample('1d', how='sum') # defaults to mean

If the columns are already strings, then omit the .astpye(str)'s as necessary. However,

df.groupby('datecol').sum()['Hourcol']

will also work if this is the only analysis you want to do. You may need to add a .astype(int) if the hour column is not numeric.

answered Jan 7, 2016 at 14:14

Chris

9675 silver badges10 bronze badges

Collectives™ on Stack Overflow

Adding rows that have the same column value in a pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related