python efficient way to append all worksheets in multiple excel into pandas dataframe

1
votes

I have around 20++ xlsx files, inside each xlsx files might contain different numbers of worksheets. But thank god, all the columns are the some in all worksheets and all xlsx files. By referring to here", i got some idea. I have been trying a few ways to import and append all excel files (all worksheet) into a single dataframe (around 4 million rows of records).

Note: i did check here" as well, but it only include file level, mine consits file and down to worksheet level.

I have tried below code

# import all necessary package
import pandas as pd
from pathlib import Path
import glob
import sys

# set source path
source_dataset_path = "C:/Users/aaa/Desktop/Sample_dataset/"
source_dataset_list = glob.iglob(source_dataset_path + "Sales transaction *")

for file in source_dataset_list:
#xls = pd.ExcelFile(source_dataset_list[i])
    sys.stdout.write(str(file))
    sys.stdout.flush()
    xls = pd.ExcelFile(file)
    out_df = pd.DataFrame() ## create empty output dataframe

    for sheet in xls.sheet_names:
        sys.stdout.write(str(sheet))
        sys.stdout.flush() ## # View the excel files sheet names
        #df = pd.read_excel(source_dataset_list[i], sheet_name=sheet)
        df = pd.read_excel(file, sheetname=sheet)
        out_df = out_df.append(df)  ## This will append rows of one dataframe to another(just like your expected output)

Question:

My approach is like first read the every single excel file and get a list of sheets inside it, then load the sheets and append all sheets. The looping seems not very efficient expecially when datasize increase for every append.

Is there any other efficient way to import and append all sheets from multiple excel files?

3
pythonexcelpandasdataframe
Hi @run-out thanks for comment, i have checked the link, and i have read that before, it only append up to file level but not worksheet level. – yc.koong

3 Answers

2
votes

Use sheet_name=None in read_excel for return orderdict of DataFrames created from all sheetnames, then join together by concat and last DataFrame.append to final DataFrame:

out_df = pd.DataFrame()
for f in source_dataset_list:
    df = pd.read_excel(f, sheet_name=None)
    cdf = pd.concat(df.values())
    out_df = out_df.append(cdf,ignore_index=True)

Another solution:

cdf = [pd.read_excel(excel_names, sheet_name=None).values() 
            for excel_names in source_dataset_list]

out_df = pd.concat([pd.concat(x) for x in cdf], ignore_index=True)
1
votes

If i understand your problem correctly, set sheet_name=None in pd.read_excel does the trick.

import os
import pandas as pd

path = "C:/Users/aaa/Desktop/Sample_dataset/"

dfs = [
    pd.concat(pd.read_excel(path + x, sheet_name=None))
    for x in os.listdir(path)
    if x.endswith(".xlsx") or x.endswith(".xls")
]

df = pd.concat(dfs)
0
votes

I have a pretty straight forward solution if you want to read all the sheets.

import pandas as pd
df = pd.concat(pd.read_excel(path+file_name, sheet_name=None), 
               ignore_index=True)