What is the fastest way to output large DataFrame into a CSV file?
output, pandas, performance, python
Solution
Lev. Pandas has rewritten `to_csv` to make a big improvement in native speed. The process is now i/o bound, accounts for many subtle dtype issues, and quote cases. Here is our performance results vs. 0.10.1 (in the upcoming 0.11) release. These are in `ms`, lower ratio is better.
Results:
t_head t_baseline ratio
name
frame_to_csv2 (100k) rows 190.5260 2244.4260 0.0849
write_csv_standard (10k rows) 38.1940 234.2570 0.1630
frame_to_csv_mixed (10k rows, mixed) 369.0670 1123.0412 0.3286
frame_to_csv (3k rows, wide) 112.2720 226.7549 0.4951
So Throughput for a single dtype (e.g. floats), not too wide is about 20M rows / min, here is your example from above.
In [12]: df = pd.DataFrame({'A' : np.array(np.arange(45000000),dtype='float64')})
In [13]: df['B'] = df['A'] + 1.0
In [14]: df['C'] = df['A'] + 2.0
In [15]: df['D'] = df['A'] + 2.0
In [16]: %timeit -n 1 -r 1 df.to_csv('test.csv')
1 loops, best of 1: 119 s per loop
Problem
For python / pandas I find that df.to_csv(fname) works at a speed of ~1 mln rows per min. I can sometimes improve performance by a factor of 7 like this: ``` def df2csv(df,fname,myformats=[],sep=','): """ # function is faster than to_csv # 7 times faster for numbers if formats are specified, # 2 times faster for strings. # Note - be careful. It doesn't add quotes and doesn't check # for quotes or separators inside elements # We've seen output time going down from 45 min to 6 min # on a simple numeric 4-col dataframe with 45 million rows. """ if len(df.columns) <= 0: return Nd = len(df.columns) Nd_1 = Nd - 1 formats = myformats[:] # take a copy to modify it Nf = len(formats) # make sure we have formats for all columns if Nf < Nd: for ii in range(Nf,Nd): coltype = df[df.columns[ii]].dtype ff = '%s' if coltype == np.int64: ff = '%d' elif coltype == np.float64: ff = '%f' formats.append(ff) fh=open(fname,'w') fh.write(','.join(df.columns) + '\n') for row in df.itertuples(index=False): ss = '' for ii in xrange(Nd): ss += formats[ii] % row[ii] if ii < Nd_1: ss += sep fh.write(ss+'\n') fh.close() aa=DataFrame({'A':range(1000000)}) aa['B'] = aa.A + 1.0 aa['C'] = aa.A + 2.0 aa['D'] = aa.A + 3.0 timeit -r1 -n1 aa.to_csv('junk1') # 52.9 sec timeit -r1 -n1 df2csv(aa,'junk3',myformats=['%d','%.1f','%.1f','%.1f']) # 7.5 sec ``` Note: the increase in performance depends on dtypes. But it is always true (at least in my tests) that to_csv() performs much slower than non-optimized python. If I have a 45 million rows csv file, then: ``` aa = read_csv(infile) # 1.5 min aa.to_csv(outfile) # 45 min df2csv(aa,...) # ~6 min ``` Questions: ``` What are the ways to make the output even faster? What's wrong with to_csv() ? Why is it soooo slow ? ``` Note: my tests were done using pandas 0.9.1 on a local drive on a Linux server.