Merge rows of a dataframe in pandas based on a column

Question

I am new to pandas. I have a dataframe that looks like this

sitename name date count 0 chess.com Autobiographer 2012-05-01 2 1 chess.com Autobiographer 2012-05-05 1 2 chess.com Autobiographer 2012-05-15 1 3 chess.com Autobiographer 2012-05-01 1 4 chess.com Autobiographer 2012-05-15 1 5 chess.com Autobiographer 2012-05-01 1

How to merge the rows based on date and sum up the count for the same date. Like in sql

select sitename, name, date count(*) from table group by date

I used df = dataframe.groupby('date') . I got this error pandas.core.groupby.DataFrameGroupBy object at 0x7f0d2de6f9d0> <pandas.core.groupby.DataFrameGroupBy object at 0x32bdb90> — user3527975
– user3527975, Commented May 28, 2014 at 18:58
@user3527975: that's not an error. That's simply what a groupby object looks like when you print it: you want to perform some operation on it (like selecting a column, or performing a sum, etc.) — DSM
– DSM, Commented May 28, 2014 at 19:14
@DSM : Thanks. Yes I want to perform a sum based on the date. But I want all the columns to be preserved in the updated dataframe. — user3527975
– user3527975, Commented May 28, 2014 at 19:45
@DSM : I have posted one more question on this site - stackoverflow.com/questions/23901459/…. Haven't got anything on this one. Do you have any idea for it? — user3527975
– user3527975, Commented May 28, 2014 at 20:55

TimmyCarbone · Accepted Answer · 2014-05-28 20:14:54Z

17

If you want to keep your sitename and name in your dataframe, you can do :

df = dataframe.groupby(['date', 'sitename', 'name']).sum()

EDIT : See @DSM's comment to reset the indexes and have a non indexed dataframe.

edited May 28, 2014 at 20:14

answered May 28, 2014 at 19:14

TimmyCarbone

4453 silver badges10 bronze badges

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

4 Comments

user3527975 Over a year ago

This works perfectly. Thanks for saving time. I was writing functions and doing an dataframe.apply

user3527975 Over a year ago

This combines the sitename date and name as one single column. But these have to be separate coulmns. Any suggestions on this?

DSM Over a year ago

@user3527975: no, it doesn't. You're probably confusing the index (which in this case is a multiindex) with a column. You can add .reset_index(), or add as_index=False to groupby, e.g. groupby(["date", "sitename", "name"], as_index=False).sum().

user3527975 Over a year ago

@DSM : I should have said it combines sitename, date and name and uses it as index. as_index = False does the job. Thank you.

8one6 · Accepted Answer · 2014-05-28 19:12:05Z

0

df = dataframe.groupby('date').sum()

answered May 28, 2014 at 19:12

8one6

13.9k14 gold badges65 silver badges85 bronze badges

2 Comments

user3527975 Over a year ago

This one just keeps the date and sum in the dataframe. Whereas the structure is desired like in the post

Datacrawler Over a year ago

You can add the columns and then if you want to print the result and keep the headers/columns add this , header=True, index=True)

Collectives™ on Stack Overflow

Merge rows of a dataframe in pandas based on a column

2 Answers 2

4 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Linked

Related