Getting sum data for smoothly shifting groups of 3 months of a months data in Pandas

user_9212050 0
Viewed 197

I have a time series data of the following form:

  Item  2020 Jan  2020 Feb  2020 Mar  2020 Apr  2020 May  2020 Jun
0    A         0         1         2         3         4         5
1    B         5         4         3         2         1         0

This is monthly data but I want to get quarterly data of this data. A normal quarterly data would be calculated by summing up Jan-Mar and Apr-Jun and would look like this:

  Item  2020 Q1  2020 Q2
0    A        3       12
1    B       12        3

I want to get smoother quarterly data so it would shift by only 1 month for each new data item, not 3 months. So it would have Jan-Mar, then Feb-Apr, then Mar-May, and Apr-Jun. So the resulting data would look like this:

  Item  2020 Q1  2020 Q1  2020 Q1  2020 Q2
0    A        3        6        9       12
1    B       12        9        6        3

I believe this is similar to cumsum which can be used as follows:

df_dates = df.iloc[:,1:]
df_dates.cumsum(axis=1)

which leads to the following result:

   2020 Jan  2020 Feb  2020 Mar  2020 Apr  2020 May  2020 Jun
0         0         1         3         6        10        15
1         5         9        12        14        15        15

but instead of getting the sum over the whole time, it gets the sum of the nearest 3 months (a quarter). I do not know how this version of cumsum is called but I saw it in many places so I believe there might be a library function for that.

cumsum pandas time-series dataframe python
2 Answers

Let us solve in steps

  • Set the index to Item column
  • Parse the date like columns to quarterly period
  • Calculate the rolling sum with window of size 3
  • Shift the calculated rolling sum 2 units along the columns axis and get rid of the last two columns
s = df.set_index('Item')
s.columns = pd.PeriodIndex(s.columns, freq='M').strftime('%Y Q%q')

s = s.rolling(3, axis=1).sum().shift(-2, axis=1).iloc[:, :-2]

print(s)

      2020 Q1  2020 Q1  2020 Q1  2020 Q2
Item                                    
A         3.0      6.0      9.0     12.0
B        12.0      9.0      6.0      3.0
user_128331660

Try with column wise groupby with axis=1:

>>> df.iloc[:, [0]].join(df.iloc[:, 1:].groupby(pd.to_datetime(df.columns[1:], format='%Y %b').quarter, axis=1).sum().add_prefix('Q'))
  Item  Q1  Q2
0    A   3  12
1    B  12   3
>>>

Edit:

I misread your question, to do what you want try rolling sum:

>>> x = df.rolling(3, axis=1).sum().dropna(axis='columns')
>>> df.iloc[:, [0]].join(x.set_axis('Q' + pd.to_datetime(df.columns[1:], format='%Y %b').quarter.astype(str)[:len(x.T)], axis=1))
  Item    Q1   Q1   Q1    Q2
0    A   3.0  6.0  9.0  12.0
1    B  12.0  9.0  6.0   3.0
>>>
user_87083640
Related