如何用Python优化Excel表格生成程序：实现多所有者动物统计布局

我明白你的需求：要把多个所有者的动物数量整理成左侧是固定动物列表、顶部是所有所有者名称、单元格对应数量的Excel表格。你的现有代码每次处理一个文件就写一行，导致布局分散，我们可以调整逻辑，先收集所有数据再统一排版，效率和可读性都会更好。

先梳理原代码的几个小问题 #

• 重复调用了两次pd.DataFrame.from_csv，完全没必要
• s_id的获取有错误（原代码里file_name未定义），应该从文件路径里提取所有者ID
• 统计动物数量的嵌套循环效率低，用Pandas内置方法更简洁可靠

修改后的实现思路和代码 #

核心逻辑是：先遍历所有.tab文件，收集每个所有者的动物数量统计结果，然后一次性写入Excel，这样就能轻松实现你要的布局：

import pandas as pd
import glob
import xlsxwriter
import os

tab_data_folder = "C:\\Users\\Hanna\\Document\\Private\\Animals"
tab_files = glob.glob(f'{tab_data_folder}/Names_*.tab')
# 固定的动物列表
Animal_names = ['dog', 'cat', 'bird', 'fish', 'pig', 'snake', 'frog', 'monkey', 'zebra', 'sheep']

# 第一步：收集所有所有者的统计数据
owner_data = {}
for tab_file in tab_files:
    # 提取所有者ID（从文件名中去掉前缀和后缀）
    file_name = os.path.basename(tab_file)
    s_id = file_name.replace('Names_', '').replace('.tab', '')
    
    # 读取tab文件，这里假设你的tab文件有表头，比如包含"animal"列
    df = pd.read_csv(tab_file, sep='\t')
    # 统计每个动物的数量，没有的动物填充0
    animal_counts = df['animal'].value_counts().reindex(Animal_names, fill_value=0)
    # 存入字典，键是所有者ID，值是数量Series
    owner_data[s_id] = animal_counts

# 第二步：写入Excel
workbook = xlsxwriter.Workbook('animal_owner_table.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})

# 写入表头：第一列是动物名称，后面是各个所有者ID
worksheet.write('A1', 'Animal Names', bold)
for col, owner_id in enumerate(owner_data.keys(), start=1):
    worksheet.write(0, col, owner_id, bold)

# 写入动物名称和对应数量
for row, animal in enumerate(Animal_names, start=1):
    # 写入第一列的动物名称
    worksheet.write(row, 0, animal)
    # 写入每个所有者对应的数量
    for col, owner_id in enumerate(owner_data.keys(), start=1):
        worksheet.write(row, col, owner_data[owner_id][animal])

workbook.close()

关键部分解释 #

1. 数据收集阶段：
   • 用os.path.basename提取文件名，避免路径干扰，更可靠
   • 用value_counts().reindex()统计数量：value_counts自动统计各动物出现次数，reindex确保所有指定动物都在结果里，没有的填充0，完美匹配你的固定动物列表
2. Excel写入阶段：
   • 先写第一行表头：第一列是"Animal Names"，后续列依次是所有所有者ID
   • 然后逐行写入动物名称，再逐列写入对应所有者的数量，这样就实现了你要的矩阵布局

这样修改后，生成的Excel就是你期望的格式：左侧固定动物列表，顶部所有所有者，单元格对应数量。

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