how to convert monthly data to weekly data keeping the other columns constant

user_10523971 0
Viewed 648

I have a data frame as follows.

pd.DataFrame({'Date':['2020-08-01','2020-08-01','2020-09-01'],'value':[10,12,9],'item':['a','d','b']})

I want to convert this to weekly data keeping all the columns apart from the Date column constant.

Expected output

pd.DataFrame({'Date':['2020-08-01','2020-08-08','2020-08-15','2020-08-22','2020-08-29','2020-08-01','2020-08-08','2020-08-15','2020-08-22','2020-08-29','2020-09-01','2020-09-08','2020-09-15','2020-09-22','2020-09-29'],
          'value':[10,10,10,10,10,12,12,12,12,12,9,9,9,9,9],'item':['a','a','a','a','a','d','d','d','d','d','b','b','b','b','b']})

It should be able to convert any month data to weekly data. Date in the input data frame is always the first day of that month. How do I make this happen? Thanks in advance.

pandas datetime python
2 Answers

Since the desired new datetime index is irregular (re-starts at the 1st of each month), an iterative creation of the index is an option:

df = pd.DataFrame({'Date':['2020-08-01','2020-09-01'],'value':[10,9],'item':['a','b']})

df = df.set_index(pd.to_datetime(df['Date'])).drop(columns='Date')

dti = pd.to_datetime([]) # start with an empty datetime index
for month in df.index: # for each month, add a 7-day step datetime index to the previous
    dti = dti.union(pd.date_range(month, month+pd.DateOffset(months=1), freq='7d'))

# just reindex and forward-fill, no resampling needed
df = df.reindex(dti).ffill()

df
            value item
2020-08-01   10.0    a
2020-08-08   10.0    a
2020-08-15   10.0    a
2020-08-22   10.0    a
2020-08-29   10.0    a
2020-09-01    9.0    b
2020-09-08    9.0    b
2020-09-15    9.0    b
2020-09-22    9.0    b
2020-09-29    9.0    b
user_101974180

I added one more date to your data and then used resample:

df = pd.DataFrame({'Date':['2020-08-01', '2020-09-01'],'value':[10, 9],'item':['a', 'b']})

df['Date'] = pd.to_datetime(df['Date'])
df.set_index('Date', inplace=True)
df = df.resample('W').ffill().reset_index()
print(df)


        Date  value item
0 2020-08-02     10    a
1 2020-08-09     10    a
2 2020-08-16     10    a
3 2020-08-23     10    a
4 2020-08-30     10    a
5 2020-09-06      9    b
user_61683230
Related