I have a dataframe with some yearly data in it that I want to extrapolate to monthly. I had a process that ran in SAS, which I am moving to Python3, that did this. It took the yearly value and used it as the average for the full year, spreading out the monthly values (with month 6 basically being the value).
Here is the SAS code:
proc expand data = interpolation out = interpolate1 from = year to =month;
id MDY;
convert WW_UnempRateFC / observed = average;
convert US_gas/observed = average;
run;
Below is the starting data.
Year RW_UnempFC Usgas
0 2011 7.49 3.40
1 2012 7.30 3.54
2 2013 7.10 3.33
3 2014 7.00 2.90
4 2015 6.90 2.21
Here is the SAS output for one year:
JAN2011 7.5054662504 3.1396263397
FEB2011 7.5116185928 3.2054411594
MAR2011 7.5147516515 3.2648661888
APR2011 7.515085202 3.3200225579
MAY2011 7.512602436 3.3689424153
JUN2011 7.5075223604 3.4119533453
JUL2011 7.5000068984 3.4492530654
AUG2011 7.4900880953 3.4816285652
SEP2011 7.4782763777 3.5082981244
OCT2011 7.464603791 3.5300487326
NOV2011 7.4492789568 3.5471936524
DEC2011 7.4324741835 3.5599449231
Here is what I have so far in Python. I've created the datetime column and moved it to the index for the .interpolate to work. However, no matter which method I try (linear,spline, etc) I can't get the same output.
interpolation['Date'] = interpolation['Year'].astype(str).apply(lambda x:
pd.to_datetime(x, format='%Y'))
interpolation = interpolation.set_index(['Date'])
interpolationexp = interpolation.resample('M').mean().reset_index()
interpolationexp = interpolationexp.interpolate()
This second part is some failed attempts to manipulate the data to get something different. Like moving the value to a specific month and expanding it linearly from there.
interpolationexp['Year'] = interpolationexp['Year'].apply(np.floor)
interpolation2 = interpolationexp.merge(interpolation, on='Year')
interpolation2['Usgas_y'] = np.where(interpolation2['Date'].month==6,
interpolation2['Usgas_y'], '')
interpolation2.head(20)
I really can't find anything else online on how to extrapolate numbers similar to that SAS method, please help!