Programmatically Combine Excel Worksheets on Certain Columns - Five Minute Python Scripts

Hey Everyone! Subscriber request on how to programmatically join excel files in a directory using Pandas.

Support the Channel on Patreon --
Join The Socials --
*****************************************************************
Full code from the video:
import os
import pandas as pd

data_location = "ExcelData/"
desired_headings = ["Valuable Information"]
df_total = pd.DataFrame(columns=desired_headings)

Packages (& Versions) used in this video:
Python 3.7
Pandas 0.25

*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:

Check out my website:

If you liked the video - please hit the like button. It means more than you know. Thanks for watching and thank you for all your support!!

--- Channel FAQ --

What text editor do you use?

What Equipment do you use to film videos?

What computer do you use/desk setup?

What editing software do you use?
Premiere Pro for video editing
Photoshop for images
After Effects for animations

Do I have any courses available?
Yes & always working on more!

Where do I get my music?
I get all my music from the copyright free Youtube audio library

Let me know if there's anything else you want answered!

-------------------------

Always looking for suggestions on what video to make next -- leave me a comment with your project! Happy Coding!

[dayooladipo]

Derrick, I don't know how else to say this but, "YOU ARE AWESOME!". I mean it. Thank you, man.

[Motivos]

Just came across this in a quest to combine 1000+ files. This worked brilliant, just had to change the file type from excel to csv. Thank you!

[bensonm]

Derrick, your teaching methodology is on point! I have learnt more from you in 5 minutes than I would learn with a 30 min video from someone else! Please keep it going.

[oak]

After weeks of searching, I found the two solutions on your channel. **clicks subscribe button**

[timercadoindustrialmi]

So many thanks Derrick. If you need an Excel with all the sheets information you will need the argument sheet_name=None, but it will return a dictonary not a DataFrame, so the line where you read the Excel files will become in: df_file=pd.concat(pd.read_excel(data_location+file), ignore_index=True)

[jasperb]

Thank you so much for this tutorial Derrick! I got a data analyst job because I was able to show the recruiters that I can merge my Python skills with the more commonly used Microsoft Excel. You ended my 8 month job hunt!

PS I graduated with a BA Psychology Degree. Anyone can get a Data Analyst position with Pythton!

[gpozzi.]

Amazing tutorial! If you are running low on ideas, here's one that I'd find VERY useful and I think a lot of people may too:


On monday:
- Checking if email of 5 diff people has arrived. If not, send alert.
- Checking if formatting of attachments (xls files) has remained consistent, like no new columns have been added, the name format has changed, the data is not on the same spot as before, etc
- If data has remained consistent, send generic thank you email (maybe with the option of switching between more than one templates)
- If data is not the same in some way, prepare on draft (but not send so that it can be manually checked by the script owner if the email is worth sending) an email warning the person that has changed the file, what has changed and in the automatically downloaded file paint the changed cells

[felipelandim]

Very very good Derrick!

Just one thing: I think the understanding will be facilitated if you run your code step by step and print the outcome.
So, in this exemple, print the blank dataframe "df_total". Then, in the "for" loop, first print the excel files that you read. And go like this till the end of the code.

[gritmanish]

Hi Derrick, I am completely non-programmer and just started learning Python from your videos. In my project, we are lot dealing with Access and Excel Reporting. Like data cleansing and transformation. It would be great if you post more and more videos on working with files...Thank you

[baldevkumar]

Awesome, no crap, straight to the point

[dontbeafraid]

This guy is a legend. I wish you are my personal teacher

[khalidjj]

Great videos. You make it so simple...
I have a bunch of xls in one folder
These xls have single cell data in one tab and x, y data with many rows in another tab
Would like to combine all of the single cells in one tab and combine all of the x, y many rows in a second tab
But place the single in rows
Place the x, y many rows into different columns
The finally chart these

Also, add the file name in each row

[kapamagicman]

I like these videos. Straight and to the point.

[suzaam]

Thank you for these practical & useful videos. I'm a Python beginner and these bite-size videos are exactly what I need. Would you considering doing a tutorial on how we can create an email with data from an Excel? I write reports every week to talk about company's performance and would love to automate this. E.g. This week's sales were X% ("lower" if number is negative, "higher" if number is positive).

[amauta]

Broo! I’ve been skipping lunches to figure this out. This video gets me closer to what I need. A challenge I have is that I’d need the user to be able to use this script bot using python. So I made it in python, but I need to distribute the tool to other users. I’ve no idea how to export it into a usable form. My search continues. Thank you for the video!

[matthewhayward]

Thank you so much. I have to combine 3 excel files into one.

[svb]

These are great videos that are very practical! Business schools must be grinding their teeth at you!

[emelnurkaraman]

Thank you so much for this video. It's helped me in my project.

[imranbhatti]

This is what I was exactly after. thanks.

[warrensanders]

I've never seen you import OS. Is that operating system? Love your work! It's applicable to all I'm trying to do!