Pandas: Convert Quarterly Data into Monthly Data

user_8309944 0
Viewed 4250

I have some quarterly data that I need to convert to monthly in order to work with another data set. The data looks like this:

Date Value
1/1/2010 100
4/1/2010 130
7/1/2010 160

What I need to do is impute the values for the missing months so that it looks like this:

Date Value
1/1/2010 100
2/1/2010 110
3/1/2010 120
4/1/2010 130
5/1/2010 140
6/1/2010 150
7/1/2010 160

Couldn't find many previous questions on how to do this. Only the reverse (monthly to quarterly). I tried one of those methodologies in reverse, but it didn't work:

pd.PeriodIndex(df.Date, freq='M')

What would be the easiest way to go about doing this in Pandas?

pandas time-series datetime python
1 Answers

You can use resample:

# convert to period
df['Date'] = pd.to_datetime(df['Date']).dt.to_period('M')

# set Date as index and resample
df.set_index('Date').resample('M').interpolate()

Output:

         Value
Date          
2010-01  100.0
2010-02  110.0
2010-03  120.0
2010-04  130.0
2010-05  140.0
2010-06  150.0
2010-07  160.0
user_42384080
Related