How do I apply multiple filter criteria to a pandas DataFrame?

Let's say that you want to filter the rows of a DataFrame by multiple conditions. In this video, I'll demonstrate how to do this using two different logical operators. I'll also explain the special rules in pandas for combining filter criteria, and end with a trick for simplifying chained conditions!
SUBSCRIBE to learn data science with Python:
www.youtube.co...
JOIN the "Data School Insiders" community and receive exclusive rewards:
/ dataschool
== RESOURCES ==
GitHub repository for the series: github.com/jus...
"isin" documentation: pandas.pydata.o...
== LET'S CONNECT! ==
Newsletter: www.dataschool...
Twitter: / justmarkham
Facebook: / datascienceschool
LinkedIn: / justmarkham

[@carkar1776]

The clarity you provide is incredible. Please know that you are helping a lot of people by making these concepts easy to grasp. I hope you continue to provide this amazing content! You''re doing a wonderful service for the data science community.

[@dataschool]

Wow, thank you so much for your incredibly kind comment! I greatly appreciate it. I will do my best to continue creating useful content for the community!

[@sarikadatta3706]

You’re indeed a very good teacher. I am so impressed with your skill of teaching. God bless you

[@dataschool]

Thank you so much!

[@bobrossatron]

You are a lifesaver!!! That last tip at the end of the video is exactly what I was looking for. Thank you so much!

[@dataschool]

You are very welcome! 🙌

[@alexandra-stefaniamoloiu2431]

Question: How can you be such a great teacher?

[@dataschool]

You are very kind! Thank you. I think the answer is this: I have a lot of experience teaching in the classroom, I spend a ton of time preparing for every lesson (or video), and I remember what it's like to not understand something (as well as the mental path I took to gain that understanding). Plus, I care about my students, even those I will never meet! :)

[@megalaughs89]

@@dataschool Thank you for caring about us ( your students that u never meet)

[@ashiktm4188]

Yep

[@helloworld-hv9oy]

@@dataschool "Plus, I care about my students, even those I will never meet! :)" a few teachers bother to say this ..Happy Belated teachers Day !

[@shreyaskulkarni7612]

@@dataschool we probably may never meet, this line touched me. I care about my students, even those I will never meet! :)

[@pannaroy6569]

Best teacher i have ever seen .its a pleasure to watch your videos

[@DataScienceSchools]

This man has a solutions to every weird question that comes in my mind.

[@dataschool]

Great!

[@madhushreedas5113]

i am new to Python and Dataframe, you have explained it in a wonderful way to visualize the over all picture of data and way to extract ! thank you

[@dataschool]

You're welcome!

[@felipemendes7408]

the awesomeness of your teaching and will to share your knowledge has been already stated by several comenters, therefore I just want to say thank you!

[@dataschool]

Thank you so much! 😊

[@phuccoiinkorea3341]

The best teacher i've ever seen :D Thank you so much!

[@dataschool]

You're very welcome! :)

[@arjunkirpal9776]

Awesome content

[@hvsalza4068]

@@dataschool thanks in 2019

[@markl9451]

Thanks for posting this, very easy to follow and incredibly useful to someone just starting out with Pandas. Great work!

[@dataschool]

Glad it was helpful!

[@geofflyons3438]

You're a fantastic teacher. Well done & thank you!

[@SheIsSinging]

Fantastic... you are among the top 0.000001% of the best teachers on earth. Thanks:)

[@dataschool]

Thanks very much for your kind words!

[@williamsullivan9316]

Exactly what I was looking for. Explained very well. Thank you!

[@dataschool]

You're very welcome!

[@suman14san]

Why its always like, that i feel you are reading my mind. When you explain something, i get something in mind "what will happen if I do this" and just the very next moment you explain the same. God bless you, teachers like you have a different place in heaven

[@dataschool]

Wow, you are so very kind Suman! Thank you! 🙏

[@doumansarouei4523]

I really appreciate your time and effort in these amazing videos. I learned too much

[@dataschool]

Thank you so much for your kind words! 🙏

[@aguilarjulianandres]

You're right, is not easy to explain that way, the best teacher I've ever seen.

