CODEWITHSUNDEEP
pythonpandasdataframepivot-table
fjurt
fjurt

Reputation: 793

Pivot table with rolling average

I try to create a pivot table to get a time series with a rolling average of two days over time. For that, I am using the pivot_table() with aggfunc='mean' but so far I was only able to create a mean for each day, without taking the previous day also into account. This would be a simple example data.frame:

df = pd.DataFrame({
    'Date':['2021-01-01', '2021-01-01', '2021-01-01', '2021-01-02', '2021-01-02', '2021-01-02', '2021-01-02', '2021-01-03', '2021-01-03', '2021-01-03'],
    'Name':['Tim', 'Tim', 'Ben', 'Leo', 'Tim', 'Ben', 'Leo', 'Leo', 'Ben', 'Tim'], 
    'Ratings':[9.0, 8.0, 5.0, 3.0, 'NaN', 'NaN', 6, 5, 3, 5]})

This is what I tried, but I don't know how to incorporate a rolling window of 2, to also consider the previous day for the mean of the rating.

df.pivot_table(
        values='Ratings', index='Date', columns='Name',
        fill_value=0, aggfunc='mean')

This would be what I try to achieve.

df = pd.DataFrame({
    'Date':['2021-01-01', '2021-01-02', '2021-01-03'],
    'Tim':[8.5, 8.5, 5], 
    'Ben':[5, 5, 3],
    'Leo':['NaN', 4.5, 4.66],})

Thank you very much for your help :)

Upvotes: 1

Views: 463

Answers (1)

BENY
BENY

Reputation: 323286

In your case

out = df.pivot_table(
    values='Ratings', index='Date', columns='Name',
     aggfunc='mean').ffill(limit=1)
Name        Ben  Leo  Tim
Date                     
2021-01-01  5.0  NaN  8.5
2021-01-02  5.0  4.5  8.5
2021-01-03  3.0  5.0  5.0

Upvotes: 2

Related Questions