Faster way to modify a few values in large Excel files

userweCJ 0
Viewed 43

The problem:

I have around 20 large Excel files with multiple sheets (over 50,000 values in each), and I have a list of about 500 entires that I'd like to modify, for example:

|    Excel file    | Sheet name | Row | Col | Change value to |
| ---------------- | ---------- | --- | --- |---------------- |
|    Tasks.xlsx    | Objectives | 173 |  67 |       5.07      |
|  Insights.xlsx   |  Notices   | 50  | 281 |      161.85     |
|   Tasks.xlsx     | Solutions  | 59  |  77 |      -3.81      |
|        ...       |    ...     | ... | ... |       ...       |

So for example, I would overwrite the value in Tasks.xlsx, on the Objective sheet, at position (173,67) from whatever it is to 5.07.

Currently I'm doing this with Python 3.7.7 and Pandas 1.1.3 + Numpy 1.19.1 using the following steps:

  • Open every Excel file that is mentioned on the above list as a Pandas ExcelFile object.
  • Open every sheet that is mentioned at least once on the above list. (Ignore sheets that aren't mentioned.)
  • Convert every sheet into Numpy with .toNumpy().
  • (*) Overwrite the value in every location on the above list with the new value.
  • Overwrite the sheets with the new Numpy sheets. (Every sheet is only overwritten at most once - sheets that aren't mentioned above aren't loaded into Python.)
  • Save the Excel Files with the new sheets.

However, even though each Excel sheet is only opened once and only overwritten once, the method is still way too slow for what I need it for. With the approximate values of 20 Excel files, with each of them having around 10 relevant sheets, with around 5000 values on each sheet (so 50,000 values per sheet being loaded into Numpy) this whole operation takes around 14-15 minutes.

This makes my part the slowest on the pipeline, as the next operation (which is actually a complex algorithm on these Excel files) is done in about 3-4 minutes.

Basically I really only want to do the step marked with a (*), but the Python / Pandas / Numpy environment requires me to do additional work.

Question:

Is there a faster programming language / method for overwriting some specific values on large Excel sheets? I don't actually need to read in around 99% of the data values, but currently, Pandas + Numpy does. If there exists a way to read in only some specific cells on each Excel sheet, that would speed up the process tremendously. Any help/suggestion would be appreciated!

P.S. The data has to specifically be stored in Excel, I can't use LibreOffice / Google Sheets / SQL Database / Notepad etc. The algorithm we're running specifically only works with Excel.

file-management pandas numpy excel python
1 Answers

Using openpyxl...

import openpyxl
wb=openpyxl.load_workbook('Tasks.xlsx')
ws=wb['Objective']
ws.cell(row=173,column=67).value = 5.07
wb.save('Tasks.xlsx')
Redox0
Related