Return value from other dataframe from partial string match

Question

I'm trying create a new dataframe column that is a partial string match from another dataframe. How would I do the example below?

df1:
#   id
1   666666
2   666667
3   666668
4   666667

df2
#   ref
1   ref_666666_blah blah
2   ref_666667_blah blah
3   ref_666668_blah blah
4   ref_666667_blah blah

df3 #what I want
#   id      match
1   666666  ref_666666_blah blah
2   666667  ref_666667_blah blah
3   666668  ref_666668_blah blah
4   666667  ref_666667_blah blah

I know this is not the code but I'm trying to do the below:

df1['match'] = df2['ref'].map(lambda x: x if x.str.contains(df1['match'])

Thanks!

hilberts_drinking_problem · Accepted Answer · 2015-09-29 21:26:48Z

1

There are a number of ways to accomplish this.

If you are able to extract the id from the ref column, as you would in this particular example by df2[id] = df2.ref.apply(lambda c: c.split('_')[1]), you could proceed with df1.join(df2, on = 'id').

If you need to call some more complicated match function, you could do the following:

def getMatch(str_id):
    matches = (c for c in df2['ref'] if str_id in c)
    try:
        return matches.next()
    except:
        return None

df1['match'] = df1['id'].apply(getMatch)

This will result in a number of redundant comparisons, so you should think if there are relationships in your data that can simplify the match. For instance, if each ref matches to at most one id or if you can somehow sort both DataFrames in a meaningful way and merge them recursively.

answered Sep 29, 2015 at 21:26

hilberts_drinking_problem

11.6k3 gold badges25 silver badges55 bronze badges

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

1 Comment

Andy Do Over a year ago

Thank you the split thing and merging dataframes is what I needed!

Collectives™ on Stack Overflow

Return value from other dataframe from partial string match

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related