read a full excel file chunk by chunk using pandas

user_14336485 0
Viewed 710

what is quickest way to read a file chunk by chunk in pandas: I am doing something like this which I found on stackoverflow as well . but how I will keep track of skiprow and skip footer if my file rows are for example like 1000 ?

# if the file contains 300 rows, this will read the middle 100
df = pd.read_excel('/path/excel.xlsx', skiprows=100, skip_footer=100)
pandas python
2 Answers

The read_excel does not have a chunk size argument. You can read the file first then split it manually:

df = pd.read_excel(file_name) # you have to read the whole file in total first
import numpy as np
chunksize = df.shape[0] // 1000 # set the number to whatever you want
for chunk in np.split(df, chunksize):
    # process the data

Unfortunately for Excel, there is no escaping reading the whole file in memory so you will have to do it this way.

If you want to do this with skiprows and skipfooter, you need to know the size of the df (you can read it first).

df = pd.read_excel('/path/excel.xlsx')
df_size = df.shape[0]
columns = df.columns.values
chunksize = 1000
for i in range(0, df_size - chunksize, chunksiz):
    df_chunk = pd.read_excel('/path/excel.xlsx', skiprows=i, skip_footer= (df_size - chunksize*(i+1)), names=columns)
user_41459410

You could use range. Assuming you want to process chunks of 100 lines in in 1000 lines excel file:

total = 1000
chunksize = 100
for skip in range(0, total, chunksize):
    df = pd.read_excel('/path/excel.xlsx', skiprows=skip, nrows=chunksize)
    # process df
    ...
user_35452730
Related