python将dataframe导出为csv_将大型DataFrame输出到CSV文件的最快方法是什么？

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.

解决方案

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