title: Speed up writing billions of rows to HDF5
body: <p>This is a continuation of the scenario I tried to discuss in my question <a href="https://stackoverflow.com/questions/33251445/tips-to-store-huge-sensor-data-in-hdf5-using-pandas">https://stackoverflow.com/questions/33251445/tips-to-store-huge-sensor-data-in-hdf5-using-pandas</a>. Please read the question for more details about what follows.</p>
  
  <p>Since the linked question above was closed as the subject was too broad, I did not get a chance to gather ideas from people more experienced at handling hundreds of gigabytes of data. I do not have any experience with that whatsoever, and I am learning as I go. I have apparently made some mistake somewhere, because my method is taking way too long to complete.</p>
  
  <p>The data is as I described in the linked question above. I decided to create a node (group) for each sensor (with the sensor ID as the node name, under root) to store the data generated by each of the 260k sensors I have. The file will end up with 260k nodes, and each node will have a few GB of data stored in a Table under it. The code that does all the heavy lifting is as follows:</p>
  
  <pre><code>with pd.HDFStore(hdf_path, mode='w') as hdf_store:
      for file in files:
          # Read CSV files in Pandas
          fp = os.path.normpath(os.path.join(path, str(file).zfill(2)) + '.csv')
          df = pd.read_csv(fp, names=data_col_names, skiprows=1, header=None,
                           chunksize=chunk_size, dtype=data_dtype)
  
          for chunk in df:
              # Manipulate date &amp; epoch to get it in human readable form
              chunk['DATE'] = pd.to_datetime(chunk['DATE'], format='%m%d%Y', box=False)
              chunk['EPOCH'] = pd.to_timedelta(chunk['EPOCH']*5, unit='m')
              chunk['DATETIME'] = chunk['DATE'] + chunk['EPOCH']
  
              #Group on Sensor to store in HDF5 file
              grouped = chunk.groupby('Sensor')
              for group, data in grouped:
                  data.index = data['DATETIME']
                  hdf_store.append(group, data.loc[:,['R1', 'R2', 'R3']])
  
      # Adding sensor information as metadata to nodes
      for sens in sensors:
          try:
              hdf_store.get_storer(sens).attrs.metadata = sens_dict[sens]
              hdf_store.get_storer(sens).attrs['TITLE'] = sens
          except AttributeError:
              pass
  </code></pre>
  
  <p>If I comment out the line <code>hdf_store.append(group, data.loc[:,['R1', 'R2', 'R3']])</code>, the bit under <code>for chunk in df:</code> takes about <strong>40 - 45 seconds</strong> to finish processing an iteration. (The chunk size I am reading is 1M rows.) But with the line included in the code (that is if the grouped chunk is being written to HDF file) the code takes about <strong>10 - 12 minutes</strong> for each iteration. I am completely baffled by the increase in execution time. I do not know what is causing that to happen.</p>
  
  <p>Please give me some suggestions to resolve the issue. Note that I cannot afford execution times that long. I need to process about 220 GB of data in this fashion. Later I need to query that data, one node at a time, for further analysis. I have spent over 4 days researching the topic, but I am still as stumped as when I began.</p>
  
  <p><strong>#### EDIT 1 ####</strong>
  Including <code>df.info()</code> for a chunk containing 1M rows.</p>
  
  <pre><code>&lt;class 'pandas.core.frame.DataFrame'&gt;
  Int64Index: 1000000 entries, 0 to 999999
  Data columns (total 7 columns):
  SENSOR      1000000 non-null object
  DATE        1000000 non-null datetime64[ns]
  EPOCH       1000000 non-null timedelta64[ns]
  R1          1000000 non-null float32
  R2          773900 non-null float32
  R3          483270 non-null float32
  DATETIME    1000000 non-null datetime64[ns]
  dtypes: datetime64[ns](2), float32(3), object(1), timedelta64[ns](1)
  memory usage: 49.6+ MB
  </code></pre>
  
  <p>Of these, only <code>DATETIME, R1, R2, R3</code> are written to the file.</p>
  
  <p><strong>#### EDIT 2 ####</strong>
  Including <code>pd.show_versions()</code></p>
  
  <pre><code> In [ ] : pd.show_versions()
  Out [ ] : INSTALLED VERSIONS
            ------------------
            commit: None
            python: 3.4.3.final.0
            python-bits: 64
            OS: Windows
            OS-release: 8
            machine: AMD64
            processor: Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
            byteorder: little
            LC_ALL: None
            LANG: None
  
            pandas: 0.17.0
            nose: 1.3.7
            pip: 7.1.2
            setuptools: 18.4
            Cython: 0.23.2
            numpy: 1.10.1
            scipy: 0.16.0
            statsmodels: 0.6.1
            IPython: 4.0.0
            sphinx: 1.3.1
            patsy: 0.4.0
            dateutil: 2.4.1
            pytz: 2015.6
            blosc: None
            bottleneck: 1.0.0
            tables: 3.2.2
            numexpr: 2.4.4
            matplotlib: 1.4.3
            openpyxl: 2.0.2
            xlrd: 0.9.4
            xlwt: 1.0.0
            xlsxwriter: 0.7.3
            lxml: 3.4.4
            bs4: 4.3.2
            html5lib: None
            httplib2: None
            apiclient: None
            sqlalchemy: 1.0.8
            pymysql: None
            psycopg2: None
  </code></pre>