时间戳

首页 > 解决方案 > 根据第三个 df3 比较 df1 和 df2 列中的数据，并从 df2 最后一列获取匹配行数据的数据

python - 根据第三个 df3 比较 df1 和 df2 列中的数据，并从 df2 最后一列获取匹配行数据的数据

问题描述

我在 df1 中有 3 个数据框

srno col1 col2 col3 col4
1 a1 a2 a3
2 b1 c2 c3
3 d1 b2
4 e1 e2 e3

df2

srno col1 col2 col3 col4
1 a1 g1
2 b2 g2
3 c2 c3 g3

df3

优先 col_combination
1 col1
2 col2,col3

我正在寻找以下输出 df1

srno col1 col2 col3 col4
1 a1 a2 a3 g1
2 b1 c2 c3 g3
3 d1 b2 g2
4 e1 e2 e3

我尝试了多种方法但无法实现这一点，我是 Python 编码的新手，有什么方法可以实现吗？下面的代码我试过它确实匹配并返回找到/未找到但还不能分配 df1[col4] = df2[col4] 匹配行。

for i in df3.index: 
    if "," in df3.loc[i,"col_combination"]:        
        print("multi column values to handle later")
    else:
        df1['col4'] = np.where(df1[df3.loc[i, "col_combination"]].isin(df2[df3.loc[i, "col_combination"]]),'found','not found')

标签： pythonpandasdataframe

解决方案

del df1['col4']

# read every condition in df3
# and merge related df2 columns to df1
# with the condition column

for i, row in df3.iterrows():
    priority = row['priority']
    col_list = row['col_combination'].split(',')
    df1 = pd.merge(df1, df2[col_list + ['col4']], on=col_list, how='left')
    # rename every merge df1's new col4 to priority no.
    df1.rename(columns={'col4':priority}, inplace=True)
    

print(df1)

#    srno col1 col2 col3    1    2
# 0     1   a1   a2   a3   g1  NaN
# 1     2   b1   c2   c3  NaN   g3
# 2     3   d1   b2  NaN  NaN   g2
# 3     4   e1   e2   e3  NaN  NaN

priority_list = df3['priority'].tolist()
obj = df1[priority_list[0]]
# use combine_first to merge priority(columns) 1, 2
for priority in priority_list[1:]:
    obj = obj.combine_first(df1[priority])

# re-assign
df1['col4'] = obj

print(df1)

#    srno col1 col2 col3    1    2 col4
# 0     1   a1   a2   a3   g1  NaN   g1
# 1     2   b1   c2   c3  NaN   g3   g3
# 2     3   d1   b2  NaN  NaN   g2   g2
# 3     4   e1   e2   e3  NaN  NaN  NaN


# finally del priority columns
df1.drop(priority_list, axis=1, inplace=True)

推荐阅读