ENH: improve performance of df.to_csv GH3054
State: closed

Needs more testing before merging.

Following [SO question](http://stackoverflow.com/questions/15417574) mentioned in #3054:

``` python
In [7]: def df_to_csv(df,fname):
   ...:     fh=open(fname,'w')
   ...:     fh.write(','.join(df.columns) + '\n')
   ...:     for row in df.itertuples(index=False):
   ...:         slist = [str(x) for x in row]
   ...:         ss = ','.join(slist) + "\n"
   ...:         fh.write(ss)
   ...:     fh.close()
   ...: 
   ...: aa=pd.DataFrame({'A':range(100000)})
   ...: aa['B'] = aa.A + 1.0
   ...: aa['C'] = aa.A + 2.0
   ...: aa['D'] = aa.A + 3.0
   ...: 
   ...: %timeit -r10 aa.to_csv('/tmp/junk1',index=False)   
   ...: %timeit -r10 df_to_csv(aa,'/tmp/junk2') 
   ...: from hashlib import sha1
   ...: print sha1(open("/tmp/junk1").read()).hexdigest()
   ...: print sha1(open("/tmp/junk2").read()).hexdigest()
```

<table>
<tr>
<th>current pandas</th><th>with PR</th><th>example code</th>
</tr>
<tr>
<td>2.3 s</td><td>1.29 s</td><td>1.28 s</td>
</tr>
</table>


wins:
- convert numpy numerics to native types to eliminate expensive numpy specific
  stringify calls.
- if number of columns is < 10000, precompute the `cols` loop range rather
  then creating and walking a generator at each iteration of the inner loop.
- some cargo cult stuff that's probably in the noise.


Labels:

Comments:
Case 2

```
aa=pd.DataFrame({'A':range(100000),'B' : pd.Timestamp('20010101')})
aa.ix[100:2000,'A'] = np.nan
```

Current pandas

```
In [4]: %timeit aa.to_csv('test.csv')
1 loops, best of 3: 1.57 s per loop
```

case 2

```
In [7]: %timeit aa.to_csv('test.csv')
1 loops, best of 3: 2.66 s per loop
```

With change (starting with yours + my patch)

```
In [3]: %timeit aa.to_csv('test.csv')
1 loops, best of 3: 825 ms per loop
```

case 2

```
In [5]: %timeit aa.to_csv('test.csv')
1 loops, best of 3: 1.96 s per loop
```

Thanks jeff. the patch was garbled by GH. Can you pull this branch, add your patch 
as a new commit and push to your fork? I'll pick up the commit and add it to the PR.

https://github.com/jreback/pandas/commit/5c4d0bf5f44a490189bdd352e1857a2ff9357828

After jeff's commit:

<table>
<tr>
<th>current pandas</th><th>with PR</th><th>example code</th>
</tr>
<tr>
<td>2.3 s</td><td>0.58s</td><td>1.28 s</td>
</tr>
</table>

[L1389](https://github.com/y-p/pandas/blob/ffe06fdd59cfa51cee40623ac53e251211be64c9/pandas/core/frame.py#L1389) sure looks like one loop too many.

I played around more with this, but all of the pre-allocation and array indexing seems to be working, (even the map around asscalar makes a big diff), so unless we write this loop in cython, not sure how much better we can do 

Another 25%:

<table>
<tr>
<th>current pandas</th><th>with PR</th><th>example code</th>
</tr>
<tr>
<td>2.3 s</td><td>0.44s</td><td>1.28 s</td>
</tr>
</table>

I get about 20% more with cython.....(on my test case that has lots of dates).....
https://github.com/jreback/pandas/commit/5335a8070aa6544bf6186b348e1204ecabf68e54
https://github.com/jreback/pandas/commit/fa12e630a7ca01fbbb60f51a5701a69b1a404ab8

I think you win! (I get 0.47s) with the same test (an 2.3s with current pandas) :(
hmm....though If i were to pre-allocate the rows...., I am using a single row and overwriting (and copy to the writer)

wait, I'm following your lead and cythonizing the latest python version. let's see...

Remember we're benchmarking on different machines, timings differ. I get 0.407 with your cython branch.

310ms

whoosh
it's the preallocation of rows
pretty good 8x speedup

hang on, I'm working on a linux kernel patch...

the `list()` cast in the call into cython eliminates some test failures that 
occur when passing in the index ndarray, have no idea why.

another 20% (relative):

<table>
<tr>
<th>current pandas</th><th>with PR</th><th>example code</th>
</tr>
<tr>
<td>2.3 s</td><td>0.35s</td><td>1.28 s</td>
</tr>
</table>

any more tricks? probably heavily IO bound at this point.
**edit:** not really.

could try doing a tolist() on the numeric arrays (in helper, when creating the series)
so that can eliminate the np.asscalar test and conversions

If it works, post the commit hash and i'll update.

try this out

https://github.com/jreback/pandas/commit/d78f4f6a6bb03b0f2ac658cac630456c6971a755

240ms. very nice.
But this doubles the memory footprint doesn't it? definitely add as option.

your wish is my command :) had the same issue in HDFStore, so chunked it

https://github.com/jreback/pandas/commit/55adfb7bfd16a85b33bbf9a87896057e688f4cbd

very good, and that also bounds the memory used by the list() copy in the call to cython. 

I hereby declare these games over. 10x will have to do...

:+1: 

yep

go team!

refactored things a bit. @jreback the last iteration broke a vbench, take a look?

I'll be adding some tests, and if all goes well I'm +1 for this going into 0.11.

I will take a look 

what vbench fails for you, all of io_bench.py seems to run for me?

added this commit, just revised the vbenchs a bit (added one that is identical to the op),
as well as the mixed test case

51793db20ebd0f87141ac72d1f07e0e2a9236656

You must have done something, speed is even better than last night!

This is amazing (not a wide frame but 25M+  rows p/m)

```
In [20]: aa=pd.DataFrame({'A':range(10000000)})

In [21]: aa['B'] = aa.A + 1.0

In [22]: aa['C'] = aa.A + 2.0

In [23]: aa['D'] = aa.A + 3.0

In [24]: %timeit aa.to_csv('test.csv')
1 loops, best of 3: 22 s per loop
```

Against: 3e55bd7d481dcedc182bf467049fa08f0075ee09

```
Results:
                                                     t_head t_baseline      ratio
name                                                                    
frame_to_csv2  (100,000)                           182.7691  2237.0021     0.0817
write_csv_standard  (10,000)                        35.4316   238.0822     0.1488
frame_to_csv_mixed (10,000 + wide)                 379.7200  1126.1289     0.3372
frame_to_csv  (3,000)                              150.2640   220.4599     0.6816

```

I did the native type treatment for the index and colums as well, and
yanked a repeated cast out of the chunking loop. But mostly for "correctness", 
I couldn't measure any effect on the base case, maybe with more 
complex indexes it does more.

the vbench failue was from travis: https://travis-ci.org/y-p/pandas/jobs/5549764.

I fixed another vb problem in c682aa6. random data generated in the setup
sometimes hits corner cases where the operation is unsupppored, e.g. 
non-unique index.

Maybe that's the reason for the failue, but it would be a strange coincidence
if the "csv_mixed" vbench happend to fault by chance, unrelated to our new 
commits.

I replaced that vbench with unique names in the columns

new one should be ok

that vbench had duplicate column names across dtypes

I'll have to look at a test for that

Do you want me to add the commits to this PR? better just push to master, I think.