Let's say I work for a company that hands out different types of loans. We are getting our loan information from from a big data mart from which I need to calculate some additional things to calculate if someone is in arrears or not, etc. Right now, for clarity's sake I have done this a rather dumb function that iterates over all rows (where all information over a loan is stored) by using the pd.DataFrame.apply(myFunc, axis=1) function, which is horribly slow off course.

Now that we are growing and that I get more and more data to process, I am starting to get concerned over performance. Below is an example of a function that I call a lot, and would like to optimize (some ideas that I have below). These functions are applied to a DataFrame which has (a.o.) the following fields:

  • Loan_Type : a field containing a string that determines the type of loan, we have many different names but it comes down to either 4 types (for this example); Type 1 and Type 2, and whether staff or not has this loan.

  • Activity_Date : The date the activity on the loan was logged (it's a daily loan activity table, if that tells you anything)

  • Product_Account_Status : The status given by the table to these loans (are they active, or some other status?) on the Activity_Date, this needs to be recalculated because it is not always calculated in the table (don't ask why it is like this, complete headache).

  • Activation_Date : The date the loan was activated

  • Sum_Paid_To_Date : The amount of money paid into the loan at the Activity_Date

  • Deposit_Amount : The deposit amount for the loan

  • Last_Paid_Date : The last date a payment was made into the loan.

So two example functions:

    def productType(x):
        # Determines the type of the product, for later aggregation purposes, and to determine the amount to be payable per day
        if ('Loan Type 1' in x['Loan_Type']) & (not ('Staff' in x['Loan_Type'])):
            return 'Loan1'
        elif ('Loan Type 2' in x['Loan_Type']) & (not ('Staff' in x['Loan_Type'])):
            return 'Loan2'
        elif ('Loan Type 1' in x['Loan_Type']) & ('Staff' in x['Loan_Type']):
            return 'Loan1Staff'
        elif ('Loan Type 2' in x['Loan_Type']) & ('Staff' in x['Loan_Type']):
            return 'Loan2Staff'
        elif ('Mobile' in x['Loan_Type']) | ('MM' in x['Loan_Type']):
            return 'Other'
            raise ValueError(
                'A payment plan is not captured in the code, please check it!')

This function is then applied to the DataFrame AllLoans which contains all loans I want to analyze at that moment, by using:

AllLoans['productType'] = AllLoans.apply(lambda x: productType(x), axis = 1)

Then I want to apply some other functions, one example of such a function is given below. This function determines whether the loan is blocked or not, depending on how long someone hasn't paid, and some other statuses that are important, but are currently stored in strings in the loan table. Examples of this are whether people are cancelled (for being blocked for too long), or some other statuses, we treat customers differently based on these tags.

def customerStatus(x):
    # Sets the customer status based on the column Product_Account_Status or
    # the days of inactivity

    if x['productType'] == 'Loan1':
        dailyAmount = 2
    elif x['productType'] == 'Loan2':
        dailyAmount = 2.5
    elif x['productType'] == 'Loan1Staff':
        dailyAmount = 1
    elif x['productType'] == 'Loan2Staff':
        dailyAmount = 1.5
        raise ValueError(
            'Daily amount to be paid could not be calculated, check if productType is defined.')

    if x['Product_Account_Status'] == 'Cancelled':
        return 'Cancelled'
    elif x['Product_Account_Status'] == 'Suspended':
        return 'Suspended'
    elif x['Product_Account_Status'] == 'Pending Deposit':
        return 'Pending Deposit'
    elif x['Product_Account_Status'] == 'Pending Allocation':
        return 'Pending Allocation'
    elif x['Outstanding_Balance'] == 0:
        return 'Finished Payment'
    # If this check returns True it means that Last_Paid_Date is zero/null, as
    # far as I can see this means that the customer has only paid the deposit
    # and is thus an FPD
    elif type(x['Date_Of_Activity'] - x['Last_Paid_Date']) != (pd.tslib.NaTType):
        if (((x['Date_Of_Activity'] - x['Last_Paid_Date']).days + 1) > 30) | ((((x['Date_Of_Activity'] - x['Last_Paid_Date']).days + 1) > 14) & ((x['Sum_Paid_To_Date'] - x['Deposit_Amount']) <= dailyAmount)):
            return 'Blocked'
        elif ((x['Date_Of_Activity'] - x['Last_Paid_Date']).days + 1) <= 30:
            return 'Active'
    # If this is True, the customer has not paid more than the deposit, so it
    # will fall on the age of the customer whether they are blocked or not
    elif type(x['Date_Of_Activity'] - x['Last_Paid_Date']) == (pd.tslib.NaTType):
        # The date is changed here to 14 because of FPD definition
        if ((x['Date_Of_Activity'] - x['Activation_Date']).days + 1) <= 14:
            return 'Active'
        elif ((x['Date_Of_Activity'] - x['Activation_Date']).days + 1) > 14:
            return 'Blocked'
    # If we have reached the end and still haven't found the status, it will
    # get the following status
    return 'Other Status'

This is again applied by using AllLoans['customerStatus'] = AllLoans.apply(lambda x: customerStatus(x), axis = 1). As you can see there are many string comparisons and date comparisons, which are a bit confusing for me on how I can 'properly' vectorize these functions.

Apologies if this is Optimization 101, but have tried to search for answers and strategies on how to do this, but couldn't find really comprehensive answers. I was hoping to get some tips here, thanks in advance for your time.

Some thoughts on making this faster/getting towards a more vectorized approach:

  • Make the customerStatus function slightly more modular by making a function that determines the daily amounts, and stores this in the dataframe for quicker access (I need to access them later anyway, and determine this variable in multiple functions).

  • Make the input column for the productType function into integers by using some sort of dict, so that fewer string functions need to called to this (but feel like this won't be my biggest speed up)

Some things that I would like to do but don't really know where to start on;

  • How to properly vectorize these functions that contain many if statements based on string/date comparisons (business rules can be a bit complex here) based on different columns in the dataframe. The code might become a bit more complex, but I need to apply these functions multiple times to slightly different (but importantly different) dataframes, and these are growing larger and larger so these functions need to be in some sort of library for ease of access, and the code needs to be speed up because it simply takes up to much time.

Have tried to search for some solutions like Numba or Cython but I don't understand enough of the inner workings of C to properly use this (or just yet, would like to learn). Any suggestions on how to improve performance would be greatly appreciated.

  • \$\begingroup\$ The suggestion to move this to CR was just one person's opinion. However I suspect the SO one may be closed as too broad. I suspect this is mostly a pandas issue. \$\endgroup\$ – hpaulj Mar 15 '17 at 11:22
  • \$\begingroup\$ I've found a good solution for this, when I have more time I'll post the solutions to this as a bit of a guide on how to do this... \$\endgroup\$ – Tim.Lucas Aug 30 '17 at 16:16
  • 1
    \$\begingroup\$ Well, what has been keeping you? \$\endgroup\$ – greybeard Nov 12 '19 at 9:52
  • \$\begingroup\$ @greybeard good question, I'll see if I can dig up the answer here as it's a while ago I tackled this. Instead of using if's, I used a couple of consecutive .loc operators in Pandas to use vectorization in a better way (instead of using apply). This speeds up things by quite a bit, but you have to make sure the right sequence of operations is applied, otherwise things get messy. Happy to write up more if you need it, let me know! \$\endgroup\$ – Tim.Lucas Nov 14 '19 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.