How can I filter values in a dataframe that are not in DD/MM/YYYY format

user_19428094 0
Viewed 26

At the moment i'm trying to write some code that will scan through a dataframe and find any values that are not in valid DD/MM/YYYY format and export this data into a separate dataframe. For example:

Incident Ref              User    Priority level Date raised Date Resolved                                                                                                                                      
38103             Bruce Banner    Priority 2     07/05/2022  08/05/2022      
35210             Thor Odinson    Priority 1     02/05/2022  04/05/2022    
10491               Tony Stark    Priority 1     29/04/2022  29/04/2022  
48109                Nick Fury    Priority 3     abc         20/05/2022
58391         Natasha Romanoff    Priority 2     31/02/2021  01/03/2022

Within this dataframe, the last two entries are invalid, one because it is in the wrong format, and one because it is out of range. I want the code to filter through the dataframe and split it into two separate dataframes, one with correct values and one that includes the erroneous data as follows:

Incident Ref              User    Priority level Date raised Date Resolved                                                                                                                                       
48109                Nick Fury    Priority 3     abc         20/05/2022
58391         Natasha Romanoff    Priority 2     31/02/2021  01/03/2022

I've tried the following:

df['Date raised'] = pd.to_datetime(df['Date raised'], format='%Y%m%d', errors='coerce')

However this just removes the removes the erroneous entries and doesn't preserve them for use in another dataframe.

Is there a way to do this?

Thanks!

pandas dataframe python
1 Answers

You can chain conditions by | for bitwise OR with test if missing values after converting to datetimes, here is used %d/%m/%Y for match format DD/MM/YYYY:

m1  = pd.to_datetime(df['Date raised'], format='%d/%m/%Y', errors='coerce').isna()
m2  = pd.to_datetime(df['Date Resolved'], format='%d/%m/%Y', errors='coerce').isna()

df = df[m1 | m2]
print (df)
   Incident Ref              User  Priority  level Date raised Date Resolved
3         48109         Nick Fury  Priority      3         abc    20/05/2022
4         58391  Natasha Romanoff  Priority      2  31/02/2021    01/03/2022

Or is possible create list of masks and then chain them with:

cols = ['Date raised','Date Resolved']
masks  = [pd.to_datetime(df[x], format='%d/%m/%Y', errors='coerce').isna() for x in cols]
df = df[np.logical_or.reduce(masks)]

Another possible solution is remove format parameter, but e.g. 2021 parse like valid datetime, because converting to 2021-01-01:

cols = ['Date raised','Date Resolved']
masks  = [pd.to_datetime(df[x], errors='coerce').isna() for x in cols]
df = df[np.logical_or.reduce(masks)]
print (df)
   Incident Ref              User  Priority  level Date raised Date Resolved
3         48109         Nick Fury  Priority      3         abc    20/05/2022
4         58391  Natasha Romanoff  Priority      2  31/02/2021    01/03/2022
user_29010020
Related