How to add new Column in Python pandas dataframe by searching keyword value given in list?

user_7671519 0
Viewed 220

I want to add new Column in the dataframe on the basis of Identified keyword:

This is Current Data(Dataframe name = df):

    Topic                   Count
0   This is Python          39
1   This is SQL             6
2   This is Paython Pandas  98
3   import tkinter          81
4   Learning Python         94
5   SQL Working             85
6   Pandas and Work         67
7   This is Pandas          30
8   Computer                20
9   Mobile Work             55
10  Smart Mobile            69

My desired output as below

    Topic                   Count       Groups
0   This is Python          39          Python_Group
1   This is SQL             6           SQL_Group
2   This is Paython Pandas  98          Python_Group
3   import tkinter          81          Python_Group
4   Learning Python         94          Python_Group
5   SQL Working             85          SQL_Group
6   Pandas and Work         67          Python_Group
7   This is Pandas          30          Python_Group
8   Computer                20          Devices_Group
9   Mobile Work             55          Devices_Group
10  Smart Mobile            69          Devices_Group

How to Identify Groups Column Value

The Groups created on the basis of below Identity in Topics Column. if particular word found in Topics then particular group name need assign to it

List of Keywords from Topic Column

Python_Group = ['Python','Pandas','tkinter']
SQL_Group = ['SQL', 'Select']
Devices_Group = ['Computer','Mobile']

I have tried below code for it:

df['Groups'] = [
    'Python Group' if "Python" in x 
    else 'Python Group' if "Pandas" in x
    else 'Python Group' if "tkinter" in x
    else 'SQL Group' if "SQL" in x
    else 'Devices Group' if "Computer" in x
    else 'Devices Group' if "Mobile" in x
    else '000' 
    for x in df['Topic']]
print(df)

Above code is also giving me the desired output but I want to make it more short and quick because in above mentioned dataframe has almost 2MM+ Records and its very difficult for me to write 1k+ line of code to define grouping.

Is there any way where I can utilized List of keyword falling under Topic Column? OR any Custom Function that can help me in this iterative process?

Code:2 Another below code tried after consulting Stack overflow Experts:

d = pd.read_excel('Map.xlsx').to_dict('list')
keyword_groups = {x:k for k, v in d.items() for x in v}
pat = '({})'.format('|'.join(keyword_groups))   #This line is giving an error
df['Groups'] = (df['Topic'].str.extract(pat, expand=False)
               .map(keyword_groups)
               .fillna('000'))

The Error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-131-543675c0b403> in <module>
      3 
      4 keyword_groups = {x:k for k, v in d.items() for x in v}
----> 5 pat = '({})'.format('|'.join(keyword_groups))
      6 pat

TypeError: sequence item 5: expected str instance, float found

Thanks for you help.

pandas list-comprehension python-3.x
2 Answers

you can do this using np.select. np.select receives 3 parameters, one of conditions, one of results and the last the default value when no condition is found.

Python_Group = ['Python','Pandas','tkinter']
SQL_Group = ['SQL', 'Select']
Devices_Group = ['Computer','Mobile']

conditions = [
    df['Topic'].str.contains('|'.join(Python_Group))
    ,df['Topic'].str.contains('|'.join(SQL_Group))
    ,df['Topic'].str.contains('|'.join(Devices_Group))
]

results = [
    "Python_Group"
    ,"SQL_Group"
    ,"Devices_Group"
]

df['Groups'] = np.select(conditions, results, '000')
#output:
    Topic                   Count   Groups
0   This is Python          39      Python_Group
1   This is SQL             6       SQL_Group
2   This is Paython Pandas  98      Python_Group
3   import tkinter          81      Python_Group
4   Learning Python         94      Python_Group
5   SQL Working             85      SQL_Group
6   Pandas and Work         67      Python_Group
7   This is Pandas          30      Python_Group
8   Computer                20      Devices_Group
9   Mobile Work             55      Devices_Group
10  Smart Mobile            69      Devices_Group
user_58478540

One way could be to consider maintaining your groups and keywords in a dict:

d = {'Python_Group': ['Python','Pandas','tkinter'],
     'SQL_Group': ['SQL', 'Select'],
     'Devices_Group': ['Computer','Mobile']}

From here, you could easily reverse this to a "keyword: Group" dict.

keyword_groups = {x:k for k, v in d.items() for x in v}

# {'Python': 'Python_Group',
#  'Pandas': 'Python_Group',
#  'tkinter': 'Python_Group',
#  'SQL': 'SQL_Group',
#  'Select': 'SQL_Group',
#  'Computer': 'Devices_Group',
#  'Mobile': 'Devices_Group'}

Then you can use Series.str.extract to find these keywords using regex and map them to the correct group. Use fillna to catch any non-matching groups.

pat = '({})'.format('|'.join(keyword_groups))

df['Groups'] = (df['Topic'].str.extract(pat, expand=False)
               .map(keyword_groups)
               .fillna('000'))

[out]

                     Topic  Count          Groups
0           This is Python     39    Python_Group
1              This is SQL      6       SQL_Group
2   This is Paython Pandas     98    Python_Group
3           import tkinter     81    Python_Group
4          Learning Python     94    Python_Group
5              SQL Working     85       SQL_Group
6          Pandas and Work     67    Python_Group
7           This is Pandas     30    Python_Group
8                 Computer     20   Devices_Group
9              Mobile Work     55   Devices_Group
10            Smart Mobile     69   Devices_Group
user_102015800
Related