Replace Excel If Function with Python Pandas

[@danish5850]

Please dont ever stop making these python for excel videos

[@scochran3265]

As always.... clear and concise. That is what I like about your KZbin 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.

[@hamdamjonganijonov8825]

neat. this guys is giving away marketable skills

[@theminertom11551]

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.

[@datawolf2318]

Im getting pay raises cuz of this homie.

[@lawrencedoliveiro9104]

Presumably you don’t work for Public Health England ... www.theregister.com/2020/10/06/excel/

[@datawolf2318]

@@lawrencedoliveiro9104 i live in the United states.

[@Redwarszawaftw]

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

[@datawolf2318]

@@Redwarszawaftw no sir. we teach everybody. I like to spread the knowledge and make code as clean as possible.

[@nicholasrobins2835]

I only code in R and Python at home because work only allows Excel...2010

[@hilkokriel5659]

You Legend!!! I’m teaching students to use Python visuals in PBI and your tutorials are absolutely epic in getting students accustom to Python.

[@willykitheka7618]

Am transitioning to python from excel and I must say I find you're tutorials super useful! Thanks a heap!👍👌

[@filobonda]

Derrick is on a holy crusade to replace Excel functions by Python. You have my sword.

[@babiblackbb]

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!

[@SriKanth-jm1nu]

i have been working excel for ages now... this is simply beautiful ! thanks Derrick love your videos

[@neptune4167]

"Kane Brown teaches Python". im an old sql guy and your videos are insanely helpful. Thank you!

[@Zimust]

Thank you so much Derrick! Thanks to your teaching methodolog, you made our life much easier... please keep it up 👍🏻

[@deldridg]

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

[@jannonflores1113]

Being addicted to your videos right now. Thanks for this :)

[@nicklove3047]

Derrick sir you are very generous in sharing and make it convenient for us with the codes thank you !!

[@amarnadhgunakala2901]

Coolest coding, great Derrick !

[@mrmuranga]

Thanks for sharing ...always something new to learn..np.where and np.select

[@oxand1]

Thanks for that ! Love your tutorial, keep going Derrick

[@leisureloaflightfoot1655]

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

[@amauta5]

Thanks man. I’ve been using python little by little to do excel things.

[@hyipdaily]

what editor and IDE do you use in your tutorial?

[@CodeWithDerrick]

Sorry for the delayed reply. I use Atom.io in my tutorials

[@zma314125]

Derrick, you're awesome man! Thanks again for your help!

[@nicobus3112]

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

[@yosolonopuedo]

Very practical videos. Love them.

[@maciejwaldowski1917]

GR8, especially multiconditions statement, that may replace select case in VBA

[@evedickson2496]

Thanks so much... this saved me so much time on a project at work :-)

[@CarlosGarcia-tq3zq]

Thanks Derrick, Regards from Colombia!

[@averybrooks2099]

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!!!

[@alank1995]

Well explained and easy to follow. Great job!

[@kiwijordy]

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

[@soeaung1984]

I will watch and learn data analysis, WayScript. Now Python Beginner Course. Thanks a lot.

[@prajaysharma]

Nice video Derrick, good to learn from you keep it up

[@user-or7ji5hv8y]

So great that fonts are easily readable size.

[@mint9121]

"You can't win them all" .. lol

[@py_tok5589]

GOOD Stuff, very practical your example Derrick, thank you

[@Mab_Tech]

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)

[@CodingIsFun]

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')

[@Richkotite1]

I learned a lot here, this is good stuff. Thank you

[@Pho6]

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!

[@bhi_the_data_anayst]

Thank you Derrick, you saved my day. I have subscribed your channel :)

[@talhaamir9023]

I saw this video in suggestion clicked on it. Again I am happy for my decision :)

[@stellaschmucker3059]

Love your videos!!! Please keep doing it!!!!!! ❤

[@blackpower747]

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

[@ylyassun]

Hi, love your tutorial, simple and get to the point.

[@CaribouDataScience]

Would you redo this video using the "match" statement?

[@alexk798]

Great content Derill. Does python read from open source excel xlsx files?

[@ntenzz1808]

CLEAR! Thank you. So np.where is like =IF in excel, and np.select is like =IFS

[@chomchom216]

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?

[@michalismichael7666]

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

[@lawrencedoliveiro9104]

This sort of thing is easier on a Linux system.

[@RNF2015]

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

[@dannyboland1363]

Thanks Derrick keep em coming please.

[@SolucionesELP]

Thanks for this video , I am looking for a video to edit an existing excel file but keeping the same format.

[@mvenkataiah]

Thank you Derrick Sherrill, It's helps

[@coolhundred21]

Great video, but I would've used pd.between so line 14 would just be scores_df['average'].between(80,89)

[@Immortal77pl]

good job !!! thx and can't wait for more tutorials

[@techobsessed1]

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.

[@Shremmer45]

Great videos ! Can you do a video on replicating Excel’s Solver functionality in Python ?

[@patrickwheeler1979]

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.

[@melvynsim9755]

Cool! Thanks a lot for this video! May i know what are the packages that you've installed in atom for the auto-completion, helping words and documentations?

[@CodeWithDerrick]

