Again, the best way to do this for a new site is to follow the steps above.įor Business Insider, the author byline is accessed the Washington Using IMPORTHTML function to scrape tables on websitesĬonsider the following Wikipedia page, showing a table of the world’s tallest buildings:Īlthough we can simply copy and paste, this can be tedious for large tables and it’s not automatic. Other websites use different HTML structures, so the formula has to be slightly modified to find the information by referencing the relevant, specific HTML tag. The result is: Two author web scrape on same row Other media web scraper examples Then in the adjancent cell, C1, I add another formula to collect the second author works by using 2 to return the author’s name in the second position of the array returned by the IMPORTXML function. The new formula the second argument is 1, which limits to the first name. To do this, I use an Index formula to limit the request to the first author, so the result exists only on that row.
a long list of URLs in column A), then you’ll want to adjust the formula to show both the author names on the same row. This is fine for a single-use case but if your data is structured in rows (i.e. The formula in step 4 above still works and will return both the names in separate cells, one under the other: Two author web scrape using importXML In this case there are two authors in the byline.
The xpath-query, looks for span elements with a class name “byline-author”, and then returns the value of that element, which is the name of our author.Ĭopy this formula into the cell B1, next to our final output for the New York Times example is as follows: Basic web scraping example using importXML in Google Sheets Web Scraper example with multi-author articles We’re going to use the IMPORTXML function in Google Sheets, with a second argument (called “xpath-query”) that accesses the specific HTML element above.
#Bencollins webscraper code#
In the new developer console window, there is one line of HTML code that we’re interested in, and it’s the highlighted one:
This brings up the developer inspection window where we can inspect the HTML element for the byline: New York Times element in developer console Hover over the author’s byline and right-click to bring up the menu and click "Inspect Element" as shown in the following screenshot: New York Times inspect element selection But first we need to see how the New York Times labels the author on the webpage, so we can then create a formula to use going forward. Note – I know what you’re thinking, wasn’t this supposed to be automated?!? Yes, and it is. Navigate to the website, in this example the New York Times: New York Times screenshot Let’s take a random New York Times article and copy the URL into our spreadsheet, in cell A1: Example New York Times URL I am able to detect Beth and Jerry Smith using re.compile(r"+ +and ++ ++", but I am not sure the best way to process it once it is detected.Grab the solution file for this tutorial:įor the purposes of this post, I’m going to demonstrate the technique using posts from the New York Times. Unfortunately, I'm having trouble finding a way to split Jane and Jerry Smith (I'm new to regular expressions) in a way that can detect their surname and produce the output: I have been able to use foo = re.compile(r" +with +|, +and +| +and +|, +") and re.split(foo) to separate the captions into different individual names such as: I have the captions stored as a list of unicode strings such as:
#Bencollins webscraper series#
I'm working on a web scraping project where I am trying to extract names from a series of photo captions.
0 kommentar(er)
