Python Pandas中DataFrame求和后除法计算等位基因频率遇问题

我有两个分别名为df1和df2的DataFrame，数据如下：

# df1数据
    Scaffold  Position  Ref_Allele_Count  Alt_Allele_Count  Coverage_Depth  Alt_Allele_Frequency
0          1        11                 7                51              58              0.879310
1          1        16                20                95             115              0.826087
2          2         9                 9                33              42              0.785714
3          2        12                86                51             137              0.372263
4          2        67                41                98             139              0.705036
5          3         8                 0                 0               0              0.000000
6          4        99                32                26              58              0.448276
7          4       101               100                24             124              0.193548
8          4       115                69                26              95              0.273684
9          5         6                40                57              97              0.587629
10         5        19                53                87             140              0.621429

# df2数据
    Scaffold  Position  Ref_Allele_Count  Alt_Allele_Count  Coverage_Depth  Alt_Allele_Frequency
0          1        11                 7                64              71              0.901408
1          1        16                10                90             100              0.900000
2          2         9                79                86             165              0.521212
3          2        12                12                73              85              0.858824
4          2        67                54                96             150              0.640000
5          3         8                 0                 0               0              0.000000
6          4        99                86                28             114              0.245614
7          4       101                32                25              57              0.438596
8          4       115                97                16             113              0.141593
9          5         6                86                43             129              0.333333
10         5        19                59                27              86              0.313953

我需要将两个DataFrame的Allele_Count和Coverage Depth求和后，用总Alt_Allele_Count除以总Coverage_Depth得到总等位基因频率（AF），但操作时遇到两个问题：

1. 尝试转换为浮点型时报错：TypeError: float() argument must be a string or a number, not 'DataFrame'
2. 直接用DataFrame相除得到全NaN的结果：

Alt_Allele_Count  Coverage_Depth
0                NaN             NaN
1                NaN             NaN
2                NaN             NaN
3                NaN             NaN
4                NaN             NaN
5                NaN             NaN
6                NaN             NaN
7                NaN             NaN
8                NaN             NaN
9                NaN             NaN
10               NaN             NaN

我的代码如下：

import csv
import pandas as pd
import numpy as np

df1 = pd.read_csv('C:/Users/Tom/Python_CW/file_pairA_1.csv')
df2 = pd.read_csv('C:/Users/Tom/Python_CW/file_pairA_2.csv')
print(df1)
print(df2)


Ref_Allele_Count = (df1[['Ref_Allele_Count']] + df2[['Ref_Allele_Count']])
print(Ref_Allele_Count)

Alt_Allele_Count = (df1[['Alt_Allele_Count']] + df2[['Alt_Allele_Count']])
print(Alt_Allele_Count)

Coverage_Depth = (df1[['Coverage_Depth']] + df2[['Coverage_Depth']]).astype(float)
print(Coverage_Depth)

AF = Alt_Allele_Count / Coverage_Depth

print(AF)

解决方案 #

问题原因 #

1. 使用df[['列名']]获取的是单列DataFrame，而非Series。两个DataFrame做除法时，会严格匹配列名和索引，导致运算后出现NaN；同时astype(float)无法直接作用于整个DataFrame，引发类型错误。
2. 数据中存在Coverage_Depth为0的行（第5行），直接除法会触发除以0的警告。

修正后的代码 #

import pandas as pd
import numpy as np

# 读取数据
df1 = pd.read_csv('C:/Users/Tom/Python_CW/file_pairA_1.csv')
df2 = pd.read_csv('C:/Users/Tom/Python_CW/file_pairA_2.csv')

# 改用df['列名']获取Series，确保运算时自动按索引对齐
total_ref = df1['Ref_Allele_Count'] + df2['Ref_Allele_Count']
total_alt = df1['Alt_Allele_Count'] + df2['Alt_Allele_Count']
total_coverage = df1['Coverage_Depth'] + df2['Coverage_Depth']

# 计算总AF，替换0深度为NaN避免除以0警告（也可根据需求设为0）
total_af = total_alt / total_coverage.replace(0, np.nan)

# 整合结果到新DataFrame，保留定位信息
result = df1[['Scaffold', 'Position']].copy()
result['Total_Ref_Allele_Count'] = total_ref
result['Total_Alt_Allele_Count'] = total_alt
result['Total_Coverage_Depth'] = total_coverage
result['Total_Alt_Allele_Frequency'] = total_af

print(result)

关键修改点 #

• 将df[['列名']]改为df['列名']，获取一维Series，运算时自动按索引对齐，不会生成NaN。
• 处理了Coverage_Depth为0的情况，避免运行时警告。
• 将所有结果整合到一个DataFrame中，方便查看完整的位点信息和计算结果。

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