CODEWITHSUNDEEP
rpackagerstudio
GodspeedYou77
GodspeedYou77

Reputation: 23

Write.xlsx Is Running Very Slow, Can I Speed It Up?

I've been using Write.xlsx to export my results into each tab of an Excel workbook and it's working just as I'd like it to be, except it's running exceptionally slow. It took me 9 and a half hours to run about 120 lines of write.xlsx. Here is the code I'm using, with "Fruit" as an example:

forecast_custom <- function(selected_fruit) {
  df_sub <- subset(FruitData, Fruit == selected_fruit)
  ts_sub <- ts(df_sub$avg)
  forecast(auto.arima(ts_sub), h = 12)
}

Then:

ForecastApple <-  forecast_custom("Apple")
ForecastBanana <-  forecast_custom("Banana")

Finally:

write.xlsx2(ForecastApple, file="ForecastModel.xlsx", sheetName="Apple", 
row.names=FALSE)
write.xlsx2(ForecastBanana, file="ForecastModel.xlsx", sheetName="Banana", 
append=TRUE, row.names=FALSE)

And lets say these forecasts and write.xlsx (I switched from write.xlsx to write.xlsx2 and it's still slow) go on for 100 lines. Any reason why it would be running so slow?

Thanks!

Upvotes: 0

Views: 4008

Answers (3)

Adriaan Nering B&#246;gel
Adriaan Nering B&#246;gel

Reputation: 350

I had the same problem, as well as some annoying errors using write.xlsx(). write_xlsx() from the writexl package works great and is way faster! And it works fine with tibbles.

For more info see https://ropensci.org/technotes/2017/09/08/writexl-release/

Upvotes: 3

Sean.H
Sean.H

Reputation: 682

Try this one:

filename='ForecastModel.xlsx`
if os.path.exists(filename):          # engine=xlsxwriter only support WRITE no matter mode='a' OR mode='w'
    with pd.ExcelWriter(filename, mode='a') as writer:
        ForecastApple.to_excel(writer, engine='xlsxwriter', sheet_name='Apple', encoding='utf8', index=False)
        ForecastBanana.to_excel(writer, engine='xlsxwriter', sheet_name='Banana', encoding='utf8', index=False)
else:                                 # creating "ForecastModel.xlsx" if not.
    with pd.ExcelWriter(filename, mode='w+') as writer:
        ForecastApple.to_excel(writer, engine='xlsxwriter', sheet_name='Apple', encoding='utf8', index=False)
        ForecastBanana.to_excel(writer, engine='xlsxwriter', sheet_name='Banana', encoding='utf8', index=False)

According to jmcnamara in the answer of Why is it so much slower to export my data to .xlsx than to .xls or .csv?:

Pandas defaults to using OpenPyXL for writing xlsx files which can be slower than than the xlwt module used for writing xls files.

Try it instead with XlsxWriter as the xlsx output engine.

Upvotes: 0

AlexandraH
AlexandraH

Reputation: 16

You could try write_csv() from the readr package (although as the same suggests you will be creating a number of csv files instead of a single workbook). It's very fast, about twice as fast as write.csv.

Upvotes: 0

Related Questions