CODEWITHSUNDEEP
pythonexcelpandasdataframe
Pablo Escobar
Pablo Escobar

Reputation: 95

Fastest way to write dataframes list to Excel sheets with python

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

Upvotes: 4

Views: 1026

Answers (1)

Sy Ker
Sy Ker

Reputation: 2180

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.

Upvotes: 2

Related Questions