使用Pandas按日期排序DataFrame时的Excel日期解析与排序问题

核心问题拆解 #

• pd.to_datetime() 默认会自动识别日期格式，遇到12/02/2023这类格式时，会优先按月/日/年解析（即2023-12-02），和你需要的日/月/年格式不符。
• 你后续用dt.strftime('%d/%m/%Y')把日期转回了字符串类型，字符串排序是按字符顺序比较的，所以31/01/2023会因为开头的3比2大，排在28/02/2023后面。

正确解决方案 #

1. 强制指定日期解析格式 #

在pd.to_datetime()中通过format参数明确指定输入格式为%d/%m/%Y，确保解析逻辑完全符合你的需求。

2. 保留datetime类型用于排序 #

不要立即将日期转成字符串，用datetime类型完成排序后，若需要显示为dd/mm/yyyy格式，可单独新增列或设置显示选项，避免破坏日期的数值排序逻辑。

修改后的代码示例 #

处理模拟数据 #

import pandas as pd

dictionary = {
      "DATE" : ['12/02/2023', '02/01/2023', '02/01/2023', '10/02/2023'],    
      "CONCEPT" : ['Supermarket','Restaurant', 'Gas', 'Suscription'],
      "EUR" : [-150,-50,-45,-95]
          }
df = pd.DataFrame(dictionary)

# 关键：指定format参数解析dd/mm/yyyy格式，保留datetime类型
df['DATE'] = pd.to_datetime(df['DATE'], format='%d/%m/%Y')

# 按日期排序（此时是datetime类型，排序逻辑正确）
df = df.sort_values(by=['DATE'], axis=0, ascending=True)

# 可选：新增显示列，保留原datetime列用于后续操作
df['DATE_DISPLAY'] = df['DATE'].dt.strftime('%d/%m/%Y')

print(df)

处理实际Excel文件 #

# 方法1：读取时直接指定解析规则
df = pd.read_excel('your_excel_file.xlsx', parse_dates=['DATE'], 
                   date_parser=lambda x: pd.to_datetime(x, format='%d/%m/%Y'))

# 方法2：读取后再转换日期列
df = pd.read_excel('your_excel_file.xlsx')
df['DATE'] = pd.to_datetime(df['DATE'], format='%d/%m/%Y')

# 执行排序
df = df.sort_values(by=['DATE'], ascending=True)

额外注意事项 #

如果Excel中日期列存在混合格式或无效值，可添加errors='coerce'参数将无法解析的内容转为NaT（缺失日期值），方便后续排查处理：

df['DATE'] = pd.to_datetime(df['DATE'], format='%d/%m/%Y', errors='coerce')

内容的提问来源于stack exchange，提问作者nekovolta