Combining Excel Sheets with Python - Five Minute Python Automation Scripts Tutorial

Takes several workbooks' fields and rewrites a new excel file with only the data you want.
Combine and verify data across unlimited excel workbooks with one python script.

Five Minute Python Scripts Full Code Along Tutorial

Kite helps fund the channel, thanks for checking them out and supporting me --

Requirements:
Python
Pandas

Automate your excel workflows using Python and Pandas to condense spreadsheets.

*****************************************************************
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!!

Useful Links
-----------------------------------------------------------------------------------------------------------------
Python Download:
(Remember Python 3 is the future!)

I use Atom Text Editor for all my tutorials
Atom Text Editor:

Packages I often use in Python tutorials:
-Pandas
-Numpy
-xlrd
-TensorFlow
-Matplotlib
-Django Framework
-Beautiful Soup
(Install through Terminal $pip3 install ....)

Other Useful Services sometimes featured:
-Amazon Web Services (AWS)
-Microsoft Azure
-Google Cloud
-Juypter Notebooks

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

[junaidmalik]

Brother, thankyou for the video, u have no idea how helpful ur content is. 😁

[robinabraham]

Thanku so much Derrick, automation with python is the primary part of my job, please keep the videos coming....

[webdevschool]

Hey Derrick, Great video. Keep doing your Job without Quitting.

[duze]

You truly are a life saver! If you’re ever free to help with a query please let me know.
It could be good content for you as well!

[riseagain]

You're just amazing. I spent a week on internet trying to find that solution and you made it so simple and easy. A big subscribe with a big like plus a heart 🥰

[OleZZ]

Notice!
If you have trouble with compability reading an excel file, save the file as a 2003 - excel or ".xls" instaed of a "xlsx" file.

[wagnerramirezserrano]

You Pandas Videos are awesome keep working on them.

[amauta]

Super sick. This is very helpful. I still havent figured out how to get python to list out the files in a folder and then do this. Ill crack it!

[ganeshsuresh]

Derrick simply one word. Your awesome. Many thanks for the videos. Its really very helpful. Kindly upload more videos related to excel automation.

[satyajit]

Hi I am a begginer in python. Could you please show me a video how to remove or correct the index field in the video which u cited at the end?
Thank you in advance

[dogonsk]

Awesome work Derrick 👍
Quick Question:
I would like to *Compare*, *Match* and *Join* two dataframes (with multiple and different column names) with a column that have an IP address column in both dataframes.
What I did:
*Join* :
Join = pd.concat(dataframes)
*Compare* :
df3=

I would like to have Df3 that joins both df1 and df2 with matching values of IP columns that match along with IPs that do not match.

Note: df1 and df2 should be joined side by side.

[viktorkutsyi]

AttributeError: module 'pandas' has no attribute 'read_excel'
Has anyone had this kind of issue? Thank You!

[yerriswamyv]

good work Derrik but it's not recommended to use same dataset for many videos. Try to solve some more real type of problems. Ex: DF1=>2 columns ItemCode and Preferred(Yes/No) . DF2=multiple columns including ItemCode, DF3= multiple columns including ItemCode. Now we want to fetch the Itemcodes & Prefered=Yes and any of 2 columns(among many) from DF2 (sharing column would be ItemCode) and Itemcodes & Prefered=No and any of 2 columns(among many) from DF3.

[shafikshamji]

Awesome video! If you had 50 xlsx file how would you concatenate without specifying the names of each?

[rajanand]

Hello
Actually i have two excel with some data
Sheet 1 SHEET_2
Country COUNTRY PLACES
AUSTRALIA AUSTRALIA GARDEN
FRANCE ZOO
JAPAN PARK

FINAL _SHEET_NEEDED
COUNTRY PLACES
AUSTRALIA GARDEN, ZOO, PARK
FRANCE
JAPAN

I want to merge in the above way so how can i achieve that.

[oribunny]

this code doesn't work on jupyter notebook df1 = pd.read_excel(excel1)

[robertbetke]

Derrick...great video! Your video is very clear on the selection of fields. However, if there were many standardised excel files (i.e. 100 +) in the one directory . How could you select all the files in that directory?

[Nexatek]

How to keep headers of only the first file and ignore headers of subsequent files in the output excel, could anyone help here. thanks in advance.

[phil.pinsky]

Great video! I'm sure you've noticed that I'm binging your content. How would you go about taking data from spreadsheet and putting it in another with different column headers?

[MSH]

Let say you are combining excel 1 and 2...

Instead of predefining... the source file name or the location... can you prompt user to type it in?
does it have to be... string literral? Can it be a input box where users can search and select like opening up a word file?