How to optimize the merging of two huge csv files using Pandas and Dask

Expired 0
Viewed 47

I have two large csv files I gather from an api. 99.9% of the time, the files have the same number of rows and the same columns and data, except two or three columns that are different between the files. I m performing an outer merge on the files based on 4 columns.However the merge time takes a lot of time, ~8 minutes for two files of 2.7 gb each , for 4GB files it takes around ~12 minutes. How can I speed up the merge?

I use python 3.6.9 and dask 2021.3.0 on a server with 50GB RAM and 24 cores.I tried to set up indexes and merge on indexes but I got no improvement in how much it took. I cannot use apache parquet either. I get csv files and I need to export the data to a single csv file as well.

dask-dataframe dask pandas python-3.x
1 Answers

Ok, you can try like this : The solution is juste to create an unique id with the four columns and to get the row who does not match, which will be the same thing as doing an outer

import numpy as np 
import pandas as pd
classes = [('Carbon', '16.7', '1','9'),
         ('Pyruvate', '30', '7','8'),
         ('Lipid', '40.5', '9','10'),
         ('Galactose', '57', '10','12'),
         ('Fatty', '22', '4','14')]
labels = ['A','B', 'C','D']
df_1 = pd.DataFrame.from_records(classes, columns=labels)

classes_ = [('Carbon', '16.78', '1','9'),
         ('Pyruvate', '30', '7','8'),
         ('Lipid', '40.5', '9','10')]
labels = ['A','B', 'C','D']
df_2= pd.DataFrame.from_records(classes_ ,columns=labels)

#Create id : 
df_1['id_df_1']=df_1['A']+"_"+df_1['B']+'_'+df_1['C']+'_'+df_1['D']
df_2['id_df_2']=df_2['A']+"_"+df_2['B']+'_'+df_2['C']+'_'+df_2['D']

#Result for df1 and df2
df_2.loc[~df_2['id_df_2'].isin(list(df_1['id_df_1'].values)),:]
df_1.loc[~df_1['id_df_1'].isin(list(df_2['id_df_2'].values)),:]
DataSciRookie0
Related