Add PyExcelerate as Excel Writer Engine

[rhstanton]

Here's an example with just 65,000 elements (it's much worse with 250,000) so I can compare xls and xlsx:

from pandas import *

df = DataFrame({'col1' : [0.0] * 65000,
'col2' : 1,
'col3' : 2})

%timeit df.to_csv('sample.csv')
10 loops, best of 3: 109 ms per loop

%timeit df.to_excel('sample.xls')
1 loops, best of 3: 8.35 s per loop

%timeit df.to_excel('sample.xlsx')
1 loops, best of 3: 1min 31s per loop

[jtratner]

I agree this is a frustrating issue (and if you get a big enough file, you can actually run out of memory entirely). I've had the thought in the back of my head that we should try to use PyExcelerate - which should be faster and use much less memory

[jreback]

these use xlwt for writing, an option would be to use openpyxl for writing xls (its used by default for .xlsx), and using optimize_write=True in the Workbook constructor. I don't know why this is not used (maybe more functionaily exposes by xlwt?).

[jreback]

@jtratner close this as a result of #4542 ?

[jtratner]

Yes, xlsxwriter is likely faster (and may become even faster in the
future). PyExcelerate may also be added. (cheers to @jmcnamara for this)

[jreback]

closed by #4542

[brydavis]

Update, default writer for df.to_excel(...) still super slow for large data.

Finally gave up and used df.to_csv(...).

Worked for my purposes. Thanks!

[sancau]

Using PyExcelerate helps a lot when it comes to dumping lots of data.

With a DataFrame (120000, 120) of real mixed data (not ones and zeros: )) it took 4 minutes to write down an .xlsx

Another test I did was a (189121, 27) DF that took only 2min 33s (.xlsx). Also tested Pandas to_excel() and it took 5min 23s. PyExcelerate was more then 2 times faster.

I also noticed that it consumes much less memory during the process

Though PyExcelerate might require some manual data preparation in some cases (NaNs NaTs and so on)
Personaly, I faced an error with a empty value in a datetime column and had to manualy fix that.
Anyways if one needs a bulk excel dump PyExcelerate is a way to go. Cheers for its authors!

For trivial cases I use something like this and it works fine:

from pyexcelerate import Workbook

def df_to_excel(df, path, sheet_name='Sheet 1'):
    data = [df.columns.tolist(), ] + df.values.tolist()
    wb = Workbook()
    wb.new_sheet(sheet_name, data=data)
    wb.save(path)

[jreback]

@sancau it you would like to add this to the supported engines would be fine (most of the work would be making sure dtypes are correct and round-trippable).

Is there any update on this?
xls does not support more than 255 columns, while saving in xlsx in pandas is really slow
I tried using pyexcelerate, but that requires to copy the entire pandas DataFrame to a multi-dimension list in memory
Is there any plan to support pyexcelerate natively, for example?

[gfyoung]

Is there any update on this?

Unfortunately, no AFAIK 😢

Is there any plan to support pyexcelerate natively, for example?

Judging from the comment from @jreback , we would certainly be happy with supporting it, but the PR needs to be done right for it to be incorporated. If you would like to jumpstart that effort, go for it!

I wouldn't even know where to start :(

[gfyoung]

Though PyExcelerate might require some manual data preparation in some cases (NaNs NaTs and so on) Personaly, I faced an error with a empty value in a datetime column and had to manualy fix that.

@sancau @jreback : this makes me skeptical about performance because data preparation is very important for us to ensure round-trippability. I think this might be why we've had issues implementing this.