Building dataset - p.4 Data Analysis with Python and Pandas Tutorial

Key Takeaways

hello everybody and welcome to part four of data analysis with python and pandas in this tutorial what we're going to be doing is beginning to build our data set for a real estate analysis so to start uh we're going to come over here to quondo and now you will need to sign up make an account you don't actually need to at this point but B by the next video you will and the code is going to include code that will require an account as well as um we're going to need the quondal module so to get the quondal module you can either install it from you know their website or again you can go pip install quondo simple as that once you have quondo you're able to use quandle doget and you can get and then use a little ticker and boom you're done so let's say we're looking for um housing price index we search we wait and we get this Freddy Mac database and then in here you can already see we've got house price index for Alaska Alabama Arkansas and so on so we click more from this database and this is the housing price index basically for every state at first anyways and then there's some other information here too now what if we wanted to grab the housing price index for every single state because the idea is that uh the housing markets are probably all going to follow each other okay so um there might be some Divergence and then there might be some laggards and there might be some uh leaders right but what ends up happening is every time there's any Divergence as far as at least directionally uh we're concerned we almost pretty much promis that we're going to get back to the mean okay of of the housing price in uh index so the idea first is to confirm that hypothesis so the hypothesis is that all housing markets follow the same path they might have a different um see magnitude of that path right so if if one market goes up they all go up or if most markets go up a specific Market is going to go up maybe not by the same degree but it's going to have to go up so here we're saying they're definitely at least correlated to some degree but we need to be able to test that and find out if that's even true we believe that to be true but we want to make sure so we want to pull every single um housing price index but we're not going to go through here by hand click Alaska download click Alabama download and then we're also you know right now we've got a list of 50 states that we would need to use and I mean we could type them all out by hand but that's going to be kind of annoying so instead what we're going to do is like let's click Alaska and we can already see up here the it's hpia a but we can also see the quondo code over here f Mac HPI a and you could also click on Python and get the quondal doget now if we scroll down the other thing you'll note uh that we'll kind of show later on though is like let's say you don't want all the way back to 1975 like you could modify this to be uh 1999 okay and then when you go to now that you've changed that when you go to click python it's going to add that trim uh and that makes the data that it's returned to you start at $ 1999 which is pretty ni anyway so we know this is the Quanto code and what we want to do is find a list of the United States and just automatically generate this quantal code uh rather than typing it out by hand like all the 50 states so uh the first step we're going to come over here and we're going to go ahead and first import quondal that's the quondo module that we had uh had you install right away and then of course import pandas as PD now when you make an account on quondo you can go to your um you know your little name uh and then go to API or something like that and it'll have your API key there you can write your API key in your script I'm just not going to because I don't want everybody to use my key so I'm just going to have API key equals open and I've put a little file in here called quondal API key. text the intention to read and then. read and now that's my API key again you don't need to do that if you don't wanted to next I'm going to say DF equals qu. getet and we can throw in basically this right here so we could say get fmac HPI for Alaska and then uh we'll do um wonder what I always thought AK was for aransa but I guess I'm wrong maybe it's a for organsa anyway moving on off token equals API key and uh that's that then we can print DF head and we'll get a returned data frame in a moment now you'll see uh that we have the date here and it's already being treated as the index which is pretty nifty if you're using the quondal uh module you'll have um the automatic assignment there of the in index now I'm going to close out of this and now what we want is the uh a list of the 50 states so let's just do um 50 USA States abbreviations or something like this um and then yeah so you come over here 50 US States abbreviations and eventually you find the Wikipedia page so we would click on that and we're kind of like looking around and here we go go we've got quite a few um looking is this I'm trying to think if this is the one that I actually used list of this one's US state abbreviations but the one I was looking for is actually just the US state so let's see let's do us 50 US [Music] states I want that with simple let's see maybe this is it yeah there we go so list of US states and it's this nice table with the abbreviations in it easy yeah AR for AR so had to figure that one out anyway moving this over so what we want to do is we want to read the table from here so we could use Python and regular expressions and split out this table or we could use something like beautiful soup or something but we want a table and this is a pandas tutorial and we actually happen to have read HTML so let's do it so moving this over we are going to say fit States in equals pd. read HTML and we want to read this link copy that over here paste now when you read HTML this puts the information into a list of data frames even though there's only one table it's going to make like multiple attempts at reading this table it might also read this little list here and it'll probably read this kind of table here so you think like oh there's only one table here I promise there's more than one table and it's going to find it so fit States will be a list of data frames okay so we can at first print F States and I'm going to go ahead and comment the these two lines out we don't need those two lines so let's save and run that real quick yeah so there you go you got a bunch of junk in here we can pretty much deduce that each like uh you know this right here this is the beginning of a list you see the square brackets then you've got data frame one would be here and then data frame number two and so on so data frame one would have the index value of a zero so it looks like the First Data frame is the one that we want that's index zero so we can close out of this and we can say instead of F50 States we can say we want fit States zero right so at the moment fit States alone and in fact let's just do this let's say this this is a list then copy that paste uh 50 states zero this is a data frame right so now we're going to print F State 0 now 50 state 0 is a data frame of its own and then what are we looking for Well turns out we're looking for column zero so that's a column which will output to us basically a series so let's copy this paste and again so now we want column zero so zero this is a column fantastic and now of column zero we want everything from element one onward because element zero is abbreviation or you know index Z is abbreviation so what do we do now now we say for abbv for abbreviation um what do we want to do infi states 0 0 one colon we are going to print and we're going to just print it out just to see that we got it right fmac HPI uncore plus string version should already be a string but just in case abbv okay so now save and run that and here are all of the basically uh I don't know what to call it ticker or whatever the API name that we want a query for right so Alabama Alaska Arizona Arkansas California Colorado Connecticut and so on all the way down the list so now we've got that now all we need to do is create new data frames based on these and begin kind of adding them together so we've got a lot of stuff that we're actually going to have to cover in order to add them together we have to first learn how to actually concatenate and combine data frames so in the next tutorial we'll be talking about how you go about combining data frames whether they share an index or not combining them on columns and all kinds of stuff it's pretty uh there's a lot of options that we can do here uh obviously because there's a lot of ways you would combine data frames right so anyways that's what we'll be doing in the next tutorial questions comments below otherwise as as always thanks for watching thanks for all the support and subscriptions and until next time