How do I forward fill a date index with duplicate period values?

Question

For example, if my series is:

date    data
2019-01 95972.0
2019-02 95951.0

How would I transform it to:

date    data
1/7/19  95972.0
1/14/19 95972.0
1/21/19 95972.0
1/28/19 95972.0
2/4/19  95951.0
2/11/19 95951.0
2/18/19 95951.0
2/25/19 95951.0

For the first series index is dtype of period while the second one is datetime64

So you second one have the date column only and you need the data ? — BENY, Sep 09 '20 at 21:21
@BEN_YO i'm trying to align the data with another time series that has that datetime index — Maxim, Sep 09 '20 at 21:29

David Erickson · Accepted Answer · 2020-09-11T00:27:01.620

I have kept your initial input dataframe as d and created a new dataframe df. The key idea is that you need to have a common month column in both dataframes; and, after you .resample('W') the dataframe, you can merge back in the data values from the initial dataframe.

If the data in your first dataframe is a dtype of period, then you can reference this thread on how to make that a string, but once you do that the below code should work flawlessly: Python/Pandas - Convert type from pandas period to string

import numpy as np
import pandas as pd
d = pd.read_clipboard().rename({'date' : 'month'}, axis=1)
d

Out[1]: 
    month     data
0 2019-01  95972.0
1 2019-02  95951.0

df = d.copy()
df['date'] = pd.to_datetime(df['month'])
df['date'] = np.where((df['date'] == max(df['date'])), df['date'] + pd.tseries.offsets.MonthEnd(), df['date'])
df = (df.set_index('date')
        .resample('W')
        .asfreq()
        .reset_index()
        .drop('data', axis=1))
df['month'] = df['date'].dt.strftime('%Y-%m')
df['date'] = df['date'] + pd.tseries.offsets.Day()
df = pd.merge(df,d).drop('month', axis=1)
df

Out[2]: 
        date     data
0 2019-01-07  95972.0
1 2019-01-14  95972.0
2 2019-01-21  95972.0
3 2019-01-28  95972.0
4 2019-02-04  95951.0
5 2019-02-11  95951.0
6 2019-02-18  95951.0
7 2019-02-25  95951.0

thanks for the reply. this was helpful, but a bit convoluted with all the offsets. I think my main takeaway was the `resample` method. Also, I assume `cols` part is a typo? — Maxim, Sep 11 '20 at 00:01
@Maksim yes, I removed cols. The offsets are required to match the exact output you are seeking in this example. You may not need it in your real dataframe. Please accept if it helped you. Thank you! — David Erickson, Sep 11 '20 at 00:28

How do I forward fill a date index with duplicate period values?

1 Answers1