title: Python numpy MemoryError - Loading multiple CSV files into HDF5 store and reading into DataFrame
body: <p>(Using Python 3.3 and Pandas 0.12)</p>
  
  <p>My question consists of two parts.</p>
  
  <h1>First</h1>
  
  <p>I'm trying to iteratively read/append multiple csv files - that amount to about 8GB in total - into a HDF5 store based on <a href="https://stackoverflow.com/questions/14262433/large-data-work-flows-using-pandas">this solution</a> and <a href="https://stackoverflow.com/questions/16997048/how-does-one-append-large-amounts-of-data-to-a-pandas-hdfstore-and-get-a-natural">this solution</a> for creating a unique index. Why I started to do this is because I read that doing so would result in a file that would be fast accessible and relatively small in size, and thus to be able to read into memory. However as it turns out I get a h5 file that is 18GB large. My (Windows) laptop has 8GB of RAM.
  My first question is why the resulting h5 is much larger than the sum of the original csv files?
  My second question is why do I not indeed get a unique index on the table?</p>
  
  <p>My code is the following:</p>
  
  <pre><code>def to_hdf(path):
      """ Function that reads multiple csv files to HDF5 Store """
      # If path exists delete it such that a new instance can be created
      if os.path.exists(path):
          os.remove(path)
      # Creating HDF5 Store
      store = pd.HDFStore(path)
  
      # Reading csv files from list_files function
      with pd.get_store(path) as store:
          for f in list_files():
              try:
                  # Creating reader in chunks -- reduces memory load
                  df = pd.read_csv(f, encoding='utf-8', chunksize=50000, index_col=False)
                  try:
                      nrows = store.get_storer('ta_store').nrows
                  except:
                      nrows = 0
                  # Looping over chunks and storing them in store file, node name 'ta_data'
                  for chunk in df:
                      # Append chunk to store called 'ta_data'
                      store.append('ta_data', chunk, index=False, min_itemsize={'Placement Ref': 50, 'Click Ref': 50})
              # Print filename if corrupt (i.e. CParserError)
              except (parser.CParserError, ValueError) as detail:
                  print(f, detail)
  
      print("Finished reading to HDF5 store, continuing processing data.")
  </code></pre>
  
  <h1>Second</h1>
  
  <p>The second part of my script reads the HDF5 store into a Pandas DataFrame. Why? Because I need to do some data transformations and filtering to get the final data that I would like to have output into a csv file. However, any attempt to reading the HDF5 store I get a <code>MemoryError</code>, using the following piece of code:</p>
  
  <pre><code>def read_store(filename, node):
      df = pd.read_hdf(filename, node)
      # Some data transformation and filtering code below
  </code></pre>
  
  <p>Another example when this error occurred was when I wanted to print the store to show that the index is not unique using the following function:</p>
  
  <pre><code>def print_store(filename, node):
      store = pd.HDFStore(filename)
      print(store.select(node))
  </code></pre>
  
  <p>My question here is first of all how I can overcome this MemoryError issue. I'm guessing I need to reduce the size of the hdf5 file, but I'm quite new to programming/python/pandas so I would be very happy to receive any input.
  Secondly, I'm wondering whether reading the store into a Pandas DataFrame is the most efficient way to do my data transformations (creating one new column) and filtering (based on string and datetime values). </p>
  
  <p>Any help is very much appreciated! Thanks :)</p>
  
  <h2>Edit</h2>
  
  <p>As requested, an censored sample from a csv file (first) and the result from <code>ptdump -av</code> (below)</p>
  
  <h3>csv sample</h3>
  
  <pre><code>A               B   C               D       E           F           G         H                       I                   J       K               L                               M           N       O
  4/28/2013 0:00  1   4/25/2013 20:34 View    Anon 2288 optional1   Optional2   Anon | 306742    252.027.323-306742  8.05    10303:41916417  14613669178715620788:10303      Duplicate   Anon  Display
  4/28/2013 0:00  2   4/27/2013 13:40 View    Anon 2289 optional1   Optional2   Anon | 306742    252.027.323-306742  8.05    10303:41916417  14613669178715620788:10303      Duplicate   Anon  Display
  4/28/2013 0:00  1   4/27/2013 23:41 View    Anon 5791 optional1   Optional2   Anon | 304142    478.323.464-304142  20.66   10304:37464168  14613663710835083509:10305      Duplicate   Anon  Display
  4/28/2013 0:00  1   4/27/2013 16:18 View    Anon 4300 optional1   Optional2   Anon | 304142    196.470.934-304142  3.12    10303:41916420  15013670724970033908:291515610  Normal      Anon  Display
  </code></pre>
  
  <h3>ptdump -av</h3>
  
  <pre><code>/ (RootGroup) ''
    /._v_attrs (AttributeSet), 4 attributes:
     [CLASS := 'GROUP',
      PYTABLES_FORMAT_VERSION := '2.1',
      TITLE := '',
      VERSION := '1.0']
  /ta_data (Group) ''
    /ta_data._v_attrs (AttributeSet), 14 attributes:
     [CLASS := 'GROUP',
      TITLE := '',
      VERSION := '1.0',
      data_columns := ['F', 'G'],
      encoding := 'UTF-8',
      index_cols := [(0, 'index')],
      info := {'index': {}},
      levels := 1,
      nan_rep := 'nan',
      non_index_axes := [(1, ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O'])],
      pandas_type := 'frame_table',
      pandas_version := '0.10.1',
      table_type := 'appendable_frame',
      values_cols := ['values_block_0', 'values_block_1', 'values_block_2', 'F', 'G']]
  /ta_data/table (Table(41957511,)) ''
    description := {
    "index": Int64Col(shape=(), dflt=0, pos=0),
    "values_block_0": Float64Col(shape=(1,), dflt=0.0, pos=1),
    "values_block_1": Int64Col(shape=(1,), dflt=0, pos=2),
    "values_block_2": StringCol(itemsize=30, shape=(11,), dflt=b'', pos=3),
    "F": StringCol(itemsize=50, shape=(), dflt=b'', pos=4),
    "G": StringCol(itemsize=50, shape=(), dflt=b'', pos=5)}
    byteorder := 'little'
    chunkshape := (288,)
    /ta_data/table._v_attrs (AttributeSet), 27 attributes:
     [CLASS := 'TABLE',
      G_dtype := 'bytes400',
      G_kind := ['G'],
      FIELD_0_FILL := 0,
      FIELD_0_NAME := 'index',
      FIELD_1_FILL := 0.0,
      FIELD_1_NAME := 'values_block_0',
      FIELD_2_FILL := 0,
      FIELD_2_NAME := 'values_block_1',
      FIELD_3_FILL := b'',
      FIELD_3_NAME := 'values_block_2',
      FIELD_4_FILL := b'',
      FIELD_4_NAME := 'F',
      FIELD_5_FILL := b'',
      FIELD_5_NAME := 'G',
      NROWS := 41957511,
      F_dtype := 'bytes400',
      F_kind := ['F'],
      TITLE := '',
      VERSION := '2.7',
      index_kind := 'integer',
      values_block_0_dtype := 'float64',
      values_block_0_kind := ['J'],
      values_block_1_dtype := 'int64',
      values_block_1_kind := ['B'],
      values_block_2_dtype := 'bytes240',
      values_block_2_kind := ['E', 'O', 'A', 'H', 'C', 'D', 'L', 'N', 'M', 'K', 'I']]
  </code></pre>
  
  <h3>Example transformation and filtering</h3>
  
  <pre><code>df['NewColumn'] = df['I'].str.split('-').str[0]
  
  mask = df.groupby('NewColumn').E.transform(lambda x: x.nunique() == 1).astype('bool')
  df = df[mask]
  </code></pre>