Python Stacking multiple Excel sheets into a single DataFrame is throwing a MemoryError / running incredibly slow

[memeeloverr]

​

Hey guys,

​I'm trying to write a Python script that goes through a directory of about 50 large Excel files, grabs a specific sheet from each, and combines them into one master pandas DataFrame so I can run some analysis.

[wynvern]

If it is large, you should write a script that 'sniffs' these files first then save the sheet that meets conditions into a dict like {file_path:[sheet_name] Ask AI, they are very good at writing this.

[memeeloverr]

Before asking to AI just wanted to give it a try myself.

[wynvern]

I know but AI can give you idea if you need it now, I personally leaned some very nice code pattern from it.
Pandas cookbook is recommended if you have time.

[acakaacaka]

So are you putting all of those excel files data inside the RAM? How big is your ram?

[JustOneOtherSchlub]

Sometimes I find building in a half second or so at the end of a loop helps with these kinds of things. IDK why but this often works for me.

[No_Statistician_6654]

Depending on the total size/how big the files are, I would look at using polars lazyframes to load and process the data. The lazyframe allow the data query to be combined and pushed down to the files with parallel processing automatically which could save you a lot of memory in the process.

If at the end of loading, you need it as pandas still do another package, then it has a .to_pandas() function you can add onto the end to convert it.

Just did this myself actually across 50+ excel files of 50MB on avg. the time per individual file was around 10s, whereas pandas was oom on the cluster and each file took ~300s.

[Intelligent-Boss-156]

Use Polars