regex for keeping correct date and time values

user_6852544 0
Viewed 43

I have two df columns with strings like this:

**date**        **time**
12JUN19         0900
34JUN19         1095
101jun19        11145
01jun19         2559

I want to exclude all dates and times where the value does not match the DDMMMYY and HM structures and I also want to filter out values which exceed the possible day, Hour and minute values. I filter for date and time structures like this:

df['date'] = df['date'].str.extract('(\d{2}[a-zA-Z]{3}[0-9]{2}$)', expand=False)
df['time'] = df['time'].str.extract('^(\d{4})$', expand=False)

The result for our data looks like this:

**date**        **time**
12JUN19         0900
34JUN19         1095
NaN             NaN
01jun19         2559

I now also want to exclude dates where day part of the string is greater than 31 and for time the hour part exceeds 24 or minute part exceeds 59. I have seperate functions to correct these values. Right now I just want to replace the incorrect values with NaN. Would appreciate some help with that, the desired output should be like:

**date**        **time**
12JUN19         0900
NaN             NaN
NaN             NaN
01jun19         NaN

Edit: I do not want to convert date and time to data and time dtypes here because later routines for correcting values expect strings, Thanks.

pandas dataframe python
1 Answers

You can use pd.to_datetime with errors='coerce' option:

df.loc[pd.to_datetime(df['date'], errors='coerce').isna(),
       'date'] = np.nan

df.loc[pd.to_datetime(df['time'], format='%H%M', errors='coerce').isna(),
       'time'] = np.nan

Output:

      date  time
0  12JUN19  0900
1      NaN   NaN
2      NaN   NaN
3  01jun19   NaN
user_42384080
Related