How to explode week period into multiple rows in a dataframe

userl0AC 0
Viewed 52

I have a pandas df that was created using groupby(df['date'].dt.to_period('W-SAT')).agg({'price': 'mean'}). Now I want to expand that table so that each date gets a row that includes that week's mean. How do I go about that? Here is an example

date                    avg_price
2020-02-09/2020-02-15   6.438312    
2020-02-16/2020-02-22   3.477643    
2020-02-23/2020-02-29   8.784417    
2020-03-01/2020-03-07   12.897191

and I want it to look like

date                    avg_price
2020-02-09              6.438312    
2020-02-10              6.438312    
2020-02-11              6.438312    
2020-02-12              6.438312

Thanks!

datetime dataframe pandas python
1 Answers

First, get timestamp representation of date:

df['date'] =  df.date.dt.to_timestamp('D')

which will return:

        date    avg_price
0   2020-02-09  6.438312
1   2020-02-16  3.477643
2   2020-02-23  8.784417
3   2020-03-01  12.897191

Then set date as index, use asfreq with daily frequency and forward fill the null values:

df.set_index('date').asfreq('D').ffill().reset_index()

and you have:

        date    avg_price
0   2020-02-09  6.438312
1   2020-02-10  6.438312
2   2020-02-11  6.438312
3   2020-02-12  6.438312
4   2020-02-13  6.438312
5   2020-02-14  6.438312
6   2020-02-15  6.438312
7   2020-02-16  3.477643
8   2020-02-17  3.477643
9   2020-02-18  3.477643
10  2020-02-19  3.477643
11  2020-02-20  3.477643
12  2020-02-21  3.477643
13  2020-02-22  3.477643
14  2020-02-23  8.784417
15  2020-02-24  8.784417
16  2020-02-25  8.784417
17  2020-02-26  8.784417
18  2020-02-27  8.784417
19  2020-02-28  8.784417
20  2020-02-29  8.784417
21  2020-03-01  12.897191
Nuri Taş0
Related