title: Time-Efficient Wide to Long Conversion Pandas
body: <p>I have an dataset of around 54 million rows that I need to read from a tab-delimited text file, convert from wide to long format, and write to a new text file.  The data is too large to fit in memory, so I've been using iterators.  There are three separate variables that I want to move from wide to long, so I have been using three of them separate iterators</p>
  
  <pre><code>import pandas as pd
  import itertools as it
  
  filename = "C:/example.txt"
  iter_a = pd.read_table(filename, iterator=True, usecols=col_list_1, chunksize=100000)
  iter_b = pd.read_table(filename, iterator=True, usecols=col_list_2, chunksize=100000)
  iter_c = pd.read_table(filename, iterator=True, usecols=col_list_3, chunksize=100000)
  </code></pre>
  
  <p>Where all usecols lists contain a common identifier and otherwise distinct columns. Column names are based on year and attribute, so the column lists could look like:</p>
  
  <pre><code>col_list_1 = ['Key', 'A90', 'A91', 'A92']
  col_list_2 = ['Key', 'B90', 'B91', 'B92']
  col_list_3 = ['Key', 'C90', 'C91', 'C92']
  </code></pre>
  
  <p>And I want to change all the column names to just the years without the leading char/chars, and melt on the years only.</p>
  
  <pre><code>new_colnames = ['Key', '1990', '1991', '1992']
  melt_values = ['1990', '1991', '1992']
  
  for achunk, bchunk, cchunk in it.izip(iter_a, iter_b, iter_c):
      achunk.columns = new_colnames
      bchunk.columns = new_colnames
      cchunk.columns = new_colnames
  
      melted_a = pd.melt(achunk, id_vars='Key', value_vars=melt_values, var_name='Year', value_name='A').set_index(['Key', 'Year']).sort_index()
      melted_b = pd.melt(bchunk, id_vars='Key', value_vars=melt_values, var_name='Year', value_name='B').set_index(['Key', 'Year']).sort_index()
      melted_c = pd.melt(cchunk, id_vars='Key', value_vars=melt_values, var_name='Year', value_name='C').set_index(['Key', 'Year']).sort_index()
  
      join1 = melted_a.join(melted_b, how='outer')
      join2 = join1.join(melted_c, how='outer')
      join2.dropna(inplace=True, how='all')
  
      join2.to_csv('C:/output_example.txt', sep='\t')
  </code></pre>
  
  <p>While this does work, it takes an extremely long time.  I clocked one iteration with a 100,000 line chunk at 15 seconds.  Assuming it scales linearly, it seems that I'm looking at 2.25 hours runtime.  Is there any good way that I can speed this up? Maybe using either numpy arrays or multiprocessing?</p>