convert quarterly data to monthly in python pandas by dividing the variable by 3

user_17005344 0
Viewed 263

I have a data frame with quarterly data like this.

Quarter     value
3/31/2014   10500
6/30/2014   10800
9/30/2014   11690
12/31/2014  14200

I want to convert the quarterly to monthly such that the quarter vale is divided by 3 for the 3 months and so on. [Expected output]

Month       value
1/31/2014   3500
2/28/2014   3500
3/31/2014   3500
4/30/2014   3600
5/31/2014   3600
6/30/2014   3600
7/31/2014   3896.66
8/31/2014   3896.66
9/30/2014   3896.66
10/31/2014  4733.33
11/30/2014  4733.33
12/31/2014  4733.33

How do I divide a quarter value and assign it to 3 months equally? the df.resample('M').interpolate() didn't work

pandas data-manipulation python
1 Answers

Convert datetimes to months periods, then repeat each row by 3 and subtract by counter with GroupBy.cumcount, last convert periods to datetimes by DataFrame.to_timestamp and remove times by Series.dt.normalize:

df['Quarter'] = pd.to_datetime(df['Quarter']).dt.to_period('m')
df['value'] /=  3

df = df.iloc[df.index.repeat(3)]

df['Quarter'] = (df['Quarter'].sub(df.groupby(level=0)
                                    .cumcount(ascending=False))
                   .dt.to_timestamp(how='e').dt.normalize())

df = df.reset_index(drop=True)
print (df)
      Quarter        value
0  2014-01-31  3500.000000
1  2014-02-28  3500.000000
2  2014-03-31  3500.000000
3  2014-04-30  3600.000000
4  2014-05-31  3600.000000
5  2014-06-30  3600.000000
6  2014-07-31  3896.666667
7  2014-08-31  3896.666667
8  2014-09-30  3896.666667
9  2014-10-31  4733.333333
10 2014-11-30  4733.333333
11 2014-12-31  4733.333333
user_29010020
Related