Extracting data from Wikipedia using curl, grep, cut and other bash commands

Wish I could upvote this more. That is some serious queryselector fu.

Nice. Nasty, but nice :)

I can't help but notice a small bug.... Driulis Gonzalez for example has medalled 4 times, but your script gives his count as only 3. Similarly Gévrise Émane isn't listed by your script. Something to do with split tables cells I suspect.

Still, it's inspiring. I've often used bash and perl to scrape data from web pages. I'll definitely consider JS in future.

This isn't for production-level data migration. It's for smooshing some source text into a shape which is useful to you.

Parsing HTML with regexps is fine if you're just curious roughly how many images are in a page. It's great for quick command line experiments. It's just not good when you need to be "doing it properly".

It's even worse. The syntax of MediaWiki markup (parser functions, etc.) will vary depending on extensions installed in a given instance. And there is no way to obtain a list of parser functions and extension tags (the latter of which look like HTML tags, but parse differently). There is literally no way to reliably parse MediaWiki markup offline. [0] can give you a parse tree — but only of a top-level page, you can't recursively expand templates with it. And even that parse tree only gives you information about which templates should be expanded. After expanding templates you have to parse the whole thing again to actually interpret the formatting markup. Oh, and expand strip markers: [1]. Because some extensions which define their own tags will want to output raw, unfiltered HTML. But tags are parsed in the template expansion stage and raw HTML isn't allowed in the formatting stage, so what do the extensions do? They output "strip markers" that are later substituted for the raw HTML.

