Find all columns with a date pattern in a dataframe

user_7686502 0
Viewed 227

I am trying to identify whether a column in my dataframe has a date or not. For context, my real dataset has hundreds of columns, and I can't go through and manually identify if a column is a date or not. Ultimately, I need to get the dates in a similar format so that I can compare different versions of my datasets and see what has changed.

df = pd.DataFrame({
        'date1':['07/23/01', '06/25/17', np.nan, '1/5/06'],
        'date2':[np.nan, '12/01/08', '9/02/03', np.nan],
        'str1':['aaaaf', '23fgsafg', 'dsf5t', np.nan]})

mask = df.astype(str).apply(lambda x : x.str.match('\d{1,2}/\d{1,2}/\d{2}').all())

I've tried the following code, but each of these variables come up as "false". What am I missing here?

Thanks

date python regex
1 Answers

You can use

for col in df.columns:
    if df[col].dtype.name == 'object':
        if all(df[col].str.contains(r'\d{1,2}/\d{1,2}/\d{2}', regex=True, na=True)):
            print(f'{col} column contains a date-like string')

# => date1 column contains a date-like string
#    date2 column contains a date-like string

Note that to find a regex match anywhere in the input string you need to use Series.str.contains with regex=True (as it is based on re.search method), since Series.str.match requires the match to only appear at the start of string (as it is based on re.match method).

na=True is necessary to make all np.nan values truthy when checking if all values in a column return True.

user_38329700
Related