Resample pandas dataframe based on two columns

user_12131009 0
Viewed 1211

I got a pandas dataframe with two columns. A date and a ratingnumber, like this:

       Date            Rating
    0  2020-07-28      9
    1  2020-07-28      10
    2  2020-07-27      8
    3  2020-07-26      10
    4  2020-07-26      9
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99

I want to resample the DataFrame with time interval per day and count the amount of ratings per day and get the average rating per day. So the new dataframe should look like this:

   Date            Amount of Ratings      Average rating
0  2020-07-28      2                      9.5
1  2020-07-27      1                      8
2  2020-07-26      2                      9.5

How would I do that?

I changed the index to Datetimeindex and counted the rows with count(), but it counts all columns, where I want to resample the Rating column to Average rating per day.

This is what I tried:

df = df.set_index(pd.to_datetime(df['Date']))
df_resampled = df.resample('D').count()

Output:
               Date    Rating
Date                    
2020-07-21     17      17
2020-07-22     14      14
2020-07-23     16      16
2020-07-24     14      14
2020-07-25      9       9
pandas python
2 Answers

Use df.agg() to aggregate different operations over multiple columns

df_resampled = df.resample('D').agg({'Date': 'count', 'Value': 'mean'}))
df_resampled = df_resampled.rename(columns = {'Date' : 'Amount of Ratings' , 'Value' : 'Average rating'})

Output:
               Amount of Ratings     Average rating
Date                   
2020-07-26     2                     9.5
2020-07-27     1                     8.0
2020-07-28     2                     9.5
user_79939770

You can use Group by Agg for this problem:

df2= df.groupby(['Date'], as_index=False).agg(['mean', 'count'])
df2.columns = ['Average rating',  'Amount of Ratings']
df2 = df2.reset_index()
df2

Output:

       Date      Average rating   Amount of Ratings
0   2020-07-26       9.5               2
1   2020-07-27       8.0               1
2   2020-07-28       9.5               2

See more at here

user_132983390
Related