CODEWITHSUNDEEP
pythonexcelpandasconcatenation
mahai217
mahai217

Reputation: 57

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

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:

enter image description here

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

Upvotes: 0

Views: 1077

Answers (2)

gtomer
gtomer

Reputation: 6564

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)

Upvotes: 1

Zae94
Zae94

Reputation: 11

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.

Upvotes: 1

Related Questions