基于其他列的字符串创建数据帧

Question

我想基于一个列条件（关键字）创建几个列

这是我的数据帧的片段

Index  wave_path

0      wav48/p225/p225_001.wav
.      wav48/p227/p227_005.wav.
5
.      ......................
.      ......................

44040  wav48/p376/p376_265.wav

现在，我有了一个文本文件，根据ID（即225、227、376…等等）有几个列。此文本文件包含以下文本

ID  AGE  GENDER  ACCENTS    REGION  
225  23  F       English    Southern  England
226  22  M       English    Surrey
227  38  M       English    Cumbria
228  22  F       English    Southern  England
229  23  F       English    Southern  England
230  22  F       English    Stockton-on-tees

我想让这5列根据关键字ID与wave_path列相匹配来设置它们各自的条目

from pandas import DataFrame

df.loc[df.wave_path == wav48/p225/p225_001.wav, 'AGE'] = '23' 
df.loc[df.wave_path == wav48/p225/p227_005.wav, 'AGE'] = '38' 

print (df)

但是，这将导致数百行代码，而且非常耗时。我们有办法吗

预期结果是：

Index  wave_path                      AGE    GENDER   ACCENT    REGION

0      wav48/p225/p225_001.wav        23     F        English   Southern England
.      wav48/p227/p227_005.wav.       38     M        English   Cumbria
5
.      ......................
.      ......................

44040  wav48/p376/p376_265.wav

Answer 1

首先分割wave\u路径，并从中获取最后的ID wav48/p225/p225_001.wav>；225个

把它转换成int

使用^{}

df['ID'] = df['wave_path'].apply(lambda x :x.split("/")[-1].split("_")[0].split("p")[-1])
df['ID'] = df['ID'].astype(int)

df2['ID']  = df2['ID'].astype(int)
final_df = pd.merge(df,df2,on=['ID'],how='left')