I have a CSV file with 400 000 rows and the following headers:

header_names = ['LEAGUE', 'YEAR', 'DATE', 'HOME', '1', 'X', '2', 'AWAY', 'SCORE', 'SCORE_1', 'SCORE_2', 'FTR', 'FAVORITE', 'UNDER-OVER'] 

The aim of my function is for every row to take all the previous, filter them by items in the current row and return some statistic.

This is my script so far:

import pandas as pd

filepath = 'data.csv'
header_names = ['LEAGUE', 'YEAR', 'DATE', 'HOME', '1', 'X', '2', 'AWAY', 'SCORE', 'SCORE_1', 'SCORE_2', 'FTR', 'FAVORITE', 'UNDER-OVER'] # Add appropriate headers
df = pd.read_csv(filepath, sep=',', na_values=['', '-'], parse_dates=True, header=None, names=header_names, skiprows=1, nrows=1000)

def mid_func(x):
    global mid
    mid += 1
    return mid

mid = -1
df.insert(0, 'MID', df.apply(mid_func, axis=1))

new_df = df.copy()

def home_1_simple_filter(x):
    mid_stop = x[0] - 1
    home = x[4]
    odd_1 = x[5]
    start = time.time()
    filtered = df[(df['HOME'] == home) & (df['1'] == odd_1)].ix[:mid_stop]['FTR']
    stop = time.time() - start
    print round(stop*1000.,2), 'ms', home, odd_1, mid_stop
    return filtered

start = time.time()
new_df['HOME_1'] = df.apply(home_1_simple_filter, axis=1)
stop = time.time() - start
print stop

The mid_func is to help me take the previous row. The whole process takes 3 seconds for the first 1000, and 0.002 seconds on average.

  • \$\begingroup\$ 0.002 seconds per row doesn't seem like much. Do you have some target time in mind? Have you profiled the code to see where the time goes (I would guess that reading in the CSV will be a big chunk of it, which you can't speed up by altering your filter)? \$\endgroup\$ – jonrsharpe Aug 16 '14 at 10:41
  • \$\begingroup\$ There will be 60 filter operations like the one I mentioned, for each one of the 400 000 rows, so the overall time needed would be in hours. \$\endgroup\$ – GiannisIordanou Aug 16 '14 at 10:47
  • \$\begingroup\$ Verify your indentation in mid_func()? \$\endgroup\$ – 200_success Aug 17 '14 at 5:50
  • \$\begingroup\$ What do you mean about the indentation ? \$\endgroup\$ – GiannisIordanou Aug 23 '14 at 22:48
  • \$\begingroup\$ @evil_inside indentation is the space before lines \$\endgroup\$ – Caridorc Jul 22 '15 at 19:03

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.