Nobel prizes are announced over a week in October and the award ceremony is today, December 10th (“It’s already tomorrow in Australia”). There is an interesting story of the how C. V. Raman (of Raman effect) booked his ticket to Stockholm for the December 10th award ceremony even before the announcement because he was confident of winning the prize!
I got this dataset from “Awesome JSON Datasets” repo at https://github.com/jdorfman/awesome-json-datasets
Nobel Prize dataset has three data files:
- Laureates — contains the profile of each laureate.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
{ "born": "1888-11-07", "bornCity": "Tiruchirappalli", "bornCountry": "India", "bornCountryCode": "IN", "died": "1970-11-21", "diedCity": "Bangalore", "diedCountry": "India", "diedCountryCode": "IN", "firstname": "Sir Chandrasekhara Venkata", "gender": "male", "id": "37", "prizes": [ { "affiliations": [ { "city": "Calcutta", "country": "India", "name": "Calcutta University" } ], "category": "physics", "motivation": "\"for his work on the scattering of light and for the discovery of the effect named after him\"", "share": "1", "year": "1930" } ], "surname": "Raman" } |
- Country — List of countries and the country code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ "code": "AR", "name": "Argentina" }, { "code": "AU", "name": "Australia" }, { "code": "AT", "name": "Austria" }, |
- Prize — the list of each prize by year and the winner grouped together.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
{ "category": "physics", "laureates": [ { "firstname": "Sin-Itiro", "id": "84", "motivation": "\"for their fundamental work in quantum electrodynamics, with deep-ploughing consequences for the physics of elementary particles\"", "share": "3", "surname": "Tomonaga" }, { "firstname": "Julian", "id": "85", "motivation": "\"for their fundamental work in quantum electrodynamics, with deep-ploughing consequences for the physics of elementary particles\"", "share": "3", "surname": "Schwinger" }, { "firstname": "Richard P.", "id": "86", "motivation": "\"for their fundamental work in quantum electrodynamics, with deep-ploughing consequences for the physics of elementary particles\"", "share": "3", "surname": "Feynman" } ], "year": "1965" } |
The prize document has an ID that links to the individual winner document in the laureate document. Let’s look at Nobel Laureates. First, you notice, the prizes information is PLURAL and is stored in an array. One person or an organization can win more than one prize. Let’s look at who all have won more than one prize.
Task 1. How many have won more than one Nobel prizes?
Query 1: Simply UNNEST the laureates array to get the individual prize winners’ object and then simply determine and filter by the length of the array. There is one entry per prize. For more on array handling and UNNEST operations, see the article: Working with JSON Arrays in N1QL.
|
1 2 3 4 5 6 7 |
select llist.firstname || " " || IFMISSING(llist.surname, ""), array_length(llist.prizes) numnobel from laureate l unnest l.laureates llist where array_length(llist.prizes) > 1; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[ { "$1": "Marie Curie, née Sklodowska", "numnobel": 2 }, { "$1": "John Bardeen", "numnobel": 2 }, { "$1": "Linus Carl Pauling", "numnobel": 2 }, { "$1": "Frederick Sanger", "numnobel": 2 }, { "$1": "Comité international de la Croix Rouge (International Committee of the Red Cross) ", "numnobel": 3 }, { "$1": "Office of the United Nations High Commissioner for Refugees (UNHCR) ", "numnobel": 2 } ] |
Task 2: Determine which country has produced the highest number of Nobel winners.
Query 2: Simply unnest laureates and group by the field bornCountry.
|
1 2 3 4 5 6 7 8 9 |
select llist.bornCountry, Count(1) totalPrizes from laureate l unnest l.laureates llist group by llist.bornCountry order by totalPrizes desc |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[ { "bornCountry": "USA", "totalPrizes": 269 }, { "bornCountry": "United Kingdom", "totalPrizes": 84 }, { "bornCountry": "Germany", "totalPrizes": 63 }, { "bornCountry": "France", "totalPrizes": 52 }, { "totalPrizes": 33 }, { "bornCountry": "Sweden", "totalPrizes": 29 }, |
Now, there are 33 winners without a country or origin… What’s going on here?
|
1 2 3 4 5 6 7 |
select llist.firstname || " " || IFMISSING(llist.surname, ""), llist.gender from laureate l unnest l.laureates llist where llist.bornCountry is missing; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{ "$1": "Institut de droit international (Institute of International Law) ", "gender": "org" }, { "$1": "Bureau international permanent de la Paix (Permanent International Peace Bureau) ", "gender": "org" }, { "$1": "Comité international de la Croix Rouge (International Committee of the Red Cross) ", "gender": "org" }, ... |
These seem to be international organizations and then a couple of winners whose data is missing bornCountry.
Task 3: How many have come from India.
Query 3: Nobel prizes have been awarded since 1901, but India got its independence in 1947. Until 1947, the born country has “British India” as the label. Let’s do an extended search.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select llist.firstname, llist.surname, llist.bornCountry as xborncountry, llist.diedCountry as ydiedcountry, llist.gender as zgender from laureate l unnest l.laureates llist where llist.bornCountry = "India" or lower(llist.bornCountry) like "%india%" or llist.diedCountry = "India" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
[ { "firstname": "Sir Chandrasekhara Venkata", "surname": "Raman", "xborncountry": "India", "ydiedcountry": "India", "zgender": "male" }, { "firstname": "Abdus", "surname": "Salam", "xborncountry": "India (now Pakistan)", "ydiedcountry": "United Kingdom", "zgender": "male" }, { "firstname": "Subramanyan", "surname": "Chandrasekhar", "xborncountry": "India (now Pakistan)", "ydiedcountry": "USA", "zgender": "male" }, { "firstname": "Ronald", "surname": "Ross", "xborncountry": "India", "ydiedcountry": "United Kingdom", "zgender": "male" }, { "firstname": "Har Gobind", "surname": "Khorana", "xborncountry": "India", "ydiedcountry": "USA", "zgender": "male" }, { "firstname": "Mother Teresa", "xborncountry": "Ottoman Empire (now Republic of Macedonia)", "ydiedcountry": "India", "zgender": "female" }, { "firstname": "Rudyard", "surname": "Kipling", "xborncountry": "British India (now India)", "ydiedcountry": "United Kingdom", "zgender": "male" }, { "firstname": "Rabindranath", "surname": "Tagore", "xborncountry": "India", "ydiedcountry": "India", "zgender": "male" }, { "firstname": "Amartya", "surname": "Sen", "xborncountry": "India", "zgender": "male" }, { "firstname": "Muhammad", "surname": "Yunus", "xborncountry": "British India (now Bangladesh)", "zgender": "male" }, { "firstname": "Venkatraman", "surname": "Ramakrishnan", "xborncountry": "India", "zgender": "male" }, { "firstname": "Kailash", "surname": "Satyarthi", "xborncountry": "India", "zgender": "male" } ] |
Some surprising results! In addition to the well known Indian Nobel Laureates, a couple of famous UK winners, including Rudyard Kipling, were born in India. There is only one winner who wasn’t born in India but died there: Mother Theresa.
Let’s find out the categories.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select lp.category, COUNT(1) prizecount from laureate l unnest l.laureates llist unnest llist.prizes lp where llist.bornCountry = "India" or lower(llist.bornCountry) like "%india%" or llist.diedCountry = "India" or lower(llist.diedCountry) like "%india%" group by lp.category order by prizecount DESC |
|
1 2 3 4 5 6 7 |
category prizecount peace 3 physics 3 literature 2 medicine 2 economics 1 chemistry 1 |
Task 4: Create a stacked graph of top 7 countries to win Nobel Prizes with categofy
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select llist.bornCountry as xborncountry, lp.category ycategory, COUNT(1) zprizecount, SUM(COUNT(1)) OVER(partition by llist.bornCountry) z1countrytot, SUM(COUNT(1)) OVER( partition by llist.bornCountry order by lp.category ) z1ctrycatcount from laureate l unnest l.laureates llist unnest llist.prizes lp group by llist.bornCountry, lp.category order by z1countrytot desc, ycategory |
Results (Subset for brevity)
From this, let’s create a stacked-column graph of top-7 countries and show the prizes by category.
All these data manipulations are for fun and learning. But, the progress and peace these winners have made sans borders.
You can download the dataset and Couchbase. Then ask your own interesting questions and answer them!
English