Extract string from a dataframe comparing to a list

Question

I am trying to extract strings from a DF in pandas dataframe and the source strings are in a list from which I have to match. I tried using a df.str.extract(list1) but i got an error of unhashable types i guess i the way I compare the list to the DF is not correct

From

Col 1   Col 2
1       The date
2       Three has come
3       Mail Sent
4       Done Deal

To

Col 1   Col 2           Col 3 
1       The date        NaN
2       Three has come  Three has
3       Mail Sent        Mail
4       Done Deal        Done

My list is like below

List1 = ['Three has' , 'Mail' , 'Done' , 'Game' , 'Time has come']

jezrael · Accepted Answer · 2017-01-31 12:15:20Z

7

You can use extract with join all values in List by | what means or in regex:

List1 = ['Three has' , 'Mail' , 'Done' , 'Game' , 'Time has come']
df['Col 3'] = df['Col 2'].str.extract("(" + "|".join(List1) +")", expand=False)
print (df)
   Col 1           Col 2      Col 3
0      1        The date        NaN
1      2  Three has come  Three has
2      3       Mail Sent       Mail
3      4       Done Deal       Done

Another solution:

List1 = ['Three has' , 'Mail' , 'Done' , 'Game' , 'Time has come']

df['Col 3'] = df['Col 2'].apply(lambda x: ''.join([L for L in List1 if L in x]))
df['Col 3'] = df['Col 3'].mask(df['Col 3'] == '')
print (df)
   Col 1           Col 2      Col 3
0      1        The date        NaN
1      2  Three has come  Three has
2      3       Mail Sent       Mail
3      4       Done Deal       Done

edited Jan 31, 2017 at 12:15

answered Jan 31, 2017 at 10:22

jezrael

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

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

6 Comments

yasin mohammed Over a year ago

Tried this but got the error ValueError: Wrong number of items passed 3, placement implies 1

jezrael Over a year ago

Problem is with sample or with real data?

yasin mohammed Over a year ago

Problem is with the real data. Having nos in some cells or special chars like - or _ will make a difference ?

jezrael Over a year ago

Yes, there has to be problem. Because it seems some characters are read as regex. Can you extract all special chars?

yasin mohammed Over a year ago

Since they list actually contains server names I will not be removing the special chars

|

Collectives™ on Stack Overflow

Extract string from a dataframe comparing to a list

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related