Fastest way to write dataframes list to Excel sheets with python

Question

I have 50 CSV files of 20,000 rows each, I allready joined them and then split them by date. My result is a list of dataframes that I want to write each dataframe of the list to one sheet in an output excel. I already try with:

with pd.ExcelWriter('output.xlsx') as writer:
        cont=0
        for x in List:
            x.to_excel(writer,sheet_name="csv_"+str(cont),index=False, engine='xlsxwriter',na_rep="NAN",startrow=1,startcol=1)
            cont+=1

But I get a memory error and it takes forever. So my question is, does anyone know how to write big dataframes to an excel sheet in a really fast way using python? Or I should use another language to write it?

I already have posted another question showing my error: Writing pandas dataframes to excel crash

Related to: Python: fastest way to write pandas DataFrame to Excel on multiple sheets — DarrylG
– DarrylG, Commented Jun 10, 2020 at 19:19
You must store and run all 50 CSVs in a single operation? Can't you load, transform, and then export per file to reduce the memory footprint of your script? This question isn't really a matter of speed, but more an issue with memory usage I gather. — r.ook
– r.ook, Commented Jun 10, 2020 at 20:02

Sy Ker · Accepted Answer · 2020-06-10 19:30:02Z

2

To fix your memory error, you have to increment your cont variable;

with pd.ExcelWriter('output.xlsx') as writer:
        cont = 0
        for x in List:
            x.to_excel(writer,sheet_name="csv_"+str(cont),index=False, engine='xlsxwriter',na_rep="NAN",startrow=1,startcol=1)
            cont += 1

A better syntax for this;

with pd.ExcelWriter('output.xlsx') as writer:
        for i, x in enumerate(List):
            x.to_excel(writer,sheet_name="csv_"+str(i),index=False, engine='xlsxwriter',na_rep="NAN",startrow=1,startcol=1)

And check if the file you want to output follows the Excel Specifications.

If all checks out and it is still too slow, you can use a faster excel module.

edited Jun 10, 2020 at 19:30

answered Jun 10, 2020 at 19:21

Sy Ker

2,2081 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo Escobar Over a year ago

Thanks, but that is not my question

Collectives™ on Stack Overflow

Fastest way to write dataframes list to Excel sheets with python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related