Excel多值单元格昆虫数据处理：拆分与统计方法求助

一、将原表格转换为长表结构 #

Excel 操作步骤（适合非编程用户） #

• 选中insect (family)列的所有数据，点击「数据」选项卡 → 「分列」，选择「分隔符号」，勾选「逗号」完成拆分，多科名称会被分到相邻列
• 选中包含样本编号和拆分后所有昆虫科列的区域，点击「数据」→ 「从表格/区域」，进入Power Query编辑器
• 选中样本编号列，点击「转换」选项卡 → 「逆透视列」→ 选择「逆透视其他列」
• 删除自动生成的「属性」列，将「值」列重命名为insect (family)，点击「关闭并上载」即可生成目标长表

Python（Pandas）实现（适合大数据量/自动化需求） #

import pandas as pd

# 读取原始数据（支持csv/xlsx格式，替换为你的文件路径）
df = pd.read_excel("insect_data.xlsx")

# 拆分单元格内的昆虫科并展开为长表
df_long = df.assign(insect_family=df["insect (family)"].str.split(", ")).explode("insect_family")
df_long = df_long.rename(columns={"insect_family": "insect (family)"})

# 保存长表
df_long.to_excel("insect_long_table.xlsx", index=False)

二、统计昆虫科出现频次 #

基于长表的Excel统计 #

• 以上述长表为数据源，插入数据透视表：行区域选择insect (family)，值区域选择样本编号并设置为「计数」，即可得到频次统计表格

Python（Pandas）统计 #

基于生成的长表直接统计：

# 统计各昆虫科出现频次
freq_table = df_long["insect (family)"].value_counts().reset_index()
freq_table.columns = ["insect (family)", "统计数量"]

# 保存统计结果
freq_table.to_excel("insect_frequency.xlsx", index=False)

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