5000 people on Brexit & US Elections

First some charts and links to the data:

See all occupations.

See all universities and schools.

And regarding to the US elections:

See all occupations.

And all universities and schools.

How did I get this? First, the data of who agrees on topics such as these ones is on AgreeList — which is crowdsourced and at some point it will be partially automated. For example, it has the opinion of Barack Obama or Stephen Hawking on Brexit. Second, the data from occupations and education comes from Wikidata which is the structured database from Wikimedia Foundation (Wikipedia).

I also find really useful to use Google BigQuery to play with the data because we can do joins of AgreeList’s tables with Wikidata’s ones. For example, if we want to get all Nobel laureates in economics that agreed or disagreed on Brexit before the referendum we can do:

select w.name, a.extent from agreelist.agreements a
join agreelist.individuals i on i.id=a.individual_id
join(
 SELECT JSON_EXTRACT_SCALAR(item, ‘$.id’) id,
 JSON_EXTRACT_SCALAR(item, ‘$.sitelinks.enwiki.title’) name,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[0].mainsnak.datavalue.value.numeric-id’) award1,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[1].mainsnak.datavalue.value.numeric-id’) award2,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[2].mainsnak.datavalue.value.numeric-id’) award3,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[3].mainsnak.datavalue.value.numeric-id’) award4,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[4].mainsnak.datavalue.value.numeric-id’) award5,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[5].mainsnak.datavalue.value.numeric-id’) award6,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[6].mainsnak.datavalue.value.numeric-id’) award7
 FROM [fh-bigquery:wikidata.latest_en_v1]
) w ON i.wikidata_id=w.id
WHERE a.statement_id=7 AND (w.award1=”47170" OR w.award2=”47170" OR w.award3=”47170" OR w.award4=”47170" OR w.award5=”47170" OR w.award6=”47170" OR w.award7=”47170")
limit 100

And we get:

extent is the degree to which they agree (at least for now it can only be 100=agree or 0=disagree). Therefore we get that from all Nobel laureates in economics that have ever given their opinion on Brexit (on the BBC, their twitter account or whatever), all 11 of them disagreed. As every opinion/vote on AgreeList has a source, we see then that 10 of them signed a letter published on The Guardian and the other one is Paul Krugman who gave his opinion in The New York Times.

Now if for example we go to Paul Krugman’s Wikidata page, we see that he worked for the MIT in the past. What if we want now to get all the public figures that support Hillary Clinton or Donald Trump who work or have worked for the MIT? Easy, we just change the property to P108 (employer), set it in the where clause to Q49108 (MIT) and select the statement_id=182 (or we could add a new join and specify the title). The new query is:

select w.name, a.extent from agreelist.agreements a
join agreelist.individuals i on i.id=a.individual_id
join(
 SELECT JSON_EXTRACT_SCALAR(item, ‘$.id’) id,
 JSON_EXTRACT_SCALAR(item, ‘$.sitelinks.enwiki.title’) name,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[0].mainsnak.datavalue.value.numeric-id’) award1,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[1].mainsnak.datavalue.value.numeric-id’) award2,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[2].mainsnak.datavalue.value.numeric-id’) award3,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[3].mainsnak.datavalue.value.numeric-id’) award4,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[4].mainsnak.datavalue.value.numeric-id’) award5,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[5].mainsnak.datavalue.value.numeric-id’) award6,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[6].mainsnak.datavalue.value.numeric-id’) award7
 FROM [fh-bigquery:wikidata.latest_en_v1]
) w ON i.wikidata_id=w.id
WHERE a.statement_id=182 AND (w.award1=”49108" OR w.award2=”49108" OR w.award3=”49108" OR w.award4=”49108" OR w.award5=”49108" OR w.award6=”49108" OR w.award7=”49108")
limit 100

And the result:

We see that from 7 people who are or have been employed by the MIT, 6 of them prefer Hillary Clinton to Donald Trump.

Related posts:

AgreeList & the biggest problem in the world

BigQuery, Wikidata & AgreeList — idea