CODEWITHSUNDEEP
pythonpandasdatetimegroup-byaggregate
imad97
imad97

Reputation: 286

Group by month, sum one column and average another

I have the a dataframe that looks like the following:

Date Games Played Rating
2019-05-23 8 22
2023-01-29 10 32

The actual table is much longer. I want to group the table by month (the date column is a DateTime format column), and in doing so, sum together the games played column but average the rating column. Essentially, every row will have a month, total games played that month, and average rating for that month. How can I do these separate aggregations while still grouping by month in the date column.

Upvotes: 0

Views: 198

Answers (2)

Guilherme Alves Ribeiro
Guilherme Alves Ribeiro

Reputation: 64

Use aggregate

df.groupby(df.Date.dt.month).aggregate(
    {'Games Played': 'sum', 'Rating': 'mean'})

Upvotes: 1

Andrej Kesely
Andrej Kesely

Reputation: 195613

Try:

x = df.groupby(df['Date'].dt.month).agg({'Games Played': 'sum', 'Rating': 'mean'})
print(x)

Prints:

      Games Played  Rating
Date                      
1               13    18.5
5               11    21.0

DataFrame used:

        Date  Games Played  Rating
0 2019-05-23             8      22
1 2019-05-24             1      21
2 2019-05-25             2      20
3 2023-01-28             3       5
4 2023-01-29            10      32

If you want to group by year and month:

x = df.groupby([df['Date'].dt.year, df['Date'].dt.month]).agg({'Games Played': 'sum', 'Rating': 'mean'})
print(x)

Upvotes: 1

Related Questions