CODEWITHSUNDEEP
pythonexcelpandas
Larx
Larx

Reputation: 111

How to export one dataframe, which contains +1.048.576 rows, into multiple excel files/sheets

I'm planning to import multiple excel files and use append method to obtain a dataframe that contains all of the information I need. However, I am certain that the number of rows in the dataframe will exceed the row limit of excel. Therefore, while exporting the data must be divided and stored in multiple excel sheets or, more preferably, files. How can I perform that?

Upvotes: 1

Views: 1161

Answers (2)

Chris Adams
Chris Adams

Reputation: 18647

Here is another solution using numpy.array_split, to split your DataFrame into chunks.

ROW_LIMIT = 1048576
chunks = (len(df) // ROW_LIMIT) + 1

Splitting into multiple sheets of a single Workbook

workbook = pd.ExcelWriter('./output.xlsx')

for i, chunk in enumerate(np.array_split(df, chunks)):
    chunk.to_excel(workbook, sheet_name=f'Sheet{i+1}')

Splitting into multiple Workbooks

for i, chunk in enumerate(np.array_split(df, chunks)):
    chunk.to_excel(f'output{i+1}.xlsx')

Upvotes: 2

Ekrem Gurdal
Ekrem Gurdal

Reputation: 1258

You can do it in for loop while printing out to file. Sample code:

number_of_row = 60000

loop_count = int(len(df) / number_of_row)

for cycle in range(loop_count):
    df[cycle*number_of_row:(cycle+1)*number_of_row].to_excel('part-{}.xls'.format(cycle))

Upvotes: 2

Related Questions