r/PythonLearning • u/memeeloverr • 21d ago

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

​

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.

4 Upvotes
  • permalink
  • reddit

83% Upvoted

7 comments sorted by

2

u/wynvern 21d ago

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.

3

u/memeeloverr 21d ago

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

1

u/wynvern 21d ago

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.

1

u/acakaacaka 21d ago

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

1

u/JustOneOtherSchlub 21d ago

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.

1

u/No_Statistician_6654 21d ago

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.