## Replace Excel If Function with Python Pandas

Simple conditional logic in excel can get very complex whenever you start having multiple conditionals. With only one line to include your conditionals, or having to write VBA, Excel isn’t as user friendly for these operations.

Sample Data
https://github.com/Derrick-Sherrill/DerrickSherrill.com/blob/master/Sample%20Data/sample_scores.xlsx

Python + Pandas makes these operations so much easier!

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.

#Python #Excel #Automation

Merch Store —
https://derricksherrill.store/
Support the Channel on Patreon —
https://www.patreon.com/join/derricksherrill
Join The Socials —
Reddit – https://www.reddit.com/r/CodeWithDerrick/
Insta – https://www.instagram.com/codewithderrick/
GitHub – https://github.com/Derrick-Sherrill
*****************************************************************
Full code from the video:

https://github.com/Derrick-Sherrill/DerrickSherrill.com/blob/master/replace_excel_if.py

import numpy as np
import pandas as pd

#print(scores_df)

scores_df[‘average’] = scores_df.mean(axis=1)

#scores_df[‘Pass/Fail’] = np.where(scores_df[‘average’] #greater than 60, ‘Pass’, ‘Fail’)
#print(scores_df)

conditions = [
# “Angled Brackets aren’t allowed” in YouTube Descriptions. 🙁
]
results = [‘A’, ‘B’, ‘C’, ‘D’, ‘F’]

#print(scores_df)

