JSON to Insights: Nobel Prize Winners' Dataset

Nobel prizes are announced over a week in October and the award ceremony was December 10th. 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.

   {
          "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

    {
          "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.

        {
          "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.

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": "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.

select
  llist.bornCountry,
  Count(1) totalPrizes
from
  laureate l unnest l.laureates llist
group by
  llist.bornCountry
order by
  totalPrizes desc

[
  {
    "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?

select
  llist.firstname || " " || IFMISSING(llist.surname, ""),
  llist.gender
from
  laureate l unnest l.laureates llist
where
  llist.bornCountry is missing;

  {
    "$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.

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"

[
  {
    "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.

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

Output, in tabular form.

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 the category statistics.

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 (In tabular form, subset for brevity)

From this, let’s create a stacked-column graph of top-7 countries and show the prizes by category.