Absolutely! The auto completes are from Kite, it's linked in the description if you want to use my affiliate code or just at kite.com

[@pontz5978]

thanks Derrick love your channel

[@purplefan204]

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?

[@neldopardoyuvero5181]

Nice job bro. Saludos desde Cuba. Thanks!

[@mutesijk]

@Derrick, how else would you have done the allocation of the letter grades without defining the results and conditions lists?

[@mahmoudgamal9835]

keep up the good work man

[@sridharanjagadeesan5629]

Clean and simple ; Nice !!

[@rajeevmenon1975]

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.

[@karate-ka]

Thanks, Derrick. Great video! What makes Pandas better than openpyxl?

[@CodeWithDerrick]

Thanks a ton. They're pretty similar! I think it just comes down to personal preference in most situations

[@tinal7121]

Super helpful! Thank you for the content!

[@J.s.private.channel]

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.

[@ahmadaboeleneen3357]

Thank you Very helpful for excel users like me 😀

[@joseyanez8398]

Thanks D. Great tutorial. short and sweet..

[@gsv202000]

Thank you so much Derrick.

[@CurrentElectrical]

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.

[@sstream17]

I'm worried about the ergonomics of your setup.

[@bitchslapper12]

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

[@jacksparrowcapitan3232]

Keep em coming 👍🏻

[@1200cjh]

What about replacing an xlookup with multiple arrays?

[@jaz4300]

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?

[@marcoortiz1107]

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 :)

[@Marlem80]

Fantastic! Thanks a lot for this amazing videos!

[@lawrencedoliveiro9104]

Do you use Jupyter at all? That would make it easier to develop the program incrementally, and check intermediate results along the way.

[@jorge-3768]

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...

[@BMontellano]

Good ish, Derrick!

[@ollenciodsouza9496]

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?

[@GusMD84]

I'm trying to automate google sheets with python. I have a range on Sheet1 that gets refreshed daily. I would like to copy that range and paste it in another tab 'sheet2' (to keep the history safe as a backup if the refresh on sheet1 fails). The next day sheet1 refreshes and stores new values (new dates). I would like to make python grab only the new values on sheet1 and paste them in sheet2. Any hints on how to tackle this?

[@mouhammadkebe8642]

what editor are you using?

[@patriciacm4657]

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.

[@database_tips_tricks]

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

[@tyswenson7542]

Love this channel, love this video. It hit the spot lol

[@nghiepcrypto7034]

Just 1 word: Awesome :))

[@warrensanders5744]

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!!

[@AndySpamer]

But how do I write that data back to the XLS sheet?

[@CodeWithDerrick]

.to_excel() will write it back. So it’ll look like: scores_df.to_excel(“newsheetname.xls”)

[@minastaros]

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...

[@ajithendra]

do you have video to do same with using two data frames ?? i.e. replace a value in df1 using conditions in df2

[@lhpl]

When defining the test for pass/fail, you write 'Pass' if x > 60. However, the definition for grade D is average >= 60. Off-by one error?

[@firstname4337]

idiot its the concepts that matter -- not the actual number used -- hell, he could make pass >= 99 and fail everything else

[@lhpl]

@@firstname4337 it is obvious who is the idiot here. And take a hint: it isn't me.

[@josmithephraim9078]

Derrick, can you show us some advanced visualization in python similar to the ones that we can create in Excel

[@lawrencedoliveiro9104]

You mean like this matplotlib.org/gallery/index.html ?

[@jyotipriya3686]

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

[@wasimshaikh5794]

Great piece of work😎

[@gorflunk]

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.

[@Scoty98]

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

[@sanelsalkic1255]

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.

[@astrogaymerxd7290]

soy excel user who can't recognize the far reach potential of a few lines of code vs pointing and clicking in excel spotted

[@sanelsalkic1255]

Sure, you can see it like that. Alternatively I could argue the opposite, that people don't understand just how versatile Excel is; including the few lines of code in VBA. I mean, you can even define variables in formulas now. But I'll stick with my main point; it's highly impractical. And I say this as someone who worked in analytics, including the "few lines of code.'

[@astrogaymerxd7290]

@@sanelsalkic1255 highly impractical? looks like your "analytical" work was just basic data analysis if you can handle it with vba and excel. the fact that you have to load data into the excel interface before even accessing vba is straight up asinine. I'm aware you can load vba scripts outside of excel, but at that point you pandas is just light years ahead. anyone who does any serious analytical work with substantial amount of data is far better off with python.

[@sanelsalkic1255]

Yeah, not that I disagree with you. But, when you need Stephanie from fourth floor to be able to do what's done in the video, Excel is way way way easier. Imagine upskilling thousands of associates to use python for something so trivial. And on the other hand, don't underestimate PowerPivot. Handling millions of data points and hundreds of measures is as simple as few clicks. Without loading the data in spreadsheet. Seems to me you're building on the assumption I cannot code.

[@danielszalok8540]

@@astrogaymerxd7290 99% of work in Excel is not serious analytical work, so Python is not necessary in such cases. The rest can be done with Excel's in-built functions, or the native VBA, so Python is not necessary, again.

[@DilshadHussainDH]

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!!

[@esquire9152]

New subscriber here. I hope you could add more videos about Python