return missing dates Python

Question

I have a CSV file with 1600 dates and I'm trying to find all missing dates. For example:
03-10-2019
01-10-2019
29-09-2019
28-09-2019
should return : 02-10-2019,30-09-2019.

Here's what I've wrote:

with open('measurements.csv','r') as csvfile:
df = pd.read_csv(csvfile,  delimiter=',')

timestamps = df['observation_time'] #Getting only the date

for line in timestamps:
date_str = line
try: # convert string to time
    date = date_time_obj = datetime.datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
    dates.append(date) 
except:
    print("Date parsing failed")

dates = pd.DataFrame(dates,columns =['actual_date']) 

pd.date_range(start = dates.min(), end = dates.max()).difference(dates.index)

This returns an error that

"Cannot convert input [actual_date 2018-09-17 22:00:00 dtype: datetime64[ns]] of type to Timestamp"

jezrael · Accepted Answer · 2019-10-30 07:53:39Z

1

Idea is use DataFrame.asfreq for add all missing values to DatetimeIndex, so possible filter by boolean indexing with Series.isna:

df['observation_time'] = pd.to_datetime(df['observation_time'], dayfirst=True)
df1 = df.set_index(df['observation_time']).sort_index().asfreq('d')
print (df1)
                 observation_time
observation_time                 
2019-09-28             2019-09-28
2019-09-29             2019-09-29
2019-09-30                    NaT
2019-10-01             2019-10-01
2019-10-02                    NaT
2019-10-03             2019-10-03

dates = df1.index[df1['observation_time'].isna()]
print (dates )
DatetimeIndex(['2019-09-30', '2019-10-02'], dtype='datetime64[ns]', 
name='observation_time', freq=None)

edited Oct 30, 2019 at 7:53

answered Oct 30, 2019 at 7:35

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

12major e Over a year ago

thanks for the quick answer, I see get an error when trying executing the code: ValueError: cannot reindex from a duplicate axis

jezrael Over a year ago

@12majore - Can you test if change df1 = df.set_index(df['observation_time']).sort_index().asfreq('d') to df1 = df.set_index(df['observation_time']).drop_duplicates().sort_index().asfreq('d') ?

Collectives™ on Stack Overflow

return missing dates Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related