Upsample data and interpolate

Question

I have the following dataframe:

Month   Col_1    Col_2

1       0,121    0,123
2       0,231    0,356
3       0,150    0,156
4       0,264    0,426
...

I need to resample this to weekly resolution and to interpolate between the points. The latter part, the interpolation is straight-forward. The reindex part is a bit tricky, on the other hand, at least for me.

If I use the DataFrame.reindex() method, it will only erase all the entries from the dataframe. I have tried to do it manually, by using .loc() to create new 'NaN' entries between each consecutive months, but this method overwrites the entries I already have.

Any clue how to do it? Thanks!

Answer 1

I have to assume a start date, I chose 2009-12-31.

To get resample to work, you need a pd.DateTimeIndex.

start_date = pd.to_datetime('2009-12-31')
df.Month = df.Month.apply(lambda x: start_date + pd.offsets.MonthEnd(x))
df = df.set_index('Month')

df.resample('W').interpolate()

---

Replicable code

from StringIO import StringIO
import pandas as pd

text = """Month   Col_1    Col_2
1       0,121    0,123
2       0,231    0,356
3       0,150    0,156
4       0,264    0,426"""

df = pd.read_csv(StringIO(text), decimal=',', delim_whitespace=True)

start_date = pd.to_datetime('2009-12-31')
df.Month = df.Month.apply(lambda x: start_date + pd.offsets.MonthEnd(x))
df = df.set_index('Month')

df.resample('W').interpolate()