I have two dataframe df_X and df_Y, and I want to merge these two (df_X has 1000 rows) with merge (how='inner'), everything is fine when df_Y has 1000 rows but it is very slow with big dataframe (expected: 40_000_000 rows). I would like to speed up the calculations. I tried to use df1.join(df2) and sort them before the merge but with poor results. Any ideas to improve the code ? NB : I am working with Linux or MacOS computers / python 3.12 Here is reproductible example of my attempts : import pandas as pd import numpy as np import time match_columns = ['a', 'b', 'c', 'd', 'e'] extra_columns_X = ['X' + str(i) for i in range(9)] extra_columns_Y = ['Y' + str(i) for i in range(9)] # Define function to generate synthetic dataframes def generate_synthetic_data(): # Generate df1 with coarse resolution n_rows = 1000 # Desired number of rows df_X = pd.DataFrame({ match_columns[0]: np.linspace(0, 10000, n_rows), # Finer steps match_columns[1]: np.linspace(0, 5000, n_rows), match_columns[2]: np.linspace(10, 11000, n_rows), match_columns[3]: np.linspace(20, 12000, n_rows), match_columns[4]: np.linspace(30, 13000, n_rows), }) # Add some extra columns to df1 for col in extra_columns_X: df_X[col] = np.random.uniform(0, 1, len(df_X)) # Random values df_X = df_X.astype('float32') df_X = np.around(df_X, 2) # Generate df2 with finer resolution for match columns n_rows = 40000000 # Desired number of rows df_Y = pd.DataFrame({ match_columns[0]: np.linspace(0, 10000, n_rows), # Finer steps match_columns[1]: np.linspace(0, 5000, n_rows), match_columns[2]: np.linspace(10, 11000, n_rows), match_columns[3]: np.linspace(20, 12000, n_rows), match_columns[4]: np.linspace(30, 13000, n_rows), }) # This enforce common values in match_columns interval = n_rows // len(df_X) for i, col in enumerate(match_columns): df_Y.iloc[::interval, i] = df_X[col].values # Assign df_X values at regular intervals # Add some extra columns to df2 for col in extra_columns_Y: df_Y[col] = np.random.uniform(0, 1, len(df_Y)) # Random values df_Y = df_Y.astype('float32') df_Y = np.around(df_Y, 2) return df_X, df_Y # Generate the dataframes df_X, df_Y = generate_synthetic_data() # Timing for pd.merge start_time = time.time() merged_with_merge = pd.merge(df_X, df_Y, on=match_columns, how='inner') print("Merge Time:", time.time() - start_time) # # Timing for pd.merge # start_time = time.time() # df_X.sort_values(match_columns, inplace=True) # df_Y.sort_values(match_columns, inplace=True) # merged_with_merge = pd.merge(df_X, df_Y, on=match_columns, how='inner') # print("Merge Time if sorted:", time.time() - start_time) # Timing with dask import dask.dataframe as dd start_time = time.time() ddf_X = dd.from_pandas(df_X, npartitions=10) ddf_Y = dd.from_pandas(df_Y, npartitions=10) merged_with_dask = dd.merge(ddf_X, ddf_Y, on=match_columns, how='inner').compute() print("Merge Time with dask:", time.time() - start_time) # Timing for set_index + join start_time = time.time() df_X.sort_values(match_columns, inplace=True) df_Y.sort_values(match_columns, inplace=True) df_X_indexed = df_X.set_index(match_columns) df_Y_indexed = df_Y.set_index(match_columns) merged_with_join = df_X_indexed.join(df_Y_indexed, how='inner').reset_index() print("Join Time if sorted:", time.time() - start_time) # Results print("Rows Merged with Merge:", len(merged_with_merge)) print("Rows Merged with Join:", len(merged_with_join)) Continue reading...