[@dataschool]

Thank you! 😄

[@aaronebake6564]

Exactly what I needed to solve a problem. Thanks a bunch.

[@dataschool]

You're welcome!

[@SheshadriMadhu]

You remind me of Dr Sheldon Cooper! :) Great Effort! :) Keep up the good work!

[@dataschool]

Ha! Thanks! :)

[@NicatBehbudov]

OMG YESS! I was thinking about whom he reminds me of :D That's right. Sheldon!

[@TheValobalok]

Except the fact that Dr. Sheldon Cooper is not at al la brilliant teacher as this guy! :D

[@radekrehacek9404]

Yes! I was thinking about this since the first video :D

[@mashalnabh2747]

Excellent as always !! I have already recommended your videos to my colleagues :). Something I am not able to find any where on youtube is how to use If Else to add new columns in a dataframe. Example: Based on Genre, I create a new column: If Genre = 'Drama' then New_Column = 'Col_Drama', Else if Genre isin(["Thriller","Horror"]) then New_Column = 'Col_Thril_horrer".

[@dataschool]

Thanks for sharing my videos! As for your question, that will be multiple lines of code though I won't have the time to detail it all out. Good luck!

[@katerynakasianenko6971]

Incredible videos, the best explanations of pandas I've ever encountered

[@dataschool]

Thanks so much!

[@ghulammujtabaadil2247]

I get alot from your videos. Stay blessed

[@dataschool]

Thank you!

[@ratiram9556]

Simply awesome explanation...👌👌

[@dataschool]

Thank you!

[@tejaswiniavsv5395]

Great lecture.. I loved it ! My question is .. For multiple conditions on one column, you have used isin method. What if I have multiple conditions on multiple columns ? How can I write in a concise manner

[@abhishekjain96]

First of all, THANK YOU SO MUCH for working on this series as well as the series on ML with Python. I have learned from from you than all the other resources combined. I have a question though. Why can't we use 'and' instead of '&' ?

[@dataschool]

You're very welcome for the videos! Regarding your question, it's a technical detail that was explained to me once before, but I don't fully remember the explanation... sorry!

[@mratanusarkar]

Python's and, or and not logical operators are designed to work with scalars. So Pandas had to do one better and override the bitwise operators to achieve a vectorized (element-wise) version of this functionality. exp1 and exp2 # logical AND exp1 & exp2 # element-wise logical AND using "and" You are implicitly asking Python to convert to a boolean value. NumPy arrays (of length greater than 1) and Pandas objects such as Series do not have a boolean value -- in other words, they raise ValueError: The truth value of an array is ambiguous. Use a.empty, a.any() or a.all(). stackoverflow.com/questions/21415661/logical-operators-for-boolean-indexing-in-pandas Hope this helps...

[@IncredibleGrim]

why did you use bitwise and (&) instead of logical and(and)?

[@morshedshahed1444]

I am learning python in a data analytics course, you are awesome. I subscribed straight away.

[@dataschool]

Thank you so much!

[@aguilarjulianandres]

Simply the best, so easy to understand to follow and to learn. Thank you. I have a question, what if I want to know which genres are associated to an actor? I mean, how can I split that column?

[@partspieces8165]

Your videos are so useful, the subscribe button is too clickable

[@dataschool]

Thank you!

[@harryconnor4157]

Hi - I don't normally comment on videos but these are fantastic! I thought I'd show some support :) Keep on pushing on! -Harry

[@dataschool]

Awesome, thanks for your support! :)

[@annelizabeth728]

Been coding for years and this is some of the best teaching I've ever seen. Thanks!

[@dataschool]

Thank you! I really appreciate it :)

[@kyleparsotan6799]

How would I use the mutliple filter criteria to a pandas df to drop a condition?

[@ahmadzaimhilmi]

.isin()... Thank you for this tips. I have been searching for this for a long time on how to simplify my search query script.

[@dataschool]

Great to hear! :)

[@drwiwern]

Your tutotials are very good, I do appreciate also way of speaking -friendly for non native users of English. I have a question about comparing two datasets made from filtered Pandas series (as You explained in this tutorial) how to compare them and plot this comparision? I ve tried with seaborn.jointplot but it returned error. Thanks

