4
\$\begingroup\$

I have a bare-bones example of how I plan to load excel sheets into pandas data frames. However, the code runs unexpectedly slow. I'm open to suggestions on ways I can speed this up, even converting the source files to a different file format. Anything to make it snappier because I will need a loop to do this several times over multiple sheets and workbooks. Thank you.

import os
import pandas as pd

path_to_data_files = 'C:/DataArchive/'
files = sorted(os.listdir(path_to_data_files), reverse=True)

file = pd.ExcelFile(path_to_data_files + files[0])

sheet_names = file.sheet_names

df = file.parse(sheets[0])
\$\endgroup\$
  • 2
    \$\begingroup\$ Can you give a directory listing showing typical file sizes and counts? \$\endgroup\$ – Reinderien Oct 31 at 23:17
2
\$\begingroup\$

There are several ways you can go about doing this.

  1. Use pandas.read_excel

  2. Manually convert excel workbook to csv file then use pandas.read_csv

  3. Use Python code to convert excel workbook to csv file then use pandas.read_csv

The third method is your best approach. It's the fastest.

Here is my excel workbook

enter image description here

1

df1 = pandas.read_excel('workbook.xlsx')
print(df1)

Out

  col1    col2     col3        col4
0   I   should       be  completing
1   my  linear  algebra    homework

2

I named the .csv file 'workbook.csv'

df2 = pandas.read_csv('workbook.csv')
print(df2)

Out

  col1    col2     col3        col4
0   I   should       be  completing
1   my  linear  algebra    homework

3

import csv
import xlrd
with xlrd.open_workbook('workbook.xlsx') as wb:
    sh = wb.sheet_by_index(0)
    with open('workbook.csv', 'w', newline="") as csv_file:
        col = csv.writer(csv_file)
        for row in range(sh.nrows):
            col.writerow(sh.row_values(row))
df3 = pandas.read_csv('workbook.csv')
print(df3)

Here is the .csv produced, calle

col1,col2,col3,col4
I,should,be,completing
my,linear,algebra,homework

And then the subsequent dataframe

  col1    col2     col3        col4
0   I   should       be  completing
1   my  linear  algebra    homework

VERDICT

All the outputs for each method is the same but method 3 is the fastest. This means you should import csv and xlrd to convert each of your xlsx files to csv files and then use read_csv. You can use os to get into your specific directories. Add for loops for each file for solution 3.

NOTE

Test method 1 versus 2 for yourself because I am getting somewhat inconsistent results using the timeit module and writing

start = timeit.timeit()
# code
end = timeit.timeit()
print(f"Time {end - start} {df}")

but I am not sure if I am using it correctly. So, at the very least, try the first and last methods for yourself and see which ones go faster.

\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you Trevor. I will try these. If I could upvote, I would. Very thorough answer. \$\endgroup\$ – Heather Gray Nov 1 at 1:27
  • 1
    \$\begingroup\$ No problem, @Heather Gray, if you want, I think you can check the answer instead of upvoting. Also, I am usually on this site, so just comment if you run into anything. \$\endgroup\$ – TrevorPaulMartin Nov 1 at 1:38
  • 1
    \$\begingroup\$ Interesting that reading the excel file, writing a csv file (which includes a loop in pure Python) and then reading and parsing that csv file is faster for you than just letting pandas directly read and parse the excel file. It would be interesting to see the timings, including for larger files. \$\endgroup\$ – Graipher Nov 1 at 12:51
  • 2
    \$\begingroup\$ Also, if #2 is not faster than #3, you are doing your timings wrong. #2 is fully included in #3, as the last step. Unless you count the time it takes you to manually save the worksheet as a CSV, of course. \$\endgroup\$ – Graipher Nov 1 at 12:59
  • 3
    \$\begingroup\$ Case in point, for two files with four columns and 1501 (3001) rows, one being the header, all data numerical, I get 0.057s (0.112s), 0.002s (0.003s), 0.056s (0.115s), respectively, making #1 the preferred choice if you don't want to do the conversion manually, because it is a lot easier to use and not really slower. \$\endgroup\$ – Graipher Nov 1 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.