fill in missing dates, from weekly to daily using python/pandas

user_8378431 0
Viewed 736

I have this dataframe and I would like to make weekly data just repeat for daily until the next week

Input

Week          Netflix: (Worldwide)
2012-12-02    50
2012-12-09    51

Output

Week        Netflix: (Worldwide)
2012-12-02  50
2012-12-03  50
2012-12-04  50
2012-12-05  50
2012-12-06  50
2012-12-07  50
2012-12-08  50
2012-12-09  51
pandas python
2 Answers

Use resample():

In [129]: df.set_index('Week').resample('1D').ffill().reset_index()
Out[129]:
        Week  Netflix: (Worldwide)
0 2012-12-02                    50
1 2012-12-03                    50
2 2012-12-04                    50
3 2012-12-05                    50
4 2012-12-06                    50
5 2012-12-07                    50
6 2012-12-08                    50
7 2012-12-09                    51

or alternative solution from @Wen:

In [147]: df.set_index('Week').asfreq('D').ffill().reset_index()
Out[147]:
        Week  Netflix: (Worldwide)
0 2012-12-02                  50.0
1 2012-12-03                  50.0
2 2012-12-04                  50.0
3 2012-12-05                  50.0
4 2012-12-06                  50.0
5 2012-12-07                  50.0
6 2012-12-08                  50.0
7 2012-12-09                  51.0
user_57412050

Here is a complete example - 

import pandas as pd

df = pd.DataFrame( [ ['2012-12-02', 50], ['2012-12-09', 51]], columns=['Week', 'Netflix: (Worldwide)'])
df['Week'] = pd.to_datetime(df['Week'])

min_date = df['Week'].min()
max_date = df['Week'].max()

idx = pd.date_range(min_date, max_date)
df = df.set_index('Week')
df = df.reindex(idx)
df = df.fillna(method='ffill')
df = df.reset_index(drop=True)
df.rename(columns={'index':'Week'}, inplace=True)
print(df)

Output - 

   Week                Netflix: (Worldwide)
0 2012-12-02                  50.0
1 2012-12-03                  50.0
2 2012-12-04                  50.0
3 2012-12-05                  50.0
4 2012-12-06                  50.0
5 2012-12-07                  50.0
6 2012-12-08                  50.0
7 2012-12-09                  51.0
user_40980130
Related