Python: How to Combine (concat) Multiple Excel Files into One File? (Not append)

Question

Every department completes an annual budget in Excel and submits the budget. The individual budgets get rolled up into a single master budget.

I've used file linking Excel formulas in the past but, this can be very time-consuming and prone to human error.

I think this is a perfect job for Python with Pandas (and/or other libraries).

Here is a picture sample data:

Here is what I have tried so far: (edited/cleaned-up a little from the original)

#import libraries
import pandas as pd
import glob

# import excel files
path = '*.xlsx'
files = glob.glob(path)

# loop thru
combined_files = pd.DataFrame()
for i in files:
    df = pd.read_excel(i, index_col=None, skiprows=11,
                       nrows=147, usecols='D:P')
combined_files = combined_files.concat(df)

combined_files.to_excel('output4.xlsx', index=False)

If I run print(files) the files are listed

I've also played around with variations of the "combined_excels" variable but no joy.

Desired output:

A spreadsheet or .csv that has the general ledger description, (ie, "supplies"), in the first column, followed by combined amounts from all files under; Jan, Feb, Mar, etc.

So if dept1 budgets for $100 in supplies in January, dept2 budgets $200 in supplies in January, and dept3 budgets for $400 in supplies in January then the result will say: Supplies: Under January will be: $700.

I will have approximately 65 different Excel files and will need to iterate over the list. Most workbooks have multiple sheets. All of the worksheets have a sheet called, "Budget" and that is where we pulled from.

I removed all supporting sheets from my three sample files so I wouldn't have to deal with that aspect yet, but I will need to add that filter back soon.

I appreciate any help you can provide!

John

Thanks, @randomer64 but I am trying to use python. I see a great value in Python for automating Excel tasks and using this to solve a problem and learn at the same time. — mahai217
– mahai217, Commented Oct 11, 2021 at 20:48

gtomer · Accepted Answer · 2021-10-13 15:19:38Z

1

Try this code ad your loop thru and concat:

# Budget Roll-up
# Used to roll-up individual budgets into one master budget

#import libraries
import pandas as pd
import glob

# import excel files
path = '*.xlsx'
files = glob.glob(path)

# loop thru
combined_files = pd.DataFrame()
for i in files:
    df = pd.read_excel(i, index_col=None,
                       skiprows=11, nrows=147, usecols='D:P')
    df.rename(columns={ df.columns[0]: 'test'}, inplace = True)
    df.set_index('test', inplace=True)
    combined_files = combined_files.add(df, fill_value=0, axis=1)
    
combined_files.to_excel('output.xlsx', index=False)

edited Oct 13, 2021 at 15:19

answered Oct 11, 2021 at 20:09

gtomer

6,6041 gold badge14 silver badges29 bronze badges

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

12 Comments

mahai217 Over a year ago

This looks like it will work but when I run it, I get AttributeError: 'DataFrame' object has no attribute 'concat'

mahai217 Over a year ago

I did update @gtomer code to match my updated variables - still no joy. Any ideas?

mahai217 Over a year ago

Any ideas? @gtomer Thanks!

gtomer Over a year ago

I have corrected the answer above

mahai217 Over a year ago

Thank you so much! @gtomer I lost a little sleep last night trying to figure this out and have been working on reading manuals, trying solutions, etc. I appreciate you taking the time to help out.

|

hyperneutrino · Accepted Answer · 2021-10-11 20:40:24Z

1

Use the below function after you have read these excel files in pandas:

combined_excels = pd.concat((df1, df2), axis = 0)

if you want to concat it vertically.

edited Oct 11, 2021 at 20:40

hyperneutrino

5,4362 gold badges32 silver badges52 bronze badges

answered Oct 11, 2021 at 20:36

Zae94

111 silver badge3 bronze badges

Collectives™ on Stack Overflow

Python: How to Combine (concat) Multiple Excel Files into One File? (Not append)

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related