[@dataschool]

Glad you like my tutorials! Regarding your question, it's hard for me to say without knowing more details about exactly what you would like to see in the visualization. Good luck!

[@drwiwern]

I'd like to observe correlations between various sets of data. Thanks for answer!

[@dataschool]

Got it! Task 2 of this notebook might be helpful to you: github.com/justmarkham/DAT8/blob/master/notebooks/10_yelp_votes_homework.ipynb

[@Mor3Lif3]

One question please: what if, in minute 5:23 , the name of the collum was like “A.genre” or “33.duration” ? We would have to put on brackets then? Thank you so much for this videos!!!

[@dataschool]

That's correct. Glad the videos are helpful to you!

[@ryanhaberle4884]

awesome video series! Thanks for sharing the knowledge!

[@dataschool]

You're very welcome!

[@nihu6654]

You are the best teacher! Wonder if you can make a vedio to show how to create decile by an attribute in Python. Thanks

[@dataschool]

Thanks for your kind words! Could you clarify what exactly you are looking for?

[@MinecraftMartin]

Just want to say thank you for these videos. I was having so much trouble conceptualizing all this, being new to Python as well. You've helped tremendously. Definitely have a knack for explaining things for a general audience and not going over anyone's head.

[@dataschool]

That's awesome! Thanks very much for your kind words!

[@lahiruweliwitiya1]

Someone, somewhere around the globe, (and by that I mean some random Indian guy,) kills himself everytime someone subscribes to this channel.

[@dogruyatrm5703]

I have to say that you're an EXCELLENT teacher, greeting from Turkey! :)

[@dataschool]

Thank you so much!

[@ganeshbala6129]

Very clear explanations. Thank you!

[@dataschool]

You are welcome!

[@difesaperez]

Hi Kevin, thank you very much for this videos!! They are very useful. My question is what can i do if i want to filter a part of a date of a date series, for instance, i have a series with different dates like (YYYY-MM-DD) and i want to filter the column by (YYYY-MM) or (MM-DD) I don't know if i was clear about the question, sorry about my English and Grettings from Colombia! Thank you.

[@dataschool]

I think this video should be helpful for answering your question: kzbin.info/www/bejne/r3TKe3qpnJWLl5Y Good luck!

[@AmadoSerna]

THANK YOU for this video. I've been trying to figure out how to filter multiple columns by a list and could not figure it out. Using the | got it done for me! Thank you thank you thank you

[@dataschool]

You're so very welcome! Congrats for figuring it out!

[@malathimurugesan2316]

Great service . It shows that you take a lot of time and effort preparing these videos and uploading them. Excellent work. You have set an example. would like to see your profile.

[@dataschool]

Thanks very much for your kind words! Here's more information about me: www.dataschool.io/about/

Awesome!!! Very well done!!!

[@dataschool]

Thanks!

[@investosights]

HiI! Could you please specify that how can we know that when to use " ( ) " or " [ ] " while running any function or method?

[@chaitanyavardhan1176]

() this is a tuple and [ ] is a list

[@lajuklengtu]

You are good, you know that, right?

[@dataschool]

😊

[@aliebrahimi98]

thanks for the video. What about if you need to check two columns and if the value of the list isin either one?

[@Rasengandattebayo82]

Hey not sure if you would reply, but I was wondering if these filters can be used on pandastable to show me a filtered dataframe WITHOUT using the in built string query in pandastable gui

[@Vinay_Gurram]

Hey, It is really good video step by step. Really appreciated. Could you Plz let me know how to get the (highest in a column of) top 10 lists in CSV file using python pandas? - thanks, advace.

[@dataschool]

You're welcome! Not sure I understand your question - are you asking about sorting? kzbin.info/www/bejne/sIqXlaJ8a92Grrs

[@Vinay_Gurram]

You're amazing understanding about requirements man. Thank you very much dude...

[@dataschool]

You're very welcome!

[@MoonShine-bs6cl]

