Dataframe Updating a column to category name based on a list of string values in that category

user_11661267 0
Viewed 228

I have lists that are categorized by name, such as:

dining = ['CARLS', 'SUBWAY', 'PIZZA']
bank = ['TRANSFER', 'VENMO', 'SAVE AS YOU GO']

and I want to update a new column to the category name if any of those strings are found in the other column. An example from my other question here, I have the following data set (an example bank transactions list):

import pandas as pd
import numpy as np

dining = ['CARLS', 'SUBWAY', 'PIZZA']
bank = ['TRANSFER', 'VENMO', 'SAVE AS YOU GO']

data = [
    [-68.23 , 'PAYPAL TRANSFER'],
    [-12.46, 'RALPHS #0079'],
    [-8.51, 'SAVE AS YOU GO'],
    [25.34, 'VENMO CASHOUT'],
    [-2.23 , 'PAYPAL TRANSFER'],
    [-64.29 , 'PAYPAL TRANSFER'],
    [-7.06, 'SUBWAY'],
    [-7.03, 'CARLS JR'],
    [-2.35, 'SHELL OIL'],
    [-35.23, 'CHEVRON GAS']
]

df = pd.DataFrame(data, columns=['amount', 'details'])
df['category'] = np.nan
df

    amount  details             category
0   -68.23  PAYPAL TRANSFER     NaN
1   -12.46  RALPHS #0079        NaN
2   -8.51   SAVE AS YOU GO      NaN
3   25.34   VENMO CASHOUT       NaN
4   -2.23   PAYPAL TRANSFER     NaN
5   -64.29  PAYPAL TRANSFER     NaN
6   -7.06   SUBWAY              NaN
7   -7.03   CARLS JR            NaN
8   -2.35   SHELL OIL           NaN
9   -35.23  CHEVRON GAS         NaN

Is there an efficient way for me update the category column to either 'dining' or 'bank' based on if the strings in the list are found in data.details?

I.e. Desired Output:
    amount  details             category
0   -68.23  PAYPAL TRANSFER     bank
1   -12.46  RALPHS #0079        NaN
2   -8.51   SAVE AS YOU GO      bank
3   25.34   VENMO CASHOUT       bank
4   -2.23   PAYPAL TRANSFER     bank
5   -64.29  PAYPAL TRANSFER     bank
6   -7.06   SUBWAY              dining
7   -7.03   CARLS JR            dining
8   -2.35   SHELL OIL           NaN
9   -35.23  CHEVRON GAS         NaN

From my previous question, so far I'm assuming I need to work with a new list that I create by using str.extract.

pandas dataframe numpy python
2 Answers

We can do this with np.select since we have multiple conditions:

dining = '|'.join(dining)
bank = '|'.join(bank)

conditions = [
    df['details'].str.contains(f'({dining})'),
    df['details'].str.contains(f'({bank})')
]

choices = ['dining', 'bank']

df['category'] = np.select(conditions, choices, default=np.NaN)

   amount          details category
0  -68.23  PAYPAL TRANSFER     bank
1  -12.46     RALPHS #0079      nan
2   -8.51   SAVE AS YOU GO     bank
3   25.34    VENMO CASHOUT     bank
4   -2.23  PAYPAL TRANSFER     bank
5  -64.29  PAYPAL TRANSFER     bank
6   -7.06           SUBWAY   dining
7   -7.03         CARLS JR   dining
8   -2.35        SHELL OIL      nan
9  -35.23      CHEVRON GAS      nan
user_90812670

You can do with findall + dict map 

sub = {**dict.fromkeys(dining, 'dining'), **dict.fromkeys(bank, 'bank')}
df.details.str.findall('|'.join(sub)).str[0].map(sub)
Out[146]: 
0      bank
1       NaN
2      bank
3      bank
4      bank
5      bank
6    dining
7    dining
8       NaN
9       NaN
Name: details, dtype: object

#df['category'] = df.details.str.findall('|'.join(sub)).str[0].map(sub)
user_79645270
Related