Python, Pandas, combining data from certain sheets of x number of excel documents

Question

I am trying to create a series of functions to extract data from certain sheets of x number of excel documents into one data frame.

What I have so far is:

import os
import glob
os.chdir(r'path')
FileList = glob.glob('*.xlsm')
print(FileList)

for file in FileList: 
    df = extract_account(file, '2016')
    df = df.dropna()
    df_combined = pd.concat([df])

However, when I call df_combined it is returning only one df (I am expecting a minimum of 5 in my test). extract_account(file_name, sheet_name) is a function I have created which extracts the data I want for one file and it returns a pandas.core.frame.DataFrame object. My next step would then be to pass this function a list of years to extract from.

jezrael · Accepted Answer · 2018-11-09 11:10:00Z

1

Here is necessary create list of DataFrames and then concat together, also parameter ignore_index=True is for avoid duplicated index values in final DataFrame:

dfs = []
for file in FileList: 
    df = extract_account(file, '2016')
    df = df.dropna()
    dfs.append(df)
df_combined = pd.concat(dfs, ignore_index=True)

If want also loop by years:

years = range(2016, 2020)

dfs = []
for file in FileList: 
    for year in years:
        df = extract_account(file, str(year))
        df = df.dropna()
        dfs.append(df)

df_combined = pd.concat(dfs, ignore_index=True)

Solution with list comprehension:

dfs = [extract_account(file, '2016').dropna() for file in FileList]
df_combined = pd.concat(dfs, ignore_index=True)

dfs = [extract_account(file, str(y)).dropna() for file in FileList for y in years]

edited Nov 9, 2018 at 11:10

answered Nov 9, 2018 at 10:55

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Maverick Over a year ago

Thanks for this. However, I want to add these on horizontally and your solution does this vertically?

jezrael Over a year ago

@Maverick - so need df_combined = pd.concat(dfs, ignore_index=True, axis=1) ?

Maverick Over a year ago

Yes thanks that does the job. Sorry not so proficient with Pandas yet, still learning!

jezrael Over a year ago

@Maverick - You are welcome, btw, no necessary excuse :)

Collectives™ on Stack Overflow

Python, Pandas, combining data from certain sheets of x number of excel documents

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related