Create column of categories from a column of transactions using regular expressions within a dictionary
Question: I have a csv file containing banking information that I am importing as a
pandas DataFrame. I want to create a new column that contains the transaction
categories (e.g. income, expense, transfer), created from a dictionary
containing regular expressions to apply to the transaction descriptions.

For example,

    
    
    import pandas as pd
    import re
    
    data = pd.read_csv("data/transactions.csv", parse_dates=['Date'])
    

Here is ouput of the `data` DataFrame:

    
    
          Date                  Description   Amount
    2016-01-01          checkcard good food   -12.45
    2016-01-02              visa peppy lube   -30.34
    2016-01-05           deposit bank of me  5000.00
    2016-01-05             transfer to bank  2500.00
    2016-01-10         gift from aunt sally    25.00
    

Here are the regular expressions:

    
    
    income = re.compile('.*deposit|gift.*')
    expense = re.compile('good food|.*peppy lube.*')
    transfer = re.compile('.*transfer.*')
    

And here is the dictionary:

    
    
    catdict = {income: 'income',
               expense: 'expense',
               transfer: 'transfer'}
    

I want code that creates a new column named `Category` that uses the regular
expressions to assign the values of the dictionary to rows where the
`Description` column matches one of the regular expressions, so the result
would be:

    
    
          Date                  Description   Amount  Category
    2016-01-01          checkcard good food   -12.45   expense 
    2016-01-02              visa peppy lube   -30.34   expense
    2016-01-05           deposit bank of me  5000.00    income
    2016-01-05             transfer to bank  2500.00  transfer
    2016-01-10         gift from aunt sally    25.00    income
    

Ideally, this code would also insert 'RECONCILE' in the category column for
rows where no matches are found in the regular expressions.

I am new to python, and suspect there a pythonic way to do this I am missing.

Thanks in advance


Answer: You can define a function that map a string (description) to a category
according to your `regex`'s. The first time it matches a pattern, the function
returns the name of that category. It returns 'RECONCILE' if none matches.

    
    
    from collections import OrderedDict
    
    def category(s):
    
        catdict = OrderedDict([(income, 'income'),
                              (expense, 'expense'),
                              (transfer, 'transfer'),
                              ])
        for ptn, name in catdict.iteritems():
            if ptn.search(s):
                return name
        return 'RECONCILE'
    

Then you can apply this function to the 'Description' column.

    
    
    data['Category'] = data.Description.map(category)
    
    print data
    

And this should give you what you want.

