从其他数据帧替换数据帧列中dict列表中的值

Question

数据帧1

df1 = pd.DataFrame([[1221,"aptq",[{'id': 100051}, {'id': 100050}]]], columns = ["offid","name","sub_ids"])

   offid  name                           sub_ids
0   1221  aptq  [{'id': 100051}, {'id': 100050}]

数据帧2

df2 = pd.DataFrame([[100051, "zonal"], [100050, "upper"],
                    [100056, "capital | national"]], columns=["id", "name"])

       id                name
0  100051               zonal
1  100050               upper
2  100056  capital | national

结果数据帧

offid          name                sub_ids
 1221          aptq         [zonal, upper]

想要用Dataframe 2中id的名称替换Dataframe 1的sub_ids列中的值，以获得类似result Dataframe的结果。任何帮助都将不胜感激

Answer 1

使用{}上的{a1}，然后使用{}列上的{a2}从字典中提取与键{}关联的值，然后使用{a3}将{}映射到{}中的名称，并使用{}上的{a4}和{}上的{}：

names = (
    df1['sub_ids'].explode().str.get('id')
    .map(df2.set_index('id')['name']).groupby(level=0).agg(list)
)
df = df1.assign(sub_ids=names)

结果:

print(df)

   offid  name         sub_ids
0   1221  aptq  [zonal, upper]

Answer 2

您可以使用以下选项：

df1 = pd.DataFrame([[1221,"aptq",[{'id': 100051}, {'id': 100050}]]], columns = ["offid","name","sub_ids"])
df2 =   pd.DataFrame([[100051,"zonal"],
[100050,"upper"],
[100056,"capital | national"]], columns = [    "id","name"])
df2 = df2.set_index("id").T.to_dict(orient='records')[0]

现在我们只需创建一个列表并在字典中查找它：

df1["sub_ids"] = df1["sub_ids"].apply(lambda row: [item for sublist in [list(row[i].values()) for i in range(len(row))] for item in sublist] if len(row) > 0 else "-")
df1["sub_ids"]  = df1["sub_ids"].apply(lambda row:  [df2[row[i]] for i in range(len(row))] if len(row)>0 else "-")
df1

    offid   name    sub_ids
0   1221    aptq    [zonal, upper]