Pandas categorize a dataframe based on another dataframe with substrings-CodePudding

I'm trying to learn pandas and python to transfer some problems from excel to pandas/python. I have a big csv file from my bank with over 10000 records. I want to categorize the records based on the description. For that I have a big mapping file with keywords. In excel I used vLookup and I'm trying to get this solution into Pandas/python

So I can read the csv into a dataframe dfMain. One column (in dfMain) with text called description is for me input to categorize it based on an the mapping file called dfMap.

dfMain looks simplified something like this:

Datum       Bedrag  Description     
2020-01-01  -166.47 een cirkel voor je uit
2020-01-02  -171.79 even een borreling
2020-01-02  -16.52  stilte zacht geluid
2020-01-02  -62.88  een steentje in het water
2020-01-02  -30.32  gooi jij je zorgen weg
2020-01-02  -45.99  dan ben je laf weet je dat
2020-01-02  -322.44 je klaagt ook altijd over pech
2020-01-03  -4.80   jij kan niet ophouden zorgen
2020-01-07  5.00    de wereld te besnauwen

dfMap looks simplified like this

    sleutel     code
0   borreling   A1
1   zorgen      B2
2   steentje    C2
3   een         C1

dfMap contains keywords('sleutel') and a Category code ('code').

When the 'sleutel' is a substring of 'description' in dfMain an extra to be added column called 'category' in dfMain will get the value of the code. I'm aware that multiple keywords can apply to certain values of description but first come counts, in other words: the number of rows in dfMain must stay the same.

the resulting data frame must then look like this:

Out[34]:

Datum        Bedrag Description                     category        
2020-01-01  -166.47 een cirkel voor je uit          C1
2020-01-02  -171.79 even een borreling              A1
2020-01-02  -16.52  stilte zacht geluid             NaN
2020-01-02  -62.88  een steentje in het water       C2
2020-01-02  -30.32  gooi jij je zorgen weg          B2
2020-01-02  -45.99  dan ben je laf weet je dat      NaN
2020-01-02  -322.44 je klaagt ook altijd over pech  NaN
2020-01-03  -4.80   jij kan niet ophouden zorgen    B2
2020-01-07  5.00    de wereld te besnauwen          NaN

I tried a lot of things with join but can't get it to work.

CodePudding user response：

An efficient solution is to use a regex with extract and then to map the result:

regex = '(%s)' % dfMap['sleutel'].str.cat(sep='|')

dfMain['category'] = (
 dfMain['Description']
   .str.extract(regex, expand=False)
   .map(dfMap.set_index('sleutel')['code'])
 )

Output:

        Datum  Bedrag                     Description category
0  2020-01-01 -166.47          een cirkel voor je uit       C1
1  2020-01-02 -171.79              even een borreling       C1
2  2020-01-02  -16.52             stilte zacht geluid      NaN
3  2020-01-02  -62.88       een steentje in het water       C1
4  2020-01-02  -30.32          gooi jij je zorgen weg       B2
5  2020-01-02  -45.99      dan ben je laf weet je dat      NaN
6  2020-01-02 -322.44  je klaagt ook altijd over pech      NaN
7  2020-01-03   -4.80    jij kan niet ophouden zorgen       B2
8  2020-01-07    5.00          de wereld te besnauwen      NaN

The regex generated will end up as '(borreling|zorgen|steentje|een)'

CodePudding user response：

Try this:

import pandas as pd

# prepare the data

Datum = ['2020-01-01', '2020-01-02', '2020-01-02', '2020-01-02', '2020-01-02', '2020-01-02', '2020-01-02', '2020-01-03', '2020-01-03']
Bedrag = [-166.47, -171.79, -16.52, -62.88, -30.32, -45.99, -322.44, -4.80, 5.00]
Description = ["een cirkel voor je uit", "even een borreling", "stilte zacht geluid", "een steentje in het water",
               "gooi jij je zorgen weg", "dan ben je laf weet je dat", "je klaagt ook altijd over pech", "jij kan niet ophouden zorgen", "de wereld te besnauwen"]

dfMain = pd.DataFrame(Datum, columns=['Datum'])
dfMain['Bedrag'] = Bedrag
dfMain['Description'] = Description

sleutel = ["borreling", "zorgen", "steentje", "een"]
code = ["A1", "B2", "C2", "C1"]

dfMap = pd.DataFrame(sleutel, columns=['sleutel'])
dfMap['code'] = code
print(dfMap)
# solution

map_code = pd.Series(dfMap.code.values ,index=dfMap.sleutel).to_dict()


def extract_codes(row):
    for item in map_code:
        if item in row:
            return map_code[item]
    return "NaN"

dfMain['category'] = dfMain['Description'].apply(extract_codes)

print(dfMain)