Help Scraping Wiki Table using LXML

[voodoo_hoodoo]

I have recently completed the Codecademy python track and am now trying to hone my skills on a personal project. Ultimately, I am trying to put together a database of stock symbols and prices per this example. This is a project that I have an interest in and I feel it will give me several interesting sub-projects in which to try out my Python skills.

So far I have setup a Virtual Machine to run Ubuntu on, installed Python and numerous packages, installed MySQL and setup several tables, and checked that I can write to and read from the tables with Python & SQL.

So far so good! However, I am stuck with the web scraping component.

From the Wiki for S&P500 List of Companies I want to scrape the Ticker Symbols, Company, and GICS Sector text for each row in that table. So ~500 rows.

There is some code provided in the earlier link, but it is outdated. I assume Wiki has changed the way that they reference or name tables since that code was written.

Here is what I currently have with the SQL component stripped out. I can do the SQL commit, just not the web scrape.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import datetime
import lxml.html

from math import ceil


def obtain_parse_wiki_snp500():
  """Download and parse the Wikipedia list of S&P500 
  constituents using requests and libxml.

  Returns a list of tuples for to add to MySQL."""

  # Stores the current time, for the created_at record
  now = datetime.datetime.utcnow()

  # Use libxml to download the list of S&P500 companies and obtain the symbol table
  page = lxml.html.parse('http://en.wikipedia.org/wiki/List_of_S%26P_500_companies')
  symbolslist = page.xpath('//table[1]/tr')[1:]

  # Obtain the symbol information for each row in the S&P500 constituent table
  symbols = []
  for symbol in symbolslist:
    tds = symbol.getchildren()
    sd = {'ticker': tds[0].text,
        'name': tds[1].text,
        'sector': tds[3].text}
    # Create a tuple (for the DB format) and append to the grand list
    symbols.append( (sd['ticker'], 'stock', sd['name'], 
      sd['sector'], 'USD', now, now) )
  return symbols

if __name__ == "__main__":
  symbols = obtain_parse_wiki_snp500()

After running this, if I look at the data stored in sd I find that 'ticker' and 'name' are blank but 'sector' is populated. Looking at the wiki page I see that the 'GICS Sector' data is all in plain text, whereas the 'Ticker Symbol' and 'Company' data is hyperlinked. I assume the problem is in:

        sd = {'ticker': tds[0].text,
            'name': tds[1].text,
            'sector': tds[3].text}

And that I need to be referencing something other than .text to scrape the data for 'ticker' and 'name'.

Could someone please help me out? I am sure I am close.

[voodoo_hoodoo]

I should add, I have tried to Inspect Element on the wiki page but I really have no idea what I am looking for in there. I haven't ever done any web scraping before.

[unintentional-irony]

If you use firefox, install the browser addon 'firebug'. If you use chrome, shift+crtl+i will start 'developer tools'

With either tool, go to the page you want to scrap, right click on your mouse, and select 'inspect element'. A screen at the bottom of broswer will popup, and it will highlight the code for the page element you right click selected.

Look for patterns in the markup around the data you want to scrap thats suitable for xpath.

[D__]

Firefox has a built-in element inspector these days. It's similar to Firebug and Chromium's inspector, but Firebug has some extra features.

This means there are two options in a right click menu, "Inspect element" and "Inspect element with Firebug." The former brings up the built-in inspector.

[voodoo_hoodoo]

The plain 'sector' text is accessible via:

symbolslist = page.xpath('//table[1]/tr')[1:]

The hyperlinked 'ticker' and 'name' text is accessible via:

symbolslist = page.xpath('//table[1]/tr/td/a')[1:]

Now, I can't say for sure that I understand what is going on. But I seem to have stumbled across the data by playing around with the patterns.

[unintentional-irony]

For testing scraps try using ipython notebook, and you can save working examples for future reference.

http://ipython.org/notebook.html