Can you help me to write this conditional formula in python , pls can you write and share me the code If client amount = income date then minus this value with client amount >=1, "received", " O/S" In this above formula we are using two different data frames, please help me in this, thanks in advance

[@jonathanfriz4410]

Excellent video, very well explained. One question. Isin is for exact match only? using you example if some row has "Crime." instead of "Crime" still consider that row? there is another method to incluid that variation on the write? thank you.

[@zeflyn]

My guy you are a lifesaver. Legitimately pulled me out of an onset panic attack involving deadlines at work. Appreciate you so much.

[@dataschool]

So glad I can be helpful to you! I appreciate your gratitude! Thanks for sharing and sorry to hear about your panic attack!

[@harithameka8328]

Thank you for informative video sir I have 12 months data 01-01-2020 to 31-12-2020 in a single excel sheet with sales Now I want to print only one month sales from the data How can i do it Please reply

[@nowyouknow2249]

Just watch this clip to refresh my pandas knowledge. Thanks a lot for these wonderful tutorials 😚. Please when would you be making new videos, I can't wait

[@dataschool]

New pandas video coming out next week! :)

[@thewelfarestate]

Excellent video! Very clear Mr. Pandas :D

[@dataschool]

Thanks! I like that nickname :)

[@ahsanyousuf9011]

what is the simpliest way for and operator? ... just like you tell about and operator at the end...great approach sir.

[@edwinuzoefuna3559]

Hi Kevin Your videos been a great help! Thanks How can I install geopandas?

[@jaquesderasmo5496]

Kevin, you are the best bro

[@dataschool]

Thank you! 😄

[@princembuisha8200]

how do I display the unique values in the dataframe column and inplace them?

[@marklittlewood2418]

Great presentation. You would think the designers of Pandas would be consistent with Python and use 'and' 'or' instead of & | but of course that would make life easy

[@dataschool]

I don't know exactly where I read it, but there is actually a technical reason why they weren't able to use 'and' and 'or' in this situation. But I agree, it can be hard to remember all of these rules!

[@marklittlewood2418]

One of the challengers of Pandas is that unlike old school data structure manipulation where you only needed to understand a small subset of commands eg how to loop how to IF etc etc and with this you could do anything although I admit with more lines of code. In pandas the challenge is that what ever you want to do there is a function call or routine but the down side is that unless you are a daily user, keeping all these in your head is nigh on impossible. Let me give you a practical teaching tip, please feel free to point out my poor thinking as I bow to your superior pandas knowledge. Do not bother introducing early on the head function, it has no use beyond its primitive function. Instead check the first few rows using ix, that way you have introduced a useful, later to be used, concept and one can unclutter some head space by letting go of head.

[@marklittlewood2418]

By the way please dont think I am being critical. I have mentioned before that you have a wonderful delivery and feel for getting ideas across

[@dataschool]

Personally, head is one of my most used functions... and ix has been deprecated, so I don't teach it! :)

[@dataschool]

I just released a new video about ix deprecation, and some alternatives to ix: kzbin.info/www/bejne/qpaYe6WJeLxggrs

[@snehipachchigar7726]

Best Best Best Teaching! Thanks a lott, Kevin! You are amazing. Love from India!

[@dataschool]

Thank you so much!

[@bhavyachaudhary723]

Thanks a lot,great video as usual.I just wanted to ask why exactly can't we use 'and' instead of '&'. I roughly get the idea that and is relational and & is bitwise but still?

[@mratanusarkar]

Python's and, or and not logical operators are designed to work with scalars. So Pandas had to do one better and override the bitwise operators to achieve a vectorized (element-wise) version of this functionality. exp1 and exp2 # logical AND exp1 & exp2 # element-wise logical AND using "and" You are implicitly asking Python to convert to a boolean value. NumPy arrays (of length greater than 1) and Pandas objects such as Series do not have a boolean value -- in other words, they raise ValueError: The truth value of an array is ambiguous. Use a.empty, a.any() or a.all(). stackoverflow.com/questions/21415661/logical-operators-for-boolean-indexing-in-pandas I hope this helps...

[@dataschool]

Excellent answer, Atanu!

[@mratanusarkar]

