Replace Excel Vlookup with Python - Five Minute Python Scripts
ฝัง
- เผยแพร่เมื่อ 14 ก.ค. 2024
- Subscriber special! We'll look at how we can replace the vlookup function in excel using python and pandas.
Kite helps fund the channel, thanks for checking them out and supporting me --
⭐ Kite is a free AI-powered coding assistant that will help you code faster and smarter. The Kite plugin integrates with all the top editors and IDEs to give you smart completions and documentation while you’re typing. www.kite.com/get-kite/?...
We'll cover:
- Reading in multiple excel sheets
- Merging dataframes
- Renaming column names
- Exporting to Excel
#Python #Excel #Automation
If you have your own request on what you want to see. Please let me know! I'm happy to help.
THANK YOU ALL for so much support! You all are incredible. 3150+ Subscribers, I'm so glad you all enjoy the content enough to follow me. You all are awesome.
*****************************************************************
Full code from the video:
import pandas as pd
import numpy as np
initial_workbook = 'A.xlsx'
info_workbook = 'B.xlsx'
output_workbook = 'output.xlsx'
df_initial = pd.read_excel(initial_workbook)
df_info = pd.read_excel(info_workbook)
print(df_initial.columns)
print(df_info.columns)
df_initial.rename(columns={'Code':'IDs'}, inplace=True)
df_3 = pd.merge(df_initial, df_info[['IDs','ID']], on='IDs', how='left')
print(df_3)
df_3.rename(columns={'IDs':'Code'}, inplace=True)
df_3 = df_3.replace(np.nan, '', regex=True)
print(df_3)
df_3.to_excel(output_workbook, index=False)
Or find it on my Github here:
github.com/Derrick-Sherrill/D...
*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:
github.com/Derrick-Sherrill/D...
Check out my website:
www.derricksherrill.com/
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?
Atom - atom.io/
What Equipment do you use to film videos?
Blue Yeti Microphone - amzn.to/2PcNj5d
Mic sound shield - amzn.to/3bVNkEt
Soundfoam - amzn.to/37NV9ci
Camera desk stand - amzn.to/3bX8xhm
Box Lights - amzn.to/2PanL95
Side Lights - amzn.to/37KSNut
Green Screen - amzn.to/37SFFnc
What computer do you use/desk setup?
Film on imac (4k screen) - amzn.to/37SEu7g
Work on Macbook Pro - amzn.to/2HJ5b3G
Video Storage - amzn.to/2Pey8sw
Mouse - amzn.to/2PhCtv3
Desk - amzn.to/37O1Mv1
Chair - amzn.to/2uqHE4E
What editing software do you use?
Adobe CC - www.adobe.com/creativecloud.html
Premiere Pro for video editing
Photoshop for images
After Effects for animations
Do I have any courses available?
Yes & always working on more!
www.udemy.com/user/derrick-sh...
Where do I get my music?
I get all my music from the copyright free TH-cam audio library
th-cam.com/users/audiolibrary...
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! - วิทยาศาสตร์และเทคโนโลยี
![[TH] VCT Pacific Stage 2 - Playoffs Day 2 // PRX vs GEN | DRX vs TLN](http://i.ytimg.com/vi/gmrSk-_ahlA/mqdefault.jpg)
I’ve been using excel for over 20 years now, more than half in professional life. I’ve been trying to automate stuff and decided on pursuing python for that reason. Thank you for sharing your experience and wisdom with us.
Dude I don't usually comment on google videos however I feel inclined to tell you how useful your content is. I've watched some of your stuff and as someone new to Python that's transitioning from excel your help has been immeasurable. Thanks and keep it coming!
Derrick, your channel is a real gem! Please keep up the good work! You just gained a new sub
Multiple people accessing the same spreadsheet. Reduce human error. story of my life.
Dude, you're just as awesome! The things you have shared are invaluable to people like me who are just beginning to learn Python. By far, your tutorial is best out there with all the actual real life applications. Thank you
This is a huge help! Excel is so processing heavy that python makes things so much easier. Really appreciate your videos and please keep up the good work.
Just started to learn python on my free time. As an accountant your channel is very helpful since you give examples of how I can use python to analyze data. Keep up the good work
Former accountant turned web developer here! Derrick's awesome.
@@studywithrobin2715 existing accountant who learn Python for data analytics here. Agree to the awesomeness
if im working with large file, wilusing python be faster than the excel built in function or vba? thank u
After watching this video I felt compelled to subscribe. A very good video, easy to follow and most importantly very helpful. Thanks man!
Thanks. Very informative, quick and easy tutorial 👌. I’ve been spending lots of times trying to figure out how to do it in python until I came across your channel.
I really like your videos. They answer practical questions the basic tutorials don't cover.
I know this is super useful content !! Derrick I can't thank you enough !!!
I found this at a time when I was almost giving up...Thank you
your videos ae so good. This and your other excel videos ae just what I needed!
you deserve more views i believe that this is the future im from tijuana and all the time is consumed by making reports documents on excel thank you sir
Thanks alot Derrick, you are the best.❤❤
Learning Python right now. Keep up the great work. I like your channel.
Tanks Derrick i’m fan of your work 👍
Thank you, Derrick!
Thanks!
As a python newbie and advanced Excel user, I struggle to learn Python as I'm very stuck to excel logic. Would be great to have a tutorial that shows how to do most of Excel data wrangling but better and quicker with python! Thanks!
This video was so quick I didn't have time to thank you 👍
Thank you so much, bro. This will be very useful for my project that I plan to implement in my job!
@Derrick Sherrill, u are the man!
Super thanks for sharing the code in description
Thanks Derrick 👍. Today I'm going to try this excercise in one of my regular task.
Excel Sumifs function from different Sheet. Thank you very much, Mr. Derrick.
Hope you do more of the videos on python. Your videos have been really helpful to me (a beginner).
Derrick, this is really helpful, it's my daily work job to find out such cases, till now I was using index match. But now I will do with the method u showed here. Thanks.
My friend, What a nice person you are and a great teacher 👏👏👏
Been searching the world wide web for 4 hours on how to do this, and this is the only video that correct depicts what I needed. Thank you so fucking much!
Pretty good video, as a begginer I could see some useful functions well explained.
Just started to learn Python. Thanks very good explanation
Thank you very clear very concise very good I will watch every video you make
Very very well explained. Thanks a lot Brother. I count on your channel here after :)
This channel is criminally under subscribed. Very very helpful!!
Chris Keo no, you
Yeah!
Agree hahaha I have created a lot of Python automated codes in my job thanks to Derrick
Very helpful. Thanks for this.
i always use kind this vlookup in SAS, like left join and now i can do this thing with python. thank your guide again sensei
I love how you can go across data sources.
Thanks dude.
This really help me.
Saludos!
smart guy! high quality video seriosly
Thankyou, finally found some decent content with someone who can speak english clearly. You are a saviour!
I look forward to more of your videos. I have screwed up some many formulas in excel.
Thanks for sharing. Congratulations on your talent
You save me! tks....greetings from Brazil
Superb bro. Keep doing more videos like this😎
Very helpful, thank you
Nice Job. Thanks
you area amazing bro!!!!
Hi Derrick
Your explanation style and codes are simple and coo😎😎l gone through many videos on TH-cam but none were practical and simple thanks bro keep it !!!👍👍👍
Have become your fan by just one vedio will explore all your content thanks again 🎉🎉🎉
Very Helpful !!
Very good exercise, thanks for this great video. I appreciate and wish you the best
Tx Derrick. To SQL users, that's a LEFT JOIN.
Amazing!
You saved me. Thank You
Hi Derrik,
This was a nice video.
Could you please make a video on how to compare two excel files and highlight the differences in a third excel file?
Could you please help. :)
great content very concise
This channel should have +1M subscribers. I am an economist from Mexico City and Derrick is my inspiration for keeping learning Python!
excellent video..really helpful
Thanks Derrick :)
thanks for this, widens the solutions range... a faster formula is index+lookup, to eliminate human errors we can use macros. I know xlsx can't have macros but we can quickly write one in the personal macros and use it on any tybe of wb
Derrick Sherrill I love you 😍
good VOD
Great content.
Great video. Need to look up regex
you make me decide to learn Phyton !
You ARE awesome
excelente video muy util
Thank You!!
Thank you
Brilliant
Hello Derrick .. I have learned so much from watching your videos.
Do you do any one on one training or a little Q&A ?
Hey Derrick, any chance you could include these cool video tutorials onto your python course on Udemy? By the way, I’m a big fan of your work👍👍
Vielen Dank!!
Hi Derrick, the common assumption which everyone makes is that, they assume we always work on a new excel file and the merged data we put it in last columns.. But i have an old excel file and from which i need information from a specific column. Using the vlookup its easy but python merge (the one you described) i get the entire data from the old file.. here are the steps
1. two files : old.xlsx which has 10 columns and column 3 is having sales data & the new.xlsx where i need bring in the last month sales in a newly inserted column.
2.need only only column data from old.xlsx to the new file. lets say, the location key is common and last month sales
not sure if the above describes the requirement well.. not sure how inner join, outerjoin helps but dont understand them
Please make a video of appending one excel data to another excel data with similar columns. And then writing it to a new workbook
This is great. I want to compare 2 excel workbook and print all the differences.. Can you please make video on this?
Good sir
great.
Would love a video showing how to loop through each sheet in a csv or a excel file
Awesome video, I just subscribed. What songs are you playing in the beginning/end?
awesome
Hey derrick thanks its really informative .. Can you also tell in some video how to use filter using pandas and how to create new column using exiting value in same sheet ....
Thank you Derrick for this wonderful chanel! Extremely useful. I have one set of email address in one workbook and another set of email address in second workbook, I want to send email to all the people who are in the first workbook but not in the second workbook. What method should I look for?
Hi bro. Could you please make a video where you explain how to select values from different rows and colums to create a new column. For instance, in the first row select the column 3 in the second row column 5 and so on. I would really appreciate it!
Hi Derrick, many thanks for this great video! This is actually helping me a lot with my daily tasks. I have a request for a video on how to use Python to recognize a text pattern in a spreadsheet. Then print a string in a new column accordingly. For example, if a text pattern start with "PM-" in a string "PM-AVdjslqo" we print "Project Manager" in the column next to it. Thanks in advance if you can help me with it.
Great suggestion! Would be a handy tool to have. I'll see what I can code up!
Simply outstanding!
Liked & subscribed.
Q: Can you suggest how we can do index and match or index and aggregate functions of Excel using Pandas & Numpy?
Thank you for sharing!
Dear Derrick Thanks for informative video. I came to know about the usage of Python in Excel through your videos. It is wonderful. If I want to learn Python in Excel, where should I start from? Which book or site do you recommend? DO you have website for python?
Great video, but it would be better if the IDs name would have been "ID_A", "ids_b" or something like that to help us newbies better.
Can u show (at some point) how to use with chrontabs to automate? Thank you Derrick!!
Can you please share one by one all the excel functions and create one series as playlist. That will be great
Hi Derrik, good video and happy to learn from you. One quick question, how to copy and paste one df to a specific position (not start from cell A1 but like cell B50) of an excel? hope you could help with it! Thank you!!!
I love you.
Thank you so much! This really helped
Great video. Thanks for sharing your experience. I'm new to Python, programming. My question is how do I implement the code? How do I run the code? Do you install it on Excel? Where does the code run from? I hope my question makes sense. Thank you for your help.
There is no relation to excel besides the output. You don't even need excel installed. It's just Python and a python code editor plus pandas Python module.
Hi, I'm starting with Python and your videos are very helpful.
I have a question: how can we apply the merge method in the A sheet directly without creating a third file ? Thank you!
Hi Derrick,
Thank you so much. I absolutely adore your tutorials!
Quick question - Have you ever come across cases in your work where you needed to execute a lookup with multiple criteria? i.e. where you've used multiple columns for the lookup instead of just matching against one column.
Please let me know if you've got any tricks or if you already have a tutorials you can point me to.
Thanks a lot in advance!
If you have multiple criteria just make a new column that concatenates those into one and those that as pseudo super key.
@@richynero Awesome. Thanks for that
Hi Derrick, could you please update a video on his to download files from Outlook inbox/subfolder to local system. Thank you
Hey, nice work! Thanks a lot for your effort! I‘m completely new to python. I wonder why we don‘t have to insert a full path to the excel files here or do I have to and you just skipped the info to keep the example easier?
Where can I find the A.xlsx and B.xlsx that you use above? Thanks a lot! Excellent job!
This is of great help!
Here I subscribed...
One request - could you please advise how can we split a Excel file into multiple by number of rows. For example: File A contains 500 rows then 5 files like A1,A2,A3,A4,A5 to be created 100 rows each?
Thank you for sharing! Is there a Python function that would allow this script to automatically run in a recurring frequency? I.e. would it be a parenthetical command that would enclose the entire script? Thanks again!