How to exclude datetime formats in pandas when casting string Series to datetime and no format has been specified

user_11530164 0
Viewed 38

In the pandas.to_datetime function, pandas infers, for each cell, its format if no format has been specified. However, without specifying a particular format, how can we exclude certain formats from being used? (I am not refering to dayfirst or yearfirst parameters).

Something like:

df = pd.DataFrame({'dates': ['21-5-2018', '2/4/2000']})
df.dates = pd.to_datetime(df.dates, excluded_formats=['%m-%dd-%Y', '%m %YYYY'])
pandas format datetime python
1 Answers

Here is my take on your interesting question.

You can define a helper function:

def to_datetime(df, col, excluded_patterns):
    """Convert df[col] to datetime.

    Args:
        df: target dataframe.
        col: target column.
        excluded_patterns: regex date patterns to exclude from being converted.
        '\d' means any digit, '\d{4}' means 4 consecutive digits.
        "\d{2}-\d{2}-\d{4}" correspond to "05-21-2018" for instance.

    Returns:
        dataframe with 'col' values converted to datetime if not excluded.

    """
    df_ = df.copy()
    if not excluded_patterns:
        df_[col] = pd.to_datetime(df_[col], infer_datetime_format=True, errors="ignore")
        return df_
    excluded = pd.concat(
        [df_.loc[df[col].str.fullmatch(pat), :] for pat in set(excluded_patterns)]
    )
    df_ = df_.loc[~df.index.isin(excluded.index), :]
    df_[col] = pd.to_datetime(df_[col], infer_datetime_format=True, errors="ignore")
    return pd.concat([df_, excluded]).sort_index()

And then, with the following toy dataframe:

import pandas as pd

df = pd.DataFrame(
    {"dates": ["21-5-2018", "21-5-18", "05/21/2018", "5/21/2018", "2018--05-21"]}

# Regular use cases
print(to_datetime(df, "dates", [r"\d{1,2}-\d-\d{4}"]))
                 dates
0            21-5-2018
1  2018-05-21 00:00:00
2  2018-05-21 00:00:00
3  2018-05-21 00:00:00
4  2018-05-21 00:00:00

print(to_datetime(df, "dates", [r"\d{1,2}-\d-\d{4}", r"\d{1,2}-\d-\d{2}"]))
                 dates
0            21-5-2018
1              21-5-18
2  2018-05-21 00:00:00
3  2018-05-21 00:00:00
4  2018-05-21 00:00:00

# Edge cases
print(to_datetime(df, "dates", []))  # No pattern
       dates
0 2018-05-21
1 2018-05-21
2 2018-05-21
3 2018-05-21
4 2018-05-21

print(to_datetime(df, "dates", [r"\d-\d-\d"]))  # Pattern not found
       dates
0 2018-05-21
1 2018-05-21
2 2018-05-21
3 2018-05-21
4 2018-05-21

print(to_datetime(df, "dates", [r"\d{1,2}-\d-\d{4}", r"\d-\d-\d"]))  # One pattern found, one not found
                 dates
0            21-5-2018
1  2018-05-21 00:00:00
2  2018-05-21 00:00:00
3  2018-05-21 00:00:00
4  2018-05-21 00:00:00

print(to_datetime(df, "dates", [r"\d{1,2}-\d-\d{4}", r"\d{1,2}-\d-\d{4}"]))  # Twice the same pattern
                 dates
0            21-5-2018
1  2018-05-21 00:00:00
2  2018-05-21 00:00:00
3  2018-05-21 00:00:00
4  2018-05-21 00:00:00
user_112460560
Related