CODEWITHSUNDEEP
pythonexcelpandasdataframe
yc.koong
yc.koong

Reputation: 175

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

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?

Upvotes: 1

Views: 7188

Answers (3)

Reshma2k
Reshma2k

Reputation: 179

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)

Upvotes: 0

jezrael
jezrael

Reputation: 862511

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)

Upvotes: 4

pythonjokeun
pythonjokeun

Reputation: 431

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)

Upvotes: 2

Related Questions