Pandas按文件名匹配将DataFrame行数据批量复制到所有行的问题

原始数据 #

df1 #

Filename    Name    1_Info1     1_Info2     1_Info3
Filename_1  Name1   First Row   01-01-1900  Information_1st Row
Filename_2  Name2   Second Row  01-01-1901  Information_2nd Row
Filename_3  Name3   Third Row   01-01-1902  Information_3rd Row

df2 #

Model       Sample_ID2  Location1   Location2   Location3
MODEL_1     ID          10          40          70
MODEL_2     ID          20          50          80
MODEL_3     ID          30          60          90
MODEL_4     ID          31          61          91
MODEL_5     ID          32          62          92
MODEL_6     ID          33          63          93

需求 #

指定文件名（如Filename_1）匹配df1的Filename列时，把df1中匹配行的所有列信息复制到df2的每一行，期望输出如下：

Model   Sample_ID2  Location1   Location2   Location3   Filename            Name    1_Info1         1_Info2         1_Info3
MODEL_1 ID          10          40          70          Filename_1          Name1   First Row       01-01-1900      Information_1st Row
MODEL_2 ID          20          50          80          Filename_1          Name1   First Row       01-01-1900      Information_1st Row
MODEL_3 ID          30          60          90          Filename_1          Name1   First Row       01-01-1900      Information_1st Row
MODEL_4 ID          31          61          91          Filename_1          Name1   First Row       01-01-1900      Information_1st Row
MODEL_5 ID          32          62          92          Filename_1          Name1   First Row       01-01-1900      Information_1st Row
MODEL_6 ID          33          63          93          Filename_1          Name1   First Row       01-01-1900      Information_1st Row

现有代码问题 #

以下代码仅能把匹配行的信息复制到df2第一行，其余行对应列无数据：

df1 = pd.read_csv("Filename_1.text.csv")
df2 = pd.read_csv("test_data.csv")

filename = os.path.basename("Filename_1.text.csv")
filename_new = filename.split('.')[0]

checked = ((df1[df1['Filename'] == filename_new]))

combined_df = pd.concat([df2, checked], axis=1)

解决方案 #

方法1：使用assign广播数据 #

提取匹配行后转为字典，通过assign将这些字段批量添加到df2，pandas会自动把单个值广播到所有行：

import pandas as pd
import os

df1 = pd.read_csv("Filename_1.text.csv")
df2 = pd.read_csv("test_data.csv")

filename = os.path.basename("Filename_1.text.csv")
filename_new = filename.split('.')[0]

# 获取匹配的单行数据
matched_row = df1.loc[df1['Filename'] == filename_new].iloc[0]

# 批量添加字段到df2
combined_df = df2.assign(**matched_row.to_dict())

方法2：重复匹配行后拼接 #

如果偏好使用concat，可以将匹配行重复到和df2相同的行数，再进行拼接：

import pandas as pd
import os

df1 = pd.read_csv("Filename_1.text.csv")
df2 = pd.read_csv("test_data.csv")

filename = os.path.basename("Filename_1.text.csv")
filename_new = filename.split('.')[0]

checked = df1[df1['Filename'] == filename_new]
# 重复匹配行，行数与df2一致
checked_repeated = checked.loc[checked.index.repeat(len(df2))].reset_index(drop=True)

combined_df = pd.concat([df2, checked_repeated], axis=1)

方法3：直接赋值新列 #

逐个提取匹配行的字段，直接赋值给df2的新列：

import pandas as pd
import os

df1 = pd.read_csv("Filename_1.text.csv")
df2 = pd.read_csv("test_data.csv")

filename = os.path.basename("Filename_1.text.csv")
filename_new = filename.split('.')[0]

matched_row = df1.loc[df1['Filename'] == filename_new].iloc[0]

# 遍历匹配行的所有列，赋值给df2
for col in matched_row.index:
    df2[col] = matched_row[col]

combined_df = df2

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