CODEWITHSUNDEEP
pandas
Seguin
Seguin

Reputation: 13

Pandas conditional group by

My dataframe look like this:

datetime              Owner    Weight   Score
2020-11-01 00:00:00    AC       40      40
2020-11-01 03:00:00    AC       40      50
2020-11-01 12:00:00    AC       30      45
2020-11-02 06:00:00    AC       20      30
2020-11-02 12:00:00    AC       20      30
2020-11-02 18:00:00    AC       20      30

I want to aggregate over each date.

Expected output:

date                  Owner    Weight   Score
2020-11-01             AC       110      135
2020-11-02             AC       60       90

How I am doing:

  1. I am extracting date from datetime
  2. grouping over data and owner (after dropping datetime) Any better way

Upvotes: 1

Views: 24

Answers (1)

jezrael
jezrael

Reputation: 862441

Use to_datetime with Series.dt.date and aggregate sum:

df['datetime'] = pd.to_datetime(df['datetime'])

df1 = df.groupby([df['datetime'].dt.date.rename('date'), 'Owner']).sum().reset_index()
print (df1)
         date Owner  Weight  Score
0  2020-11-01    AC     110    135
1  2020-11-02    AC      60     90

Alternative solution with Grouper:

df['datetime'] = pd.to_datetime(df['datetime'])

df1 = df.groupby([pd.Grouper(key='datetime', freq='D'), 'Owner']).sum().reset_index()
print (df1)
    datetime Owner  Weight  Score
0 2020-11-01    AC     110    135
1 2020-11-02    AC      60     90

Upvotes: 1

Related Questions