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? I guess we will have to use Multi-Index for this?. Is there any better way? I really appreciate any help you can provide.
It seems like you just need to repeat your data 4 times and adjust the date properly:
df['Date'] = pd.to_datetime(df['Date'])
(df.loc[df.index.repeat(4)]
.assign(Date=lambda x: x['Date'] + pd.to_timedelta(np.tile(np.arange(4),len(df)),
unit='W') )
)
Output:
Date value item
0 2020-08-01 10 a
0 2020-08-08 10 a
0 2020-08-15 10 a
0 2020-08-22 10 a
1 2020-08-01 12 d
1 2020-08-08 12 d
1 2020-08-15 12 d
1 2020-08-22 12 d
2 2020-09-01 9 b
2 2020-09-08 9 b
2 2020-09-15 9 b
2 2020-09-22 9 b