Pandas排序后日期格式异常：部分日期被整数化的问题排查

原始数据处理代码 #

import pandas as pd
from datetime import datetime

link = 'https://www.beerinstitute.org/wp-content/uploads/2021/12/2021-September-The-Brewers-Almanac-Beer-Institute-2021.xlsx'

df=pd.read_excel(link, sheet_name='Beer Shipments by State', skiprows= lambda x: x<2 or x >54)

df = df.tail(-1)
del df[df.columns[1]]
df.drop(columns=df.columns[1:29], axis=1,  inplace=True)
df.rename(columns={'Unnamed: 0': 'State'}, inplace=True)

df = pd.melt(df, id_vars='State')
df.rename(columns={'value': 'shipments'}, inplace=True)

df

原始处理后输出 #

State variable      shipments
0            Alabama   1994.1         234030
1             Alaska   1994.1          26799
2            Arizona   1994.1         295000
3           Arkansas   1994.1         127200
4         California   1994.1        1640146
...              ...      ...            ...
16825       Virginia   2021.6         439000
16826     Washington   2021.6         473000
16827  West Virginia   2021.6  119300.985455
16828      Wisconsin   2021.6      469606.08
16829        Wyoming   2021.6          47000

[16830 rows x 3 columns]

排序后的代码 #

import pandas as pd
from datetime import datetime

link = 'https://www.beerinstitute.org/wp-content/uploads/2021/12/2021-September-The-Brewers-Almanac-Beer-Institute-2021.xlsx'

df=pd.read_excel(link, sheet_name='Beer Shipments by State', skiprows= lambda x: x<2 or x >54)

df = df.tail(-1)
del df[df.columns[1]]
df.drop(columns=df.columns[1:29], axis=1,  inplace=True)
df.rename(columns={'Unnamed: 0': 'State'}, inplace=True)

df = pd.melt(df, id_vars='State')
df.rename(columns={'variable': 'date', 'value': 'shipments'}, inplace=True)

df.sort_values(by=['State', 'date'])

排序后异常输出 #

State    date shipments
561    Alabama    1995    212299
1173   Alabama    1996    224432
1785   Alabama    1997    207508
2397   Alabama    1998    232107
3009   Alabama    1999    239510
...        ...     ...       ...
16625  Wyoming  2021.2     30000
16676  Wyoming  2021.3     33000
16727  Wyoming  2021.4     37000
16778  Wyoming  2021.5     48000
16829  Wyoming  2021.6     47000

[16830 rows x 3 columns]

疑问 #

为什么Alabama的date值变成了1995这类整数，而Wyoming的date还是2021.6这种小数格式？如何让最终数据集保留州名、1994.1至2021.6的年月格式变量及原始数据？

原因分析 #

1. 混合数据类型导致格式不一致：Excel源文件中，部分年份列的表头是整数（如1995），部分是带小数的数值（如2021.2）。pd.read_excel读取时会自动识别为混合数据类型，使得date列同时存在整数和浮点数。
2. Pandas显示逻辑：当列内存在混合数据类型时，Pandas会根据每个值的类型自动调整显示格式——整数显示为整数，浮点数显示为小数，并非真正的格式转换，只是显示差异。

解决方案 #

要统一保留YYYY.M格式，可通过两种方式实现：

方案1：转换为字符串格式，强制统一显示 #

在数据处理流程中，将表头转换为字符串，并对整数格式的日期补全.0后缀：

import pandas as pd

link = 'https://www.beerinstitute.org/wp-content/uploads/2021/12/2021-September-The-Brewers-Almanac-Beer-Institute-2021.xlsx'

df=pd.read_excel(link, sheet_name='Beer Shipments by State', skiprows= lambda x: x<2 or x >54)

df = df.tail(-1)
del df[df.columns[1]]
df.drop(columns=df.columns[1:29], axis=1,  inplace=True)
df.rename(columns={'Unnamed: 0': 'State'}, inplace=True)

# 将所有表头转换为字符串，避免混合类型
df.columns = df.columns.astype(str)
df = pd.melt(df, id_vars='State', var_name='date', value_name='shipments')

# 对无小数点的日期补.0，统一为YYYY.M格式
df['date'] = df['date'].apply(lambda x: x if '.' in x else f"{x}.0")

# 排序并重置索引
df = df.sort_values(by=['State', 'date']).reset_index(drop=True)
print(df)

方案2：解析为标准日期类型，再格式化输出 #

如果需要后续进行时间序列运算，可先将日期解析为datetime类型，再格式化为YYYY.M字符串：

import pandas as pd

link = 'https://www.beerinstitute.org/wp-content/uploads/2021/12/2021-September-The-Brewers-Almanac-Beer-Institute-2021.xlsx'

df=pd.read_excel(link, sheet_name='Beer Shipments by State', skiprows= lambda x: x<2 or x >54)

df = df.tail(-1)
del df[df.columns[1]]
df.drop(columns=df.columns[1:29], axis=1,  inplace=True)
df.rename(columns={'Unnamed: 0': 'State'}, inplace=True)

df.columns = df.columns.astype(str)
df = pd.melt(df, id_vars='State', var_name='date', value_name='shipments')

# 将YYYY.M格式解析为datetime类型
df['date'] = df['date'].apply(
    lambda x: pd.to_datetime(f"{x.split('.')[0]}-{x.split('.')[1] if '.' in x else '01'}")
)
# 重新格式化为YYYY.M字符串
df['date'] = df['date'].dt.strftime("%Y.%m").str.replace('.0', '.')

df = df.sort_values(by=['State', 'date']).reset_index(drop=True)
print(df)

说明 #

• 方案1适合仅需保留原始格式展示或存储的场景，操作简单直接；
• 方案2适合需要进行日期运算、时间序列分析的场景，保留了日期的可操作性。

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