使用Pandas fillna后数据类型变为object，如何保留原类型？

我正在使用LBNL建筑自动化故障检测数据集，原始数据包含Int64、Float64和datetime64[ns]类型。但在使用Pandas的fillna方法后，所有列的数据类型都变成了object。

初始代码（此时数据类型正常）：

import pandas as pd
import datetime as dt
%matplotlib inline
df = pd.read_csv('RTU.csv') 
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

执行以下代码后，所有列数据类型全部变为object：

df['Timestamp'] = pd.to_datetime(df['Timestamp'])
def fault_mapper_FD(faultDate):
    if pd.Timestamp(2017, 8, 27, 0) <= faultDate <= pd.Timestamp(2017, 8, 28, 0):
        return 0
    if pd.Timestamp(2017, 8, 29, 0) <= faultDate <= pd.Timestamp(2017, 8, 29, 23, 59):
        return 0
    if pd.Timestamp(2017, 12, 1, 0) <= faultDate <= pd.Timestamp(2017, 12, 1, 23, 59):
        return 0
    if pd.Timestamp(2017, 12, 3, 0) <= faultDate <= pd.Timestamp(2017, 12, 3, 23, 59):
        return 0
    if pd.Timestamp(2017, 12, 7, 0) <= faultDate <= pd.Timestamp(2017, 12, 8, 0):
        return 0
    if pd.Timestamp(2017, 12, 14, 0) <= faultDate <= pd.Timestamp(2017, 12, 14, 23, 59):
        return 0
    if pd.Timestamp(2018, 2, 7, 0) <= faultDate <= pd.Timestamp(2018, 2, 7, 23, 59):
        return 0
    if pd.Timestamp(2018, 2, 9, 0) <= faultDate <= pd.Timestamp(2018, 2, 9, 23, 59):
        return 0
    if pd.Timestamp(2017, 12, 20, 0) <= faultDate <= pd.Timestamp(2017, 12, 20, 23, 59):
        return 0
    if pd.Timestamp(2018, 2, 18, 0) <= faultDate <= pd.Timestamp(2018, 2, 18, 23, 59):
        return 0
    if pd.Timestamp(2018, 2, 1, 0) <= faultDate <= pd.Timestamp(2018, 2, 1, 23, 59):
        return 0
    if pd.Timestamp(2018, 1, 31, 0) <= faultDate <= pd.Timestamp(2018, 1, 31, 23, 59):
        return 0
    if pd.Timestamp(2018, 1, 28, 0) <= faultDate <= pd.Timestamp(2018, 1, 28, 23, 59):
        return 0
    if pd.Timestamp(2018, 1, 27, 0) <= faultDate <= pd.Timestamp(2018, 1, 27, 23, 59):
        return 0
    if (pd.Timestamp(2017, 9, 1, 0) <= faultDate <= pd.Timestamp(2017, 9, 1, 23, 59) or 
    pd.Timestamp(2017, 11, 30, 0) <= faultDate <= pd.Timestamp(2017, 11, 30, 23, 59) or 
    pd.Timestamp(2017, 12, 9, 0) <= faultDate <= pd.Timestamp(2017, 12, 9, 23, 59) or 
    pd.Timestamp(2017, 12, 10, 0) <= faultDate <= pd.Timestamp(2017, 12, 11, 0) or 
    pd.Timestamp(2017, 12, 24, 0) <= faultDate <= pd.Timestamp(2017, 12, 24, 23, 59) or 
    pd.Timestamp(2018, 2, 4, 0) <= faultDate <= pd.Timestamp(2018, 2, 4, 23, 59) or 
    pd.Timestamp(2018, 2, 5, 0) <= faultDate <= pd.Timestamp(2018, 2, 6, 0)):
        return 1

df['FD'] = df['Timestamp'].apply(lambda fault_date: fault_mapper_FD(fault_date))

cond = (df.Timestamp.dt.time > dt.time(22,0)) | ((df.Timestamp.dt.time < dt.time(7,0)))
df[cond] = df[cond].fillna(0,axis=1)

