places and names and getting precise latitude longitude coordinates So you can produce a nice little google map like this Um and But if since google has made a rate limited api, I've actually Prespired a copy of a google data and I have my own sort of fake google api And so you you can do your assignments and test all your code using my fake api Um, which has no rate limits and and has no problems, but the it's only a limited set of the data And so this is the basic process and it's it's one of those things that it's it follows that basic personal data modeling Personal personal data mining pattern. And so here's this api, which is either google or me I've got my own dr. Chuck version of this dr. Chuck net version of this and there is a An input queue of the location So this is the user data where they just put in the name of where they think they live university of a tube again or something and So this is the queue of the things that are to be retrieved And in in my case when I built this map for the first time there was like 15 000 And I it took me days to get this and so it would stop and so what I would do is I would you know Read the first one into this geo load dot py check to see if I already had it in my database If I didn't already had database I would go into the api and pull the data down and I would put it in the database And then I'll go to the next one the next one the next one And so you know I might get a thousand in my database and then it blows up or I'm told I can't go any further So I wait 24 hours I started up and it reads the first thousand and says oh, they're on the database already and then it starts at 1001 And then it adds that and adds that and then until it stops and so it took me Several days of processing to get this data right now I didn't have a separate cleaning process because this data is pretty simple I was pulling out the the json latitude and longitude etc And so I didn't have to do two separate processes to clean this data up It was clean enough right as I pulled it Because I was talking to an api if you're talking to the html sometimes it gets nasty and ugly And so then I wrote this program that just reads through it It just does a select and you know reads through the stuff and it prints out some summary information and tells you what to do it also prints out you'll see this pattern because um, You know, I'm I'm divisualizing using browsers html and this happens to be using the google maps api um And putting all the data in a little javascript file So these end up being assignment statements in javascript you can take a look at that file and uh all the data Shows up as assignment statements in the javascript and then when this html loads It reads this file and puts up all those pins as long as you have access to the the in browser javascript api So the next thing we're going to talk about is page rank, which is spidering now html We talked a lot about this spider html get some links and so up next we're going to actually build a real database Full-featured search engine using page rank This is another worked code example You can download the sample code zip file if you want to follow along And the code that we're working on today is what I call the geodata code And that is a code that is going to pull some Some locations from this file We're we're simulating or using the google Places api To look places up and so we can visualize them on a map and so this is the basic picture If we take a look at this where dot data file It's just a flat file that has a list of organizations and this actually was Pulled from one of my moog Surveys we just let people type in where they were went to school and this is just a sample of them So this data is read in by this program geo load dot py And if you recall this google geodata has rate limits It also has api keys, which we'll talk about in a bit too And so the idea is this is a restartable spider like process And so we want to be able to run this and have it blow up and run it and start it and not lose what we've got Right and so this is unlike is some though So we're not now using a database as well as an api But in order to work around the rate limits of this api We're going to use the database with a restartable process And then we'll make some sense of this and then we'll visualize this but In the short term, let's start with geoload.py code geoload.py take a look here. So A lot of this hopefully by now is somewhat familiar to you url lib json sql lite And so I mentioned that the google apis these used to be free and did not require an api key But increasingly they're making you do api keys for especially New ones and so what happens you you can go to your google places. I mean go to google apis and get Uh get an api key and you can put it in here It'll be this long big long thing that looks like that And then if you have an api key you can use the places api And I've got a copy of a subset not all of it a subset of it here at this url As a matter of fact, you can just go to this url in a browser And it will tell you a list of the data that it knows about okay And um and and I made it so that that does the same basic protocol with the address look You know address equals as the google places api So this will just change how we retrieve the data either retrieve it from my serve nice thing about my server It's got no rate limit It's really fast and you're not fighting with google all the time And it means that perhaps if you're in a country that google is not well supported you can use my api I mean that's really strange that somehow my api is More reliable and available than the google one, but it's true So we're going to make a database We're going to do a create table if not exists and we'll have some address and we're really just caching the Geographical data we're going to cache the json one of the things we do when we build these processes is we tend to simplify these things And not do all the calculation and parsing the json Just load it and get it in and load it and get it in and fill the data up in this database And so that's what we're going to do um Because python doesn't ship with any legitimate certificates. We have to sort of ignore certificate errors We're going to open the file and um We're going to loop through it and pull out the address from the file and we are going to um Select from the geog data where that address is the address Let's move this in a bit And um, and so we're going to do a select and pull out that address And uh, the idea is is if it's already in the database, we don't want to do it So we do a fetch one and pull out that first thing which is the that will be the json right there If we get that we'll continue up. Otherwise, we'll keep keep going Pass just means don't blow up. So we accept and we just do a pass. That's like a no op and um, we're going to make a dictionary because that's what we do for the The key value pairs everything you've seen so far. I've used constants here But because we may or may not have an api key query equals and then that's the address and then the key equals and then the api key If you recall url encode adds the pluses and question marks and all that nice stuff We're going to retrieve it. I'm going to read it and decode it print out how much data we've got And add account and then we're going to try to parse that json data and print it if something goes wrong And as we've seen that at this top level of this json data from this geocoding api is an object Which we'll see a little bit of it in a bit And it has a status field in it and the status is okay if things went well um So if the status is not there that means our javascript is not well formed or not how we expect it If the status is not okay or not equal to zero results then print out failure to retrieve and then then quit And then we're simply going to insert this new data that we just put in And then we're going to commit it and every tenth one. This is count mod 10 We're going to pause for five seconds And we can hit ctrl c here and then we're going to play the do the geodump. Okay, so let's just run this geodata python So let's do an ls So we don't have oh, we do have let's get rid of from a previous test geodata.sqlite. So we'll start with a fresh A fresh set of data and run python geoload.py Of course, i'm always forever making the mistake if we're getting python 3 So you can see that it's running and it's adding the query and in this case I don't have the api key and it's putting the pluses in and that's this part here with all the pluses That's the url and code and you notice it's pausing a bit now depends on how fast your net connection This may or may not go so fast, but this is not that much data. So it should it's like only 2000 3000 characters And so it's working and talking to my My server and the interesting thing here is I can blow this up. I'm going to hit ctrl c And windows you'd hit ctrl and linux you'd hit ctrl c and then windows I think you'd hit ctrl z depending on what shell you're working in but i'm going to hit ctrl c And you see I sort of blew it up right and that's it causes a trace back a keyboard keyboard interrupt trace back I do an ls minus l You can see that now this geodata is there now in the in the name of restarting. I will restart this And you will see that it checks and skips and so all it runs this code here where it's Right here it grabs it and finds it in the database So you'll see it say found in the database really quick chop chop chop and go really fast And then it'll go back to catching up where it left off And so all those up there they did not actually re retrieve it because it knew about those things And so now it's catching up and doing some more and doing some more and doing some more And then I'll hit ctrl c it has a little counter in here that basically if it hits 200 it stops and you have to restart it You could obviously change this code. You could make it so it didn't sleep Doesn't hurt to sleep for like a second after every 100 or so if you want you could change that code um, and now uh, let's just hit ctrl c And blow it up ls minus l Um, and there is another bit of code and this code it's always good to write these really simple things And so we're gonna now we're going to import sqlite and json. We're going to connect ourselves up We're going to uh open Except this is a utf 8 because it's a ut we're going to open this with utf 8 and um We're going to read through and in this case. We are going to um Decode We did select star from locations and if you recall locations has a A location and a geodata And so the sub zero will be the location and the sub one will be the The geodata and we're going to parse it Convert it to a string and then parse it If something goes wrong with the json, we'll just keep skipping it We're a check to see if we have the status in our json um Let me run the sqlite browser here now open database. Let's take a look at what's in this database Oh Where are we code 3? geodata geodata sqlite So this is our the data we've got So if you make this a little bigger if I can can I make that bigger? Yeah, it's not going to show us much So you can see that these are the addresses in the geodata. That's just the json So that's the json that we got and it retrieves it and so this is a really simple database That's just a sort of spidering process run run run But now we're going to run the geodump code which is going to read this and dump this stuff out and print where.js So it's going to actually parse this stuff and that's code. We've seen before um So we're actually reading it and this line goes into the results It's the results is an array. So if we go into results results in an array We're going to go grab the zeroth item in that array And then we're going to go find geometry And then location and then lat and long for the latitude and longitude And then we're also going to take the actual address out of the formatted address Right here. So in this In this bit of code, we're actually parsing the json and we're going to um clean things up Get rid of some single quotes. This kind of data cleaning is just stuff after you play with it for a while You realize oh my data is ugly or does this i'm going to print it out And then i'm going to write this out and i'm going to write it into a javascript file and so the javascript file is this where.js and um This i'll show you what it looks like. It's going to be overwritten This is the one that came out of the zip file. It'll have the latitude the longitude and we're going to use Javascript to read this in this where.html file It's going to actually read this right there and pull that data in and that's how we're going to visualize I'm not going to go into great detail on how the visualization happens But that's what's happening and so we're going to write that so we're going to actually write this to a file So let's go ahead and run this code and say python 3 geodump Okay, so it wrote 120 records to where.js So if we look at where.js, this is now the new data that I just downloaded moments ago And it says open where.html in a browser Now this will you'll need the google maps api And you might not be able to see this depending on where you're at but here you go with uh Google maps locations, and I think if you hover over this You can see and you see the utf why we're there in that particular thing Why we had to use the utf8 when we wrote the file so that we didn't end up with trouble writing the file out And so there you go. And so that is a simple visualization and Just a simple visualization it wrote this where.js If you are smart with html and javascript you can you can look at this where.html file It's really just reading through a bunch of data and putting the points. That's that's all there is but i'm not going to to go through that so At least not in this and so I hope that this was useful to you and Thanks for watching So now we're going to write a search engine do some of the things we're going to do page rank and we're going to Visualize it in a in a web browser and show the weights We're really only going to do page rank on one page because you want to have links that More than one page that points to this to a page so that you can figure out which pages are more or less important And then we visualize it. We'll run the page rank algorithm and we'll separately do all this So at this point we're going to do pretty much the web crawling the index building and the searching We're not going to really search it. We're going to visualize the index But you could write a simple program to do searches for keywords and figure out which page was the most likely page for a keyword That would be a fun additional thing to do So the web crawler is this program that hits hits a page pulls down the html Parses the page looks for links makes a queue of incoming links that are as yet unretrieved And and I'm going to do this in a simple sqli database and starts out with The database basically starts with one link as the starting point And then it retrieves that page and then you see the database End up with lots of unretrieved pages and it goes back in and picks a random page and retrieves that one And then it just expands and expands This code that I've built that you're going to play with is only stays on one website Otherwise it would go crazy and but of course google doesn't use an sqli database running on your hard drive But you get you'll get the idea. You'll see this thing exponentially gain links And you'll run it for a while pull down a thousand web pages or whatever but of course Make sure that you Don't violate any terms conditions. And again, I've got some data sources that you can use and they're not rate limited But you can also use things like wikipedia Which I think they sort of discourage you or dr chuck.com which has no rate limit or or who knows what right? So so just be careful. Don't do this on facebook and don't do it on google. Don't get yourself in trouble and If you're using you know A internet connection where you're paying for bandwidth. Be careful So this is the idea of the web crawler and this isn't my picture. This is the classic picture of a web crawler Read a page parse it take all the URLs and stick them in a queue Crap again and again. So for us the scheduler is going to do it as long You'd say oh do a hundred pages or it runs until it blows up I mean and and again these processes that are have the network in the loop It's really important that they behave well when they blow up And that's why databases are so useful because you can be writing along to the database and some random thing happens and blows your Blows your data up and you start over. So you're reading these things. You're storing each page building up your storage Etc. Etc. So you just keep on doing that and with this program You you'll be able to retrieve some stuff then run the page rank Then you can retrieve the more and then you can run some more page rank and you can kind of see How google sort of evolves its index over time. Of course, we're we're so much simpler And like I said be careful when you crawl You're going to run a crawler that just goes as fast as it can But google doesn't do that. It's careful not to Overwhelm any websites. It's trying to be smart about the use of your bandwidth on your website There is a file Our code won't bother looking at this But there's a file called robots.txt that real web crawlers look at and it gives a list of the things you're in Are allowed to look at and not allowed to look at and so if you go to google and you see a search It says we're not allowed to show you The summary text of this page because of the robots.txt It's there and you can go and you can actually see A robots.txt Like on just go to any website. It's at the top root Blah blah blah blah slash robots.txt. Don't it's not a path. It's not slash this slash that slash something else robots It's at the very very top of a website The index building uses the page rank algorithm and the whole goal of page rank algorithm is to Figure out which pages have the Most best links. So having the most links is really easy. You can just say how many links go to this But the problem is is you got to figure out the value of those links And then you have to how do you figure the value of those links by looking at how many good links come to it So it turns out that it's a an infinite problem. It's an infinitely difficult problem to to use page rank But you can approximate it and what happens is after a while it converges To a reasonable value and so we're going to run the search index and Each time it runs you're going to see that it says, you know, how much did these numbers change and what happens is In the beginning they change very wildly, but quickly they flatten out and it has the the best way to think about the the page rank is Think about how water runs where You have a small little stream going by a house and sometimes it rains sometimes it's dry and sometimes And and there's like a little little lake And the stream is always running and it doesn't go up and it doesn't go down It might go up a little bit if it rains a lot But in general there's sort of a steady state meaning that whatever water's coming in is about the same as the water going out So we think about this in terms of web pages The the value of the links coming in is roughly the same as the value of links going out So when that starts to balance the in and the out value from each of the nodes, then You've got a pretty stable and so what google does is they have a really relatively stable assessment of goodness and value of pages And they use that to commute page rank and then they throw a few more pages in and it kind of has to adjust for a while But it reconverges and so this is a calculation that generally converges And it doesn't vary wildly and that's why it you know google is pretty good at Kind of arriving at the true value of something. So let's take a look at what we're going to do in this application Again, we have a a a file That is going to spider the web and we only have one database again in this one We'll have two databases in the next one And so this is spider is the restartable part and what we actually do is we put one url in the starting url And then spider walks in and asks are there any unretrieved pages And it does that randomly and sort of picks among the unretrieved pages and says, okay, great I'll go retrieve that page And then I'll parse that page and then I'll put in a bunch of new unretrieved pages Okay, as well as the text of that page and then a bunch of unretrieved pages And then it'll go back up and I'll say oh give me one of the randomly non retrieve pages And I'll grab the next page and pull that page down and then add to it And so this is like there's a page and then a to-do list And then this one becomes a page and then adds a few more things to the to-do list And so the to-do list or the the unretrieved urls grows very rapidly Um And the retrieved ones grow sort of as you retrieve them one at a time But you've always got this long list if you have a really short site that only has like two links If you start at dr chuck.com Slash page one dot htm it'll go to page two and then go back to page one and it'll be out of things It'll have retrieved all of the pages And so if you have a website that has no external links or has a very few pages and they point to each other This will run out of things to do But if you go to a page like my blog or the the code that I the the sample stuff that I have up for you Despite her for testing on dr. Chuck net It'll run for a very long time and you'll have far more Pages to retrieve than pages that you retrieve, but that's okay At some point you can stop this Maybe it stops because you ran out of bandwidth or maybe your computer went down or who knows what right? But it's okay This is a restartable process because it always has some pages that are retrieved and some unretrieved pages You start it back up it picks randomly from the unretrieved pages The database is the sort of persistent state of your spider rather than a bunch of dictionaries or lists inside the python Which go away when the program dies And uh, and so at some point you have let's just say a few hundred pages in here and a few thousand unretrieved pages You can run the page rank algorithm And what the page rank algorithm does is it loops through all the pages and figure out which pages are linked to which pages And then reads the numbers and then updates the numbers and then does that some number of times And so this is where the numbers all the pages sort of start out with goodness of one Uh, I think this printout is showing that goodness of one and then it changes and then the goodness goes to The some of the goodness goes up to two some of the goes it goes to seven and whatever But then it does this over and over and then it uses these numbers and then they change again And so there's a number of time steps that this page rank runs And you will see as the page rank runs when we show you the code you'll see the average sort of Change in these numbers across all these things and you'll see that it the average goes down very rapidly As you get through and so usually with a few hundred or even thousand pages like a hundred Plus times during this algorithm and these numbers have converged And that's when you sort of can begin to trust the numbers now There's this one program called sp reset which sets all the pages back to one so you can start this over So if you were to spider for a while run sp rank for a while play around And then you wanted to spider some more and start it over you could say oh, let's start the page rank completely over Or you could simply take the new pages and and and and watch it adapt it either way This is just a way to reset all the pages to have sort of their initial value of a goodness of 1.0 So at some point you run this this this runs really this part here runs really slow This part runs super fast like in the blink of an eye. This one is pretty fast Um, and then at some point you've got these Pages that have you know numbers on them. They have values on the pages And there's a couple of programs that allow us to visualize that one is the dump Which just reads it and checks to see it shows the the new page rank the old page rank Um, and various other things and shows just a way to dump it And then there's this thing that reads the whole thing You say I'd like to do 25 the top the best it sorts it by page rank and then produces a javascript file It has just the the numbers in it. And then there is some html and a visualization library called d3.js Which you can read about that when the html starts it reads this and has this nice force-directed layout of the page rank and you can hover over things and you can see What page rank you've got And so and so that is the page rank algorithm that we're going to do and up next we'll do the Largest and most complex of these things and that is the email. We're going to spider some email Which is about a gigabyte of data. Okay We're doing a bit of code walkthrough and if you want to you can get to the sample code and download it all So that you can walk through the code yourself What we're walking through today is the page rank code. And so the page rank code Um, let me get the picture of the page rank code up here Here's the picture of the page rank code and so the page rank code is a Has four chunks of code that are going to five chunks of code that are going to run The first one we're going to look at is the spidering code and then we'll do a separate look at these other guys Later, so the first one we'll look at is spidering and again. It's sort of the same pattern of we've got some stuff on the web In this case web pages. We're going to we're going to have a database that sort of just captures the stuff It's not really trying to be particular intelligent But it is going to parse these with beautiful soup and add things to the database Okay And so then we'll talk about how we run the page rank algorithm and then how we visualize the page rank algorithm In a bit now the first thing to notice is that I've got to put I I put the beautiful soup code in right here Okay, so this is you can get this from the bs4.zip file There might need to be a read me. No, but there's a read me somewhere But you got to get used beautiful soup You got to put this bs4 zip or you have to install beautiful soup for your stuff So I provide this bs4.zip as a quick and dirty way if you can't install Something for every for all of the python users on your system So that's what it's supposed to look like you're supposed to have it unzipped right here in these files And I don't know what dammit.py means that came from beautiful soup If you look it's in their source code. So I didn't I'm not swearing it's beautiful soup people are swearing. I'm sorry. I apologize Okay, so the code we're going to play with the most is in this first one is called uh spider.py And you know, we're going to do databases. We're going to read URLs and we're going to parse them with beautiful soup Okay And so what we're going to do is we're going to make a file Again, this will make spider.sql light and if you're here, we are in page rank and else minus l spider.sql light is not there. So it's going to create the database. We do create table if not exists We're going to have an integer primary key because we're going to do foreign keys here We're going to have a url and the you're going to the url Which is uh unique the html which is unique whether we got an error And then for the second half when we start doing page rank We're going to have old rank and new rank because the way page rank works is it Takes the old rank computes the new rank and then replaces the new rank with the old rank and then does it over and over again and then we're going to have a A many to many table which points really back. So I call this from id and to id We did this with some of the twitter stuff Um, and then this webs is just in case I have more than one web, but that really doesn't make much difference okay, so What we're going to do is um We're going to select id url from pages where html is null. This is our indicator that a page has not yet been retrieved And error is null ordered by random And so this is our way this long bit of stuff and this not all this sql is completely standard But this order by random is really quite nice in sql light limit ones is just randomly pick a record in this database where this true is true And then pick it randomly And then we're going to fetch a row and if that row is none right We're going to ask for a new web a starting url And this is going to fire things up and we're going to insert This new url. Otherwise, we're going to restart we we have a row to start with And otherwise we're going to sort of prime this by inserting the url We start with And insert into it if you enter it just goes to dr chuck.com which is a fine place to start And then what we do is we This this this what this does is it's page rank is it use this this webs table to Limit the links it only does links to the sites that you tell it to do links And probably the best for your page rank is to stick with one site Otherwise, you will just never find the same site again if you let this wander the web aimlessly And so I generally run with one one web which web this should be probably called web sites And I pull in all the data and I read this in and I just make myself a list of the url the legit urls And you'll see how we use that and the webs is how many what are the legit places we're going to go because we're going to Go through a loop Ask for how many pages? And we're going to look for a null page again. We're using that random order by random limit one and then we're going to have a We're going to Grab one we're going to get the from id which is the page we're linking from And then the url Otherwise there's no one retrieved and so the from id is when we start adding links To our our page links. We got to know the page we started with and that's the primary key We'll see how that primary key is set in a second So otherwise we have none and we're going to print this from id the from id and the url that we're working with Just to make sure we're going to wipe out All of the links Because it's unretrieved we're going to wipe out from the links the links is the connection table that connects From pages back to pages and so we're going to wipe out So we're going to go grab this url We're going to Read it We're not decoding it because we're using uh, we're using beautiful soup which con We're using beautiful soup which compensates for the utfa and so We can ask this is the html error code and we checked 200 is a good error And if we get a bad error, we're going to say this error on page We're going to set that error. We're going to update pages that way We don't retrieve it ever again We basically check to see if the content type Is uh text html remember an http you get the content type We only want to retrieve we only want to look for the links on html pages And so we wipe that guy out if we get a jpeg or something like that We're not going to retreat jpeg And then we commit and continue and so these are kind of like oh those were pages We didn't want to mess with and then we print out how many characters we got and parse it We do this whole thing in a try accept block because a lot of things can go wrong here It's a bit of a long try accept block Um keyboard interrupt. That's what happens if I hit control c uh at my keyboard or control z on windows um some other exception probably means beautiful soup blew up or something else blew up and so Uh, we we indicate with the error equals negative one For that url. So we don't retrieve it again At this point at line 103 We have got the html for that url And so we're going to insert it in and we're going to set the page rank to one So the way page rank works is it gives all the pages some Normal value and then it then it alters that we'll see that in a bit. So it sets it in with one We're going to insert Insert or ignore that's just in case this pages is already at the pages is not there, but Um, and then we're going to do an update and that's kind of doing the same thing twice Just sort of doubly making sure if it's already there This or ignore will cause this to do nothing and the update will cause us to retain it And then we commit it so that if we do selects later, uh, we get that information Now this code is Similar remember we use beautiful soup to pull out all the anchor tags We have a for loop. We pull out the href And you'll see this code's a little Little more complex than some of the earlier stuff because it has to deal with the real nastiness or imperfection of the web and so We're going to use url parse, which is actually part of the url lib code Um, and that's going to break the url into pieces come back We use url parse. We have the scheme, which is http or https Um, if it's a relevant this solves relative references This is solves relative references by taking the current url and hooking it up url join knows about slashes and all those other things We check to see if there's an anchor the pound sign at the end of a url And we throw everything past including the anchor away If we have a jpeg or a png or a gif we're going to skip it We don't want to bother with that these we're looking through links now We're looking at all the links And if we have a slash at the end, we're going to chop off the slash by saying minus one And so this is just kind of nasty choppage And throwing away the urls that we're going through a page and we have a bunch that we don't like or we have to clean them up or whatever And now and we've made them absolute by doing this. It's an absolute url This is just you write this slowly But surely when your code blows up and you start it over and start it over and start over Check then what we do is we check to see through all the webs Remember those were the urls that we're willing to stay with and usually it's just one If this would link off the sites of the sites we're interested in we're going to skip it We are not interested in links that leave the site So this is like link that left the site skip it But now we finally here at page line 132 We are ready to put this into pages url and the html and it's all it's all good, right? and Where so that one's that one's going to be null right there because we haven't retrieved the html This is null because this is a page. We're going to retrieve We're giving a page rank of one and we're giving it no html and that way it'll be retrieved and then we commit that Okay And then we want to get the id so we could have done this with One way or another but we're going to do a select to say hey What was the id that either was already there or Was just created and we grabbed that with a fetch one And say retrieve to id and now we're going to put a link in insert or ignore into links from id to id which is The the id the primary key of the page that we're going through and looking for links Two id is the link that we just created and a way we run So it's going to go and go and go and go If you let's go look at the create create statement up here From id and to id right there. Okay, so so let's run it Python three oops Python three spider python So it's fresh and so it wants a url with which to start and I'll just start with my favorite website www.doctorchuck.com Now this this basically this first one you put in it's going to stay on this website for a while Okay, so I'll hit enter and let's just grab like Let's grab one page just for yucks. Okay, so it grabbed that and um it printed out that it got 85 45 characters and it printed out that it got Six links so if I go to this and Open database And I go to Code three and I go to page rank and I look at this Oh Let me get out so it closes so here So notice this sqlite journal that means it's not done closing So I'm going to get out of this by pressing enter And so you'll notice now that that journal file went away. Otherwise, we would not be getting the final data. There we go Okay, so webs, let's take a look at the data webs Has just one URL. That's the URLs that we're allowing ourselves to look at You can put more than one in here if you want, but most people will just leave this as one pages So we got this first one and we retrieved this and this is the html of it and we found Six other URLs in there that are dr. Chuck com URLs, right? There was lots of other URLs in there But there were only five other ones That uh that we found okay, and so and and what we'll find is if we go to links We'll see that page one links to two links to three links to four links to five links to six because the links is just a Many to many table so page one points to page two Page one points to page two page one to three page one to five. Okay, so that's what happens when we have The first page so let's retrieve one more page now it's we could have Started a new crawl, but we're just gonna it's gonna stay on dr. Chuck comm and I'll just ask for one more page And so now it went and grabbed it randomly picked among these null guys and I'm gonna hit enter to close it and then I'll refresh this And oh so it looks like we retrieved obi sample and we didn't get any new links And so the links page. No, we didn't get any new links. So that page whatever that was obi sample had no external links. So let's do another one one more page So that one had 15 links. So let's take a look now so now we have 15 pages it picked this one to do right and now it added 15 more pages and then if you look at links you will see that Page four which is one it just retrieved Links back to page one so now we're seeing this is where the page rank is going to be cool four links to one four links to Whatever a way we go, right? One goes to four four goes to one. I should have probably put a uniqueness constraint on that.  So let's run. Uh, let's just run it a hundred times For a hundred pages It'll take a minute So you'll see it's like freaking out on certain pages and not parsing them, you know, it's fun and it's way into my blog um It's finding like 27 links this this table is growing wildly at this point It's going to take us a while before we get to 100. It's kind of slow Now the interesting thing is I can hit control c at any point in time Right and so that blew up But it's okay because the data is still there and so if we go back to pages for example and we refresh our data We see we got a ton of stuff and this will restart and all the things So if we search this that I sorted that by html You see that there's lots of files that we've got and it's never going to retrieve those again because those have html So then I can run this thing again And start it up And when I say control c your your computer might go down your network might go down There's all kinds of all kinds of things that might happen and you just pick up where it leaves off It just picks up where it leaves off and that's what's nice about this Okay so That's pretty much how this works Uh, we're we're we've got this part running. We're seeing it flow into spider to sql light We're seeing that we can start this and replace this And so what I'll do is I will come back in the next video and show you How all these things work together and then how we actually do the page rank So thanks again for listening and see you in the next video We're picking up in the middle here where we are running, uh simple spider that's retrieving data and putting it into Running this spider. Uh, a py file and it's cruising around and Doing things and and the beauty of any of these spider processes is I can stop anytime and just hit control c and uh, and so we take a look at the, uh spider sql light file And retrieve it and it looks like we got 302 pages. I don't know how many we got retrieved 70 okay, there we go We've got about, uh 100 Oh wait, I'm looking for the wrong thing. No, no, no, no, no No Yeah, we got about 107 pages So what we're going to do now with 107 pages is we are going to, um Run the page rank algorithm. Okay, so let's take a look at that code So so the idea of page rank um We're going to run this page rank algorithm The sp reset just resets the page rank and sp rank runs as many iterations of page rank So the the basic idea is that if you were to look at the links here, um You know We think of page one pointing to page two gives some of page ones loved of page two page page Page four has some value that it gives to page one You go on and page two has gives love to page 46 Over and over and over again And so but the problem is is that how good is page one and how much positive Karma does it give to page two? And so what happens is is we we we start by giving every page Uh a rank of one we say like everybody starts out equal But then what we do is we divide up in one iteration of the page rank algorithm Then we divide up the goodness of a page across its outbound links and then Accumulate that and that becomes the next rank. Okay, and so So let's take a look at the code for uh for the page rank algorithm So this is pretty simple. It only imports sql i3 because it's really doing everything in the database, right? It's going to it's going to be updating these columns right here in the database and so So we're going to do some things here to speed this up Um, this rank runs if you're thinking of google this rank runs slowly and it's going to run Continuously to keep updating these things So the first thing I do is I read in all of the from id's from the links Select distinct throws out any duplicates um, and and so I have all the The the from id's which are the all the pages that have links to other pages because uh all the pages are in pages But in links to be and have a from id you have to also have a to id Okay, and so we're also going to look at Uh the pages that receive page rank and we're kind of pre caching this stuff Okay, and so we're going to do a select distinct of from id and to id and loop through that group of things and um We're not going to we're making a links list here And so we're saying if the from id is the same as the to id we're not interested If the from id is not already in my from id's that I've got I'm going to skip it if the to id is not in the from id meaning that this is a to id that's not Also, we're not I don't we don't want links that point off to nowhere Or point to pages that we haven't retrieved yet and that's what this is saying So this is really going to give us it's a filter on the from id's and the to id's From the links table so that it only are the links that point to another page. We've already retrieved And then we're going to keep track of the entire superset of the two id's the destination id's And i'm just putting these all in lists so that I don't have to hit the database so hard Okay, so this is getting what's called the strongly connected component meaning that any of these id's there is a path From every id to every other id eventually. So that's called the strongly connected component in graph theory Then what we're going to do is we're going to grab the we're going to select new rank from pages Where for all the from id's right and so we're going to have a dictionary that's based on the Id the primary key That's what node is equals the rank and so if we look at our database That means that for the the part of the strongly connected component in links We're going to grab this number and stick it into a dictionary based on the primary key of this Based on the primary key this number right here So we're going to have a dictionary that's this map to that again. We want to do this as fast as possible Now we're only doing one iteration at the beginning. So it asks how many times you want to run it okay And so we just take it make an integer of that we check to see if there's any any values in there if there are no values we are bad And now we're going to go i equals one to range many this is going to be one to one so it might run however many times And then what it's going to do is it's going to compute the new page ranks and And so what it's really going to do is it's going to take the page rank the previous ranks and loop through them and uh This is the previous ranks is the mapping of primary key to old page rank Okay, and for each node We're going to have total equals total plus old rank and then we're going to set the next ranks to be zero Okay, and then what we're going to do is figure out the number of outbound links for each page rank item So node and old rank in the list of the previous ranks These are the ids we're going to give it to and so For this particular node We're going to have the outbound links and we're going to go through the links And not linked to itself although we we made sure that doesn't happen We make sure that this but then we're going to make a list called give ids which are the ids that node Is going to share its goodness And now what we're going to do is we're going to say how much goodness are we going to flow outbound Based on our previous rank of this particular node and the number of outbound links we have So that's how many Time that's how much we're going to give in our outbound links And then what we're doing is all the ids we're giving it to We started with the next ranks being zero for these folks These these are the the receiving end and we're going to add the amount of page rank to each one So whatever this is so we'll go through All of the links Give out fractional bits of our current goodness And it's accumulated in each one and so eventually All the incoming links will have been have granted each new link value, okay Now i'm just going to run through and calculate the new total um and And this is this evaporation. The idea is is that um You can't You can't it has to do with the page rank algorithm that you there are dysfunctional shapes in which page rank can be trapped and this evaporation is Taking a fraction away from everyone and giving it back to everybody else And so we add this evaporative factor and Then we're going to Do some computations just to show some stuff and that is we're calculating the uh difference The average difference between the page ranks and you'll see this when I start running it and that is telling us this is going to tell us the stability Of the page rank So from one iteration to the next The more it changes the least stable it is and you'll see in a sec that these things stabilize And then we're and we'd say what's the average difference in the page ranks per node, which is what this is And that's we're going to print and now we're going to take the new ranks and make them the old ranks and then Run the loop again. So i'm not actually updating the database each time through the page rank iteration But then at the very end I am going to do the update for all of these things and update all of the rankings Start to with a new rank. So i'm doing an in-memory calculation So that this runs this loop here runs scrammingly fast Even if I want to do like this loop a hundred times or a thousand times. It's really all just in-memory data structures Okay, so it's probably easier just for me to show you this it'll be very uh The code runs quite simply python three Prank rank Got py and so i'm only going to run it for one iteration and that means that it's going to loop here Is just going to run one time and so it's going to start with The page ranks The new rank of one and it's going to just run one iteration and put the rank there Okay, and then update this as well. So let's go ahead and run that once for one iteration Okay, and so it ran one iteration and the average change Between the previous rank and the new rank is one. So there it's actually quite crazy So i'm going to refresh here and you'll see that the old rank was one And the new rank Is went way down way down way down way down a little bit Down down some up a whole bunch Down down up so you see that they went down and up now the sum Of all of these numbers is going to be the same right because all it did it was like Floated out and and recalculated it And so that's what happens with page rank And so what'll happen is if I run one more page rank iteration This number will these numbers will be used to compute the new new rank And then these will be calculated to the old rank And so you'll see that these will get they will change again. So i'll just run it one more time So i'm gonna run one iteration And then we'll hit refresh So you see all these numbers got copied over but now there's a new rank that's computed based on These guys and so they're getting this one went up. This was point one three. That's gone up a little bit This one's gone up some more. This one's gone up. This one went down Right. So this one went down from six to eight And you can see that the the difference is now the average difference between This number and this number across all of them Went from one point something to point four one and you'll see that with these very few pages this page rank Converges converges really quickly. Okay So let's run it again, and i'll just run 10 and you will watch how this converges Okay, so there you go. It converges So and and you're seeing now after like 12 iterations that the difference between the old rank and the new rank Well, that's because it's that old rank. I'll run one more iteration so that you can see So this old rank is less than you know point 005 So now you can see that these numbers are sort of stabilizing This is the average that 005 number is the average difference between these two things Now if we're going to pretend to be google for a moment, we can say python three spider Dot py so and so in So let's just do 10 more pages now. What's going to happen here is these new pages Are going to have page ranks of one Okay So let's get out So if I do a refresh now and I look at new rank So there's these guys that have high rank what you'll see I hope if we yeah, okay. So you see new pages, right? These are the new ones that we just retrieved I don't know if they're linked or not and they all got one so some some old pages are way up 14 Some pages if we go downwards are way down, right? So these are like useless pages They you know, they point to somewhere but nobody points to them. That's what happens with these page ranks Okay, so what happens is is the new records get this Point one and so if I run the ranking code again And I run Let's just run five iterations You'll see that the average delta goes up just briefly as it sort of assimilates these new pages And then it goes right back down again. And so that's what's happening with google It's sort of running the spider to get more pages then running the page rank Which gets disturbed a little bit, but then it reconverges very rapidly And of course, they've got billions of pages and we've got hundreds of pages, but it's you get the idea Okay, and so I can run I can run its page rank like a hundred times And after a while it just sort of hardly is changing. So that's 2.7 to the negative tenth power. So now You know, let me run it one more time to update the stuff And if I refresh this you're going to see look at the look at how stable these numbers are 14 9 4 3 5 9 1 5 6 7 the difference is there in the seventh one So that's why this whole page rank is really cool. It seems like it's really chaotic when it first starts out and a way you go okay, so That was just this sp rank right sp rank Uh and sp reset we can look at that code. I won't bother running it It just sets the old rank to 1. That's it. That's as much code as you've got. It just Starts it and lets it rerun. So i'm gonna stop now And i'm going to start a new video where I should talk about this phase here where we're actually going to visualize the page ranked data And what we are in the middle of is we're in the middle of the page rank code And we just got done running the page rank. And so We have spidered the code We've run page rank a bunch of times sp reset allows us to restart the page rank algorithm if we want But we're not going to play with that. We're just going to play with sp dump and spj sun and do the visualization Which is the fun part. So I'll go into sp dump So this is a simple code because it's really just running a sql query and then printing stuff out right So we connect to our database create a cursor and then just do a select count and we're going to We're going to just show the number of links We're going to order by the number of inbound links descending So we see the most linked things and we'll see the top 50 that so this is just a sample You'll tend to write little helpers like this That make your life easier just to show you the kinds of things that you want sp dump Py and like you just kind of test to make sure that's like oh this looks right to me You know and so here is the number of inbound links So that's my blog that has the most inbound links followed by my uncategorized whatever that is And these are the number of inbound inbound links Within my own blog somehow. I don't know because this is not looking at the whole Whole internet at all. So so there we go. So that's sp dump pretty straightforward Um, and now we're going to go through the visualization visualization process And so this is going to look at all that data and produce Some file a javascript file. It's going to write a javascript file that will then be fed into my visualization using d3 and sp json Is um going to do a big long Join it joins the links with the thing and where html is not null and error is not null You know order by the number of inbound links. So we're looking at these the things that have the highest number of inbound links We're going to uh read all this stuff We're going to read through all those rows And um pull out the the page rank for each one We are looking for the highest and lowest rank because these numbers can vary quite widely They go all the way from you know 0.000 to 20 or 30 And so um And so we it asks how many do you want to do so it only does the top like 20 or something and you'll see why we need that in the visualization And uh, and so this is just checking and so we're going to write out a file We'll see what the format of this is it's just a little j. It's just a javascript file And we're going to uh write out We're we're basically normalizing the rank. We're subtracting the minimum rank and um Because we're going to turn this into line weight the thickness of the line. And so we're dividing by You know the we're normalizing the rank to be the thickness of the line and the size of the um Uh the size of the the ball you'll see all this and so this is really just writing some javascript with the little strings and stuff like that Um, and then we're going to finish the javascript And then we're going to write all the links out. So these are the balls that you'll see And this is showing what this is drawing all the lines and this is again normalizing things for thickness And printing these things out now. I don't want to go through this in tremendous detail. But so i'll do python sp json dot py Let's do the top 20 nodes And if I take a look at this file spider.js You can see that it's some objects that basically, uh Put the page rank in which idea it is and that's a way for me to be able to link back and forth Weight is how big the little circle is And then I have the links and I only asked for the top 20 Right and then this is the uh the the thickness of the line where the line starts where the line ends Okay, so This is read By this html file and um It's going to read Somewhere this force js file And um my own spider dot js code. This is some javascript I mean no the force js is the the visualization code and this is d3 the visualization library So this i'm using this d3.js, which is a really great visualization library And this is just drawing the circles and making the circles of colors and making the circles bigger and smaller And then connecting all the lines in between it. So this is just there This data feeds that thing and so when we're all done you simply say open you don't have to do anything open force dot html And so this all this beautiful javascript stuff is like oh, wow, that's really cool because you can move these things around whoa You can see the uh circles are bigger if you hover over it for a while. It shows you the big ones Um, you know, you can see these things and it's kind of cool So I gave you all this force dot js and force dot html And so that kind of visualizes the page rank and you could um, you know use this to visualize Uh quite a bit of stuff. I um, you know It'll take you a while to pull down enough data from a real website But after you pull down four or five hundred pages if you have some time and then the visualization is uh quite interesting But you can see why we had to pull down several hundred pages just to get this much page rank information Okay, so uh that That gives you a sense of how to run the page rank code in uh, python for everybody So thanks for us. Thanks for listening The last visualization application that we're going to take a look at is mailing lists And that's kind of ironic we started with the mailing lists And we're going to end with the mailing list the mailing lists, of course are from my open source Sakai project Which I love and very proud of and and so uh, what we're going to do is we're going to crawl the archive of a mailing list And then we're going to do two visualizations. One is a activity visualization and another is a word cloud. So, um It's probably probably the more important thing is when I do the demonstration of how the software works So this is a large data set. So you got to be careful This could spider gmain.org, which is a very free and friendly archive. This data originally came from g gmain.org But I've got a copy of it. And so gmain.org is not rate limited But if everyone who is watching this starts spidering gmain.org at the same time you will crash it It just doesn't have the horsepower to give you this data as fast And so I've got something that can give you the data super fast and has no rate limited on really good server And it's Cashed all around the world using a technology called cloud flare. So please please please Don't point this at gmain.org point this at the url here at mboxdoctorchuck.net Etc etc and then you can run this as fast as you like now another thing to worry about is if you Have a metered connection. So don't do this on a cell phone connection because you'll pay Thousands of dollars perhaps make sure you're running no cost connection Before you start running this because this is going to pull a lot of data down If you just start this from scratch and you let it run it it On a super fast connection it the whole downloading the whole thing is probably about four hours on a On my home connection When I had like about a 10 megabit connection it took several days And so so just understand that in this one It's both fun to deal with a ton of data And it's scary to deal with a ton of data. So this one is big this one is You'll see the process in action because it'll run for a while Everything the things will take a long time. So here's basically the flow of the data in this particular one You are going to have the restartable spider that talks to the api Mbox chuck doctor chuck.net which has a scalable copy of all this information And again, it's going to do kind of a raw database Not a very clean database. It's sort of a mess It's just just enough columns to keep track of whether or not we've got this page or not And so so this has you know, the ones we've retrieved so far And so what gmain does is it sort of scans down to see where to retrieve next gets that And then start scanning and then adding things here So it just adds it and then it blows up and then it comes in again and says, okay I'll start here and then it starts retrieving stuff and fills this in fills this in fills this in And sometimes you put like a delay in this so you don't overwhelm networks or don't overwhelm servers But basically this is pretty much a raw Retrieval of the email messages and this file can get rather large. This is the one that's greater than a gigabyte Now this data is actually really nasty. It's email data. The date format's changed This is data that lasted from 2004 to like 2012 or 13 And so this just data is got a lot of things wrong with it It even has things where people's email address has changed and so it has this mapping file This comes along with it this mapping file. It says Here's this one person and here are the six email addresses that they used throughout the life of the project and so There is a relatively complex and so this is this part here is super slow Very slow This part here is slow But it'll take like Depending on how fast your computer is somewhere between two minutes and ten minutes This will this first this first part will take days perhaps depending on the speed of your network connection And so what g-model does is it reads through this it actually Recreates it wipes this out and recreates index dot sqlite every time it runs So that you can change any number of things you can respite your things you can do whatever And often the cleanup this is one of those cleanup processes Then you have to tweak the cleanup process you like look at your data like oh the cleanup missed something So i've got to run it again So this produces index dot sqlite every time it runs. So this is like two to ten minutes g-model is two to ten minutes and it like maps names and when it's all said and done This is a very small highly normalized It's a nice data model. This one here had the content sqlite has a ugly data model Index dot sqlite has a pretty data model. It's got form keys It's got all this stuff and all those things we talked about in the database where It's efficient and so in your mind Keep track of how fast it is to scan all the data in a database with a bad model And then watch when you run like g basic which is a scanner or g line Which produces line date or g word and watch how fast they run they run in like Couple of seconds at the most and this runs in two to ten minutes And that and the difference is is that's because the data is efficiently modeled in index dot sqlite So you can take a look at that using sqlite browser and take a look at the data model And you'll see it looks just like the stuff we talked about in the database chapter It's got foreign keys and and all those things And so that runs and you've got this and then we do our visualizations And our analysis from this clean version of all the data and so g basic just loops through and prints some stuff out It's a great way to test things It's a pretty easy to understand program and you could take a look at it G line does some bucketing and makes a hit some histograms to produce a line graph And then g word does a different histogram It does a histogram of word frequency and then produces that as the word frequency ends up in g word.js And then we have two html files that use the d3.js Visualization to produce a line and a word chart And so you know i'll in another video i will show you how this code works, which is probably more useful than this picture but this is whole bunch of Good stuff in this particular application and and if you really understand everything in here you can build a pretty sophisticated data retrieval and analysis pipeline And so so that's it. I thank you for watching all these lectures and look forward to seeing you on the net We're doing some code walkthroughs if you want to get the source code you can take a look at the sample code and download it and and work through it and so what we're working on now is Doing some retrieval and visualization of email data. It's kind of ironic We are going to now look at the email data that we Look at the email data that that we started with it's the same Sakai developer list email data and so There's this service called g main and g main archives developer lists and various email lists and i've made a copy of their data because All the students in my class hitting the same their server with their api would crush it So in order to be a nice guy, I put up a much more powerful server with just the data from From this one list and it's about a gigabyte of data. So be real careful if you're paying for network So the basic process we're going to go through is we're going to have a spidering process. That's a simple restartable focused on the network problems Data data pulling to pull content. sql light and there's going to be a database there And then we're going to have a cleanup process This database is going to get large about a gigabyte and then we're going to have a process that takes It kind of grinds through this data. It takes a while and um And so then it's going to read this mapping and I'll show you that when it comes because things like people's names have changed Over all these years and it does a cleanup and makes a really nice highly relational Version of this data and then we visualize from here And so this this could take you several days to finish this This will take like a few minutes to run and then this will just take seconds to run. And so this is a multi-step process where If you were doing something like running something for two days to produce a visualization And it blew up three cars the way through It would do, you know, good. And so that's why we break this into a simple parts But right now we're just going to focus on this part right here and uh and take a look at the mail bit And you know the mail bit and retrieve the mail and then We'll we'll have another video to talk about the rest of this stuff. Okay, so Let's take a look at the code So here is gmain.py. That is the basic code and it's Hopefully the stuff is look starting to look familiar The thing that's weird here is we've got to do some date time parsing and there is code that's out there But you may have to install it and I had to write my code in a way that Didn't assume that you could install the date time parser And so it has it even if that's not there it uses my own date time parser And that's what this code is. Don't worry too much about that um And of course we have to deal with the lack of certificates inside of python And uh and so we start things out And this is really a simple table. We've got a messages table. That's got a primary key The email itself when it was sent what the subject and the headers and the body okay, uh, and so What we're going to do is because we have to pick up where we left off. We're going to uh select the largest primary key from the messages table and retrieve that And uh, and then we're going to go to the one after that. Okay And so we we know what the id is And we're going to pick up where we left off And so we have a starting point and starts either zero or one And we're going to ask how many messages to retrieve We've got some counters And so we're going to say okay See if select id for messages where id equals whatever that starting is it's the highest number we've seen so far and if we uh If we've got if we if row is not none that means we've already retrieved this particular email message Otherwise we're going to keep on going and we're in good shape and this is one that we want to retrieve And we're subtracting that so we don't and so this is the base url. This is the um This is the url of our api the one that i copied my i have a nice copy of all this data On a server that's accessible worldwide and won't crash So the format of this is you can say i would like the email address for one from one to two or from 100 oops from You know 102 101 message 101 to 102 and we can just kind of walk through these things So that's the message id And so if we're going to make the url We're going to take the base url add the starting address and then add plus one So we got the slash at the end of this starting address. And so that's how we form those And We're going to retrieve that and we're going to decode it We've seen this in some other ones. We're going to check to see if we got legit data If not if we didn't if like a 404 not found or something else we're going to quit Uh, if someone hits control c which is our control z we'll get the program interrupt and we'll stop Um, if there's some other problem Right We'll uh, we're going to you know Complain and keep going and if we have five failures in or we're going to quit But it will just keep on going because these things do have glitchy bits here And so at this point if we've made it this far we've we've retrieved the url and we've got the number of characters we've retrieved and If we get bad data if it doesn't start with from because this is a mail message Right and they all start with from space if it's right it starts with from space um Then what we're going to we're going to Tolerate up to five failures there for bad data because it could be bad And then we're going to find a blank line because that's the new line at the end of one line and then a blank line And then we're going to take and break this into the headers the mail headers Which is that mail headers is this stuff right here Up to but not including the blank line and then the body is everything after that okay And um, and so we'll just have break that into pieces Otherwise we'll complain and tolerate up to five characters And then we're going to use a regular expression kind of from the regular expressions chapter to pull out an email address from the From colon lines somewhere in these headers from colon right there It's going to go find a less than and then pull oops come on Pull this stuff out up to it. So you got the less than you got the parentheses You got one or more non-blank characters followed by an outside followed by one or more non-blank characters and We'll get back a list of those we should only get one If we find one we're going to grab the email We're going to strip the lower case and if we got some little nasty less than sign in there We'll tolerate that as well So this is kind of cleanup and you get used to this where you're like, oh, how come all those email addresses have this other stuff in them um And then we also look for it if there are no less than signs And we do this way. This is and that's different some some mail messages have it this way and others Again, you write this code after you watch it for a while and you're like, oh, it's crapped out and giving me bad stuff And I make them all lower case so they match better and get rid of bad characters Why now I got an email address Then what I do is I look for the date of this so I got it I'm going to graph these by date. So I look for this line and use a regular expression To pull that out, right? So it's I'm looking for a date followed by a blank followed by any number of characters followed by A comma So I'm not interested in this Wednesday bit. So I'm skipping that bit right there And going and grabbing everything after that comma space. And so it's really here To The end of the line. So that's the new line. So that's it's going all the way It's going to pull this bit right here. That's the text And this is where we're going to like say, oh, that's kind of a funky looking date and we want to standardize that date. So we're going to Let's see Yeah, we're going to we're going to chop it off at the 26 character Apparently, I don't know what the 26 why that we care about the 26 character But we chop that off at the 26 character And then we're going to parse it and that's going to give us back a nice clean date sent at date Otherwise it's going to complete. We're going to quit And if we can't parse it, then we're going to tolerate five bad email addresses in a row Um, then we're looking for this subject line using another regular expression Subject line regular expression. That's pretty easy up to but not including right There's a blank there. It's a subject Let me pull that out. We get the subject Now at this point we parsed it and we got good stuff So we reset the fail counter because I kept saying if you fail five straight times you quit And we're going to print it out and then we're just going to insert that stuff We've got the the the idea of the message which We've got the email address that has came from the time it was sent the subject And then basically the headers in the body and we're just inserting it And now we're going to say every 50th. We're going to commit it So that speeds things up and every 100th. We're going to wait a second So that's you know count is going up up up up up up and every 50th You'll see it pause And then it will every 100th that'll pause for a second Mostly that's to let me hit control c or to to not overload any server Okay, so that's that's the simple one the problem is is this data just gets ugly And so you'll find yourself wanting to reset this and start it over this one's going to work, of course But it's these are hard to build And that's why it's a good idea Python Three g main dot py How many messages? Well, let's just do one Okay, so it went and grabbed. Oh do I have this already running? 51 through 52 Let me start over Let's find a cell star sqlite Okay rm content I must have run it to test it So let's run it again Python g main dot py and ask for one message Okay, so there we went and got message one from one to two We got 226 two characters and we printed out the email address the time we got it after all that hacking And the subject line and that's what we got So if we take a look at the database And we go into the g main Well, every time you see the content sqlite journal that means it Needed to run a commit and it hasn't run a commit But I'll hit enter and that will do the commit and you see that vanish So now I can open it And I take a look at I'll come there's no messages Did that one not get stored in there for some reason? used to refresh Huh, let's run it again Maybe it didn't commit Maybe got a bug in it Let's make a change to the code I'm going to See this connection dot commit See that Connection dot commit gonna commit there And the other thing I'm going to do is every time I stop to read I'm going to commit right before I read it So I think we should I hope that doesn't blow up. We'll see so the idea is is if I want to stop I want to commit it So let's do this. Let's do one message And now I should hit Is it committed now that I should put the commits in I think that I will it will look better I can't refresh and so there it is because I committed it And I don't have yeah, I don't have the journal file. So that's good So that's a good idea to put those commits there. So I'll just leave those commits in when you download it It'll have those commits in there Um, so again, I put a commit here and a Commit at the very very end To make sure and then I so that I missed that But now we get one right And so let's just run it again and you'll see how by selecting the max of the id It's going to select the max of this and then add one to it So it doesn't it doesn't do the next one. So if I run it again I say give me one message so it goes two to three And give me two messages Right, so I hit enter and I can do refresh and now you see we've got four messages Okay, and so that's just uh, let's just fire this baby up tell it to get a hundred Run run run run run run All right, it just goes and goes And you'll it pauses once in a while to do a commit and if I if I made a commit every time Oop, it just both paused there now it finished. So this will run and we will get a bunch of data uh The problem is is if I just run this it'll take about five Hours okay to run this and get this all and I've got a really fast connection. So I have got a file that you can download. Let's go find it Let's see if I can Let's see how long it'll take me to download this I've got a file that you can download and save now. I'm going to use the command line curl Or wget is another command that we linux and mac people can use I don't know you might have to use your browser do it. Let's see how long this is going to take It's retrieving minute 30 Okay, well, I'll I'll just wait when this come back Okay, so now that's done I was averaging 10 megabits a second. I downloaded about 600 megabytes 10 megabits a second That will probably be slower for you But so now if I take a look you're going to find that that content dot sql light is 624 megabytes now what happens is I've pre spidered this and so now if you run g main dot py And ask for for five more messages. It will pick up where I left that one off So it's up to message 59,000 and I think that oh you saw an error. So you saw a bug in that one I don't know what's wrong with that one. So let's see if So at this point, we're going to have most of the data it might find its way to the very end Once you get this, it's it should be not too much more. I don't know. Maybe it's like 63,000 or something So what we'll do is we will let that run And we will come back when that's that one's finished and And run the next phase after it's got all of its data. Okay So thanks for listening The work that we're doing right now is we are in the process of building a spider and visualized a visualization tool for Email data that came originally from this Website g main, but I've got my own copy of it. And so what we've done before is we ran g main dot py and I grabbed a url I have a url that has all this data and I downloaded that And then I ran g main again To catch up So and so it took quite a bit of catching up But by the time I get to remember how I said it run tries to fails five times Well, it ran out of data at 60,421 and then It started failing and then it quit and so we pretty much have all of our data now We have all we have finished this process and as content sql light Okay, and If I take a look in the database browser, we can see we've got 59,823 Email messages and so if I look at any of these things You see the headers. You see the subject line. You see the email address. You see the body of it so remember I split the body into in half and And the headers and so that's I made this as raw as I possibly could Because as you saw I had to spend so much time in the g main just get my data Successfully retrieved and so I don't like cleaning the data up too much And so what we're going to look at next is the data cleaning process Okay, and um, and so this is gmodel.py is what we're going to take a look at now So let's get rid of those guys and look at gmodel.py I don't think I need url lib in this code. Do I have any url lib? So I don't need that. Sorry Fix that Okay, so It's going to read from the database. It's got a call It's going to use regular expressions and zlib is a way to do some compression And so I'm going to do in this one I'm going to compress some of the data to make it so that I have less data to Some of the text fields are going to be compressed. I wanted to keep these fields uncompressed inside of messages And uh, so we so so we have some just cleanup messages and Cleans things up and it turns out that the the way email addresses in this particular Mail corpus they changed over time and we There's certain kinds of things sometimes the gmain.org is the email address when people want to hide their address And I made all kinds of stuff and I split it and checked to see if it ended with this and I cleaned up things just just that thing and so I have all kinds of cleanup stuff going on in here and I have this mapping and dns mapping that I'll talk about in a bit where Um organizations sometimes sent email with different addresses over time and people sent email from different time Um, and we're going to do the parsing of the date and that is the code for that um I'm going to pull out the header information. This is uh sort of borrowed from the uh The other code, uh, we'll clean up the the email addresses and the domain names And we'll pull the date out pull the subject out We'll have the message id Various things so here's the main body of the code We're going to go from content dot sqlite to index dot sqlite And what I'm going to do every time is I'm going to wipe out index dot sqlite And drop the the messages sender subjects and reply. So this is a normalized database and that it has foreign keys So there's a messages table here with the integer primary key the guid for it The guid stands for global unique id sent times sender id And and it's going to have a blob these are blobs by near large objects for the headers in the body because i'm going to compress them in this database to make them And then the senders As each sender has a key And then uh subjects each subject line is going to have a key and then replies our connection From one message to another and so this is like a many to many Now I also have this file called mapping dot sqlite and so we can take a look at that one mapping dot sqlite and so what happened is um This has a two tables that I hand deal with and so uh sometimes india this was a email address that mapped to that That's so indiana dot edu. That's a way to take Then that's the email address and then these were a bunch of people that had Email addresses changing throughout the project and I sort of kind of mapped them In a way and so this is just sort of like a I pull this in really quick and I read all this stuff From the dns mapping and I Other than stripping and making this lowercase, etc I just am going to make a dictionary dns mapping, which is the old name to the new name and the Email address mapping from the old name to the new name and I'm using fix sender Fix sender is because the the email addresses even within g main were kind of funky. So Don't worry so much about this Um Okay, and so now what I'm going to do is I opened up a connection just to read all that stuff in And now I'm going to actually open the main content and I'm asking it to open this a little trickier. I open that read only Um, that was so that I could potentially be running the spider and running this at the same time I get a cursor And so I'm going to read through so in the content file. This is the big one I'm going to read through and go through every one and write all of these things in And I'm going to take all the email addresses and I'm going to put those in a list Um So I loaded that I've got the mappings loaded Um, and so now I'm going to go through every single message I got all the senders all the subjects and all the global unique IDs So I read in each message. So now I'm going through content one at a time I um parse the headers I check to see If the sender is name email address after it's been cleaned up Is in the is in my mapping mapping dot get sender and the default is I get back sender. That's what that's saying Look up sender. If it's in there, give me the entry of that key. Otherwise, give me sender back Uh, we're going to print every 250 things we do Uh, we'll complain if this is true We're going to go get the mapping between the senders, which is a way to look up the primary key I could have done this with a database thing, but I wanted it to be fast So that's part of the reason I read all these things in So I could have those mappings to be really fast. You'll see this takes a little while even though it You know, even though it's I got all this stuff cached um And so then if I don't have a sender id Meaning that I haven't seen it yet Then I'm going to do an insert or ignore into senders And then I'm going to do a select and then you've seen this where I grab the row back and I'm really just trying to look at the recently assigned id and Then I'm going to not only set the sender id for this iteration loop, but I'm also going to store it in The dictionary and so that builds this dictionary up And you'll see the same thing is true for subject id I'm going to insert it into the subjects table and get a primary key If I don't know what it is and then I'm going to put it into not only am I going to put it into the database but I'm also going to put it into My dictionary and the same thing Um, I guess I didn't do it for the GUID Okay So now what I have is the sender id and the subject id Which are foreign keys into the center table in the subject table And I'm going to insert the message with the sender id subject id the sent at headers and body and The values here are the GUID sender id subject id sent at now this here is zlib compress So what I'm taking is the the message the header And the body and this little bit ends up with a compressed version of this stuff and you'll see it in a second And this keeps the size of these text things down at the cost of the computation of We have to took the at the cost of the computation to compress and decompress when we want to read it Okay And then I pull the GUIDs out the the id which is the GUID Um, and I pull out the primary key for this thing based on the GUID And I update this dictionary. Okay, so Let me run that code It is doing a lot of cleanup and I'll tell you it took me a long time to make this work. So just So this code that I'm running now Oh Don't forget to take a python 3 chuck So you this is going to run every 250 so it did all this pre caching So that's how long it takes to do 250 now there's 60 000 in here and so this is Really busy the reason it's bouncing back and forth is that every time it makes this journal file That's and then does it commit so you can kind of see that it's um It's busy making journal files and committing and there's a lot of activity going on here Just so happens that adam shows me these files Okay, so it finished it took about three minutes to finish that right and so if we take a look at the size of the files We will see that the index is much smaller. It's Fully normalized and still uh 263 megabytes. It's all compressed. So let's take a look at that In the in the browser So it's 200 megabytes But it loads up a lot faster There we go So We have a sender's table, right? Which is just kind of a a many to one table We have a subjects to table, which is a many to one table And we have messages which has uh foreign keys It takes a little bit to load that up Okay, and so So we see the foreign keys for sender and subject and we're and that saves us all those foreign keys save us And so we have you can kind of see that I can't see the headers in the body because now they're compressed that saves me A whole bunch of stuff, right? It saved me a whole bunch of stuff You know and so uh, so that's what's in that file And that we've finished this process, okay? And we've finished modeling the data and making it really clean and we'll pick back up and the rest of the stuff We will do is actually visualizing pulling data out of index dot sql light The idea is this can be restarted this can be run over and over and over even though it takes like three minutes to run this That's way better than uh five hours to run this so three minutes Five hours and then you'll see and we'll see now reading this is in seconds because we got it all nice and normalized In a quite pretty way. So, uh, I hope this has been useful Uh in the next one we'll actually do the visualization We are in the process of retrieving data from this g main server one that I've made a copy of And we have so far, uh, spidered it all ended up with 600 megabytes of spidered information We have ran a rather complex cleanup process that you probably don't need to fully understand You can look at it for patterns, but in general the the cleanup process will be very sensitive to the data Um, and then we have this index dot sql light, which is 260 megabytes right now And uh, we are going to now do a this the easy the the fun easy bits here Where we're going to run little queries that just pull data out. So these are much simpler. So Part of what I wrote when I was doing this is I wanted to do some simple basic, um Calculations on the data to make sure I really was sort of looking for anomalies, right? What what was working? What wasn't working?  So I wrote things down and I counted things and so Um, do I need you are a LibreQuest in this one? I don't think so Let's let's fix that bug. It's not there. No reason to put any of that stuff in there So it just it reads that index dot sql light, which is our cleaned up data Um, it reads through and makes a dictionary this pattern. You're going to see a lot where I'm going to make a dictionary of id to senders Save myself repeatedly looking at things. I'm going to grab the subjects. I've cached them all I could have done this all with sql, but I just Wanted to do things faster um, and now I'm going to go through uh each of these messages And make a dictionary of them going to put a lot of stuff in memory and then I'm going to do some counts I'm going to see Who is sent the most right the organizations and so now I've got to go through the uh all the messages um I am not actually so you'll notice that I'm not selecting the body or the headers here I am just getting sender id subject id Um, I probably could have done this with a join. It would have been cleaner You can do that you can make that change Do that with a join so it's cleaner um and uh So I'm going through all the messages except not the body So this is going to be really quick and I'm pulling out the the sender's id I'm breaking the sender into pieces see my data is clean now I cleaned it all up in the previous processes And if I don't have two pieces I continue and I get the domain name. So I have the person I'm doing a a basic dictionary histogram and for the people and the domains and then I'm going to uh sort them right With a sorted And we're going to grab the key. We're going to sort it by the how many there are reverse And then print out the top few Of the organizations and the top few of the people. Okay, so we'll just run that code Python g basic dot py py Let's type the dump out the top 10. So we loaded uh 59 000 messages 29 000 subjects and 1800 senders and figured out the top 10 people and the top 10 organizations and You can you can write various things like that that just sort of scream through your data and It's good to get sanity checking on your data Okay, so that's g basic Now I want to do uh g word dot py Because that's kind of fun um g word dot py I don't need url. Live. Why do I keep putting url. Live in all these things? So we'll get rid of that um So this is really simple because I'm just going to go for the words in the subject line And so I go through index dot sql. I I read in all of the subjects um And I make a dictionary of those and then I go and find all the subjects This uh, and then I'm doing this code right here. I'm pulling out the subject Based on uh, uh, based on the message Um, and I'm doing this so that I when the subjects are used more than once I count the words more than once Um, this stir make trans. Uh, I talked about that in uh in an earlier chapter Um, this basically throws away a punctuation in numbers So that when I make my words, I don't end up with uh words that are like dashes They it compresses them down Then I Strip it I convert everything to lowercase. This is basically just to keep too many words from showing up Then I do a split and then I got accounts a dictionary So this is a no trans no punctuation no numbers dictionary count And then I just take the And do a dictionary and then I sort them in reverse order And then I figure out what the highest and lowest is by running through a A I could have probably done this with uh, a max and a min if I felt like it um And so now I have the highest in the lowest You know, I should have done a max and a min on that one. Why did I do that? But oh well um And now I've got to Spread out the size and so I'm going to produce this file g word.js, which is Needed by the visualization because it's going to use Uh d3.js a word visualizer and g word.js I have to tell it how big the text is and so I'm doing some text normalization took me a little experimentation So if I run this now And I say python g word G word.js And I say python 3 g word.js, which is a lot better Oh not python Okay, so now I can go look at the g word.js wherever that is g word.js. Yep And so this is basically it it normalized all the frequencies um And made it font size. These are font sizes now Okay, and so this is just the data that's needed by this g word.jm which uses this d3 visualization Uh word cloud code So this pulls in all my data and then this is just some javascript that draws the The picture in uh on the page Okay, and so the easy part now is to just open g word.htm In a browser it just so happens on a mac I can do this And so that gives me a word cloud Based on that data It kind of randomizes it shows different stuff, but it's using it's using this It's using this data To generate how big those things are and then using a bit of randomness and simulated annealing to lay it out That's not stuff that we actually have to worry about. Okay So that's how we get to the point where we're seeing a word cloud from this um Now We're going to do another visualization and this time we're going to do a line visualization And we're going to create a thing called gline.js and produce With another html file. We're going to use d3 and produce Uh that output. So let's say goodbye here. Goodbye. Goodbye Goodbye. Goodbye So gline.py Get rid of that file So again, I'm going to preload all of the senders Um in this case and again, I could have done this with a join probably should have done once with a join I'm going to preload all the messages um The sender id subject id etc. I'll load those up And now I'm going to read through I'm going to have the sending organizations and the senders And And I'm going to accumulate to split the senders and I'm going to have the sending organizations And then I'm going to do a a simple dictionary as I accumulate The sending organizations by splitting the person's name into ad signs And then based on the organization, I I'll accumulate it and then I sort them and I pull out the top 10 organizations print those out And now I'm going to um produce uh Break this down into uh months and I'll show you what this looks like in a second. Let's go to the gline.js So the month looks like this Okay, so the month looks like that. So that's the first seven characters of the date. So if we look at the date Date looks like that and the month is the first seven characters And this is the data that I've got to give it um We'll clean that up in a second that data will look better in a moment go back to gline.py and so so this is um We're doing a The key is a tuple which is the month and which organization is it is That that did it and it's only the top 10 organizations and then we're going to do a um We're going to basically do a uh a dictionary where the key is a tuple And then we're going to sort it Sort by key in this case Not by value That's uh The months is going to sort that and then we're going to write all this data out Into gline.js. So let's go ahead and run this and again. This is just the data that has to be written in a way that uh The javascript can understand it python gline python 3 gline.py Okay, so top 10 organizations. So let's take a look at that javascript. So this is what it looks like So it just so happens that you got a tellot the the the um These are the data points. These are the lines So there's this is the year the line for university michigan gmail.com swinsborg.com So this this first column is the that line points and the next line points So I you know all this code was to get the data In such a way that I could produce this javascript file because if I look at gline.htm I need that data in that particular format and I I've got all this stuff I make a line chart and I draw out with this data that data I had to go read all the documentation on how to figure this stuff out Right and that's the data that I'm going to use and I had to figure this out And I had to transform it and make it pretty it took me quite a while to get this to work And this is not a javascript class nor a how to visualize in d3, but basically um, we pulled all that stuff in and um Here's the gline that came from the javascript and then it makes an array to data table and then that data table is what G line draws. So with no further ado, let's open G line dot htm to show that data So there you go. That's the sky developer participation from 2015 through 2000 2005 through 2015 Based on which organizations did the the most commit since sky And so I know that I haven't done this all this code full justice. There's a lot of code here The fun is just to kind of run it and see it and then when the time comes to come back and see the techniques That are used when you're trying to build your own visualization pipeline So, uh, I hope that you found this useful Um, you know, this is a lot of code hard to explain in 15 20 minutes But I hope you take some time and look it over and I hope you found all these video videos This is kind of the last walkthrough video for chapter 16 of the book And so I hope that I will see you on the net