python-3.x - 如何通过搜索列表中给出的关键字值在 Python pandas 数据框中添加新列？

Question

我想根据 Identified 关键字在数据框中添加新列:
这是当前数据(数据帧名称 = 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

我想要的输出如下

    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

如何识别Groups列值
在 Topics 中基于以下身份创建的组柱子。
如果在 Topics 中找到特定词那么特定的组名需要分配给它
来自 Topic 的关键字列表栏目

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

我已经尝试过下面的代码:

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)

上面的代码也给了我想要的输出，但我想让它更短更快速，因为在上面提到的数据帧中几乎有 2MM+ 记录，我很难编写 1k+ 行代码来定义分组。
有什么方法可以使用 关键词一览属于 Topic柱子？
或
任何可以在此迭代过程中帮助我的自定义函数？
代码:2 咨询堆栈溢出专家后尝试的另一个以下代码:

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'))

错误:

---------------------------------------------------------------------------
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

谢谢你的帮助。

Answer 1

一种方法是考虑在 dict 中维护您的组和关键字。 :

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

从这里，您可以轻松地将其反转为“关键字:组” 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'}

那么你可以使用 Series.str.extract 使用正则表达式和 map 查找这些关键字他们到正确的组。使用 fillna 捕获任何不匹配的组。

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

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

[出去]

                     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