How to modify Pandas DataFrame based on reversed Dictionary values?

user_13667627 0
Viewed 85

Context:

I have a dictionary and a Dataframe.

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"])

Problem:

For each row of the Dataframe, I need to check if the value in the Dataframe["Title"] column contains one of the values in the dictionary list. If the list item is found in the value then the Dataframe["Category"] column should take the Key of the list where the match was found.

For example, "taxi" is a keyword in the dictionary list under the key "Transportation". Therefore the row that has "ny taxi" should have "Transportation" in the Category column.

Starting Dataframe:

     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

Desired Output:

         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
pandas numpy python
1 Answers

First idea is loop by dictionary and test joined values by | for regex or by Series.str.contains and set values by mask in 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

Or you can join all values of dictionary, use Series.str.extract for get first matched value and then mapping dictionary with changed keys with values in 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
user_29010020
Related