Python - Pandas getting the rolling() mean or agg of current month and prior month in addition to category groupby

user_11761638 0
Viewed 304

I have time-series data across different months for various groups/categories. Across each group, I want to calculate a rolling mean of the spendings column over the current month and the month prior (if it exists).

I'm reusing some of the data from this post

import pandas as pd
from io import StringIO

data = StringIO(
"""
date          spendings  category
2020-01-01    10         A
2020-01-01    20         A
2020-01-01    15         A
2020-02-01    10         B
2020-02-01    10         B
2020-02-01    14         A
2020-02-01    19         A
2020-03-01    50         A
2020-04-01    40         A
"""
)

df = pd.read_csv(data,sep="\s+",parse_dates=True,index_col="date")

So with the example data above, for Category A the month of 2020-02-01 should have the mean as (10+20+15+14+19)/5. For the month of 2020-03-01 category A's mean would be (14+19+50)/3.

Here's one approach I've tried so far using Panda's rolling() function:

df = df.sort_index()
df.groupby('category').rolling('30D').spendings.mean()

and the results:

category  date      
A         2020-01-01    10.000000
          2020-01-01    15.000000
          2020-01-01    15.000000
          2020-02-01    14.000000
          2020-02-01    16.500000
          2020-03-01    27.666667
          2020-04-01    40.000000
B         2020-02-01    10.000000
          2020-02-01    10.000000

as we can see there are 2 problems.

  1. The current logic takes the mean of the current row's value and all prior rows within 30 days, but it doesn't include the values from other rows of the same month.
  2. The '30D' parameter doesn't equate to 1 month. We can see for category A that the March month has the mean from March and Feb, but the April month doesn't include the value from 2020-03-01 when calculating the mean.

Some other Stackoverflow posts have mentioned using a month or month-start as the rolling frequency instead of 30D, but my main challenge right now is figuring out how to capture all values for the current month and prior month when calculating the mean.

Edit: Expected output

category  date      
A         2020-01-01    15.000000
          2020-02-01    15.600000
          2020-03-01    27.666667
          2020-04-01    45.000000
B         2020-02-01    10.000000
rolling-computation pandas python
2 Answers

Found a solution using tail() and playing around with the number of days

user_117616380

I'm sure there is probably a better solution. But as it is not a true rolling mean, I don't think you can properly calculate using rolling.

The below solution works. You can add pd.DateOffset(day=1) to offset dates in order to take into account days too.

df = pd.read_csv(data,sep="\s+")
df['date'] = pd.to_datetime(df['date']) #easier to use pd datetimes

# get average spendings in current and previous month
def get_average(row):
    current_month = row.date
    previous_month = current_month - pd.DateOffset(months=1)
    #create filter for rows with same category and same or previous month
    date_category_filter = (((df.category == row.category) & (df.date == current_month)) | ((df.category == row.category) & (df.date == previous_month)))
    #return mean of all filtered rows
    return df.loc[date_category_filter]['spendings'].mean()
    
df['moving average'] = df.apply(get_average, axis=1)

# function returns values for all rows, hence contains duplicates.
# group data and use min to remove duplicates
df.groupby(['category','date']).min()
user_82526120
Related