Outer merge on large pandas DataFrames causes MemoryError---how to do "big data" merges with pandas?
<p>I have two pandas DataFrames <code>df1</code> and <code>df2</code> with a fairly standard format:</p>

<pre><code>   one  two  three   feature
A    1    2      3   feature1
B    4    5      6   feature2  
C    7    8      9   feature3   
D    10   11     12  feature4
E    13   14     15  feature5 
F    16   17     18  feature6 
...
</code></pre>

<p>And the same format for <code>df2</code>. The sizes of these DataFrames are around 175MB and 140 MB. </p>

<pre><code>merged_df = pd.merge(df1, df2, on='feature', how='outer', suffixes=('','_features'))
</code></pre>

<p>I get the following MemoryError: </p>

<pre><code>File "/nfs/sw/python/python-3.5.1/lib/python3.5/site-packages/pandas/tools/merge.py", line 39, in merge
    return op.get_result()
File "/nfs/sw/python/python-3.5.1/lib/python3.5/site-packages/pandas/tools/merge.py", line 217, in get_result
    join_index, left_indexer, right_indexer = self._get_join_info()
File "/nfs/sw/python/python-3.5.1/lib/python3.5/site-packages/pandas/tools/merge.py", line 353, in _get_join_info
    sort=self.sort, how=self.how) 
File "/nfs/sw/python/python-3.5.1/lib/python3.5/site-packages/pandas/tools/merge.py", line 559, in _get_join_indexers
    return join_func(lkey, rkey, count, **kwargs)
File "pandas/src/join.pyx", line 187, in pandas.algos.full_outer_join (pandas/algos.c:61680)
  File "pandas/src/join.pyx", line 196, in pandas.algos._get_result_indexer (pandas/algos.c:61978)
MemoryError
</code></pre>

<p>Is it possible there is a "size limit" for pandas dataframes when merging? I am surprised that this wouldn't work. Maybe this is a bug in a certain version of pandas?</p>

<p>EDIT: As mentioned in the comments, many duplicates in the merge column can easily cause RAM issues. See: <a href="http://stackoverflow.com/questions/32750970/python-pandas-merge-causing-memory-overflow">Python Pandas Merge Causing Memory Overflow</a></p>

<p>The question now is, how can we do this merge? It seems the best way would be to partition the dataframe, somehow.</p>

<p>You can try first filter <code>df1</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unique.html" rel="nofollow"><code>unique</code></a> values, <code>merge</code> and last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow"><code>concat</code></a> output.</p>

<p>If need only outer join, I think there is memory problem also. But if add some other code for filter output of each loop, it can works.</p>

<pre><code>dfs = []
for val in df.feature.unique():
    df1 = pd.merge(df[df.feature==val], df2, on='feature', how='outer', suffixes=('','_key'))
    #http://stackoverflow.com/a/39786538/2901002
    #df1 = df1[(df1.start &lt;= df1.start_key) &amp; (df1.end &lt;= df1.end_key)]
    print (df1)
    dfs.append(df1)

df = pd.concat(dfs, ignore_index=True)
print (df)
</code></pre>

<hr>

<p>Other solution is use <a href="http://dask.pydata.org/en/latest/dataframe-api.html#dask.dataframe.DataFrame.merge" rel="nofollow"><code>dask.dataframe.DataFrame.merge</code></a>.</p>

python pandas memory dataframe out-of-memory