Converting monthly values into daily using pandas interpolation

Question

I have 12 avg monthly values for 1000 columns and I want to convert the data into daily using pandas. I have tried to do it using interplolate but I got the daily values from 31/01/1991 to 31/12/1991, which does not cover the whole year. January month values are not getting. I used date_range for index of my data frame.

date=pd.date_range(start="01/01/1991",end="31/12/1991",freq="M")

upsampled=df.resample("D")
interpolated = upsampled.interpolate(method='linear')

How can I get the interpolated values for 365 days?

Answer 1

Note that interpolation is between the known points.

So to interpolate throughout the whole year, it is not enough to have just 12 values (for each month). You must have 13 values (e.g. for the start of each month and the start of the next year).

Thus I created the source df as:

date = pd.date_range(start='01/01/1991', periods=13, freq='MS')
df = pd.DataFrame({'date': date, 'amount': np.random.randint(100, 200, date.size)})

getting e.g.:

         date  amount
0  1991-01-01     113
1  1991-02-01     164
2  1991-03-01     181
3  1991-04-01     164
4  1991-05-01     155
5  1991-06-01     157
6  1991-07-01     118
7  1991-08-01     133
8  1991-09-01     184
9  1991-10-01     183
10 1991-11-01     159
11 1991-12-01     193
12 1992-01-01     163

​Then to upsample it to daily frequency and interpolate, I ran:

df.set_index('date').resample('D').interpolate()

If you don't want the result to contain the last row (for 1992-01-01), take only a slice of the above result, dropping the last row:

df.set_index('date').resample('D').interpolate()[:-1]