Clean Mixed Excel Date Formats in Pandas with Simple Code

Introduction

Hello, I'm PiPi. A recent question in a Python community asked how to handle mixed date formats in an Excel column using Pandas.

Problem

The column contains dates like 20230516 and 2023-02-16 17:45:33, and converting them to a uniform date type caused errors.

Solution

Two approaches were suggested. First, determine the string length and apply different parsing methods. Second, replace hyphens to unify the format to YYYYMMDD, then convert.

Implemented code:

df['日期'] = df['日期'].map(lambda x: x.replace('-', '')[:8])
df['日期'] = pd.to_datetime(df['日期'])

This successfully resolved the issue.

Conclusion

The article demonstrated a practical method for cleaning mixed date strings in Pandas, providing clear code and tips for handling similar problems.

Tip: When asking for help, share a small, anonymized sample of the data and include error screenshots or the exact code.