I have 2 dataframes with the following format:
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
The full df_search has 15,000 items. df_all has 550,000 items. I am trying to merge the two dataframes based on the search item string being in the file string. My desired output is this:
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
A simple dataframe merge does not work, because the strings are never exact matches (it is always a substring). I also tried the following method based on other questions here on stackoverflow:
df_all[df_all.name.str.contains('|'.join(df_search.search))]
This gave me a full list of all the found items in df_all, but i don't know which search string returned which result.
I managed to get it to work with a for loop, but it is slow (67 minutes) with my dataset:
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)
Is it possible to do this with vectorisation to improve performance?
Thanks
Use 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With