scores_df[‘Pass/Fail’] = [‘Pass’ if x #greater than 60 else ‘fail’ for x in scores_df[‘average’]]
print(scores_df)

Packages (& Versions) used in this video:
Pandas 1.1.3
NumPy

*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:
https://github.com/Derrick-Sherrill/DerrickSherrill.com

Check out my website:
https://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 – https://atom.io/

What Equipment do you use to film videos?
https://www.amazon.com/shop/derricksherrill

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!
https://www.udemy.com/user/derrick-sherrill-2/

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!

Source

### Comment List

• Derrick Sherrill
November 29, 2020

You baited me, I thought you litteraly would replace the bahaviour of excell so that when you use the if function it somehow calls back a python script.

• Derrick Sherrill
November 29, 2020

Please do video on how to copy data from password protected excel to new workbook

• Derrick Sherrill
November 29, 2020

Using comprehension is bad idea for performance. np.where can calculate multiple rows at the same time (as "single" CPU operation). If statment cant.

• Derrick Sherrill
November 29, 2020

This is amazing. Can you name the software you’re using to draft and run Python code in? Cheers.

• Derrick Sherrill
November 29, 2020

Great video. Super concise and clear, keep it up! Thanks

• Derrick Sherrill
November 29, 2020

Me:Hi boss, here the py file to show you what grades people got.
Boss: I can’t open this file. Why didn’t you just do it in excel?
Me: Didn’t think of that, some guy on YouTube told me to do it in py

• Derrick Sherrill
November 29, 2020

I'd love to see a video on converting json data to a spreadsheet layout using pandas!

• Derrick Sherrill
November 29, 2020

Thanks Derrick, Regards from Colombia!

• Derrick Sherrill
November 29, 2020

thats so helpful thank you so much

• Derrick Sherrill
November 29, 2020

when compared to tools like knime, alteryx, power bi or even just sql, pandas is overly complicated and just extraneous for this kind of simple analysis. this can (and should) be done much more quickly and easily using any of those platforms. while python’s great for custom dev work, but there are infinitely higher quality tools with far greater data wrangling and analysis capabilities than pandas.

• Derrick Sherrill
November 29, 2020

Hey, did you think about series with visualization algorithms sorting using matplotlib. I think this was be useful. Peace

• Derrick Sherrill
November 29, 2020

Hi thank you for posting this, im a beginner in the python. Recently, im looking for how to use python for extract data from PDF ( such as only total number and date number) and Excel with multiple sheet then insert it in the another new excel
May i request this for your next video ?

• Derrick Sherrill
November 29, 2020

Excellent video

• Derrick Sherrill
November 29, 2020

No, are you saying that people should write all this?

• Derrick Sherrill
November 29, 2020

I need haw write to excel

• Derrick Sherrill
November 29, 2020

This is some great information, to be sure. But you really need to learn how to talk to be understood.

• Derrick Sherrill
November 29, 2020

This was such a beautiful explaination ! It helped me a lot

• Derrick Sherrill
November 29, 2020

Hi everyone? No, 'Hi'. Mostly you have an audience of one at a time, like radio.
You only have 'everyone' in an auditorium.

• Derrick Sherrill
November 29, 2020

Hi Derrick, I am new to python and want learn, I found your video interesting, does python help me in my Financial Planning and Analysis career..

• Derrick Sherrill
November 29, 2020

Dumb question… Why would you want to do this in python instead of doing it directly from Excel??

• Derrick Sherrill
November 29, 2020

My first time here, you have 52K subscribers, so what does that mean, it means you absolutely must have excellent pronunciation, you need elocution lessons, you need to open you mouth more and speak clearly, please improve your elocution, thanks Derrick

• Derrick Sherrill
November 29, 2020

Great! You just gave a 8+ minute very complicated programming alternative for a 5 sec excel IF-funtion… WTF….

• Derrick Sherrill
November 29, 2020

In SQL I can use a case statement for that.

• Derrick Sherrill
November 29, 2020

I am reading multiple excel files all have one sheet and writing it all to one master file. I am not able to append the data to same sheet in master file. Can you please help? Thanks in advance!!

• Derrick Sherrill
November 29, 2020

Hey Derrick, your audio/mic filter(s) (compressor maybe?) setting is set to extremes. Is hard to follow. Otherwise good video/tutorial.

• Derrick Sherrill
November 29, 2020

Well explained and easy to follow. Great job!

• Derrick Sherrill
November 29, 2020

Thank you Derrick Sherrill, It's helps

• Derrick Sherrill
November 29, 2020

Nice tutorial, but I’ve just got to say ROLL TIDE ROLL!

• Derrick Sherrill
November 29, 2020

Thank you Derrick – you have a lovely calm and concise presentation style and it's being appreciated all the way over here in Sydney, Aust. Many thanks for your excellent work and efforts. Dave

• Derrick Sherrill
November 29, 2020

Nice job bro. Saludos desde Cuba. Thanks!

• Derrick Sherrill
November 29, 2020

what editor and IDE do you use in your tutorial?

• Derrick Sherrill
November 29, 2020

What is a real world reason for doing this because i feel like this would have been much more simple to do in excel

• Derrick Sherrill
November 29, 2020

Assalamualaikum brother Dereck

I am the CTO of Light Theory LLC

I believe that TF Automation, among many innovative things, will save our planet, careers, families, for the long-term, IF we can unite, and work on projects that are needed TODAY!

I have a project, only meant for a select few…

in Tensor Flow

for Pattern Recognition…

The project valuation is 120M usd.

A Strong Dividend of that profit share goes directly to each Developer.

This is a Project of a Life-time.

in sha ALLAH, Our Small team, would be very excited to have you join fulltime or even as an Adjunct.

info@LightTheory.tech

Salam

LT

• Derrick Sherrill
November 29, 2020

Just discovered your channel, excellent stuff. Taken a few nuggets into my workflows.

• Derrick Sherrill
November 29, 2020

Hi, Great video! Wanted to know how you could set up a input to output tracker for the rules. Once we build a bunch of rules, it becomes tricky to manage these on a regular basis, for changes / modifications / additions / corrections etc. What do you suggest is the best option for composing such rules and managing them, within Python?

• Derrick Sherrill
November 29, 2020

Thanks a lot Derrick for this video. I have a mistake in my script when i run it, can i show to you as i will know where my mistakes are? Please you can write me through soum.ahl225@gmail.com

• Derrick Sherrill
November 29, 2020

You could also do:

scores_df['Pass/Fail'] = scores_df['average'].apply(lambda x: 'Pass' if x > 60 else 'fail' )

Ideally there is one way to do a thing in python, but clearly not always. This way would avoid building a list in a list comprehension.

• Derrick Sherrill
November 29, 2020

But why? If you've already got an Excel sheet with that data you can easily perform those operations just within Excel… I'm confused why I would want to import the data to a different platform to then have to write it back probably to Excel again anyway.

• Derrick Sherrill
November 29, 2020

hi , can you do video on how one could do excel goal seek in python e.g.iterate through each row and do goal seek on each line plse

• Derrick Sherrill
November 29, 2020

When would one need to do this outside of excel?

• Derrick Sherrill
November 29, 2020

Is this dude Greyjoy???????????????????

• Derrick Sherrill
November 29, 2020

In row 1 columns D, E, and F, enter the headers you like for average, pass/fail, and grade.
In cell D2, enter this formula for the average:
=INT(AVERAGE(B2:C2))
In cell E2 for the PASS/FAIL, enter this formula:
=CHOOSE(–(D2>=60)+1,"FAIL","PASS")
In cell F2 for the grade, enter this formula:
=CHOOSE(MATCH(D2/10,{0,5,6,7,8,9,10},1),"F","F","D","C","B","A","A")
Select cells D2:F2, double-click the Fill Handle to fill the columns.
Not an IF() in sight.
Call it a day.

• Derrick Sherrill
November 29, 2020

Thanks Derrick! Remarkable!
Could you please replace VLookUp excel function with Python?

• Derrick Sherrill
November 29, 2020

I really enjoy this video!! Im working on prediction with a lot of data and it was impossible in Excel .. Thanks for sharing!!

• Derrick Sherrill
November 29, 2020

LMAO, this takes more code and more time to do it this way than it does in excel.

• Derrick Sherrill
November 29, 2020

wow nice vid

• Derrick Sherrill
November 29, 2020

Headline: python excels in eating pandas

• Derrick Sherrill
November 29, 2020

Damn I was hoping you were going to say you can now use python in excel.

• Derrick Sherrill
November 29, 2020

While I appreciate the educational value of this video, the problem presented is one of the most basic things that can be handled in Excel and it takes all together 3 functions. 4 if you decide to concatenate the result in a single column.