python - Pandas 合并循环内生成的数据帧

Question

假设我有这样的数据框(在循环内生成并添加到列表中):

column  row data_503    plate
0   1   A   1   2
1   1   B   2   2
2   1   C   3   2
3   1   D   4   2

column  row data_280    plate
0   1   A   1   2
1   1   B   2   2
2   1   C   3   2
3   1   D   4   2

column  row data_503    plate
0   1   A   1   1
1   1   B   2   1
2   1   C   3   1
3   1   D   4   1

column  row data_280    plate
0   1   A   1   1
1   1   B   2   1
2   1   C   3   1
3   1   D   4   1

我确实有一个布局文件，它将测量值映射到特定条件:

column  row cond    plate
0   1   A   5   1
1   1   B   5   1
2   1   C   5   1
3   1   D   4   1
0   1   A   5   2
1   1   B   5   2
2   1   C   5   2
3   1   D   4   2

我可以像这样组合数据框:

for df in df_list:
    layout= pd.merge(layout, df, on=['plate', 'row', 'column'], how = 'outer')

但是，我总是得到 data_280_x 和 data_280_y 列，但我只想获得 data_280 和 data_503列。将 outer 更改为 left 不会改变任何内容。

有什么办法可以得到类似的东西吗？:

column  row cond    plate    data_280    data_503
0   1   A   5   1    1    1
1   1   B   5   1    2    2
2   1   C   5   1    3    3
3   1   D   4   1    4    4
0   1   A   5   2    1    1
1   1   B   5   2    2    2
2   1   C   5   2    3    3
3   1   D   4   2    4    4

Answer 1

您可以组合 _x 和 _y 列，因为它们不会有任何重叠值(基于该布局 df)，如下所示:

df['data_208'] = df['data_208_x'] + df['data_208_y']

然后您可以删除 _x 和 _y 列。

用例子更新:

df1 = pd.DataFrame({"column": [1, 1, 1, 1], "row": ["A", "B", "C", "D"], "plate": [1, 1, 1, 1], "data_503": [4, 5, 6, 7]})
df2 = pd.DataFrame({"column": [1, 1, 1, 1], "row": ["A", "B", "C", "D"], "plate": [1, 1, 1, 1], "data_280": [1, 2, 3, 4]})
df3 = pd.DataFrame({"column": [1, 1, 1, 1], "row": ["A", "B", "C", "D"], "plate": [2, 2, 2, 2], "data_503": [4, 5, 6, 7]})
df4 = pd.DataFrame({"column": [1, 1, 1, 1], "row": ["A", "B", "C", "D"], "plate": [2, 2, 2, 2], "data_280": [1, 2, 3, 4]})
layout = pd.DataFrame({"column": [1, 1, 1, 1, 1, 1, 1, 1], "row": ["A", "B", "C", "D", "A", "B", "C", "D"], "cond": [5, 5, 5, 4, 5, 5, 5, 4], "plate": [1, 1, 1, 1, 2, 2, 2, 2]})

out = []
for df in [df1, df2, df3, df4]:
    _ = pd.merge(layout, df, on=['column', 'row', 'plate'], how='outer').dropna()
    out.append(_)

merged = out[0]
for df in out[1:]:
    merged = pd.merge(merged, df, on=['column', 'row', 'plate', 'cond'], how='outer')

merged = merged.fillna(0)

merged['data_280'] = merged['data_280_x'] + merged['data_280_y']
merged['data_503'] = merged['data_503_x'] + merged['data_503_y']

merged = merged.drop(['data_280_x','data_280_y','data_503_x','data_503_y'],1)

给我:

column  cond  plate row  data_280  data_503
0       1     5      1   A       1.0       4.0
1       1     5      1   B       2.0       5.0
2       1     5      1   C       3.0       6.0
3       1     4      1   D       4.0       7.0
4       1     5      2   A       1.0       4.0
5       1     5      2   B       2.0       5.0
6       1     5      2   C       3.0       6.0
7       1     4      2   D       4.0       7.0