pythonpandas

Pandas monthly rolling operation


I ended up figuring it out while writing out this question so I'll just post anyway and answer my own question in case someone else needs a little help.

Problem

Suppose we have a DataFrame, df, containing this data.

import pandas as pd
from io import StringIO

data = StringIO(
"""\
date          spendings  category
2014-03-25    10         A
2014-04-05    20         A
2014-04-15    10         A
2014-04-25    10         B
2014-05-05    10         B
2014-05-15    10         A
2014-05-25    10         A
"""
)

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

Goal

For each row, sum the spendings over every row that is within one month of it, ideally using DataFrame.rolling as it's a very clean syntax.

What I have tried

df = df.rolling("M").sum()

But this throws an exception

ValueError: <MonthEnd> is a non-fixed frequency

version: pandas==0.19.2


Solution

  • Use the "D" offset rather than "M" and specifically use "30D" for 30 days or approximately one month.

    df = df.rolling("30D").sum()
    

    Initially, I intuitively jumped to using "M" as I figured it stands for one month, but now it's clear why that doesn't work.