Wikipedia table to JSON (or other machine-readable format)

Question

I'm interested in the data listed in the tables listed here.

Is there a good way to extract the information (as JSON, preferably) without resorting to HTML parsing?

Answer 1

You can use Google Spreadsheets ImportHTML formula as detailed in this Liberating HTML Tables (using Google Spreadsheet) tutorial on School of Data by Tony Hirst - it includes a specific walk-through for Wikipedia.

The essence is to do:

=importHTML("","table",N)

In your case you could try:

=importHTML("http://en.wikipedia.org/wiki/List_of_television_stations_in_the_United_States_by_call_sign_%28initial_letter_K%29",
   "table", 3);

In your case you can tweak 3 to be the table you want to grab and obviously you can repeat this formula for multiple tables.

There a bit more on this and issues with links in this answer on ask.schoolofdata.org (also from Tony Hirst).

Answer 2

The wikitextparser Python module has good support for converting tables into structured formats.

Example:

import wikitextparser as wtp
import requests
import json

r = requests.get('http://en.wikipedia.org/w/index.php?title='
    + 'List_of_country_calling_codes&action=raw')
wt = wtp.parse(r.text)
print(json.dumps({'root': wt.tables[1].getdata()[0:3]}, indent=2))

Which dumps the first 3 rows of the country code table:

{
  "root": [
    [
      "Country, Territory or Service",
      "Code",
      "Time Zone",
      "[[Daylight saving time|DST]]"
    ],
    [
      "Abkhazia",
      "{{ntsh|78400}}[[+7 840]], [[+7 940]],<ref name=\"zone7\" group=\"notes\"/>[[+995 44]]<ref name=\"abkhazia\"/>",
      "UTC+03:00",
      ""
    ],
    [
      "Afghanistan",
      "{{ntsh|93000}}[[+93]]",
      "UTC+04:30",
      ""
    ]
  ]
}

You can also use wikitextparser to parse the contents of the table cells, as well. For example to extract the target URLs of certain link elements.

Answer 3

Using OpenRefine (previously Google Refine) you can easily convert the wikipedia table to a JSON document. The following video will walk you through the steps to clean the wikipedia table, then using the template exporter you will be able to define the JSON format of your output document.

Working with OpenRefine will also allow you to extend your data using freebase.

Disclamer: I am part of the OpenRefine team.

Answer 4

If you are using google chrome and google spreadsheet, there is a nice extension (still in beta development, but for the tables you mentioned it worked well) called scraper:

https://chrome.google.com/webstore/detail/scraper/mbigbapnjcgaffohmbkdlecaccepngjd

If you have the extension installed, just select one row of the desired table, rightclick on the selection and select "Scrape similar". you will then get a spreadsheet wich you can easily download as a .csv.

Although, you still have to convert it to json format.

Answer 5

Basically, you have two choices: either parse the rendered HTML or parse the source wikicode.

The best I could find on Wikipedia's Tools page is wiki2csv, which converts table in wikicode format to CSV. Converting the CSV to JSON should be trivial using some CSV library.

Answer 6

Sometimes it pays off to look one step ahead, i.e. to check if the items and its properties represented by a Wikipedia table are also available in the Wikidata knowledge base.

Depending on the data, instead of parsing the wiki text table, it might be easier to query Wikidata for that data.

As an example, when trying to extract - say - country calling codes from Wikipedia, the data can also be retrieved with following SPARQL from Wikidata:

PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
SELECT ?cc ?alpha2 ?itemLabel ?item
WHERE {
  ?item wdt:P474 ?cc .
  ?item wdt:P297 ?alpha2 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en"  }    
}
order by ?cc

Such a query can be entered in the Wikidata Query Service form or directly issued as REST get request.

Example json output of the request:

[..]
    }, {
      "item" : {
        "type" : "uri",
        "value" : "http://www.wikidata.org/entity/Q265"
      },
      "alpha2" : {
        "type" : "literal",
        "value" : "UZ"
      },
      "cc" : {
        "type" : "literal",
        "value" : "+998"
      },
      "itemLabel" : {
        "xml:lang" : "en",
        "type" : "literal",
        "value" : "Uzbekistan"
      }
    } ]
  }
}

When omitting the format=json parameter, the REST endpoint return the results as XML document. For CSV output one has to omit the format parameter and add an accept header, e.g. via curl:

curl -H 'Accept: text/csv'  'https://query.wikidata.org/sparql?query=...

Note that the PREFIX declaration in the SPARQL queries aren't mandatory with the Wikidata endpoint, since it seems to understand some default ones.

Answer 7

another option, and imo the easiest to implement, although that comes with a tradeoff in regards to owning your data outright.

sign up for basic (free) scraperwiki.org account, and then log in. select create a new dataset, select extract data tables, then place the wikipedia url (any url) into the input form control and click extract tables. now your dataset(s) have been created, but you still have more options of what you can do with it...i always select view in a table, then download the data in .csv/.xlsx formats, which takes care of me owning my data. if you don't do that, you'll just be relying on some service, probably scraperwiki, to host the data for you. which isn't necessarily a bad thing. its also the reason why this solution is the easiest to implement.

here's your wikipedia article url, converted into a dataset:
https://scraperwiki.com/dataset/a6ddhxy

sraperwiki has more options, as well as even more options if you upgrade your account (which you should if you have to deal with data locked up in .pdfs, .csvs, etc., and you are not writing/using your own parsers for them.

my favorite scraperwiki option is to push your dataset(s) to a ckan api! utilize this everytime, even if you are never going to the repo to use...someone else can.

Answer 8

I don't usually do things by manually, but if you only need to do it once, it's sometimes cost effective.

In this case, you can go to the Edit page and copy the text.

Since it's formatted as a table, you can use command line tools (or a text editor like Notepad++ to parse into a CSV).

....
See also the [[List of television stations in the United States by call sign (initial letter W)|list of TV stations beginning with ''W'']] and the [[List of television stations in Mexico by call sign|list of TV stations beginning with ''X'']].

==KA==
{| class="wikitable sortable"
|-
! Call letters !! Channel !! Network(s) !! City and state !! Meaning or notes
|-
| [[KAAH-TV]]
| 27 PSIP 26
| [[Trinity Broadcasting Network|TBN]]
| [[Honolulu]], Hawaii
| K '''A'''ll '''A'''merican TV '''H'''onolulu
|-
| [[KAAL]]
... 

First, remove the non-table stuff. Everything in the table has a pipe character (|), so that would be the first thing to grep (and remove with sed. Then you can replace [ and ] with nothing and then replace the line breaks (\r\n in this case) with your delimiter. I'd choose a pipe or a tab (\t) since the text has commas.

This is a trial and error procedure, but in about 10 minutes you can usually have a working CSV file without writing elaborate code to do the same. With Notepad++ you can go to View -> Show Symbol -> Show All Characters and this will show you whitespace characters. In linux you can use od -c filename.

I have a lot practice, and it took 3 minutes. Here is a nearly correct CSV (requires some fine tuning. I used || as a delimiter. You should be able to fix the rest with find/replace.

Answer 9

You can try with http://pasty.link/ that it has ad hoc function to export in csv and json file.

Answer 10

TableTools2

• https://addons.mozilla.org/en-US/firefox/addon/tabletools2/

It's a Firefox addon that enables right-click copying of the table like this:

It works quite well and is very stable.