Merging dataframes in Pandas is taking a surprisingly long time

Question

I'm trying to merge a list of time series dataframes (could be over 100) using Pandas. The largest file has a size of $\approx$ 50 MB. The number of rows and columns vary (for instance, one file could have 45,000 rows and 20 columns, another has 100 rows and 900 columns), but they all have common columns of "SubjectID" and "Date", which I'm using to merge the dataframes. I read the dataframes into the list from CSV files, where I know the datatypes for each column.

However, when I try to merge even 10 of the dataframes, it takes around 7 hours, and for all 100, my kernel crashes. Reading all the files in takes about a minute. These aren't files that I would consider "big data" or even large files, like in the posts here, here, or here, so I'm surprised it's taking so long to merge them all. For all 100 dataframes, I'd expect the final dataframe size to be about 1.5 GB, but even that I think Pandas could handle. My code to merge the dataframes is

merged_df = reduce(lambda l,r: l.merge(r, on=['SubjectID', 'Date'], how='outer', suffixes=['_COPYL', '_COPYR']), df_list)

because I want columns to be added on at if they don't already exist for a subjectID and date (I take care of duplicates later).

EDIT

Some sample dataframes are produced by the following code

import numpy as np
import pandas as pd
from functools import reduce

df1 = pd.DataFrame({'SubjectID': ['A', 'A', 'A', 'B', 'B', 'C', 'A'], 'Date': ['2010-03-14', '2010-03-15', '2010-03-16', '2010-03-14','2010-05-15', '2010-03-14', '2010-03-14'], 'Var1': [np.nan, 1, 4, 7, 90, np.nan, 9], 'Var2': [np.nan, 0, 1, 1, 0, np.nan, 1]})
df2 = pd.DataFrame({'SubjectID': ['A', 'A', 'B', 'B', 'C', 'A'], 'Date': ['2010-03-14', '2010-03-15', '2010-03-14', '2010-05-15', '2010-03-14', '2010-03-14'], 'Var2': [ np.nan, 0, 1, 1, np.nan, 1], 'Var3': [0, 0, 1, np.nan, 0, 1]})

df3 = pd.DataFrame({'SubjectID': ['A', 'A', 'A', 'B', 'B', 'C'], 'Date':['2010-03-14', '2010-03-15', '2010-03-16', '2010-03-14', '2010-05-15', '2010-03-14'], 'Var3': [np.nan, 1, 0, np.nan, 0, 1]})

df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])
df3['Date'] = pd.to_datetime(df3['Date'])

My code to merge the dataframes is

df_list = [df1, df2, df3]
merged_df = reduce(lambda l,r: l.merge(r, on=['SubjectID', 'Date'], how='outer', suffixes=['_COPYL', '_COPYR']), df_list)
merged_df = merged_df.groupby([x.split('_COPY')[0] for x in merged_df.columns], 1).apply(lambda x: x.mode(1)[0])
merged_df['Date'] = pd.to_datetime(merged_df['Date'])

My expected output is

            Date    SubjectID   Var1    Var2    Var3
0     2010-03-14           A    NaN      NaN    0.0
1     2010-03-14           A    NaN      1.0    1.0
2     2010-03-14           A    9.0      1.0    0.0
3     2010-03-14           A    9.0      1.0    1.0
4     2010-03-15           A    1.0      0.0    0.0
5     2010-03-16           A    4.0      1.0    0.0
6     2010-03-14           B    7.0      1.0    1.0
7     2010-05-15           B    90.0     0.0    0.0
8     2010-03-14           C    NaN      NaN    0.0

So far, I've tried reducing the memory of each column datatype by downcasting, different ways to merge in Pandas and CSV files, converting to a sparse dataframe (my data does have a fair amount of NaN), using Dask, and I'm at the point where I'm considering creating a relational database using sqllite in Python. My versions are python-64 3.7.0, pandas 0.23.4, Ipython 6.5.0, using a Mac OSX Darwin with 32 GB memory. I would think my computer should have no trouble doing this, but it does. Is there really no better way or am I doing something wrong that I'm missing?

Answer 1

There are a couple of points I can maybe give to help.

Firstly, Pandas is not great at merging multiple large dataframes in general because every time you merge a new dataframe to an old one, it makes a copy of both to make a third dataframe - this obviously starts taking a lot of time as your master dataframe grows in each step.

Secondly (untested), you could try to overcome any memory bottlenecks in the merging steps by trying something dumb, like merging two or three files, then writing it to disk. Repeat that until you have 30 larger CSV files, then do it again so you have, say 10 files, and so on. I am not sure at which point the python garbage collection would kick in with you single reduce function, so it could even be the case that you start using SWAP memory, which would make things really slow! This solution could at least prevent that from happening. You should perform each repeat in a new Python session.

One other idea, as you are reading the data from CSV files: you could use a terminal tool to simply paste all the CSV files into one file, then read that into pandas once. Then perform required clean up actions. The cleaning might be a pain (given your merge on requirements, but you would likely overcome the long waiting times for pandas to make the copies. If you files are all in one folder, you can run the following in terminal to put them all in one file

cat *.csv > merged.csv

There are other methods here, in case you need to leave out the header line etc.

Lastly I would recommend having a look at the data.table package, which is now functional in Python - it started in the R world. It has a much more robust memory management system (built on ideas from databases) and is able to store huge amounts of data in a single "DataTable", in cases where Pandas would crash. It is a great package to fill the gap between Pandas and Dask (both awesome packages!).

Here is a video, showing how Python data.table works and also shows some benchmarks.

Answer 2

I ended up using the native csv library to read in the files row by row and store each row into a nested dictionary of lists. I also wrote a function to check if 1) the row already exists in the dictionary and if it does, move on to the next row, 2) if the row doesn't exist, see if it can be merged on a column with an existing row and merge if possible, otherwise, add it as a new row to the dictionary. This method works much faster since the whole file isn't being read in and stored at once, and dictionaries use less memory than Pandas dataframes.

n1k31t4's first point was why it was taking so long and crashing the first time. I could also have use SQLite or another relational database package, but I didn't since I don't have a server to really store everything. I think that would be the ideal though. Hope this helps someone.