pandas fill missing dates in time series

Question

I have a dataframe which has aggregated data for some days. I want to add in the missing days

I was following another post, Add missing dates to pandas dataframe, unfortunately, it overwrote my results (maybe functionality was changed slightly?)... the code is below

import random
import datetime as dt
import numpy as np
import pandas as pd

def generate_row(year, month, day):
    while True:
        date = dt.datetime(year=year, month=month, day=day)
        data = np.random.random(size=4)
        yield [date] + list(data)

# days I have data for
dates = [(2000, 1, 1), (2000, 1, 2), (2000, 2, 4)]
generators = [generate_row(*date) for date in dates]

# get 5 data points for each
data = [next(generator) for generator in generators for _ in range(5)]

df = pd.DataFrame(data, columns=['date'] + ['f'+str(i) for i in range(1,5)])

# df
groupby_day = df.groupby(pd.PeriodIndex(data=df.date, freq='D'))
results = groupby_day.sum()

idx = pd.date_range(min(df.date), max(df.date))
results.reindex(idx, fill_value=0)

Results before filling in missing date indices

Results after

It looks promising, but I'm struggling to apply it from the docs — Alter, Nov 10 '17 at 21:51
I think I got it... `df.set_index(df.date, inplace=True)` + `df = df.resample('D').sum()` That is very convenient — Alter, Nov 10 '17 at 21:54
Exactly. If it works, write it as an answer and I'll pass you an upvote. — cs95, Nov 10 '17 at 21:56

score 48 · Accepted Answer · answered Nov 10 '17 at 21:40

You need to use period_range rather than date_range:

In [11]: idx = pd.period_range(min(df.date), max(df.date))
    ...: results.reindex(idx, fill_value=0)
    ...:
Out[11]:
                  f1        f2        f3        f4
2000-01-01  2.049157  1.962635  2.756154  2.224751
2000-01-02  2.675899  2.587217  1.540823  1.606150
2000-01-03  0.000000  0.000000  0.000000  0.000000
2000-01-04  0.000000  0.000000  0.000000  0.000000
2000-01-05  0.000000  0.000000  0.000000  0.000000
2000-01-06  0.000000  0.000000  0.000000  0.000000
2000-01-07  0.000000  0.000000  0.000000  0.000000
2000-01-08  0.000000  0.000000  0.000000  0.000000
2000-01-09  0.000000  0.000000  0.000000  0.000000
2000-01-10  0.000000  0.000000  0.000000  0.000000
2000-01-11  0.000000  0.000000  0.000000  0.000000
2000-01-12  0.000000  0.000000  0.000000  0.000000
2000-01-13  0.000000  0.000000  0.000000  0.000000
2000-01-14  0.000000  0.000000  0.000000  0.000000
2000-01-15  0.000000  0.000000  0.000000  0.000000
2000-01-16  0.000000  0.000000  0.000000  0.000000
2000-01-17  0.000000  0.000000  0.000000  0.000000
2000-01-18  0.000000  0.000000  0.000000  0.000000
2000-01-19  0.000000  0.000000  0.000000  0.000000
2000-01-20  0.000000  0.000000  0.000000  0.000000
2000-01-21  0.000000  0.000000  0.000000  0.000000
2000-01-22  0.000000  0.000000  0.000000  0.000000
2000-01-23  0.000000  0.000000  0.000000  0.000000
2000-01-24  0.000000  0.000000  0.000000  0.000000
2000-01-25  0.000000  0.000000  0.000000  0.000000
2000-01-26  0.000000  0.000000  0.000000  0.000000
2000-01-27  0.000000  0.000000  0.000000  0.000000
2000-01-28  0.000000  0.000000  0.000000  0.000000
2000-01-29  0.000000  0.000000  0.000000  0.000000
2000-01-30  0.000000  0.000000  0.000000  0.000000
2000-01-31  0.000000  0.000000  0.000000  0.000000
2000-02-01  0.000000  0.000000  0.000000  0.000000
2000-02-02  0.000000  0.000000  0.000000  0.000000
2000-02-03  0.000000  0.000000  0.000000  0.000000
2000-02-04  1.856158  2.892620  2.986166  2.793448

This is because your groupby uses PeriodIndex, rather than datetime:

df.groupby(pd.PeriodIndex(data=df.date, freq='D'))

You could have instead used a pd.Grouper:

df.groupby(pd.Grouper(key="date", freq='D'))

which would have give a datetime index.

score 22 · Answer 2 · edited Nov 10 '17 at 22:16

22

From cᴏʟᴅsᴘᴇᴇᴅ's hints in the comments:

resample fits well here.

Resample: Convenience method for frequency conversion and resampling of time series. Object must have a datetime-like index (DatetimeIndex, PeriodIndex, or TimedeltaIndex), or pass datetime-like values to the on or level keyword.

import random
import datetime as dt
import numpy as np
import pandas as pd

def generate_row(year, month, day):
    while True:
        date = dt.datetime(year=year, month=month, day=day)
        data = np.random.random(size=4)
        yield [date] + list(data)

# days I have data for
dates = [(2000, 1, 1), (2000, 1, 2), (2000, 2, 4)]
generators = [generate_row(*date) for date in dates]

# get 5 points for each
data = [next(generator) for generator in generators for _ in range(5)]

# make dataframe
df = pd.DataFrame(data, columns=['date'] + ['f'+str(i) for i in range(1,5)])

# using the resample method
df.set_index(df.date, inplace=True)
df = df.resample('D').sum().fillna(0)

edited Nov 10 '17 at 22:16

cs95

379,657
97
704
746

answered Nov 10 '17 at 22:07

Alter

3,332
4
31
56

2

You have some fancy editing skills, I didn't even know you could link to a comment – Alter Nov 10 '17 at 22:18
2

Thank you... thought it would be more apt to link to the comment than my profile ;-) – cs95 Nov 10 '17 at 22:21
2

Is it possible to do the exact thing as above but not sum the data, keep the data as is for those dates where there is more than one data point? For eg have 5 data points for 1st Jan and 5 for 2nd Jan but then add 3rd as just 0? – spiff Sep 11 '18 at 02:52
1

never mind my question, its as simple as an outer merge with a dates series (made into a DataFrame) - thanks vm! – spiff Sep 11 '18 at 03:24

Nidhi Sharma · Answer 3 · 2022-04-28T21:09:38.990

you can refer below code link for filling missing dates in timeseries data and to find out missing dates, you can refer below code.

** code tested on YYYY-MM-DD format. Check the link below for complete code

#fill missing dates in dataframe and return dataframe object
# tested on only YYYY-MM-DD format
# ds=fill_in_missing_dates(ds,date_col_name='Date')
# ds= dataframe object
# date_col_name= col name in your dataframe, has datevalue
def fill_in_missing_dates(df, date_col_name = 'date',fill_val = 
np.nan,date_format='%Y-%m-%d'):
  df.set_index(date_col_name,drop=True,inplace=True)
  df.index = pd.to_datetime(df.index, format = date_format)
  idx = pd.date_range(df.index.min(), df.index.max())
  print('missing_dates are',idx.difference(df.index))
  df=df.reindex(idx,fill_value=fill_val)...

https://github.com/n-idhisharma/mywork/blob/09942f15f6859e94e5dbb9fcb1af05ac7f627b06/Py_filling_missing_dates

pandas fill missing dates in time series

3 Answers3

Linked