大熊猫合并在一个循环中产生的dataframes

Question

假设我有一个像这样dataframes（一环内产生并添加到列表）：

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 

我可以结合dataframes这样的：

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

使用pd.concat东西，数据帧列表合并成一个大的数据帧。

Answer 2

，因为他们不会有任何重叠值（基于该布局DF），这样的事情你可以结合_x和_y列：

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 

Answer 3

我不知道这是最复杂的解决方案，但你可以通过连接所有data_503和data_280 dataframes一起启动，然后合并它们。

的代码是不漂亮，我要运行到超过列的工作，虽然:)

df_list = [df1, df2, df3, df4] 

data_280_list=[] 
for k in df_list: 
    if 'data_280' in k.columns: 
     data_280_list.append(k) 

data_503_list=[] 
for k in df_list: 
    if 'data_503' in k.columns: 
     data_503_list.append(k) 


df_503= pd.concat(data_503_list) 
df_280= pd.concat(data_280_list) 

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

Answer 4

合并剥离下来的后缀后和应用ffill填补楠以前的专栏，并通过保持下降的重复列最后一个，这样就可以完全充满，即

layout.columns = [i.strip('_x').strip('_y') for i in layout.columns] 
layout.sort_index(1).ffill(1).loc[:,~layout.sort_index(1).columns.duplicated(keep='last')] 

输出：

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