如何根据Excel工作表B2单元格姓名，控制Dummy列填0或留空？

需求说明 #

默认将各工作表的Dummy列空值填充为0，但当对应工作表的B2单元格学生姓名为'Roberto'或'Leonardo'时，Dummy列需保持空白。

原始数据 #

{'Sheet_1':     ID      Name  Surname  Grade favourite color    favourite sport  Dummy
 0  104  Eleanor     Rigby      6            blue  American football    NaN
 1  168  Barbara       Ann      8            pink             Hockey    0.0
 2  450    Polly   Cracker      7           black      Skateboarding    NaN
 3   90   Little      Josy     10          orange            Cycling    NaN,
 'Sheet_2':     ID       Name   Surname  Grade favourite color favourite sport  Dummy
 0  106       Lucy       Sky      8          yellow          Tennis    NaN
 1  128    Delilah     Perez      5     light green      Basketball    0.0
 2  100  Christina   Rodwell      3           black       Badminton    NaN
 3   40      Ziggy  Stardust      7             red          Squash    NaN,
 'Sheet_3':     ID   Name   Surname  Grade favourite color favourite sport  Dummy
 0   22   Lucy  Diamonds      9           brown            Judo    NaN
 1   50  Grace     Kelly      7           white       Taekwondo    NaN
 2  105    Uma   Thurman      7          purple      videogames    NaN
 3   29   Lola   McQueen      3             red            Surf    0.0}

期望结果 #

{'Sheet_1':     ID      Name  Surname  Grade favourite color    favourite sport  Dummy
 0  104  Eleanor     Rigby      6            blue  American football    NaN
 1  168  Barbara       Ann      8            pink             Hockey    NaN
 2  450    Polly   Cracker      7           black      Skateboarding    NaN
 3   90   Little      Josy     10          orange            Cycling    NaN,
 'Sheet_2':     ID       Name   Surname  Grade favourite color favourite sport  Dummy
 0  106       Lucy       Sky      8          yellow          Tennis      0
 1  128    Delilah     Perez      5     light green      Basketball      0
 2  100  Christina   Rodwell      3           black       Badminton      0
 3   40      Ziggy  Stardust      7             red          Squash      0,
 'Sheet_3':     ID   Name   Surname  Grade favourite color favourite sport  Dummy
 0   22   Lucy  Diamonds      9           brown            Judo    NaN
 1   50  Grace     Kelly      7           white       Taekwondo    NaN
 2  105    Uma   Thurman      7          purple      videogames    NaN
 3   29   Lola   McQueen      3             red            Surf    NaN}

原代码问题 #

原代码仅读取了活动工作表的B2单元格值，导致所有工作表的Dummy列都被统一填充为0，无法针对每个工作表单独判断条件。

正确代码方案 #

import pandas as pd
import openpyxl

# 定义文件路径
file_path = r'C:/Users/machukovich/stack_2.xlsx'

# 加载openpyxl工作簿，读取单元格实际值而非公式
wb = openpyxl.load_workbook(file_path, data_only=True)

# 加载所有工作表为DataFrame字典，跳过前2行表头
dict_of_df = pd.read_excel(file_path, sheet_name=None, skiprows=2)

# 遍历每个工作表，按条件处理Dummy列
for sheet_name, df in dict_of_df.items():
    # 获取当前工作表的B2单元格学生姓名
    student_name = wb[sheet_name]['B2'].value
    # 仅当姓名不在指定列表时，填充Dummy列空值为0
    if student_name not in ('Roberto', 'Leonardo'):
        df['Dummy'] = df['Dummy'].fillna(0)

# 将处理后的结果写回原Excel文件，替换原有工作表
with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    for sheet_name, df in dict_of_df.items():
        df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=2)

代码说明 #

• 用data_only=True加载openpyxl工作簿，确保读取单元格的实际值而非公式计算结果
• 遍历每个工作表时单独获取对应表的B2值，实现按表独立判断条件
• 仅满足非指定姓名条件时才填充空值，否则保留Dummy列原有空白
• 写回文件时指定startrow=2，保证表头行与原文件格式一致

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