如何在Pandas中格式化整数列并忽略空白单元格（Excel导出无报错）

问题背景 #

使用Pandas对比不同时间区间的HR数据集并导出Excel时，遇到员工ID格式相关问题：

• 员工ID需保持8位带前导零格式（例如00045642），但Excel默认会自动识别为数字并丢失前导零
• 尝试用map函数格式化时出现两个异常：
  1. 导出Excel后，目标列显示自定义格式且触发报错，添加astype(int)转换也无法解决
  2. 空白单元格被错误格式化为000000nan，不符合业务需求
• 期望实现：指定列跳过空白单元格，有值的单元格转为8位带前导零格式，且导出Excel无报错

解决方案 #

1. 修正ID格式化逻辑 #

• 使用applymap替代直接map，确保每个单元格独立处理
• 先判断值是否为空，空值返回空字符串；非空值先转为整数，再格式化为8位带前导零的字符串
• 避免使用浮点型格式化规则（如{:08.0f}），防止NaN被错误转换

2. 确保导出时的文本格式 #

• 格式化后的ID列保持字符串类型，避免Excel自动识别为数字丢失前导零
• 导出Excel时无需额外设置单元格格式，因为数据本身已是带前导零的字符串

3. 清理冗余代码 #

• 移除无效的astype(int, errors='ignore')转换（会保留NaN导致后续格式化出错）
• 调整格式化函数的执行时机，确保在数据合并后、导出前完成

修改后的完整代码 #

import pandas as pd   
import numpy as np

# 读取新数据集
new_WFRL = pd.read_excel("H:/DIR/Human Resources/HR Audits/Raw Files/WFRL 6.10.24.xlsx", 
                         usecols=["Empl ID", "Name",'Reports To', 'Reports To Position Number', 'Reports To Empl ID']).sort_values("Name")  
new_WFRL = new_WFRL.add_prefix("New ")  

# 读取旧数据集
old_WFRL = pd.read_excel("H:/DIR/Human Resources/HR Audits/Raw Files/WFRL 4.18.24.xlsx", 
                         usecols=["Empl ID", "Name",'Reports To', 'Reports To Position Number', 'Reports To Empl ID']).sort_values("Name")  
old_WFRL = old_WFRL.add_prefix("Old ")  

# 合并数据集
merged_WFRL = pd.merge(old_WFRL, new_WFRL, how = "outer", left_on = "Old Empl ID", right_on = "New Empl ID")  

# 定义对比函数：判断汇报对象是否变化
def compare_WFRL(df):  
    return 1 if df["Old Reports To"] == df["New Reports To"] else 0  

# 应用对比函数
merged_WFRL["changes"] = merged_WFRL.apply(compare_WFRL, axis=1)  

# 定义员工ID格式化函数
def format_employee_id(x):
    if pd.notnull(x):
        # 先转整数再格式化为8位带前导零的字符串
        return f"{int(x):08d}"  
    else:
        return ""  # 空值返回空字符串

# 对指定列应用格式化函数
id_columns = ['Old Empl ID','Old Reports To Empl ID','New Empl ID','New Reports To Empl ID']
merged_WFRL[id_columns] = merged_WFRL[id_columns].applymap(format_employee_id)

# 筛选出有变化的记录并移除changes列
merged_changes = merged_WFRL[merged_WFRL["changes"] == 0].drop(columns=["changes"])  

# 导出到Excel
export_file = "H:/DIR/Human Resources/HR Audits/Reports To Changes/6.10.24 Coach Changesv14.xlsx"
merged_changes.to_excel(export_file, index=False, engine='openpyxl') 

# -------------------------- Excel样式调整部分 --------------------------
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter 
from openpyxl.styles import Border, Side, Alignment, PatternFill

# 加载工作簿
wb = load_workbook(export_file)
ws = wb.active
ws.title= 'Reports To Changes'

# 定义样式
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
thin_border = Border(left=Side(border_style='thin', color='FF000000'),
                     right=Side(border_style='thin', color='FF000000'),
                     top=Side(border_style='thin', color='FF000000'),
                     bottom=Side(border_style='thin', color='FF000000'))
center_alignment = Alignment(horizontal='center')

# 应用条件格式：标记变化的汇报对象
for row in range(2, len(merged_changes) + 2):    
    old_reports_to_cell = ws.cell(row=row, column=5)  
    new_reports_to_cell = ws.cell(row=row, column=10)  
    if new_reports_to_cell.value != old_reports_to_cell.value:
        new_reports_to_cell.fill = yellow_fill

# 应用对齐和边框样式
for row in range(2, len(merged_changes) + 2):
    for col in range(1, ws.max_column + 1):
        cell = ws.cell(row, col)
        cell.alignment = center_alignment
        cell.border = thin_border

# 调整列宽
dims = {}
for row in ws.rows:
    for cell in row:
        if cell.value:
            dims[cell.column_letter] = max(dims.get(cell.column_letter, 0), len(str(cell.value)))
for col, value in dims.items():
    ws.column_dimensions[col].width = value * 1.2  

# 保存工作簿
wb.save(export_file)

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