Map two excel worksheets using Python - Five Minute Python Automation Scripts

How To merge Excel Files together by field -- Python Solution to never using VLOOKUP again

Post a suggestion for what video you think I should make next!

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

*****************************************************************
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
-----
Python Excel, Pandas, Tutorial, Map Excel Sheets, Atom Text Editor, Python Excel Automation, Derrick Sherrill

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

[DeepakChauhan-mnjw]

No fancy work, straight solution. I will follow you.

[zezeandjr]

The video became clear past minute 2:21, but it didn't take away from the quality of the content, keep up the good work.

[Freedom-ebhs]

Great work. Helps reduce boring excel sheet work on daily basis.

[bruce-le-smith]

Tight! Thanks for the no fuss no muss practical tutorial!

[yanhaolin]

Awesome Video! You alway explain each line! Cannot find tutorials like this!

[cliffkwok]

Hi Derrick, your work is so good, Please share more
do u provide course or write book?

[reydeloso]

I’m now a fan. Great works. Thanks Derrick.

[kennethvela]

This is your second video I watched and loved your channel.

[christianlira]

Great work Derrick and I appreciate you taking the time to make these video(s).

[harshman]

You have a subscriber for your work. I would like to see more about how we can create a target excel file containing a few information from all the different source files.

[MrTbill]

These videos are great! Wish I had found them sooner.

[enricomendiola]

Hi Derrick, this is a great video! I hope you can share more about manipulating excel sheets using python.

[manojtilloo]

Thank you Derrick. Very simple and clearly explained.

[bolajisaka]

Good video, please can you do a video on excel file schedule automation where we create a folder and pull some excel files into it from a source at specific time and date and make those files be email to a user at specific date and time.thanks

[aradhanaverma]

Thanks for the videos... what if we have merge based on the values of multiple columns and how can we find unique values considering multiple columns.

[pieterjdw]

Thanks Derrick. This is very informative, but is it that dynamic? If you change something in the underlying sheets a re-run of the script is necessary. The old-fashioned Vlookup looks a bit more dynamic.

[smstoaj]

Hi, I recently started to follow ur videos. It's simple and clear.
I need help in solving a problem

assume two dataframe

df1 = pd.DataFrame({'Text': ['Some text 1', 'Some text 2', 'The monkey eats a banana', 'Some text 4']})
df2 = pd.DataFrame({'Keyword': ['apple', 'banana', 'chicken'], 'Type': ['fruit', 'fruit', 'meat']})

df1

Text
0 Some text 1
1 Some text 2
2 The monkey eats a banana
3 Some text 4

df2

Keyword Type
0 apple fruit
1 banana fruit
2 chicken meat


Thus, the preferable outcome would be:

Text Type
0 Some text 1 -
1 Some text 2 -
2 The monkey eats a banana fruit
3 Some text 4 -


the problem, however, is that banana is in a sentence not a standalone value.

Thanks in advance

[shankerm]

I have wasted >10 yrs of my life not using this amazing script!

[BruceSimmons]

Derrick, a SUGGESTION for another video... combing excel spreadsheets but where Ben was Ben_S and Kevin was Kevin_W in the second spreadsheet. Again, thank you for your resources.

[GiggsBeckham]

If both excel sheets have different number of rows, how to merge and determine which cells are blank and which cells do matched with each other after merging?