如何在Pandas中对包含特定目标的行求和FPKM与count值？

我想要对Ko_class列中包含各个特定target的行，求和对应的FPKM与count值，并将所有目标及其求和结果输出到新的DataFrame中。

我的代码 #

# coding=utf-8
import pandas as pd
import numpy as np

classes = [('Carbon;Pyruvate;vitamins', 16.7, 1),
         ('Pyruvate;Carbohydrate;Pentose and glucuronate', 30, 7),
         ('Lipid;Carbon;Galactose', 40.5, 9),
         ('Galactose;Pyruvate;Fatty acid', 57, 10),
         ('Fatty acid;Lipid', 22, 4)]
labels = ['Ko_class','FPKM', 'count']
alls = pd.DataFrame.from_records(classes, columns=labels)


target = [['Carbon'],['Pyruvate'],['Galactose']]
targetsum = pd.DataFrame.from_records(target,columns=['target'])

#######          
targets = '|'.join(sum(target, []))
targetsum['total_FPKM']=(alls['FPKM']
                                    .groupby(alls['Ko_class']
                                            .str.contains(targets))
                                    .sum())

targetsum['count']=(alls['count']
                                    .groupby(alls['Ko_class']
                                            .str.contains(targets))
                                    .sum())

targetsum

当前运行结果 #

target  total_FPKM  count
0     Carbon         NaN    NaN
1   Pyruvate         NaN    NaN
2  Galactose         NaN    NaN

期望结果 #

target  total_FPKM  count
0     Carbon         57.2     10
1   Pyruvate        103.7     18
2  Galactose         97.5     19

代码问题在于：用str.contains(targets)将所有目标合并为正则匹配，分组后仅得到「包含任意目标」和「不包含」两组结果，与每个目标单独求和的需求不符，且直接赋值因索引不对应产生NaN。

修正后的代码 #

# coding=utf-8
import pandas as pd
import numpy as np

classes = [('Carbon;Pyruvate;vitamins', 16.7, 1),
         ('Pyruvate;Carbohydrate;Pentose and glucuronate', 30, 7),
         ('Lipid;Carbon;Galactose', 40.5, 9),
         ('Galactose;Pyruvate;Fatty acid', 57, 10),
         ('Fatty acid;Lipid', 22, 4)]
labels = ['Ko_class','FPKM', 'count']
alls = pd.DataFrame.from_records(classes, columns=labels)

# 将嵌套目标列表转为一维列表
targets = [item for sublist in [['Carbon'],['Pyruvate'],['Galactose']] for item in sublist]
targetsum = pd.DataFrame({'target': targets})

# 遍历每个目标，计算对应求和值
for idx, tgt in enumerate(targets):
    # 筛选Ko_class包含当前目标的行
    filtered_rows = alls[alls['Ko_class'].str.contains(tgt)]
    # 填入求和结果
    targetsum.loc[idx, 'total_FPKM'] = filtered_rows['FPKM'].sum()
    targetsum.loc[idx, 'count'] = filtered_rows['count'].sum()

print(targetsum)

运行结果 #

target  total_FPKM  count
0     Carbon         57.2     10
1   Pyruvate        103.7     18
2  Galactose         97.5     19

核心逻辑 #

• 把嵌套的目标列表转为一维，便于逐个遍历处理
• 对每个目标，用str.contains(tgt)精准筛选出Ko_class包含该目标的行
• 对筛选后的行分别求和FPKM和count，通过索引对应填入结果DataFrame

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