Why does performance decrease with the size of the data frame?

user_9414906 0
Viewed 147

I'm working with a relatively large dataset (approx 5m observations, made up of about 5.5k firms).

I needed to run OLS regressions with a 60 month rolling window for each firm. I noticed that the performance was insanely slow when I ran the following code:

for idx, sub_df in master_df.groupby("firm_id"):
    # OLS code

However, when I first split my dataframe into about 5.5k dfs and then iterated over each of the dfs, the performance improved dramatically.

grouped_df = master_df.groupby("firm_id")
df_list = [group for group in grouped_df]

for df in df_list:
    my_df = df[1]
    # OLS code

I'm talking 1-2 weeks of time (24/7) to complete in the first version compared to 8-9 hours tops.

Can anyone please explain why splitting the master df into N smaller dfs and then iterating over each smaller df performs better than iterating over the same number of groups within the master df?

Thanks ever so much!

pandas memory python performance
1 Answers

I'm unable to reproduce your observation. Here's some code that generates data and then times the direct and indirect methods separately. The time taken is very similar in either case.

Is it possible that you accidentally sorted the dataframe by the group key between the runs? Sorting by group key results in a noticeable difference in run time.

Otherwise, I'm beginning to think that there might be some other differences in your code. It would be great if you could post the full code.

import numpy as np
import pandas as pd
from datetime import datetime

def generate_data():
  ''' returns a Pandas DF with columns 'firm_id' and 'score' '''
  # configuration
  np.random.seed(22)
  num_groups = 50000 # number of distinct groups in the DF
  mean_group_length = 200 # how many records per group?
  cov_group_length = 0.10 # throw in some variability in the num records per group
  
  # simulate group lengths
  stdv_group_length = mean_group_length * cov_group_length
  group_lengths = np.random.normal(
    loc=mean_group_length,
    scale=stdv_group_length,
    size=(num_groups,)).astype(int)
  group_lengths[group_lengths <= 0] = mean_group_length
  
  # final length of DF
  total_length = sum(group_lengths)
  
  # compute entries for group key column
  firm_id_list = []
  for i, l in enumerate(group_lengths):
    firm_id_list.extend([(i + 1)] * l)
    
  # construct the DF; data column is 'score' populated with Numpy's U[0, 1)
  result_df = pd.DataFrame(data={
      'firm_id': firm_id_list,
      'score': np.random.rand(total_length)
    })
    
  # Optionally, shuffle or sort the DF by group keys
  
  # ALTERNATIVE 1: (badly) unsorted df
  result_df = result_df.sample(frac=1, random_state=13).reset_index(drop=True)
  # ALTERNATIVE 2: sort by group key
  # result_df.sort_values(by='firm_id', inplace=True)
  return result_df

def time_method(df, method):
  ''' time 'method' with 'df' as its argument '''
  t_start = datetime.now()
  method(df)
  t_final = datetime.now()
  delta_t = t_final - t_start
  print(f"Method '{method.__name__}' took {delta_t}.")
  return

def process_direct(df):
  ''' direct for-loop over groupby object '''
  for group, df in df.groupby('firm_id'):
    m = df.score.mean()
    s = df.score.std()
  return

def process_indirect(df):
  ''' indirect method: generate groups first as list and then loop over list '''
  grouped_df = df.groupby('firm_id')
  group_list = [pair for pair in grouped_df]
  for pair in group_list:
    m = pair[1].score.mean()
    s = pair[1].score.std()

df = generate_data()
time_method(df, process_direct)
time_method(df, process_indirect)
user_17069180
Related