CODEWITHSUNDEEP
pythonpandas
Dnaiel
Dnaiel

Reputation: 7832

convert month-end, or week-end series to daily series

Let us say I have a pandas series like this:

monthly = 
2017-05-31    403.43
2017-06-30    292.92
2017-07-31    184.62

Where the index is of type 'datetime64[ns]'.

I'd want to convert it into a daily series where the value each day is just the same value as the month, i.e., 

daily_imputed = 
2017-05-01   403.43
2017-05-02   403.43
2017-05-03   403.43
2017-05-04   403.43
....
2017-07-29   184.62
2017-07-30   184.62
2017-07-31   184.62

What'd be a good way to achieve this?

Also let us say I have a bi-weekly series,

biweekly = 
2017-06-26    151.6
2017-07-10    204.7
2017-07-24    119.2

So now I'd want to also convert it to daily where each biweekly value get's imputed as the daily value:

daily_imputed = 
2017-06-13    151.6
2017-06-14    151.6
....
2017-07-23    119.2
2017-07-24    119.2

It is the same logic as the monthly to daily just with a 13-day lag from last day. That is why 2017-06-26 starts at 2017-06-13 in the daily imputed data.

Upvotes: 2

Views: 336

Answers (1)

Steven G
Steven G

Reputation: 17122

I would use resample, only problem is that it your first index wont start at the first day of the first month, but you could reindex

df.resample('D').first().bfill()

you could reindex like so:

import datetime as dt
start = df.index[0]
end = df.index[-1]
d_range = pd.date_range(dt.date(start.year, start.month, 1), end)
df.reindex(d_range).bfill()

same process with your bi-weekly df should work.

edit: now that I am thinking about it, you could just reindex the entire dataframe with the new date range and backfill / forward fill it without using resample!

Upvotes: 3

Related Questions