Change monthly data to daily in pandas

Question

I have a dataframe where the date is the first day of the month

date           spend      category
1/Feb/2018     565          Red
1/Dec/2019     674          Blue
1/Feb/2019     878          Green

I'd like to create extra rows in the dataframe to include every day in the month, whilst duplicating the other data

date           spend      category
1/Feb/2018     565          Red
2/Feb/2018     565          Red
.
.
.
27/Feb/2018     565          Red
28/Feb/2018     565          Red
1/Dec/2019     674          Blue
2/Dec/2019     674          Blue
.
.
.
30/Dec/2019     674          Blue
31/Dec/2019     674          Blue
1/Feb/2019     878          Green
2/Feb/2019     878          Green
.
.    
27/Feb/2019     878          Green
28/Feb/2019     878          Green

I have a long way of doing this but is there a quick couple of lines that can achieve the same thing?

thanks alot,

Does this answer your question? [Convert daily data in pandas dataframe to monthly data](https://stackoverflow.com/questions/50997339/convert-daily-data-in-pandas-dataframe-to-monthly-data) — NStavrakoudis, Mar 23 '21 at 08:29

jezrael · Accepted Answer · 2021-03-23T08:55:27.353

Use resample by months periods by Series.dt.to_period with Resampler.ffill, last add DataFrame.to_timestamp for DatetimeIndex:

df['date'] = pd.to_datetime(df['date']).dt.to_period('m')
df = df.set_index('date').resample('d').ffill().to_timestamp()
print (df)
            spend category
date                      
2018-02-01    565      Red
2018-02-02    565      Red
2018-02-03    565      Red
2018-02-04    565      Red
2018-02-05    565      Red
          ...      ...
2019-12-27    674     Blue
2019-12-28    674     Blue
2019-12-29    674     Blue
2019-12-30    674     Blue
2019-12-31    674     Blue

[699 rows x 2 columns]

Change monthly data to daily in pandas

1 Answers1