python - 如何根据反向字典值修改 Pandas DataFrame？

Question

上下文:
我有一本字典和一个数据框。

categories = { "Transport": ["taxi", "b u s", "bike"],
               "Housing": ["r-ent","jysk", "ikea"]}

data = { "Date": ["2020-09-29", "2020-09-29", "2020-09-29"],
         "Amount": [-28.0, -20.0 , -13.7],
         "Title": ["ny taxi", "brooklyn*ikea", "Burger Joint Co"],
         "Category": [None, None, None]}

df = pd.DataFrame(data, columns = ["Date", "Amount", "Title", "Category"])

问题:
对于 Dataframe 的每一行，我需要检查 Dataframe["Title"] 列中的值是否包含字典列表中的值之一。如果在值中找到列表项，则 Dataframe["Category"] 列应采用找到匹配项的列表的键。
例如，“taxi”是字典列表中关键字“Transportation”下的关键字。因此，具有“ny 出租车”的行应在“类别”列中包含“交通”。
起始数据帧:

     Date  Amount            Title     Category
0  2020-09-29   -28.0          ny taxi     None
1  2020-09-29   -20.0    brooklyn*ikea     None
2  2020-09-29   -13.7  Burger Joint Co     None

所需输出:

         Date  Amount            Title   Category
0  2020-09-29   -28.0          ny taxi  Transport
1  2020-09-29   -20.0    brooklyn*ikea    Housing
2  2020-09-29   -13.7  Burger Joint Co    Missing

Answer 1

第一个想法是按字典循环并通过 | 测试连接值正则表达式 or来自 Series.str.contains 并在 DataFrame.loc 中通过掩码设置值:

for k, v in categories.items():
    df.loc[df['Title'].str.contains('|'.join(v)), 'Category'] = k
 
df['Category'] = df['Category'].fillna('Missing')
 
print (df)
         Date  Amount            Title   Category
0  2020-09-29   -28.0          ny taxi  Transport
1  2020-09-29   -20.0    brooklyn*ikea    Housing
2  2020-09-29   -13.7  Burger Joint Co    Missing

或者你可以加入字典的所有值，使用 Series.str.extract 获取第一个匹配的值，然后使用更改的键映射字典，其中的值位于 Series.map :

pat = r'({})'.format('|'.join(x for k, v in categories.items() for x in v))
d = {k: oldk for oldk, oldv in categories.items() for k in oldv}
df['Category'] =  df['Title'].str.extract(pat, expand=False).map(d).fillna('Missing')
print (df)
         Date  Amount            Title   Category
0  2020-09-29   -28.0          ny taxi  Transport
1  2020-09-29   -20.0    brooklyn*ikea    Housing
2  2020-09-29   -13.7  Burger Joint Co    Missing