SQL Queries For Pandas DataFrames

Key Takeaways

what is going on guys welcome back in today's video we're going to learn how to use SQL statements to query data from Panda's data frames so let us get right into it it's not a game it's a [Music] red all right so obviously for this video we're going to need pandas which is an external python module so we need to install it and in addition to that we're also going to need pandas ql which is basically pandas SQL but with only one s so pandas ql uh which is also an external python module so in order to install those two we open up a command line and we type pip install first of all pandas if you don't have it already and then pip install pandas ql to enable to allow us to do uh the SQL querying with pandas data frames and then what you need is you need some data frames so you can either uh use something like pandas data reader to get some financial data from the web you can create your own data frame by creating a dictionary parsing it into a data frame or you can import just some CSV file which is what I'm going to do so I have here this simple CSV file uh where I have the ID name age height gender of a bunch of artificial people here so we have one to eight for the ID we have a bunch of names here we have ages and Heights and then we have MF for male female um that is just some sample data you can create whatever data you want you can put it into a CSV file and then you just import it so the focus not going to be obviously on the data the focus is going to be on quarrying using SQL statements and for that we're going to start by importing pandas SPD which is the usual Alias and by importing pandas qls uh PS I'm not sure if that is like the uh the goto best practice Alias or if you would use an alias at all but we're just going to use PS for this video here and now we're going to say data equals PD read CSV and we're going to get the data. CSV file here and we're going to say data set _ index is going to be uh ID and we're going to assign that to data and then we're going to take a look at it by printing data to see if that works and now you can see we have name AG height gender as the columns here we have the ID column which is the index and this is basically what we have now and now we might want to find for example all persons all people that have an age above 30 for example now before we go into the SQL query in we're going to do a simple pandas query to see how it would work without SQL statement so what we can do here is we can say print data. query which is a pandas function without using pandas ql um and here we can just uh specify a string expression that uh is the query that we want to have for example we can say okay the age has to be above 30 and then what happens is we get only the entries where the age is above 30 so you see 1 3 5 5 68 Rd IDs where this is the case we can also of course say greater or equal to 30 and then we get uh I think an additional record here anna because the age of Anna is exactly 30 so this is how you do it without SQL and this is fine uh you can also do stuff like for example gender equals and then single quotations F to only get the women as you can see here so this works and this is the simple way to do it uh with pandas with only pandas now we can use pandas ql to do the same thing with SQL statements and for that what we can do is we can say uh print or actually let's first Define the SQL query in a statement here we can do something like select everything so select star from and um then essentially what we have to provide here is we have to provide the data uh the data frame so in the string we're going to say data and this name here has to be the same as this name here so this query will be used on this uh data frame here so the data frame will be treated as a table which it is essentially so this is a database table now we're going to say select everything from data where and then AG is greater than 30 for example and then what I can do now is I can say print PS SQL DF so ps. SQL DF and then we execute the SQL query um and we pass the local post function here as well and then you can see we have the exact same output so now the difference here is that we get now U uh again the ID as a column here so we have now a different index we could also just store the data frame set index to ID again and drop the index that we have here but essentially we get the entries that we're interested in and of course we can do the same thing here with uh for example gender equals uh I think we need to provide it here again in single quotation right there you go gender equals F uh and I think we should also be able to do end so age is above 30 I think this should work as well there you go we only have one entry left here so this works uh and we can do even more complicated stuff like for example let's say we have now uh data 2 which is going to be a second data frame we're going to just create it now from scratch we're going to say here the Pan's data frame of a given dictionary the dictionary is going to have an ID uh list and it's only going to have one ID in that list ID 2 and then we're going to say that we want to specify a job here and we want to say the jop list is just programmer so this is essentially just uh a data frame now we can actually print this data 2 let me just oh come on data 2 let's common this out here um this is now just a simple ID job data frame that we have here now we can take take this and merge it we can join it via an SQL statement to make an advanced query with panda ql so what I can do here now is I can go ahead and say the SQL query that I want to make is the following it is Select everything from data and I can also provide an alias like d so select everything from data D left join um and then I can say data 2 D2 is D alas now on D ID is the same as D2 doid and of course you can play around with more details you can make it more professional everything but we can now go ahead and say print ps. uh SQL DF sqlquery and then locals again and then you will be able to see that we have now ignore the first column here but that we have here now um Anna which is id2 and she now has a job all the others do not have a job this is because we did a left join which means that we keep all the entries even though the job is none for all of them um we could also drop this second ID column here but essentially uh since we joined on the ID we now have for Anna which is which has the same ID here the drop programmer this is just some very basic example but that is how you do it this is how you can use SQL queries to get data from data frames and you get a resulting data frame from from that so you can get uh a smaller version of the data frame uh where certain conditions are met you can do joints you can do a lot of things you cannot do everything I think stuff like uh I tried to do I think a full outer joint and I think the error message set it's not supported as far as I remember there you go um what was it write and full outter joints are not currently supported so you have some limitations here but essentially this is how you can do it in Python so that's it for today's video I hope you enjoyed it and I hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you in the next video and bye [Music]