Add missing dates do datetime column in Pandas using last value

Question

I've already checked out Add missing dates to pandas dataframe, but I don't want to fill in the new dates with a generic value.

My dataframe looks more or less like this:

date (dd/mm/yyyy)	value
01/01/2000	a
02/01/2000	b
03/01/2000	c
06/01/2000	d

So in this example, days 04/01/2000 and 05/01/2000 are missing. What I want to do is to insert them before the 6th, with a value of c, the last value before the missing days. So the "correct" df should look like:

date (dd/mm/yyyy)	value
01/01/2000	a
02/01/2000	b
03/01/2000	c
04/01/2000	c
05/01/2000	c
06/01/2000	d

There are multiple instances of missing dates, and it's a large df (~9000 rows).

Thanks for your time! :)

ziying35 · Accepted Answer · 2022-06-14T02:08:31.217

2

try this:

# If your date format is dayfirst, then use the following code

df['date (dd/mm/yyyy)'] = pd.to_datetime(df['date (dd/mm/yyyy)'], dayfirst=True)
out = df.set_index('date (dd/mm/yyyy)').asfreq('D', method='ffill').reset_index()
print(out)

edited Jun 14 '22 at 02:08

answered Jun 14 '22 at 00:49

ziying35

1,190
3
6

Returned an empty df to me :/ – Gabriel Tkacz Jun 14 '22 at 00:59
It should not return an empty df, I tested it before posting it. – ziying35 Jun 14 '22 at 01:04
https://prnt.sc/tcMmRGTh2Lqi don't know what's wrong. ("data" is Portuguese for "date") – Gabriel Tkacz Jun 14 '22 at 01:29
I didn't notice the note in your field, I've updated the code – ziying35 Jun 14 '22 at 01:44

score 0 · Answer 2 · answered Jun 14 '22 at 00:45

Assuming that your dates are drawn at a regular frequency, you can generate a pd.DateIndex with date_range, filter those which are not in your date column, crate a dataframe to concatenate with nan in the value column and fillna using the back or forward fill method.


# assuming your dataframe is df:

all_dates = pd.date_range(start=df.date.min(), end=df.date.max(), freq='M')
known_dates = set(df.date.to_list()) # set is blazing fast on `in` compared with a list.
unknown_dates = all_dates[~all_dates.isin(known_dates)]
df2 = pd.DateFrame({'date': unknown_dates})
df2['value'] = np.nan
df = pd.concat([df, df2])
df = df.sort_values('value').fillna(method='ffill')

Dates are day by day, but this code threw: "AttributeError: 'DataFrame' object has no attribute 'date'" — Gabriel Tkacz, Jun 14 '22 at 01:03

Add missing dates do datetime column in Pandas using last value

2 Answers2