Replace Excel If Function with Python Pandas




[ad_1]

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/
FB – https://www.facebook.com/CodeWithDerrick/
Insta – https://www.instagram.com/codewithderrick/
Twitter – https://twitter.com/codewithderrick
LinkedIn – https://www.linkedin.com/in/derricksherrill/
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

scores_df = pd.read_excel(‘sample_scores.xlsx’)
#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’]

scores_df[‘Letter Grade’] = np.select(conditions, results)
#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?
Adobe CC – https://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!
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
https://www.youtube.com/audiolibrary/music?nv=1

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


[ad_2]

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…

    We already have a buyer lined up.

    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.

    Please email us

    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.

Write a comment