Python Pandas - Merge based on substring in string

Question

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

jezrael · Accepted Answer · 2018-02-12 10:34:32Z

17

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

edited Feb 12, 2018 at 10:34

answered Feb 12, 2018 at 10:29

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ArcJamMcE Over a year ago

This is exactly what i needed. I was having a problem with the insert, and realised it was because one of my search items had a '()' character in it, which was giving me two groups in the regex. I also filtered out all of the NaN values after this, as some files didn't match. Thanks a lot.

ah bon Over a year ago

Sorry, it raises an error for my data: ValueError: Wrong number of items passed 12, placement implies 1. Do you know how to deal with it?

jezrael Over a year ago

@ahbon - Hard question, one idea should be use pat = "|".join([re.escape(x) for x in df_search.SEARCH]) if possible some special chars in column - escape values

CezarySzulc · Accepted Answer · 2018-02-12 10:27:33Z

1

I will do it in this way:

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

answered Feb 12, 2018 at 10:27

CezarySzulc

2,0191 gold badge18 silver badges33 bronze badges

Collectives™ on Stack Overflow

Python Pandas - Merge based on substring in string

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related