What is the fastest way to output large DataFrame into a CSV file?

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()

Edit Report

Your df_to_csv function is very nice, except it does a lot of assumptions and doesn't work for the general case.

If it works for you, that's good, but be aware that it is not a general solution. CSV can contain commas, so what happens if there is this tuple to be written? ('a,b','c')

The python csv module would quote that value so that no confusion arises, and would escape quotes if quotes are present in any of the values. Of course generating something that works in all cases is much slower. But I suppose you only have a bunch of numbers.

You could try this and see if it is faster:

#data is a tuple containing tuples

for row in data:
    for col in xrange(len(row)):
        f.write('%d' % row[col])
        if col < len(row)-1:
            f.write(',')
    f.write('\n')

I don't know if that would be faster. If not it's because too many system calls are done, so you might use StringIO instead of direct output and then dump it to a real file every once in a while.

answered 2013-03-15T10:25:08.880

What is the fastest way to output large DataFrame into a CSV file?

1 Answer

Your Answer