Create a column based on if a string is a substring in pandas Dataframe

Question

One of the columns in my data frames are identifier names with a specific naming convention. When it was entered, it wasn't entered correctly. I wanted to ask how I can find specific keywords to input in its own column in python. Maybe some sort of loop?

Example:

types = ['XYZ', 'OPQ', 'MNO', 'ABC']

current df:

ID  ID Name
45  I_name_ls_XYZ_random
46  I_22_name_ABC_random
47  I_name_ls_XYZ_random_45
48  I_name_ls_MNO_random
49  I_ls_OPQ_random_name
50  I_name_ls_ABC_random
51  I_name_ls_XYZ_random
52  I_name_MNO_random

Wanted result:

ID  ID Name                types
45  I_name_ls_XYZ_random    XYZ
46  I_22_name_ABC_random    ABC
47  I_name_ls_XYZ_random_45 XYZ
48  I_name_ls_MNO_random    MNO
49  I_ls_OPQ_random_name    OPQ
50  I_name_ls_ABC_random    ABC
51  I_name_ls_XYZ_random    XYZ
52  I_name_MNO_random       MNO

user3483203 · Accepted Answer · 2018-06-22 23:35:40Z

1

Using str.extract

df['types'] = df.Name.str.extract('({})'.format('|'.join(types)))

   ID                     Name types
0  45     I_name_ls_XYZ_random   XYZ
1  46     I_22_name_ABC_random   ABC
2  47  I_name_ls_XYZ_random_45   XYZ
3  48     I_name_ls_MNO_random   MNO
4  49     I_ls_OPQ_random_name   OPQ
5  50     I_name_ls_ABC_random   ABC
6  51     I_name_ls_XYZ_random   XYZ
7  52        I_name_MNO_random   MNO

If you might want multiple matches you can use findall

df
   ID                     Name
0  45  I_name_ls_XYZ_ABCrandom

df.Name.str.findall(r'|'.join(types))
0    [XYZ, ABC]
Name: Name, dtype: object

edited Jun 22, 2018 at 23:35

answered Jun 22, 2018 at 23:30

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

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

Comments

jpp · Accepted Answer · 2018-06-22 23:38:34Z

1

Using pd.Series.apply with a custom function / generator expression:

types = {'XYZ', 'OPQ', 'MNO', 'ABC'}

def string_filter(x):
    return next((i for i in x.split('_') if i in types), None)

df['types'] = df['ID_Name'].apply(string_filter)

print(df)

   ID                  ID_Name types
0  45     I_name_ls_XYZ_random   XYZ
1  46     I_22_name_ABC_random   ABC
2  47  I_name_ls_XYZ_random_45   XYZ
3  48     I_name_ls_MNO_random   MNO
4  49     I_ls_OPQ_random_name   OPQ
5  50     I_name_ls_ABC_random   ABC
6  51     I_name_ls_XYZ_random   XYZ
7  52        I_name_MNO_random   MNO

answered Jun 22, 2018 at 23:38

jpp

166k37 gold badges301 silver badges362 bronze badges

Collectives™ on Stack Overflow

Create a column based on if a string is a substring in pandas Dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related