现在执行df.dtypes显示所有列均为object类型，请问如何操作才能在Pandas处理后保留原数据类型？

问题根源 #

问题出在df[cond] = df[cond].fillna(0,axis=1)这一行：

• 对筛选后的子DataFrame填充0并赋值回原DataFrame时，Pandas会自动将列类型转换为object——因为填充的0是整数类型，与原列的浮点/日期类型混合，触发类型降级。
• Timestamp列被错误包含在填充范围内，用整数0覆盖了日期值，直接导致该列类型变为object。

修复步骤 #

1. 精准选择填充列（推荐） #

只对数值列填充，排除Timestamp和FD列：

# 筛选出所有数值类型的列
numeric_cols = df.select_dtypes(include=['int64', 'float64']).columns
# 仅对符合条件的行中的数值列填充0
df.loc[cond, numeric_cols] = df.loc[cond, numeric_cols].fillna(0)

2. 已执行错误操作后的恢复 #

如果已经完成错误填充，可手动将各列转换回原类型：

# 恢复Timestamp列的日期类型
df['Timestamp'] = pd.to_datetime(df['Timestamp'])
# 恢复数值列类型
for col in numeric_cols:
    if df[col].dtype == 'int64':
        df[col] = df[col].astype('int64')
    elif df[col].dtype == 'float64':
        df[col] = df[col].astype('float64')
# 恢复FD列的整数类型
df['FD'] = df['FD'].astype('int64')

3. 优化故障标记函数（可选） #

简化原函数的大量if判断，同时确保返回值类型一致：

# 定义正常日期区间
normal_intervals = [
    (pd.Timestamp(2017,8,27), pd.Timestamp(2017,8,28)),
    (pd.Timestamp(2017,8,29), pd.Timestamp(2017,8,29,23,59)),
    (pd.Timestamp(2017,12,1), pd.Timestamp(2017,12,1,23,59)),
    (pd.Timestamp(2017,12,3), pd.Timestamp(2017,12,3,23,59)),
    (pd.Timestamp(2017,12,7), pd.Timestamp(2017,12,8)),
    (pd.Timestamp(2017,12,14), pd.Timestamp(2017,12,14,23,59)),
    (pd.Timestamp(2018,2,7), pd.Timestamp(2018,2,7,23,59)),
    (pd.Timestamp(2018,2,9), pd.Timestamp(2018,2,9,23,59)),
    (pd.Timestamp(2017,12,20), pd.Timestamp(2017,12,20,23,59)),
    (pd.Timestamp(2018,2,18), pd.Timestamp(2018,2,18,23,59)),
    (pd.Timestamp(2018,2,1), pd.Timestamp(2018,2,1,23,59)),
    (pd.Timestamp(2018,1,31), pd.Timestamp(2018,1,31,23,59)),
    (pd.Timestamp(2018,1,28), pd.Timestamp(2018,1,28,23,59)),
    (pd.Timestamp(2018,1,27), pd.Timestamp(2018,1,27,23,59))
]

# 定义故障日期区间
fault_intervals = [
    (pd.Timestamp(2017,9,1), pd.Timestamp(2017,9,1,23,59)),
    (pd.Timestamp(2017,11,30), pd.Timestamp(2017,11,30,23,59)),
    (pd.Timestamp(2017,12,9), pd.Timestamp(2017,12,9,23,59)),
    (pd.Timestamp(2017,12,10), pd.Timestamp(2017,12,11)),
    (pd.Timestamp(2017,12,24), pd.Timestamp(2017,12,24,23,59)),
    (pd.Timestamp(2018,2,4), pd.Timestamp(2018,2,4,23,59)),
    (pd.Timestamp(2018,2,5), pd.Timestamp(2018,2,6))
]

def fault_mapper_FD(faultDate):
    for start, end in normal_intervals:
        if start <= faultDate <= end:
            return 0
    for start, end in fault_intervals:
        if start <= faultDate <= end:
            return 1
    return None

# 使用Int64类型支持缺失值
df['FD'] = df['Timestamp'].apply(fault_mapper_FD).astype('Int64')

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