@@dataschool thanks a lot... Just love the way you are going... Already a fan! ps: just saw your series of playlists on statistical learning... if possible, do some series on "statistics" and "python libraries on statistics" And also on "deeplearning", "TensorFlow", "GANs" and other advanced topics in the field of Ai & ML Everyone is more of application and using it blindly approach... The theoretical step by step approach in depth is just what I was looking for, and I finally got to you, and am really happy :)

[@bhavyachaudhary723]

@@mratanusarkar Thank you.

[@subasurf]

Great video, clarifying what is such a simple thing, yet so many tutorials manage to make it seem so complex.

[@dataschool]

Thank you so much for your kind words! 🙌

[@abhilashc2965]

How to join two data-frames which has no common columns? Like white wine and red wine. I want a dataframe with column name wine color .. Thanks

[@redcat7467]

Helped me.

[@ghulammujtabaadil2247]

How can we filter the data by each column . ?

[@im4485]

I use and in python code. Are we not able to use it here because we are dealing with boolean?

[@vergilt8308]

What if you were to filter the title that has for example "Fight" on the title. How to do it?

[@andrederoide7611]

Fantastic explanation! Just one doubt, please, why does it do not work? est[(est.loc[1:10, :]) & (est.ativo == 'N')]

[@dataschool]

Glad the video was helpful to you! That code doesn't work because this doesn't count as a condition: est.loc[1:10, :]

[@chaitanyavardhan1176]

I want to filter 2 or more categories data in a single column so would I do that?

[@brad13579]

Better than the class i'm in!

[@dataschool]

Ha! Thank you :)

[@Svs1959]

is there any option for and condition?

[@kelvinedozieobed4899]

and God directed me to your page. Thanks

[@prabhuvenkatramaniyer7522]

Hi, Thank you for such wonderful videos... I just loved it.. I have one question.. that is "what is the opposite of isin condition., .. for example is (movies.genre.isnotin(['Crime', 'Drama', 'Action'])), so list of all movies except the mentioned genre.". I checked google but not getting the desired result.

[@dataschool]

Thanks for your kind words! I think this is what you are looking for: movies[~movies.genre.isin(['Crime', 'Drama', 'Action'])] The tilde character means "not".

[@manjulaannam257]

Hello sir, is this enough to learn as a data analyst.....

[@tobalaba]

Excellent videos, thanks. Question: I have a dataset in which I've been asked to filter the rows which contain a certain value in a certain column. How do I do that? they told me to use maks but I can't grasp it. Thanks!

[@dataschool]

Maybe this video will help you? kzbin.info/www/bejne/aHKpeIOag9NnfK8

[@eliasmichalis]

Hi nice work. Please can you explain how do I write a title on top in merged cells, and afrer the exporting data to xls or cvs . Is this possible .

[@dataschool]

I'm not sure what you mean, I'm sorry!

[@iloveyou143639]

