Load excel sheet to data frame 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])
There are several ways you can go about doing this.


Use pandas.read_excel
Manually convert excel workbook to csv file then use pandas.read_csv
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



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.