title: Efficient way to merge multiple large DataFrames
body: <p>Suppose I have 4 small DataFrames</p>
  
  <p><code>df1</code>, <code>df2</code>, <code>df3</code> and <code>df4</code></p>
  
  <pre><code>import pandas as pd
  from functools import reduce
  import numpy as np
  
  df1 = pd.DataFrame([['a', 1, 10], ['a', 2, 20], ['b', 1, 4], ['c', 1, 2], ['e', 2, 10]])
  df2 = pd.DataFrame([['a', 1, 15], ['a', 2, 20], ['c', 1, 2]])
  df3 = pd.DataFrame([['d', 1, 10], ['e', 2, 20], ['f', 1, 1]])  
  df4 = pd.DataFrame([['d', 1, 10], ['e', 2, 20], ['f', 1, 15]])   
  
  
  df1.columns = ['name', 'id', 'price']
  df2.columns = ['name', 'id', 'price']
  df3.columns = ['name', 'id', 'price']    
  df4.columns = ['name', 'id', 'price']   
  
  df1 = df1.rename(columns={'price':'pricepart1'})
  df2 = df2.rename(columns={'price':'pricepart2'})
  df3 = df3.rename(columns={'price':'pricepart3'})
  df4 = df4.rename(columns={'price':'pricepart4'})
  </code></pre>
  
  <p>Create above are the 4 DataFrames, what I would like is in the code below.</p>
  
  <pre><code># Merge dataframes
  df = pd.merge(df1, df2, left_on=['name', 'id'], right_on=['name', 'id'], how='outer')
  df = pd.merge(df , df3, left_on=['name', 'id'], right_on=['name', 'id'], how='outer')
  df = pd.merge(df , df4, left_on=['name', 'id'], right_on=['name', 'id'], how='outer')
  
  # Fill na values with 'missing'
  df = df.fillna('missing')
  </code></pre>
  
  <p>So I have achieved this for 4 DataFrames that don't have many rows and columns.</p>
  
  <p><strong>Basically, I want to extend the above outer merge solution to MULTIPLE (48) DataFrames of size 62245 X 3:</strong></p>
  
  <p>So I came up with this solution by building from another StackOverflow answer that used a lambda reduce:</p>
  
  <pre><code>from functools import reduce
  import pandas as pd
  import numpy as np
  dfList = []
  
  #To create the 48 DataFrames of size 62245 X 3
  for i in range(0, 49):
  
      dfList.append(pd.DataFrame(np.random.randint(0,100,size=(62245, 3)), columns=['name',  'id',  'pricepart' + str(i + 1)]))
  
  
  #The solution I came up with to extend the solution to more than 3 DataFrames
  df_merged = reduce(lambda  left, right: pd.merge(left, right, left_on=['name', 'id'], right_on=['name', 'id'], how='outer'), dfList).fillna('missing')
  </code></pre>
  
  <p>This is causing a <code>MemoryError</code>. </p>
  
  <p>I do not know what to do to stop the kernel from dying.. I've been stuck on this for two days.. Some code for the EXACT merge operation that I have performed that does not cause the <code>MemoryError</code> or something that gives you the same result, would be really appreciated.</p>
  
  <p>Also, the 3 columns in the main DataFrame (NOT the reproducible 48 DataFrames in the example) are of type <code>int64</code>, <code>int64</code> and <code>float64</code> and I'd prefer them to stay that way because of the integer and float that it represents. </p>
  
  <p>EDIT:</p>
  
  <p>Instead of iteratively trying to run the merge operations or using the reduce lambda functions, I have done it in groups of 2! Also, I've changed the datatype of some columns, some did not need to be <code>float64</code>. So I brought it down to <code>float16</code>. It gets very far but still ends up throwing a <code>MemoryError</code>.</p>
  
  <pre><code>intermediatedfList = dfList    
  
  tempdfList = []    
  
  #Until I merge all the 48 frames two at a time, till it becomes size 2
  while(len(intermediatedfList) != 2):
  
      #If there are even number of DataFrames
      if len(intermediatedfList)%2 == 0:
  
          #Go in steps of two
          for i in range(0, len(intermediatedfList), 2):
  
              #Merge DataFrame in index i, i + 1
              df1 = pd.merge(intermediatedfList[i], intermediatedfList[i + 1], left_on=['name',  'id'], right_on=['name',  'id'], how='outer')
              print(df1.info(memory_usage='deep'))
  
              #Append it to this list
              tempdfList.append(df1)
  
          #After DataFrames in intermediatedfList merging it two at a time using an auxillary list tempdfList, 
          #Set intermediatedfList to be equal to tempdfList, so it can continue the while loop. 
          intermediatedfList = tempdfList 
  
      else:
  
          #If there are odd number of DataFrames, keep the first DataFrame out
  
          tempdfList = [intermediatedfList[0]]
  
          #Go in steps of two starting from 1 instead of 0
          for i in range(1, len(intermediatedfList), 2):
  
              #Merge DataFrame in index i, i + 1
              df1 = pd.merge(intermediatedfList[i], intermediatedfList[i + 1], left_on=['name',  'id'], right_on=['name',  'id'], how='outer')
              print(df1.info(memory_usage='deep'))
              tempdfList.append(df1)
  
          #After DataFrames in intermediatedfList merging it two at a time using an auxillary list tempdfList, 
          #Set intermediatedfList to be equal to tempdfList, so it can continue the while loop. 
          intermediatedfList = tempdfList 
  </code></pre>
  
  <p>Is there any way I can optimize my code to avoid <code>MemoryError</code>, I've even used AWS 192GB RAM (I now owe them 7$ which I could've given one of yall), that gets farther than what I've gotten, and it still throws <code>MemoryError</code> after reducing a list of 28 DataFrames to 4..</p>