在 pandas 中串联时拆分并与其他列合并 [英] Split and column and merge with another column while concatenating in pandas

问题描述

根据另一列的值在熊猫中创建值的串联

Create a concatenation of values in pandas based on values of another column

我有2个数据框

df1

NAME    CODE
andy    a,d
roger   b
danny   d
cole    

df2

CODE    MATERIAL
a       paper
b       plastic
b       metal
d       wood
e       glass

我想用materials列更新df2并在必要时进行连接

I want to update df2 with the materials column and concatenate where necessary

预期结果:

CODE    MATERIAL    NAME
a       paper       andy
b       plastic     roger
b       metal       roger
d       wood        andy, danny
e       glass   

我该怎么办?

推荐答案

您可以首先创建代码以基于df1命名映射，然后使用该映射在df2中创建所需的列:

You can first create a code to name mapping based on df1, and then use the mapping to create the needed column in df2:

# revert NAME to CODE mapping as CODE to NAME
code_2_name = df1.assign(CODE = lambda x: x.CODE.str.split(','))
                 .explode('CODE')
                 .groupby('CODE')
                 .agg(','.join)  
code_2_name
#            NAME
#CODE            
#a           andy
#b          roger
#d     andy,danny

# create the NAME column based on the code to name mapping
df2['NAME'] = df2.CODE.map(code_2_name.NAME)
df2
#  CODE MATERIAL        NAME
#0    a    paper        andy
#1    b  plastic       roger
#2    b    metal       roger
#3    d     wood  andy,danny
#4    e    glass         NaN

这篇关于在 pandas 中串联时拆分并与其他列合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！