How to scrape like the Data Doctor with OutWit Hub
Scraping is a complicated sounding word which basically means: getting a programme to do any task it would take you ages to complete yourself. Usually the task is repeated, but scraping can just be winnowing the tables you want from a website, for example.
A common problem occurs when you want to get the same piece of information from a bunch of different web pages, or ‘URLs’. It takes ages to go into each page seperately and get the numbers out.
The URL is the address of the page which you want to get information from. Here is one:
http://billyehrenberg.tumblr.com/post/77524260275/of-beer-wayne-rooneys-new-contract-and-data
This URL, incidentally, is for an article I wrote, and the article I’m going to use as an example. The tool I used is called OutWit Hub: the paid for version will allow you to winnow many, many rows of data, but the free version will let you take 100, which isn’t bad.
So first, download OutWit from here.
Now let’s have a look at my article. I wanted to find out (roughly, the data is far from watertight) how many beers a freshly-contracted Wayne Rooney could buy in different countries.
For this I needed the average pint price, and so naturally I went to pintprice.com.
It would have taken ages to copy them all down but I noticed that the URL changed very predictably depending on the country:
http://www.pintprice.com/region.php?/NAME_OF_COUNTRY_HERE/
When you see a URL with only one element changing, think: OutWit Hub.
First stage here is to accept you’re going to have to look at HTML. I know, that’s looking through a page of oddly-spaced gobbledygook, but it is pretty easy if you know what you’re looking for, especially armed with CTRL F.
So let’s venture to PintPrice.com and its Brazil page. But we’ll open it through OutWit, not not our browser.
Open OutWit and click scrapers on the left hand toolbar
Click the button that says “New”
Name it something
Now we need to find the info we are after. So put the Brazil page URL (http://www.pintprice.com/region.php?/Brazil/GBP.htm) into the bar at the top of OutWit, like so:
Now what we can see is the webpage as it usually looks, and the source code.
Now we see that the price of a pint there is shown thus:
So when we delve into the HTML, we’ll look for the word “lager”. We find this:
Now OutWit works by finding common HTML both before and after the data you want to extract. These bits of code you choose are called markers.
You can’t use Brazil, the, as that’s unique to this page. We want code that will work for Belarus and Barbados and Zimbabwe.
Lets choose this then: </strong> is <strong>£
And this for after: GPB</strong>
Now we need to put this into OutWit and see if it works. At the bottom will be your scraper. Click on the boxes to input the information. “Marker” means those common pieces of HTML we are going to use isolate the data we want. It should look like this when you’re done:
Notice that there is a second URL bar above the scraper: make sure this has www.pintprice.com in it BUT NOT the specific URL.
Now we are ready: press execute, above and to the right of the scraper box. This should happen:
Success! We have scraped a line of data. But what we really want is to get the price for a whole bunch of countries. Here’s how.
First we need to open up an Excel workbook. But note well: Google Docs will not do here, because we need to save it as a webpage later.
Now in this Excel workbook we need to make a list of the URLS that we want to scrape. Lets try five countries worth.
Brazil
France
Germany
Argentina
Spain
Before you go any further, remember to check the names on your list are exactly the same as how the countries appear in the URL. If they have an underscore between parts of the name, such as Czech_Republic, put ‘em in. Even a space at the beginning or end will mess this up (Spaces before/after can be removed with the =trim function).
Now in Excel do the following: divide the URL into three cells, with EVERY SINGLE CHARACTER before the country in the first, only the letters in the country’s name in the second, and everything after the country’s name in the third. Voila:
Now underneath Brazil write the names of the other countries, and then drag the first and third cells down by the bottom-right-hand corner until you get this:
It is now time, ladies and gentlemen, for some Excel wizardry to make even the Data Doctor feel “that was pretty easy but cool-ish.”
We need to glue the three cells together. So in the fourth cell enter the following: =CONCATENATE(A1,B1,C1)
Concatenate means “please combine the following with no spaces.” Then drag down (or click on) the bottom-right-hand corner of the fourth cell to get this:
Wow. We have the exact URLs we want and are nearly there. What we need to do is get rid of the unwanted bits. So select column D, and copy all the info. We want to paste it BUT ONLY THE INFO AS IT APPEARS, NOT THE FORMULAS. If we paste the formulas, we’ll end up with errors when we get rid of the other bits.
So instead of pressing ctrl V, we need to paste special. You can click the clipboard and select values. Or go to edit -paste special - values. Done? Good. Now delete the rest and you should have just the URLs in the first column. Stretch that column out so none of the url protrudes across the border into B.
Now we need to save it in a format that OutWit can use. So go to save as and choose “Save as webpage.”
We’re almost there. Quickly, back to OutWit to import our list of URLs: file - open - your webpage. Easy. You should now have a screen that looks like this:
Now all we need to do is right click on the list of URLs and choose:
Apply scraper
To highlighted links
And:
There we have a list of the pint prices in each of the countries, as if by magic. The only thing left to do is to get them into an Excel spreadsheet and ready to use. That’s easy, just choose export and you’re done.
I hope this has helped you scrape something, and that you will use the power for something equally profound as beer. Lovely beer.
