how to convert monthly data to quarterly in pandas

Question

I have monthly data. I want to convert it to "periods" of 3 months where q1 starts in January. So in the example below, the first 3 month aggregation would translate into start of q2 (desired format: 1996q2). And the data value that results from mushing together 3 monthly values is a mean (average) of 3 columns. Conceptually, not complicated. Does anyone know how to do it in one swoop? Potentially, I could do a lot of hard work through looping and just hardcode the hell out of it, but I am new to pandas and looking for something more clever than brute force.

1996-04   1996-05 1996-06  1996-07 .....
25          19       37      40

So I am looking for:

1996q2  1996q3   1996q4  1997q1  1997q2 .....
 avg      avg      avg     ...     ...

MaxU - stand with Ukraine · Accepted Answer · 2018-04-18T13:21:49.473

51

you can use pd.PeriodIndex(..., freq='Q') in conjunction with groupby(..., axis=1):

In [63]: df
Out[63]:
   1996-04  1996-05  2000-07  2000-08  2010-10  2010-11  2010-12
0        1        2        3        4        1        1        1
1       25       19       37       40        1        2        3
2       10       20       30       40        4        4        5

In [64]: df.groupby(pd.PeriodIndex(df.columns, freq='Q'), axis=1).mean()
Out[64]:
   1996Q2  2000Q3    2010Q4
0     1.5     3.5  1.000000
1    22.0    38.5  2.000000
2    15.0    35.0  4.333333

UPDATE: to get columns in a resulting DF as strings intead of period dtype:

In [66]: res = (df.groupby(pd.PeriodIndex(df.columns, freq='Q'), axis=1)
                  .mean()
                  .rename(columns=lambda c: str(c).lower()))

In [67]: res
Out[67]:
   1996q2  2000q3    2010q4
0     1.5     3.5  1.000000
1    22.0    38.5  2.000000
2    15.0    35.0  4.333333

In [68]: res.columns.dtype
Out[68]: dtype('O')

edited Apr 18 '18 at 13:21

answered Nov 08 '16 at 22:06

MaxU - stand with Ukraine

205,989
36
386
419

2

I've neglected `pd.PeriodIndex`. I shan't any more. – piRSquared Nov 08 '16 at 22:51
@piRSquared, yes, it's pretty convenient – MaxU - stand with Ukraine Nov 08 '16 at 22:52
Unfortunately `pd.PeriodIndex` cause the column names to become in format `pandas.tseries.period.PeriodIndex`. How can I avoid this because I need "2000Q3" as a string? – Seymour Apr 18 '18 at 13:06
I decided to comment because the expected answer of the OP was a dataframe with column like `1996q2 ` and not `1996Q2 `. Therefore, I am trying to obtain the expected answer of the OP – Seymour Apr 18 '18 at 13:17
1

@MaxU I remember doing a problem on Coursera dealing with just this issue and I struggled. I wish I had seen this method back then. +1 – Scott Boston Apr 18 '18 at 13:27
@ScottBoston, thank you! What a coincidence! I'm answering Coursera's quiz at this particular moment... :-) – MaxU - stand with Ukraine Apr 18 '18 at 13:30

Sayyor Y · Answer 2 · 2021-12-13T10:10:57.957

0

Just to add to @MaxU's answer above, to convert the resulting PeriodIndex columns back to str and add spaces between year and quarter number (i.e. 1999 Q1, not 1999Q1 ), you can do this:

res = res.columns.to_series().astype(str)

edited Dec 13 '21 at 10:10

answered Dec 13 '21 at 08:52

Sayyor Y

1,130
2
14
27

how to convert monthly data to quarterly in pandas

2 Answers2

Linked