如何为df1添加Sum列，按Taxon汇总df2的Number列总和？

需求回顾 #

我有两个Pandas DataFrame：

df1 数据 #

COL1 COL2 Taxon 
A    B    Canis_lupus
C    D    Felis_catus 
E    F    Mus_musculus
G    H    Canidae
I    J    Felidae
K    L    Muridae
M    N    Canis_lupus_familiaris

df2 数据 #

COL3 Number Taxonomy
1    120    d__Eukaryota;p__Metazoera;c__tetrapoda;o__Carnivora;f__Canidae;g__Canis;s__Canis_lupus
2    129    d__Eukaryota;p__Metazoera;c__tetrapoda;o__Carnivora;f__Canidae;g__Canis;s__Canis_lupus_familiaris
3    134    d__Eukaryota;p__Metazoera;c__tetrapoda;o__Carnivora;f__Felidae;g__Felis;s__Felis_catus
4    234    d__Eukaryota;p__Metazoera;c__tetrapoda;o__Rodentia;f__Muridae;g__Mus;s__Mus_musculus
5    12     d__Eukaryota;p__Metazoera;c__tetrapoda;o__Rodentia;f__Muridae;g__Rattus;s__Rattus_norgevigus
6    289    d__Eukaryota;p__Metazoera;c__tetrapoda;o__Rodentia;f__Muridae;g__Rattus;s__Rattus_Rattus

需要给df1新增一列Sum，值为df2中Taxonomy字段包含对应Taxon的所有Number之和，最终结果如下：

COL1 COL2 Taxon                    Sum
A    B    Canis_lupus              120  # df2中Taxonomy匹配Canis_lupus的Number总和
C    D    Felis_catus              134  # df2中Taxonomy匹配Felis_catus的Number总和
E    F    Mus_musculus             234  # df2中Taxonomy匹配Mus_musculus的Number总和
G    H    Canidae                  249  # df2中Taxonomy匹配Canidae的Number总和（120+129）
I    J    Felidae                  134  # df2中Taxonomy匹配Felidae的Number总和
K    L    Muridae                  535  # df2中Taxonomy匹配Muridae的Number总和（234+12+289）
M    N    Canis_lupus_familiaris   129  # df2中Taxonomy匹配Canis_lupus_familiaris的Number总和

数据源代码：

from io import StringIO
import pandas as pd

data = """COL1\tCOL2\tTaxon
A\tB\tCanis_lupus
C\tD\tFelis_catus
E\tF\tMus_musculus
G\tH\tCanidae
I\tJ\tFelidae
K\tL\tMuridae
M\tN\tCanis_lupus_familiaris"""

df1 = pd.read_csv(StringIO(data), sep='\t')

df2 = pd.DataFrame({'COL3': [1, 2, 3, 4, 5, 6],
                    'Number': [120, 129, 134, 234, 12, 289],
                    'Taxonomy': ['d__Eukaryota;p__Metazoera;c__tetrapoda;o__Carnivora;f__Canidae;g__Canis;s__Canis_lupus',
                                    'd__Eukaryota;p__Metazoera;c__tetrapoda;o__Carnivora;f__Canidae;g__Canis;s__Canis_lupus_familiaris',
                                    'd__Eukaryota;p__Metazoera;c__tetrapoda;o__Carnivora;f__Felidae;g__Felis;s__Felis_catus',
                                    'd__Eukaryota;p__Metazoera;c__tetrapoda;o__Rodentia;f__Muridae;g__Mus;s__Mus_musculus',
                                    'd__Eukaryota;p__Metazoera;c__tetrapoda;o__Rodentia;f__Muridae;g__Rattus;s__Rattus_norgevigus',
                                    'd__Eukaryota;p__Metazoera;c__tetrapoda;o__Rodentia;f__Muridae;g__Rattus;s__Rattus_Rattus']})

方法一：直接使用apply匹配（小数据集首选） #

遍历df1的每个Taxon，筛选df2中Taxonomy包含该Taxon的行，对Number求和：

df1['Sum'] = df1['Taxon'].apply(lambda x: df2[df2['Taxonomy'].str.contains(x)]['Number'].sum())

这种方法代码简洁，但如果df2数据量很大，apply循环会比较慢。

方法二：预处理分类阶元后分组求和（大数据集高效） #

利用df2的Taxonomy结构，先拆分所有分类阶元，分组求和后再和df1匹配，效率更高：

1. 拆分Taxonomy，提取每个阶元的名称（去掉d__、f__这类前缀）

df2['taxa'] = df2['Taxonomy'].apply(lambda x: [t.split('__')[1] for t in x.split(';')])

2. 把每个阶元拆成单独行，和Number对应

taxa_expanded = df2.explode('taxa')

3. 按阶元分组求和

taxa_sum = taxa_expanded.groupby('taxa')['Number'].sum().reset_index(name='Sum')

4. 和df1合并，填充Sum列

df1 = df1.merge(taxa_sum, left_on='Taxon', right_on='taxa', how='left').drop('taxa', axis=1)

两种方法都能得到期望的结果，根据数据集大小选择即可。

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