Skip to main content
 首页 » 编程设计

python之如果列值来自不同的文件,如何将列插入到数据框中

2025年02月15日7grandyang

目前我正在从一个文件中读入,它正在生成这个文件(output.txt):

Atom nVa avgppm stddev delta 
1.H1' 2 5.73649 0.00104651803616 1.0952e-06 
1.H2' 1 4.85438 
1.H8 1 8.05367 
10.H1' 3 5.33823 0.136655138213 0.0186746268 
10.H2' 1 4.20449 
10.H5 3 5.27571333333 0.231624986634 0.0536501344333 
10.H6 5 7.49485 0.0285124165935 0.0008129579 

这是读取生成此文件的代码(我正在从文本文件中读取以生成这些值)

df = pd.read_csv(expAtoms, sep = ' ', header = None) 
df.columns = ["Atom","ppm"] 
gb = (df.groupby("Atom", as_index=False).agg({"ppm":["count","mean","std","var"]}).rename(columns={"count":"nVa", "mean":"avgppm","std":"stddev","var":"delta"})) 
 
gb.head() 
 
gb.columns = gb.columns.droplevel() 
gb = gb.rename(columns={"":"Atom"}) 
 
gb.to_csv("output.txt", sep =" ", index=False) 

在我的 nVa 列和我的 avgppm 列之间,我想插入另一个名为 predppm 的列。我想从一个名为 file.txt 的文件中获取值,该文件如下所示:

5.H6 7.72158 0.3 
6.H6 7.70272 0.3 
7.H8 8.16859 0.3 
1.H1' 7.65014 0.3 
9.H8 8.1053 0.3 
10.H6 7.5231 0.3 

如何检查 file.txt 第一列的值是否等于 output.txt 第一列的值,如果是,插入file.txt 第二列中的值到我的输出文件中 nVa 列和 avgppm 列之间的列?

例如,1.H1' 在 output.txt 和 file.txt 中,所以我想在我的 output.txt 文件中创建一个名为 predppm 的列并为 1.H1' 原子插入值 7.65014(来自 file.txt 的第二列)。

我想我了解如何添加列,但仅限于我可以与 groupby 一起使用的函数,但我不知道如何将任意列插入到我的输出中。

请您参考如下方法:

最简单的方法是在 pandas.DataFrame 上创建一个 index。 Pandas 具有很好的索引匹配逻辑。

from io import StringIO 
import pandas as pd 
 
# if python2, do: 
# data = u"""\ 
data = """\ 
Atom nVa avgppm stddev delta 
1.H1' 2 5.73649 0.00104651803616 1.0952e-06 
1.H2' 1 4.85438 
1.H8 1 8.05367 
10.H1' 3 5.33823 0.136655138213 0.0186746268 
10.H2' 1 4.20449 
10.H5 3 5.27571333333 0.231624986634 0.0536501344333 
10.H6 5 7.49485 0.0285124165935 0.0008129579 
""" 
 
# if python2, do: 
# other_data = u"""\ 
other_data = """\ 
5.H6 7.72158 0.3 
6.H6 7.70272 0.3 
7.H8 8.16859 0.3 
1.H1' 7.65014 0.3 
9.H8 8.1053 0.3 
10.H6 7.5231 0.3 
""" 
 
# setup these strings so they can be read by pd.read_csv 
# (not necessary if these are actual files on disk) 
data_file = StringIO(data) 
other_data_file = StringIO(other_data) 
 
# don't say header=None because the first row has the column names 
df = pd.read_csv(data_file, sep=' ') 
# set the index to 'Atom' 
df = df.set_index('Atom') 
 
# header=None because the other_data doesn't have header info 
other_df = pd.read_csv(other_data_file, sep=' ', header=None) 
# set the column names since they're not specified in other_data 
other_df.columns = ['Atom', 'predppm', 'some_other_field'] 
# set the index to 'Atom' 
other_df = other_df.set_index('Atom') 
 
# this will assign other_df['predppm'] to the correct rows, 
# because pandas uses the index when assigning new columns 
df['predppm'] = other_df['predppm'] 
 
print(df) 
#         nVa    avgppm    stddev     delta  predppm 
# Atom                                               
# 1.H1'     2  5.736490  0.001047  0.000001  7.65014 
# 1.H2'     1  4.854380       NaN       NaN      NaN 
# 1.H8      1  8.053670       NaN       NaN      NaN 
# 10.H1'    3  5.338230  0.136655  0.018675      NaN 
# 10.H2'    1  4.204490       NaN       NaN      NaN 
# 10.H5     3  5.275713  0.231625  0.053650      NaN 
# 10.H6     5  7.494850  0.028512  0.000813  7.52310 
 
# if you want to return 'Atom' to being a column: 
df = df.reset_index()