title: Pandas: Why should appending to a dataframe of floats and ints be slower than if its full of NaN
body: <p>I am taking data from a file that receives data from Interactive Brokers 5-second OHLCVT bars via Sierra Chart.</p>
  
  <p>Following advice in earlier posts, rather than append each new row to the dataframe I construct a dataframe with the historical file and append 5000 "blank" records to it with correct timestamps.  I then write each new row over a blank row, filling any rows if timestamps are missing and updating pointers.</p>
  
  <p>This works well.  Here's the current <a href="http://pastebin.com/yc4i95EX" rel="nofollow">classes and functions</a>.  My initial version created 5000 lines of NaNs (OHLCVxyz).  I thought it would be tidier to start with the end data types so converted the "blank" records to zeros with OHLC being floats and Vxyz being ints using:</p>
  
  <pre><code>dg.iloc[0:5000] = 0.0
  dg[[v, x, y, z]] = dg[[v, x, y, z]].astype('int')
  </code></pre>
  
  <p>This only occurs once per additional 5000 lines (once a day for HSI).  What surprised me was the impact on the read/write loops.  They went from 0.8ms to 3.4ms per row.  The only change was from NaNs to zeros.</p>
  
  <p><a href="http://i.imgur.com/SFrrghZ.png" rel="nofollow">This picture</a> shows an inital run with a zero filled frame (see timestats 0.0038) then a run with a NaN filled frame (timestats 0.0008).</p>
  
  <p>Can anyone provide insight on why it might add so much time to write to fields of [0.0, 0.0, 0.0, 0.0, 0, 0, 0, 0] instead of [NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN] ?</p>
  
  <p>Any thoughts on code improvements are also welcome. :)</p>
  
  <p>Thanks</p>
  
  <blockquote>
    <p></p>
  </blockquote>
  
  <p><strong>EDIT</strong> +17 hours</p>
  
  <p>Following the questions from @BrenBarn I built a simpler model that could be run by anyone without data.  In doing so I eliminated the question of whether the NaNs impact it.  In this version I was able to write 0.0s to both versions and the difference was the same:</p>
  
  <ul>
  <li>the array that has 8 columns of floats is added to 10 times faster than the array that has 4 columns of floats and 4 of int64s.</li>
  <li>in each case the row being added was [1.0, 2.0, 3.0, 4.0, 5, 6, 7, 8]</li>
  <li>the add is done 10000 times with self.df.iloc[self.end] = datarow and increment end.</li>
  </ul>
  
  <p>So, unless I'm mistaken (always possible) it seems that adding to a dataframe with 4 columns of floats and 4 of ints takes 10x as long.  Is this an issue for pandas or just what one should expect?</p>
  
  <p><a href="http://pastebin.com/8dbNr7TU" rel="nofollow">Here's the test code</a>
  and <a href="http://i.imgur.com/xQQkRMt.png" rel="nofollow">here is the output picture</a></p>
  
  <p>I think that having an array of 350,000 rows of 8 columns before you add to it makes a significant difference.  My initial tests adding to 10 rows showed no impact - I must go back and retest them.</p>
  
  <blockquote>
    <p></p>
  </blockquote>
  
  <p><strong>EDIT</strong> +10 minutes</p>
  
  <p>No I went back and created the intial array with only 10 rows and the impact on the add loops didn't change so its not the size of the original array/dataframe.  Its probable that in my earlier test I thought I'd converted the columns to ints but I hadn't - checking this proved that the command I thought would do this didn't.</p>
  
  <pre><code>da = SierraFrame(range(10), np.zeros((10,8)))
  da.extend_frame1()
  </code></pre>
  
  <blockquote>
    <p></p>
  </blockquote>
  
  <p><strong>EDIT and Possible Answer</strong> +35 minutes</p>
  
  <p>Should this question not be answered in more detail.</p>
  
  <p>At this point, my <strong>hypothesis</strong> is that the underlying functionality to add [1.0, 2.0, 3.0, 4.0, 5, 6, 7, 8] to a spare line in the dataframe is different if the df comprises all one type than if it comprises columns of floats and ints.  I just tested it with all int64s and the average add was 0.41ms vs 0.37ms for all floats and 2.8ms for a mixed dataframe. Int8s took 0.39ms.  I guess that the mix affects pandas ability to optimize its action <strong>so if efficiency is very important then a dataframe with all columns being the same type (float64 probably) is the best bet.</strong></p>
  
  <p><em>Tests conducted on Linux x64 with Python 3.3.1</em></p>