3
\$\begingroup\$

I have two dataframes, df which contains a list of members in addition to the type of contract that they purchased on a given date, df is about 10 000 entries. I have another df_prices which contains the average price of a given contract for a given year and month.

df resembles:

    Member Nbr      Date-Joined         Contract Type
1           1        2010-03-31     1 Year Membership
2           1        2011-04-16     1 Year Membership
3           1        2012-08-06     1 Year Membership
4           1        2013-08-21     1 Year Membership
5           1        2014-08-31     1 Year Membership
6           2        2015-09-03     1 Year Membership
7           2        2012-12-10     4 Month Membership
8           2        2013-03-13     1 Year Membership
9           3        2014-03-15     1 Year Membership
10          3        2010-02-09     1 Year Membership
...
10095    7374        2016-02-29     1 Month Membership
10096    7375        2016-03-01     1 Year Membership
10097    7376        2016-03-01     1 Month Membership
10098    7378        2016-03-04     1 Month Membership
10099    7379        2016-03-06     1 Month Membership
10100    7380        2016-03-05     1 Year Membership
10101    7387        2016-03-10     3 Month Membership

and df_prices

          Date                   Description      Amount
0   2010-01-31            1 Month Membership   54.036316
1   2010-01-31             1 Year Membership  325.000000
2   2010-01-31            4 Month Membership  147.642353
3   2010-01-31            7 Month Membership  227.890000
5   2010-02-28            1 Month Membership   55.283846
6   2010-02-28             1 Year Membership  333.250000
7   2010-02-28            4 Month Membership  146.257358
8   2010-02-28            6 Month Membership  165.000000
9   2010-02-28            7 Month Membership  223.905714
10  2010-02-28                     Pool Only  250.000000
...
462 2015-12-31            4 Month Membership  146.390000
463 2015-12-31            6 Month Membership  204.815000
464 2016-01-31            1 Month Membership   45.037143
465 2016-01-31             1 Year Membership  265.000000
466 2016-01-31            3 Month Membership  112.927273
467 2016-01-31            4 Month Membership  147.413333
468 2016-01-31            6 Month Membership  204.093333
469 2016-02-29            1 Month Membership   45.699444
470 2016-02-29             1 Year Membership  265.000000
471 2016-02-29            3 Month Membership  110.285556
472 2016-02-29            4 Month Membership  139.477500
473 2016-02-29            6 Month Membership  202.650000

I have written some code in python that fills a list based on a customers join date and contract type and adds it to df, which is exactly what I want. The problem that I'm facing is that is is extremely slow to finish, and was hoping for some help to optimize it, especially as I plan on using a this on a larger dataset.

Here is what I have:

#### Standard Libraries ####
import time

#### Third-party libraries ####
import pandas as pd
import numpy as np

def Main():
    start = time.time()
    location = '/home/lukasz/Documents/Xtreme Fitness/Members/AllMembers.xlsx'
    location2 = '/home/lukasz/Documents/Xtreme Fitness/' + \
                'AverageContractPrice.xlsx'

    df = pd.read_excel(location)
    df_prices = pd.read_excel(location2)
    df_prices.fillna(method='ffill',inplace=True)

    prices = []

    for i in range(len(df)):
        df_date = df.loc[i, 'Date-Joined'].year * 1000 + \
                  df.loc[i, 'Date-Joined'].month
        df_contract = df.loc[i, 'Member Type']

        for j in range(len(df_prices)):
            if (df_prices.loc[j, 'Date'].year * 1000 + \
                df_prices.loc[j, 'Date'].month == df_date and 
                df_prices.loc[j, 'Description'] == df_contract): 

                # print("%s, %s" % (i, df_prices.loc[j, 'Amount']))
                prices.append(df_prices.loc[j, 'Amount'])

    prices
    df.to_excel(location, index=False)
    end = time.time()
    print(end-start)

if __name__ == '__main__':
    Main()
\$\endgroup\$
  • \$\begingroup\$ Looking at the code and the description, it looks like that what is called "contract type" in df description is called "member type" in the code. Is that so? If yes, can you edit question so both match? \$\endgroup\$ – 409_Conflict Jul 15 '16 at 19:24
  • 1
    \$\begingroup\$ Also you don't seem to modify df at all in the for loop, so what is the purpose of writting it back in an excell file? \$\endgroup\$ – 409_Conflict Jul 15 '16 at 19:26
  • \$\begingroup\$ There's also a line consisting of just prices, which doesn't do anything. Overall it seems like you have not been very careful with this code, and I'd be hesitant to get started on optimizing it until I could be confident that I understood what it's doing and that it actually works correctly. \$\endgroup\$ – David Z Jul 16 '16 at 11:42
  • \$\begingroup\$ @David Z, prices is a an empty list that gets append with the values of a given contract type from a given date. prices then gets added to df \$\endgroup\$ – nonameswereavailable Jul 18 '16 at 22:15
  • \$\begingroup\$ @David Z to understand what is going on I have two separate excel files, one containing a list of members with the type of contract that they have purchased on a given date, and a second excel file with the prices of those contracts on a given date. Now I would like to include a column in my first file that contains those prices based on what type of contract they had. \$\endgroup\$ – nonameswereavailable Jul 18 '16 at 22:19
3
\$\begingroup\$

Consider a left side merge to match member data with corresponding prices by contract type and date. No for loops or external list needed for this approach:

df = pd.read_excel(location)
df_prices = pd.read_excel(location2)
df_prices.fillna(method='ffill',inplace=True)

finaldf = pd.merge(df, df_prices, left_on=['Date-Joined','Contract Type'],
                   right_on=['Date','Description'],
                   how='left')[['Member Nbr','Date-Joined','Contract Type','Amount']]

finaldf.to_excel(location, index=False)
\$\endgroup\$

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.