ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
Replace Excel If Function with Python Pandas
ฝัง
- เผยแพร่เมื่อ 9 ต.ค. 2020
- 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
github.com/Der...
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 --
derricksherril...
Support the Channel on Patreon --
/ derricksherrill
Join The Socials --
Reddit - / codewithderrick
FB - / codewithderrick
Insta - / codewithderrick
Twitter - / codewithderrick
LinkedIn - / derricksherrill
GitHub - github.com/Der...
*****************************************************************
Full code from the video:
github.com/Der...
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 TH-cam 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:
github.com/Der...
Check out my website:
www.derrickshe...
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?
www.amazon.com...
What editing software do you use?
Adobe CC - www.adobe.com/...
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/...
Where do I get my music?
I get all my music from the copyright free TH-cam audio library
www.youtube.co...
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!
Please dont ever stop making these python for excel videos
As always.... clear and concise. That is what I like about your TH-cam videos. What sets you apart from the others out there is that you take one concept and teach it in a clear and concise manner. Other videos try to teach too much in the video which leads to the student not being able to remember the material. Thank you.
neat. this guys is giving away marketable skills
Im getting pay raises cuz of this homie.
Presumably you don’t work for Public Health England ... www.theregister.com/2020/10/06/excel/
@@lawrencedoliveiro9104 i live in the United states.
Obviously because you're making it too complicated for the rest of the "office" to use any of the excel book, thereby making you a kee employee = profit
@@Redwarszawaftw no sir. we teach everybody. I like to spread the knowledge and make code as clean as possible.
I only code in R and Python at home because work only allows Excel...2010
Derrick is on a holy crusade to replace Excel functions by Python. You have my sword.
Being addicted to your videos right now. Thanks for this :)
Actually, as opposed to 99.9% of the content out there, I find your content to be helpful and informative and worth the time spend viewing. Thank You.
Am transitioning to python from excel and I must say I find you're tutorials super useful! Thanks a heap!👍👌
Derrick sir you are very generous in sharing and make it convenient for us with the codes thank you !!
You Legend!!!
I’m teaching students to use Python visuals in PBI and your tutorials are absolutely epic in getting students accustom to Python.
Omg I loved it! I'm learning Python now, and seeing all those on point, clear but detailed, objective explanations, and also different ways to do it, was so cool. I'll definitely gonna try it and continue to watch your videos. Thanks!
Thank you so much Derrick! Thanks to your teaching methodolog, you made our life much easier... please keep it up 👍🏻
i have been working excel for ages now... this is simply beautiful ! thanks Derrick love your videos
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
Thanks for sharing ...always something new to learn..np.where and np.select
"Kane Brown teaches Python". im an old sql guy and your videos are insanely helpful. Thank you!
Thanks for that !
Love your tutorial, keep going Derrick
Derrick, you're awesome man! Thanks again for your help!
Coolest coding, great Derrick !
Thanks Derrick, Regards from Colombia!
Just discovered your channel, excellent stuff. Taken a few nuggets into my workflows.
Thanks so much... this saved me so much time on a project at work :-)
I will watch and learn data analysis, WayScript. Now Python Beginner Course. Thanks a lot.
Very practical videos. Love them.
GR8, especially multiconditions statement, that may replace select case in VBA
GOOD Stuff, very practical your example Derrick, thank you
I learned a lot here, this is good stuff. Thank you
So great that fonts are easily readable size.
CLEAR! Thank you. So np.where is like =IF in excel, and np.select is like =IFS
Thank you Derrick Sherrill, It's helps
Well explained and easy to follow. Great job!
Great video. Super concise and clear, keep it up! Thanks
Love your videos!!! Please keep doing it!!!!!! ❤
Thanks man. I’ve been using python little by little to do excel things.
I really enjoy this video!! Im working on prediction with a lot of data and it was impossible in Excel .. Thanks for sharing!!
Hi, love your tutorial, simple and get to the point.
Thank you Derrick, you saved my day. I have subscribed your channel :)
Great video, but I would've used pd.between so line 14 would just be
scores_df['average'].between(80,89)
Clean and simple ; Nice !!
thanks Derrick love your channel
Thank you so much Derrick.
This was such a beautiful explaination ! It helped me a lot
Hey Derrick, if you could do a video on how to create pivot tables as an export with rows that can collapse, so that you can dig down into multiple rows for each item listed in the row; it would be awesome. Example would be a grocery store has receipts and those are unique numbers, and then there is a customer that buys those items and each item would be in the row. So the value would be a distinct count on the receipt number, the columns would be the dates and the rows would be the customer, with the items, the category of items and the receipt itself in the row as well so that when you dig down into the table you can dig all the way to the original receipt. Thank you so much for the awesome videos!!!
Great videos ! Can you do a video on replicating Excel’s Solver functionality in Python ?
Thanks for your presentation. However I install the Latest Python 3 Release - Python 3.9.0 on a new PC (Win 10, 64bits) and I realise that I cant istall the libraries numpy and pandas. Is there a problem or should I install a previous version of Python
This sort of thing is easier on a Linux system.
"You can't win them all" .. lol
Nice video Derrick, good to learn from you keep it up
Thank you so much for you great video. I just have one question: What happens when none of the conditions meets Results and you want to add a Result for any other case, just the way you do on Excel when nested IF function?
Derrick what about nesting python in the excel sheet so a user can press a button and execute the code? Like when dealing with VBA, you can build out your modules and assign them to button/objects.
Thanks Derrick! These videos are amazing and extremely helpful (I'm also a student of yours on Udemy!). I've watched a few python tutorials online and these are by far the most practical and exactly what I need.
Could you please make a video on applying def functions and how someone would reuse those functions to automate a task in Python (such as using a function to merge multiple tabs from multiple files together?)
Thanks!
Thanks Derrick keep em coming please.
good job !!! thx and can't wait for more tutorials
Super helpful! Thank you for the content!
keep up the good work man
Keep em coming 👍🏻
I'd love to see a video on converting json data to a spreadsheet layout using pandas!
Thanks D. Great tutorial. short and sweet..
Hi DS! Enjoy your videos very much!!!!
Put some tutorial on panda styling.
Also teach how to format numbers in panda dataframe based on contents. Like $1,23,000 Or 23.68% and convert figures into words.
Thanks for the great share. Could please make scraping tutorials?
Do you use Jupyter at all? That would make it easier to develop the program incrementally, and check intermediate results along the way.
what editor and IDE do you use in your tutorial?
Sorry for the delayed reply. I use Atom.io in my tutorials
Hi Derrick firstable thanx for de video. Very clear and helpfull.
I just have a doubt. If i wanna create a multiple condition depending if for example: IF(AND( Column K = "something")*OR (Column L != "something")*OR (Column L != ""),"yes","not") how could i write the code? im very confused...
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.
I saw this video in suggestion clicked on it.
Again I am happy for my decision :)
Thank you
Very helpful for excel users like me 😀
do you have video to do same with using two data frames ?? i.e. replace a value in df1 using conditions in df2
Fantastic! Thanks a lot for this amazing videos!
Thanks for this video , I am looking for a video to edit an existing excel file but keeping the same format.
Nice job bro. Saludos desde Cuba. Thanks!
Good ish, Derrick!
@Derrick, how else would you have done the allocation of the letter grades without defining the results and conditions lists?
Hello Derrick - Nice to e-meet you :)
Can you please create a video of the problem statement - mentioned below?
Input :-
Column 1. Column 2
category A A,B,C
Output:-
Column1. Column 2
category A. A
category A. B
category A. C
Background:
A lot many times I face this problem as an analyst where clients send in the data like the input above and output I am updating manually but if there are a lot of records it’s very time consuming
It would be really really helpful if you could make a video of this , it would be great for the analysts to automate the manual boring work?
Thank you so much!
New subscriber here. I hope you could add more videos about Python
Was this the officially last video of this channel ? Now that you do videos for wayscript, will there be any more python 3 tutorials here? Was great while it lasted.
Can you chain elif conditions together in a list comprehension? What's the correct method?
I notice you use Atom with One dark theme. But your "PD" in pd.read_excel is red, mine is not? It's so much easier to see that, how do I change it?
Derrick you amaze me - only there is not a single ah or stammer - just a straigh precise lesson - takes time to keep up. I have a project at Macquarie Uni (taken time off from work to do research in process optimisation) . My requirement of the hour is - I have a large time series database of 61locations, over two years of of data in precise 15timed slices, that gives me "count" (people), Temperature and "humidity". I have to create an animation that places "daily" data one after another (maybe named ranges?) in a "time lapse sequence". Would you guide me some way please?
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
Hi
I have a question
I have a excel in which there are some columns. In the first column there are some numbers and in the last columns there are also some numbers .Now i have to make a another column just beside of last column where if the first column numbers are present in last column then will say true otherwise will say false
How would i do data analysis from data on a pdf and use that to create barcharts and scatterplots?
How to get the intersection between data in two workbooks and create a new one? Using Python ,also if the number of entries vary
BRAVO Derrick. Nice and Good Job. This can help. Can you make nested If statement with dict comprehension ? Thnx.
Hi derrick, your help with finding the solution for the below would be greatly appreciated!
Let's say I have an excel that has 3 columns(name, class, Result) but the result is either pass or fail.
What would be the appropriate way to count how many have failed and how many passed in each class and graph it?
I tried using pd.pivot_table but I’m currently having issues with finding the function that would best fit the task of grouping or counting similar texts in order to graph it.
Import pandas as pd
Import matplotlib.pyplot as plt
Import bumpy as np
Excelfile = ”grades.xlsx”
df = pd.read_excel(excelfile)
dfpivot = pd.pivot_table(df, columns= ’test result’ , index=’class’ , aggfunc= np.sum)
Print(dfpivot)
Hi there,
one solution could be:
import pandas as pd
data = {'name': ['Jeff', 'John', 'Laura', 'Mike', 'Peter', 'Max'],
'class': ['Beginner', 'Beginner', 'Beginner', 'Expert', 'Expert', 'Expert'],
'result': ['pass', 'fail', 'fail', 'pass', 'fail', 'fail']}
df = pd.DataFrame(data=data)
df.groupby(['class', 'result']).count().plot(kind='bar')
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?
Great content Derill. Does python read from open source excel xlsx files?
Would you redo this video using the "match" statement?
Awesome vid.. Your videos have helped me so much! I was wondering if maybe you could do a video or tutorial on worksheet protection or password protect an excel sheet. Thanks! Keep those tutorials coming :)
What about replacing an xlookup with multiple arrays?
Hi , I know this is off topic, but basically idk if you made a video about it or not, but i don't think i noticed if so: would you mind answering a quick question:
So the thing is I'm an 18 yrs old girl with a company on my name, so I figured I wanted to create a website to make things like shipping my art and paintings more easily like a store or somewhat. so I'm a beginner in coding and/programming, but all the website creators are just missing too much features. So I figured why not use the python or simpler method all the way long to at least have the basics, since i did make a website with microsoft publisher , but i'm too lazy to pay and reinstall for the app on my new pc. So i figured I'd do it the long way round, but i only have the beginner's basics, as in bold/italic text formatting and like knowing how to put down simple elements and knowing the html type of reader. Do you know something that could help, . thx
from:@Janesworld buisseness inquiries founder.
Hi Derrick, your videos are an excellent resource. In a next edition, could you please make a video using python and storing data in S3 (AWS)? Thank you for this amazing content.
Hey, did you think about series with visualization algorithms sorting using matplotlib. I think this was be useful. Peace
Thanks, Derrick. Great video!
What makes Pandas better than openpyxl?
Thanks a ton. They're pretty similar! I think it just comes down to personal preference in most situations
Derrick, can you show us some advanced visualization in python similar to the ones that we can create in Excel
You mean like this matplotlib.org/gallery/index.html ?
But how do I write that data back to the XLS sheet?
.to_excel() will write it back. So it’ll look like: scores_df.to_excel(“newsheetname.xls”)
I had the same question.
It only makes sense when the program can extend Excel, but still keeping it as the main user worksheet.
If there was only a way to program Python _inside_ Excel, instead of this horrible VBA...
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!!
Hi, your videos are very useful to me i have automated many thing in my excel work using your techniques
Thanks for that.
I have one more quire on python I have one dataframe i need to extract the rows which is pass on below condition
If "Message" column is having more than one Category on "Category" column that row should print.
plz help me on this.
Hi Just installed 3.12 on win 11. For some reason, I can't get jupyter installed. Pip or Pip3 just gives a syntax error. HELP!!
How can I sum a column if another column has a specific text string. Example sum all g column if employee = Maria. I hope this make sense. So far I spent an eternity recording a macro. Thank you.
Really helpful function! Thank you so much.
I'm worried about the ergonomics of your setup.
Don't worry in that age you usually get away with almost anything, though if he'll use the same setup after thirty - then there's a problem