Most alternative parsing libraries (at least all the ones I looked at, and that includes Wikimedia's own Parsoid!) don't bother with implementing all that complexity. Which means that they can be often tripped by sufficiently tricky markup; and this turns out to be a quite low bar.

Parsing MediaWiki markup properly is an insanity on a level comparable only with TeX and Unix shell scripts. Even PHP is saner.

[0] https://www.mediawiki.org/wiki/API:Parsing_wikitext

[1] https://www.mediawiki.org/wiki/Strip_marker

dbpedia provides very low coverage of wikipedia information.

I looked at dbpedia, but it was non-obvious to me what statements to use. We can also use SPARQL with wikidata, although the coverage isn't particularly great.I threw together an example query for medalists and michael phelps doesn't make the list, because he doesn't have the appropriate participant of/award received statements:

https://query.wikidata.org/#SELECT%20%3Fhuman%20%3FhumanLabe...

  SELECT ?human ?humanLabel ?count WHERE {
    {
      SELECT ?human (COUNT(*) as ?count) WHERE {
        ?event wdt:P31 wd:Q18536594 . # All items that are instance of Olympic sporting event
  
        ?medal wdt:P279 wd:Q636830 .    # All items that are subclass of Olympic medal 

        ?human p:P1344 ?participantStat . # Humans with a participant of statement
        ?participantStat ps:P1344 ?event . # .. that has any of the values of ?event
        ?participantStat pq:P166 ?medal . # .. with the award received qualifier of any of the values of ?medal
      }
      GROUP BY ?human
    }
  
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
  ORDER BY DESC(?count)
  LIMIT 100

EDIT: The dbpedia search should be something like: http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbp...

  SELECT ?human ?count WHERE
  {
    {
      SELECT ?human (count(*) as ?count) WHERE {
        ?event rdf:type dbo:OlympicEvent
        {
          ?event dbo:bronzeMedalist ?human .  
        } UNION {
          ?event dbo:silverMedalist ?human
        } UNION {
          ?event dbo:goldMedalist ?human
        }
      }
      GROUP BY ?human
    }
  }
  ORDER BY DESC(?count)
  LIMIT 100

100% this. Coming from someone whose made disgusting chained commands to parse access logs for relevant information, you _need_ better documentation than you can do with a single line of commands.

I have so many `zgrep ... | awk ... | sort` scripts that I legitimately couldn't tell you what they do anymore, just what the correct output is.

I really like the feeling of proving to myself that I know how to use my command line well, but in most cases I end up spending longer trying to remember the differences between OS X built-in `sed` and the `sed` I know and blah. Lots of wasted time.

I've started keeping a "scratch" git repo with scripts for one-offs, so the next time I say "oh I want to go through this CSV and run X on each line with Y" I can look at my old code and replace the necessary parts.

I've used Makefiles to coordinate a zoo of perl/bash scripts before. It's pretty effective.

> far preferable

Well, that depends. Shell scripts can be commented, and they can be built progressively and interactively by building a pipeline. That's a great choice for a one-off task, and in my experience much faster than many other approaches.

It also works well for tasks close to the system. For example, our users are able to download large archives of data, and we keep over 99% of such downloads indefinitely. We delete downloads > 100GB after they're more than 6 months old.

With a shell script run by cron that's achieved with find + rm + curl|jq (to tell the API the download is deleted).

Which company/industry do you work in, where people who would be parsing your code are unable to grok something as basic as bash?

Most interesting data-analysis problems require multiple iterations. It's not a bad idea to take a couple quick exploratory passes at your data with interactive command-line tools, look at its shape, figure out what areas you need to take a closer look at, and then write real programs to work specifically with those.

I've gotten surprisingly far using just curl/cat/cut/sed/awk/wc & friends. When I need to build on top of that, I go write a real program, but the UNIX-fu tells me what I need to build on top of.

What? For a one-off, one-time "I wonder who has the most medals" curiosity script?

> 8 LOC scraper that those without bash knowledge will be unable to parse.

And someone without knowledge of Node.js will be unable to parse the 50-line JavaScript monstrosity.

> And the 8 LOC bash script is not much of a time savings as this post demonstrates; you still have to check each function output manually to find which data to parse / handle edge cases.

It only took that long because he detailed every step as a tutorial. If you have basic literacy of the shell, it's no time at all.

To refute you, I decided to solve it myself, using only the "hint" at the beginning of the article to use ?action=raw to work with the source wikitext (I had not yet read the rest of the article; I had not seen his solution).

It took me literally 2 minutes: (Setting in url='https://en.wikipedia.org/wiki/List_of_Olympic_medalists_in_j... for readability on HN)

    [2016-08-15 17:52] curl -s ${url}
    [2016-08-15 17:52] curl -s ${url}|grep flagIOCmedalist
    [2016-08-15 17:52] curl -s ${url}|grep -o '{{flagIOCmedalist.*}}'
    [2016-08-15 17:53] curl -s ${url}|grep -o '{{flagIOCmedalist.*}}'|cut -d'|' -f2|sed 's/[[\]]//g'
    [2016-08-15 17:53] curl -s ${url}|grep -o '{{flagIOCmedalist.*}}'|cut -d'|' -f2
    [2016-08-15 17:54] curl -s ${url}|grep -o '{{flagIOCmedalist.*}}'|cut -d'|' -f2|sed 's/\(\[\|\]\)//g'
    [2016-08-15 17:54] curl -s ${url}|grep -o '{{flagIOCmedalist.*}}'|cut -d'|' -f2|sed 's/\(\[\|\]\)//g'|sort |uniq -c
    [2016-08-15 17:54] curl -s ${url}|grep -o '{{flagIOCmedalist.*}}'|cut -d'|' -f2|sed 's/\(\[\|\]\)//g'|sort |uniq -c|sort -n

You can see the only place I fumbled a bit was with escaping brackets inside of brackets in sed, which is admittedly a little wonky.

Sure, for something that might need to run repeatedly, it probably doesn't handle future edge cases that might arise. But it's not production, it's a one-off exploring the data script. Not every one-off program you write needs to be production quality.

And being worried about "those without bash knowledge"... don't be afraid to use your operating system!

That said, this line of solutions has a big edge case: it relies on the editors of Wikipedia being consistent and formatting each row as one line in the source. If I were to solve this totally on my own, choosing to ignore the "hint", and work with the rendered HTML, and just do most of it with a nokogiri one-liner.

Knowing that there's an ?action=something to get just the page HTML without the navigation and such. I spent about 3 minutes finding the "render" action (documented here: https://www.mediawiki.org/wiki/Manual:Parameters_to_index.ph... ). Then anther 3-ish minutes poking around the DOM in my browser to get an idea of what I'm working with, and visually inspecting the layout of the article; each cell with a medalist has two links, the first to the medalist, and the second to the country. Then it took me a whopping 5 minutes to hammer out the rest of the one-liner.

(Similarly, url='https://en.wikipedia.org/wiki/List_of_Olympic_medalists_in_j... for HN readability)

    [2016-08-15 18:14] curl -s ${url}
    [2016-08-15 18:15] curl -s ${url}|nokogiri -e '$_.css("table.wikitable td a:first-child").each{|a| puts a.text}'
    [2016-08-15 18:16] curl -s ${url}|nokogiri -e '$_.css("table.wikitable td a:first-child").each{|a| puts a.text}'|grep -vE '^[0-9]{4} '
    [2016-08-15 18:17] curl -s ${url}|nokogiri -e '$_.css("table.wikitable td a:first-child").each{|a| puts a.text.sub!("\n", " ")}'
    [2016-08-15 18:18] curl -s ${url}|nokogiri -e '$_.css("table.wikitable td a:first-child").each{|a| puts a.text.sub("\n", " ")}'
    [2016-08-15 18:18] curl -s ${url}|nokogiri -e '$_.css("table.wikitable td a:first-child").each{|a| puts a.text}'|grep -vE -e '^[0-9]{4} ' -e '^details$'
    [2016-08-15 18:19] curl -s ${url}|nokogiri -e '$_.css("table.wikitable td a:first-child").each{|a| puts a.text}'|grep -vE -e '^[0-9]{4} ' -e '^details$'|sort |uniq -c|sort -n

And most of it was fumbling around with thinking that the year/details links were one link with a newline in them, when they are in fact two separate links.

For a grand total of 11 minutes; generously.

My point is: Don't be afraid to play around with your tools and data! Have fun! Not everything needs to be production quality! And being written primarily in bash doesn't necessarily mean that it isn't production quality!

see also the SPARQL search: https://query.wikidata.org/

Is converting Wikitext to HTML/RDFa really going to help with this task? I'd say it's actually clearer how to get the data out of the original Wikitext.

Agreed. I was a long time abuser of cut, but has moved to relying on sed instead. I find that it's generally a lot more robust if you think through your expressions. For certain cases awk will also do the job. Perl oneliners do seem convenient but that has never been my cup of tea.

But he didn't parse any HTML in the article.

This is my goto approach every time I have to parse html or XML. I still don't understand why people don't use something as simple as google spreadsheets and write a simple xpath to load tabular data using =IMPORTHTML().

I was thinking the same thing. Wikimedia makes all of wikipedia available for download. You don't need to screen scrape. LOL. I guess they had some other wikis they want to get data from but the "main" worldwide wikipedia site, that everyone thinks of as wikipedia makes the data freely downloadable, and I've downloaded it before.

cut, awk, grep, and perl can churn through an initial data dump like nobody's business.

Honestly, what novelty factor?