Validate dataframe dates, return non-matching values-CodePudding

This is the workflow I need to accomplish:

Validate the date format of all dates in column_1 and column_2.
If date is not in either format: mm/dd/yy hh:mm or mm/dd/yyyy hh:mm
Need assistance - Print the non-matching values.

Note: I do not know what format the dates will be in and some will not be dates at all.

Sample input data CSV:

column_1    column_2
8/22/22 15:27   8/24/22 15:27
8/23/22 15:27   Tuesday, August 23, 2022
8/24/22 15:27   abc123
8/25/22 15:27   8/25/2022 15:27
8/26/22 15:27   8/26/2022 18:27
8/26/22 15:27   8/22/22

The following method always throws an exception, as designed, when the to_datetime() function returns a ValueError. How can I validate the date and then capture the values that do not match format_one or format_two?

df = pd.read_csv('input.csv', encoding='ISO-8859-1', dtype=str)

date_columns = ['column_1', 'column_2']

format_one = '%m/%d/%y %H:%M'
format_two = '%m/%d/%Y %H:%M'

for column in date_columns:
    for item in df[column]:
        try:
            if pd.to_datetime(df[item], format=format_one):
                print('format 1: '   item)
            elif pd.to_datetime(df[item], format=format_two):
                print('format 2: '   item)   
            else:
                print('unknown format: '   item)
        except Exception as e:
            print('Exception:' )
            print(e)

Output:

Exception:
'8/22/22 15:27'
Exception:
'8/23/22 15:27'
Exception:
'8/24/22 15:27'
Exception:
'8/25/22 15:27'
Exception:
'8/26/22 15:27'
Exception:
'8/26/22 15:27'
Exception:
'8/24/22 15:27'
Exception:
'Tuesday, August 23, 2022'
Exception:
'abc123'
Exception:
'8/25/2022 15:27'
Exception:
'8/26/2022 18:27'
Exception:
'8/22/22'

Desired output:

Exception:
'Tuesday, August 23, 2022'
Exception:
'abc123'
Exception:
'8/22/22'

Thank you.

CodePudding user response：

You'll need to test each allowed format individually. Here's one way:

import pandas as pd

allowed = ('%m/%d/%y %H:%M', '%m/%d/%Y %H:%M')

# dummy df
df = pd.DataFrame({"date": ["8/24/22 15:27", "Tuesday, August 23, 2022",
                            "abc123", "8/25/2022 15:27"]})

# this will be our mask, where the input format is invalid.
# initially, assume all invalid.
m = pd.Series([True]*df["date"].size)

# for each allowed format, test where the result is not NaT, i.e. valid.
# update the mask accordingly.
for fmt in allowed:
    m[pd.to_datetime(df["date"], format=fmt, errors="coerce").notna()] = False
    
# invalid format:
print(df["date"][m])
# 1    Tuesday, August 23, 2022
# 2                      abc123
# Name: date, dtype: object

CodePudding user response：

Just sharing the logic thinking technically works. Pls, Try it. Let me know it deosn't wor.

import pandas as pd
df = pd.DataFrame({'date': {0: '8/24/22 15:27', 1: '24/8/22 15:27', 2: 'a,b,c', 3: 'Tuesday, August 23, 2022'}})

       
mask1 = df.loc[pd.to_datetime(df['date'], errors='coerce',format='%m/%d/%y %H:%M').isnull()]
mask2 = df.loc[pd.to_datetime(df['date'], errors='coerce',format='%d/%m/%y %H:%M').isnull()]

df = pd.merge(mask1,mask2,on = ['date'],how ='inner')

print(df)

Sample obsorvations #

Input df

                       date
0             8/24/22 15:27
1             24/8/22 15:27
2                     a,b,c
3  Tuesday, August 23, 2022

output #

                       date
0                     a,b,c
1  Tuesday, August 23, 2022