Hi, how to apply multiple filter in loc rows ? ie: i tried movies.loc[[movies.dutation>=200],[movies.genre=='Drama'],['duration',['genre']] but it dint work , i can able to apply only one filter in rows like movies.loc[movies.duration>=200, [ 'duration','genre']]

[@ChartExplorers]

Try this: filter_criteria = (movies['duration'] >= 200) & (movies['genre'] == 'Drama') movies_filtered = movies[ filter_criteria ] Here is a video explanation that I created on this topic (see 2 min 30 sec specifically) kzbin.info/www/bejne/pJqcn5pqf95mkJo

[@happypet9449]

Thank you for great videos!

[@kyleparsotan6799]

how do you use that condition to drop that condition

[@MohamedGamal-pj6wd]

First I want to thank you for your great efforts 😊 Second I want to know how filter series of number as the example below 54328 67550 67854 23087 56890 I want to get the count of serial which ends with zero number.

[@dataschool]

Use a string method, which will output a boolean Series, and then use that Series as a filter condition.

[@amit28879]

I have a df which has to filtered on multiple conditions for ex: CONDITIONS: Column1

[@dataschool]

You can accomplish what you are looking for just by writing more conditions. Hope that helps!

[@subhadeepsarkar6402]

Hi , How to select particular column using mutiple filter criteria like movies[(movies["duration"]>=200) & (movies["genre"].isin(["Drama","Action"]))].title here i can get only title where these conditions are met but if i want to get title and star_rating then how to do that?

[@ChartExplorers]

It looks like you are on the right track. Just add two square pairs of square brackets [[ ]] with the names of the columns that you want returned. Be sure to put quotation marks around the column names. movies[(movies["duration"]>=200) & (movies["genre"].isin(["Drama","Action"]))][['title','star_rating']]

[@mohitbalani7956]

if(df.AOA

[@dataschool]

sum(df.AOA

[@magibis7519]

how write this filter in pandas. Can sombody help me df columns ==1 and df column2

[@dataschool]

Looks like you just need to write multiple conditions and surround each one with parentheses. Hope that helps!

[@sin3divcx]

Hello! So, how do i get all the Al Pacino movies with duration over 200 minutes? movies[(movies.duration >= 200) & (u'Al Pacino' in movies.actors_list)] won't do the trick...

[@dataschool]

You'll need a string method, see this video: kzbin.info/www/bejne/mKDJknZmfsieftE

[@terrancedejesus625]

Fantastic video and easy to follow and understand!

[@dataschool]

Thanks very much! Glad it was helpful to you :)

[@ajaysinha1338]

You're Amazing 🙌🙌 !! Sorry for those who is disliked his Video. If they don't understood from here, nobody can teach them...

[@dataschool]

Thanks for your kind words!

[@janviquanta6693]

Awesome!!

[@dataschool]

Thanks!

[@rajeshd3574]

Hi Kevin, I have been watching your videos its a great help to the DS community. I have a question. Can you help me by answering the below items, when you get some time? def get_uniquevals(df): for col in df.columns: #run for loop for all the columns if len(df[col].unique())

[@dataschool]

It's hard for me to say, sorry!

[@QuickNewsFeed]

hi if i have two data frames with diff indexes,, same length, and i want to filter data from first using condition on a column of the second. Is it possible to implement in some way?? when i tried..df1[df2[4]>40]...i received this error.......(Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match),,i understand this is because of diff index...but is there a way??

[@dataschool]

I think you would have to reset one or both of the indexes so that they match.

[@ItsWithinYou]

your each video is a masterpiece of learning pandas. Many thanks for your efforts and nobility to share your knowledge...

[@dataschool]

Thank you so much! 🙏

[@MrNaveengoud]

Hi Kevin! Very good explanation Sir! I am working as a Data Analyst and I do clean the Survey data using Quantum and SPSS. How can I do it using Pandas or Python. Anysource?

[@dataschool]

A lot of common data cleaning tasks are covered in my pandas video series: kzbin.info/aero/PL5-da3qGB5ICCsgW1MxlZ0Hq8LL5U3u9y Hope that helps!

[@dishydez]

How do I use the .isin() for values across other columns? I tried it and it works for values in one column only. I want a shorter code for values in other columns. Great teaching btw!

[@deepakmandal8129]

how to get multiple columns with single condition on imdb_ratings dataset suppose i want column [ ''title", "genre"] that have star_rating < 9

[@anonify88]

df = movies[(movies.star_rating < 9)] df[['title','genre']] Kevin may be able to help with a shortcut. Thanks in advance.

[@nirodhasamapatti3527]

Hi , i encounter an error as syntax error when using df[ df.col_1 > 0 ] where df is a dataframe with column name col_1 . Any idea , how to resolve. Thanks.

[@dataschool]

Maybe col_1 is not numeric?

[@Atulmishra-hs8ch]

Hi, Could you please explain how to filter rows on number of missing values and dropping the rows where missing value is greater than 40 or 60%.

[@dataschool]

I don't know off-hand, I'd have to think about it to figure this out. Good luck!

[@manishdpsg]

Thanks for your valuable lesson. May I know which screen video recorder software do you use because clarity is great. Thanks

[@jessicabrock3220]

Does Pandas have a between clause?

[@esspi9]

The Pandas-God!

[@dataschool]

🐼

[@mayurgala1613]

Can i plot on filter data if yes yes. Please make video on that