Python Pandas - 基于字符串子串合并数据

Question

Python Pandas - 基于字符串子串合并数据

7

我有两个数据框，格式如下：

df_search

SEARCH
part1
anotherpart
onemorepart


df_all

FILE             EXTENSION    PATH
part1_1         .prt    //server/folder1/part1_1
part1_2         .prt    //server/folder2/part1_2
part1_2         .pdf    //server/folder3/part1_2
part1_3         .prt    //server/folder2/part1_3
anotherpart_1   .prt    //server/folder1/anotherpart_1
anotherpart_2   .prt    //server/folder3/anotherpart_2
anotherpart_3   .prt    //server/folder2/anotherpart_3
anotherpart_3   .cgm    //server/folder1/anotherpart_3
anotherpart_4   .prt    //server/folder3/anotherpart_4
onemorepart_1   .prt    //server/folder2/onemorepart_1
onemorepart_2   .prt    //server/folder1/onemorepart_2
onemorepart_2   .dwg    //server/folder2/onemorepart_2
onemorepart_3   .prt    //server/folder1/onemorepart_3
onemorepart_4   .prt    //server/folder1/onemorepart_4

完整的df_search有15,000个项目。 df_all有550,000个项目。我正在尝试基于搜索项字符串在文件字符串中的匹配将这两个数据框合并。我期望得到的输出是这样的：

SEARCH       FILE            EXTENSION  PATH    
part1        part1_1        .prt    //server/folder1/part1_1    
part1        part1_2        .prt    //server/folder2/part1_2    
part1        part1_2        .pdf    //server/folder3/part1_2    
part1        part1_3        .prt    //server/folder2/part1_3    
anotherpart anotherpart_1   .prt    //server/folder1/anotherpart_1  
anotherpart anotherpart_2   .prt    //server/folder3/anotherpart_2  
anotherpart anotherpart_3   .prt    //server/folder2/anotherpart_3  
anotherpart anotherpart_3   .cgm    //server/folder1/anotherpart_3  
anotherpart anotherpart_4   .prt    //server/folder3/anotherpart_4  
onemorepart onemorepart_1   .prt    //server/folder2/onemorepart_1  
onemorepart onemorepart_2   .prt    //server/folder1/onemorepart_2  
onemorepart onemorepart_2   .dwg    //server/folder2/onemorepart_2  
onemorepart onemorepart_3   .prt    //server/folder1/onemorepart_3  
onemorepart onemorepart_4   .prt    //server/folder1/onemorepart_4

简单的数据框合并不起作用，因为字符串从来不是完全匹配的（它总是一个子字符串）。我还尝试了以下基于stackoverflow上其他问题的方法：

df_all[df_all.name.str.contains('|'.join(df_search.search))]

这给了我一个df_all中所有找到项的完整列表，但我不知道哪个搜索字符串返回了哪个结果。

我设法用for循环使其工作，但我的数据集速度很慢（67分钟）：

super_df = []
for search_item in df_search.search:
     df_entire.loc[df_entire.file.str.contains(search_item), 'search'] = search_item
     temp_df = df_entire[df_entire.file.str.contains(search_item)]
super_df = pd.concat(super_df, axis=0, ignore_index=True)

是否可以使用向量化来提高性能？

谢谢

- ArcJamMcE

2个回答

1

我会这样做：

df_all['SEARCH'] = ''
for val in df_search.SEARCH:
    df_all.loc[df_all['FILE'].str.match(val), 'SEARCH'] = val

- CezarySzulc

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jezrael · Accepted Answer

使用 str.extract + insert：

pat = "|".join(df_search.SEARCH)
df_all.insert(0, 'SEARCH', df_all['FILE'].str.extract("(" + pat + ')', expand=False))
print (df_all)
         SEARCH           FILE EXTENSION                            PATH
0         part1        part1_1      .prt        //server/folder1/part1_1
1         part1        part1_2      .prt        //server/folder2/part1_2
2         part1        part1_2      .pdf        //server/folder3/part1_2
3         part1        part1_3      .prt        //server/folder2/part1_3
4   anotherpart  anotherpart_1      .prt  //server/folder1/anotherpart_1
5   anotherpart  anotherpart_2      .prt  //server/folder3/anotherpart_2
6   anotherpart  anotherpart_3      .prt  //server/folder2/anotherpart_3
7   anotherpart  anotherpart_3      .cgm  //server/folder1/anotherpart_3
8   anotherpart  anotherpart_4      .prt  //server/folder3/anotherpart_4
9   onemorepart  onemorepart_1      .prt  //server/folder2/onemorepart_1
10  onemorepart  onemorepart_2      .prt  //server/folder1/onemorepart_2
11  onemorepart  onemorepart_2      .dwg  //server/folder2/onemorepart_2
12  onemorepart  onemorepart_3      .prt  //server/folder1/onemorepart_3
13  onemorepart  onemorepart_4      .prt  //server/folder1/onemorepart_4