18 - Introduction to MongoDB in Python.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1 - Flexibly Structured Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Listing databases and collections\n",
    "\n",
    "# Save a list of names of the databases managed by client\n",
    "db_names = client.list_database_names()\n",
    "print(db_names)\n",
    "\n",
    "# Save a list of names of the collections managed by the \"nobel\" database\n",
    "nobel_coll_names = client.nobel.list_collection_names()\n",
    "print(nobel_coll_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## List fields of a document\n",
    "\n",
    "# Connect to the \"nobel\" database\n",
    "db = client.nobel\n",
    "_______________________________________\n",
    "# Retrieve sample prize and laureate documents\n",
    "prize = db.prizes.find_one()\n",
    "laureate = db.laureates.find_one()\n",
    "____________________________________________________\n",
    "# Print the sample prize and laureate documents\n",
    "print(prize)\n",
    "print(laureate)\n",
    "print(type(laureate))\n",
    "\n",
    "# Get the fields present in each type of document\n",
    "prize_fields = list(prize.keys())\n",
    "laureate_fields = list(laureate.keys())\n",
    "\n",
    "print(prize_fields)\n",
    "print(laureate_fields)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## \"born\" approximation\n",
    "\n",
    "db.laureates.count_documents({\"born\": {\"$lt\": \"1800\"}})\n",
    "## $lt = less than\n",
    "___________________________________________________\n",
    "\n",
    "db.laureates.count_documents({\"born\": {\"$lt\": \"1700\"}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Composing filters\n",
    "\n",
    "# Create a filter for laureates who died in the USA\n",
    "criteria = {'diedCountry': 'USA'}\n",
    "\n",
    "# Save the count of these laureates\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)\n",
    "__________________________________________\n",
    "\n",
    "# Create a filter for laureates who died in the USA but were born in Germany\n",
    "criteria = {'diedCountry': 'USA', \n",
    "            'bornCountry': 'Germany'}\n",
    "\n",
    "# Save the count\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)\n",
    "_______________________________________________\n",
    "\n",
    "# Create a filter for Germany-born laureates who died in the USA and with the first name \"Albert\"\n",
    "criteria = {'bornCountry': 'Germany', \n",
    "            'diedCountry': 'USA', \n",
    "            'firstname': 'Albert'}\n",
    "\n",
    "# Save the count\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# Save a filter for laureates born in the USA, Canada, or Mexico\n",
    "criteria = { 'bornCountry': \n",
    "                { \"$in\": ['USA','Canada','Mexico']}\n",
    "             }\n",
    "\n",
    "# Count them and save the count\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)\n",
    "\n",
    "____________________________________________________\n",
    "\n",
    "# Save a filter for laureates who died in the USA and were not born there\n",
    "criteria = { 'diedCountry': 'USA',\n",
    "               'bornCountry': { \"$ne\": 'USA'}, \n",
    "             }\n",
    "### -- $ne is great when you don't want to have to list all other options to $in.\n",
    "\n",
    "# Count them\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Starting our ascent\n",
    "\n",
    "# Filter for laureates born in Austria with non-Austria prize affiliation\n",
    "criteria = {'bornCountry': 'Austria', \n",
    "            'prizes.affiliations.country': {\"$ne\": 'Austria'}}\n",
    "\n",
    "# Count the number of such laureates\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Our 'born' approximation, and a special laureate\n",
    "\n",
    "# Filter for documents without a \"born\" field\n",
    "criteria = { 'born': {\"$exists\": False}}\n",
    "\n",
    "# Save count\n",
    "count = db.laureates.count_documents(criteria)\n",
    "print(count)\n",
    "________________________________________\n",
    "\n",
    "# Filter for laureates with at least three prizes\n",
    "criteria = {'prizes.2': {\"$exists\": True}}\n",
    "\n",
    "# Find one laureate with at least three prizes\n",
    "doc = db.laureates.find_one(criteria)\n",
    "\n",
    "# Print the document\n",
    "print(doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "2 - Working with Distinct Values and Sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Categorical data validation\n",
    "\n",
    "assert set(db.prizes.distinct(\"category\")) == set(db.laureates.distinct(\"prizes.category\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Never from there, but sometimes there at last\n",
    "\n",
    "# Countries recorded as countries of death but not as countries of birth\n",
    "countries = set(db.laureates.distinct(\"diedCountry\")) - set(db.laureates.distinct(\"bornCountry\"))\n",
    "print(countries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Countries of affiliation\n",
    "\n",
    "# The number of distinct countries of laureate affiliation for prizes\n",
    "count = len(db.laureates.distinct('prizes.affiliations.country'))\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Born here, went there\n",
    "\n",
    "db.laureates.distinct('prizes.affiliations.country', {'bornCountry': 'USA'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Triple plays (mostly) all around\n",
    "\n",
    "# Save a filter for prize documents with three or more laureates\n",
    "criteria = {'laureates.2': {'$exists': True}}\n",
    "\n",
    "# Save the set of distinct prize categories in documents satisfying the criteria\n",
    "triple_play_categories = set(db.prizes.distinct('category', criteria))\n",
    "\n",
    "# Confirm literature as the only category not satisfying the criteria.\n",
    "assert set(db.prizes.distinct(\"category\")) - triple_play_categories == {\"literature\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Sharing in physics after World War II\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Meanwhile, in other categories...\n",
    "\n",
    "# Save a filter for laureates with unshared prizes\n",
    "unshared = {\n",
    "    \"prizes\": {'$elemMatch': {\n",
    "        \"category\": {\"$nin\": [\"physics\", \"chemistry\", \"medicine\"]},\n",
    "        \"share\": \"1\",\n",
    "        \"year\": {\"$gte\": \"1945\"},\n",
    "    }}}\n",
    "\n",
    "# Save a filter for laureates with shared prizes\n",
    "shared = {\n",
    "    \"prizes\": {\"$elemMatch\": {\n",
    "        \"category\": {\"$nin\": [\"physics\", \"chemistry\", \"medicine\"]},\n",
    "        \"share\": {\"$ne\": \"1\"},\n",
    "        \"year\": {\"$gte\": \"1945\"},\n",
    "    }}}\n",
    "\n",
    "ratio = db.laureates.count_documents(unshared) / db.laureates.count_documents(shared)\n",
    "print(ratio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Organizations and prizes over time\n",
    "\n",
    "# Save a filter for organization laureates with prizes won before 1945\n",
    "before = {\n",
    "    \"gender\": \"org\",\n",
    "    \"prizes.year\": {\"$lt\": \"1945\"},\n",
    "    }\n",
    "\n",
    "# Save a filter for organization laureates with prizes won in or after 1945\n",
    "in_or_after = {\n",
    "    'gender': 'org',\n",
    "    'prizes.year': {'$gte': \"1945\"},\n",
    "    }\n",
    "\n",
    "n_before = db.laureates.count_documents(before)\n",
    "n_in_or_after = db.laureates.count_documents(in_or_after)\n",
    "ratio = n_in_or_after / (n_in_or_after + n_before)\n",
    "print(ratio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Glenn, George, and others in the G.B. crew\n",
    "\n",
    "db.laureates.count_documents({\"firstname\": Regex(\"^G\"), \"surname\": Regex(\"^S\")})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Germany, then and now\n",
    "\n",
    "from bson.regex import Regex\n",
    "\n",
    "# Filter for laureates with \"Germany\" in their \"bornCountry\" value\n",
    "criteria = {\"bornCountry\": Regex(\"Germany\")}\n",
    "print(set(db.laureates.distinct(\"bornCountry\", criteria)))\n",
    "\n",
    "______________________________________________________\n",
    "# Filter for laureates with a \"bornCountry\" value starting with \"Germany\"\n",
    "criteria = {\"bornCountry\": Regex(\"^Germany\")}\n",
    "print(set(db.laureates.distinct(\"bornCountry\", criteria)))\n",
    "\n",
    "______________________________________________________\n",
    "\n",
    "# Fill in a string value to be sandwiched between the strings \"^\" and \"now\"\n",
    "criteria = {\"bornCountry\": Regex(\"^\" + \"Germany \\(\" + \"now\")}\n",
    "print(set(db.laureates.distinct(\"bornCountry\", criteria)))\n",
    "\n",
    "________________________________________________________\n",
    "\n",
    "# Filter for currently-Germany countries of birth. Fill in a \n",
    "# string value to be sandwiched between the strings \"now\" and \"$\"\n",
    "criteria = {\"bornCountry\": Regex(\"now\" + \" Germany\\)\" + \"$\")}\n",
    "print(set(db.laureates.distinct(\"bornCountry\", criteria)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## The prized transistor\n",
    "\n",
    "from bson.regex import Regex\n",
    "\n",
    "# Save a filter for laureates with prize motivation values containing \"transistor\" as a substring\n",
    "criteria = {'prizes.motivation': Regex('transistor')}\n",
    "\n",
    "# Save the field names corresponding to a laureate's first name and last name\n",
    "first, last = 'firstname', 'surname'\n",
    "print([(laureate[first], laureate[last]) for laureate in db.laureates.find(criteria)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "3 - Get Only What You Need, and Fast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Shares of the 1903 Prize in Physics\n",
    "\n",
    "db.laureates.find_one({\"prizes\": {\"$elemMatch\": {\"category\": \"physics\", \"year\": \"1903\"}}})\n",
    "\n",
    "# Which projection(s) will fetch ONLY the laureates' \n",
    "# full names and prize share info? I encourage you to \n",
    "# experiment with the console and re-familiarize yourself \n",
    "# with the structure of laureate collection documents.\n",
    "{\"firstname\": 1, \"surname\": 1, \"prizes.share\": 1, \"_id\": 0}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Rounding up the G.S. crew\n",
    "\n",
    "# Find laureates whose first name starts with \"G\" and last name starts with \"S\"\n",
    "docs = db.laureates.find(\n",
    "       filter= {\"firstname\" : {\"$regex\" : \"^G\"},\n",
    "                  'surname' : {\"$regex\" : \"^S\"}  })\n",
    "# Print the first document \n",
    "print(docs[0])\n",
    "\n",
    "_______________________________________________________\n",
    "\n",
    "# Use projection to select only firstname and surname\n",
    "docs = db.laureates.find(\n",
    "       filter= {\"firstname\" : {\"$regex\" : \"^G\"},\n",
    "                \"surname\" : {\"$regex\" : \"^S\"}  },\n",
    "   projection= [\"firstname\", \"surname\"]  )\n",
    "\n",
    "# Iterate over docs and concatenate first name and surname\n",
    "full_names = [doc[\"firstname\"] + \" \" + doc[\"surname\"]  for doc in docs]\n",
    "\n",
    "# Print the full names\n",
    "print(full_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Doing our share of data validation\n",
    "\n",
    "# Save documents, projecting out laureates share\n",
    "prizes = db.prizes.find({}, [\"laureates.share\"])\n",
    "\n",
    "# Iterate over prizes\n",
    "for prize in prizes:\n",
    "    # Initialize total share\n",
    "    total_share = 0\n",
    "    \n",
    "    # Iterate over laureates for the prize\n",
    "    for laureate in prize[\"laureates\"]:\n",
    "        # add the share of the laureate to total_share\n",
    "        total_share += 1 / float(laureate['share'])\n",
    "        \n",
    "    # Print the total share    \n",
    "    print(total_share)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Sorting together: MongoDB + Python\n",
    "    \n",
    "from operator import itemgetter\n",
    "\n",
    "def all_laureates(prize):  \n",
    "  # sort the laureates by surname\n",
    "  sorted_laureates = sorted(prize['laureates'], key=itemgetter('surname'))\n",
    "  \n",
    "  # extract surnames\n",
    "  surnames = [laureate[\"surname\"] for laureate in sorted_laureates]\n",
    "  \n",
    "  # concatenate surnames separated with \" and \" \n",
    "  all_names = \" and \".join(surnames)\n",
    "  \n",
    "  return all_names\n",
    "\n",
    "# test the function on a sample doc\n",
    "print(all_laureates(sample_prize))\n",
    "_________________________________________________\n",
    "\n",
    "def all_laureates(prize):  \n",
    "  # sort the laureates by surname\n",
    "  sorted_laureates = sorted(prize[\"laureates\"], key=itemgetter(\"surname\"))\n",
    "  \n",
    "  # extract surnames\n",
    "  surnames = [laureate[\"surname\"] for laureate in sorted_laureates]\n",
    "  \n",
    "  # concatenate surnames separated with \" and \" \n",
    "  all_names = \" and \".join(surnames)\n",
    "  \n",
    "  return all_names\n",
    "\n",
    "# find physics prizes, project year and name, and sort by year\n",
    "docs = db.prizes.find(\n",
    "           filter= {\"category\": \"physics\"}, \n",
    "           projection= [\"year\", \"laureates.firstname\", \"laureates.surname\"], \n",
    "           sort= [(\"year\", 1)])\n",
    "\n",
    "_________________________________________________\n",
    "\n",
    "def all_laureates(prize):  \n",
    "  # sort the laureates by surname\n",
    "  sorted_laureates = sorted(prize[\"laureates\"], key=itemgetter(\"surname\"))\n",
    "  \n",
    "  # extract surnames\n",
    "  surnames = [laureate[\"surname\"] for laureate in sorted_laureates]\n",
    "  \n",
    "  # concatenate surnames separated with \" and \" \n",
    "  all_names = \" and \".join(surnames)\n",
    "  \n",
    "  return all_names\n",
    "\n",
    "# find physics prizes, project year and name, and sort by year\n",
    "docs = db.prizes.find(\n",
    "           filter= {\"category\": \"physics\"}, \n",
    "           projection= [\"year\", \"laureates.firstname\", \"laureates.surname\"], \n",
    "           sort= [(\"year\", 1)])\n",
    "\n",
    "# print the year and laureate names (from all_laureates)\n",
    "for doc in docs:\n",
    "    print(\"{year}: {names}\".format(year=doc['year'], names=all_laureates(doc)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Gap years\n",
    "\n",
    "# original categories from 1901\n",
    "original_categories = db.prizes.distinct('category', {'year': '1901'})\n",
    "print(original_categories)\n",
    "\n",
    "# project year and category, and sort\n",
    "docs = db.prizes.find(\n",
    "        filter={},\n",
    "        projection = {'year':1, 'category':1, \"_id\":0},\n",
    "        sort=[('year', -1), ('category', 1)]\n",
    ")\n",
    "\n",
    "#print the documents\n",
    "for doc in docs:\n",
    "  print(doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Recently single?\n",
    "\n",
    "# Specify an index model for compound sorting\n",
    "index_model = [('category', 1), ('year', -1)]\n",
    "db.prizes.create_index(index_model)\n",
    "\n",
    "# Collect the last single-laureate year for each category\n",
    "report = \"\"\n",
    "for category in sorted(db.prizes.distinct(\"category\")):\n",
    "    doc = db.prizes.find_one(\n",
    "        {'category': category, \"laureates.share\": \"1\"},\n",
    "        sort=[('year', -1)]\n",
    "    )\n",
    "    report += \"{category}: {year}\\n\".format(**doc)\n",
    "\n",
    "print(report)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Born and affiliated\n",
    "\n",
    "from collections import Counter\n",
    "\n",
    "# Ensure an index on country of birth\n",
    "db.laureates.create_index([('bornCountry', 1)])\n",
    "\n",
    "# Collect a count of laureates for each country of birth\n",
    "n_born_and_affiliated = {\n",
    "    country: db.laureates.count_documents({\n",
    "        'bornCountry': country,\n",
    "        \"prizes.affiliations.country\": country\n",
    "    })\n",
    "    for country in db.laureates.distinct(\"bornCountry\")\n",
    "}\n",
    "\n",
    "five_most_common = Counter(n_born_and_affiliated).most_common(5)\n",
    "print(five_most_common)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## The first five prizes with quarter shares\n",
    "\n",
    "from pprint import pprint\n",
    "\n",
    "# Fetch prizes with quarter-share laureate(s)\n",
    "filter_ = {'laureates.share': '4'}\n",
    "\n",
    "# Save the list of field names\n",
    "projection = ['category', 'year', 'laureates.motivation']\n",
    "\n",
    "# Save a cursor to yield the first five prizes\n",
    "cursor = db.prizes.find(filter_, projection).sort('year').limit(5)\n",
    "pprint(list(cursor))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Pages of particle-prized people\n",
    "\n",
    "from pprint import pprint\n",
    "\n",
    "# Write a function to retrieve a page of data\n",
    "def get_particle_laureates(page_number=1, page_size=3):\n",
    "    if page_number < 1 or not isinstance(page_number, int):\n",
    "        raise ValueError(\"Pages are natural numbers (starting from 1).\")\n",
    "    particle_laureates = list(\n",
    "        db.laureates.find(\n",
    "            {'prizes.motivation': {'$regex': \"particle\"}},\n",
    "            [\"firstname\", \"surname\", \"prizes\"])\n",
    "        .sort([('prizes.year', 1), ('surname', 1)])\n",
    "        .skip(page_size * (page_number - 1))\n",
    "        .limit(page_size))\n",
    "    return particle_laureates\n",
    "\n",
    "# Collect and save the first nine pages\n",
    "pages = [get_particle_laureates(page_number=page) for page in range(1,9)]\n",
    "pprint(pages[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "4 - Aggregation Pipelines: Let the Server Do It For You"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Aggregating a few individuals' country data\n",
    "\n",
    "# Translate cursor to aggregation pipeline\n",
    "pipeline = [\n",
    "    {'$match': {'gender': {'$ne':'org'}}},\n",
    "    {'$project': {'bornCountry': 1, 'prizes.affiliations.country': 1}},\n",
    "    {'$limit': 3}\n",
    "]\n",
    "\n",
    "for doc in db.laureates.aggregate(pipeline):\n",
    "    print(\"{bornCountry}: {prizes}\".format(**doc))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Passing the aggregation baton to Python\n",
    "\n",
    "from collections import OrderedDict\n",
    "from itertools import groupby\n",
    "from operator import itemgetter\n",
    "\n",
    "original_categories = set(db.prizes.distinct(\"category\", {\"year\": \"1901\"}))\n",
    "\n",
    "# Save an pipeline to collect original-category prizes\n",
    "pipeline = [\n",
    "    {'$match': {'category': {'$in': sorted(original_categories)}}},\n",
    "    {\"$project\": {'category': 1, 'year': 1}},\n",
    "    {'$sort': OrderedDict([('year', -1)])}\n",
    "]\n",
    "cursor = db.prizes.aggregate(pipeline)\n",
    "for key, group in groupby(cursor, key=itemgetter(\"year\")):\n",
    "    missing = original_categories - {doc[\"category\"] for doc in group}\n",
    "    if missing:\n",
    "        print(\"{year}: {missing}\".format(year=key, missing=\", \".join(sorted(missing))))\n",
    "        \n",
    "# <script.py> output:\n",
    "#     2018: literature\n",
    "#     1972: peace\n",
    "#     1967: peace\n",
    "#     1966: peace\n",
    "#     1956: peace\n",
    "#     1955: peace\n",
    "#     1948: peace\n",
    "#     1943: literature, peace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Field Paths and Sets\n",
    "\n",
    "list(db.prizes.aggregate([\n",
    "    {\"$project\": {\"allThree\": {\"$setEquals\": [____, ____]},\n",
    "                  \"noneThree\": {\"$not\": {\"$setIsSubset\": [____, ____]}}}},\n",
    "    {\"$match\": {\"$nor\": [{\"allThree\": True}, {\"noneThree\": True}]}}]))\n",
    "\n",
    "# \"$laureates.share\", [\"3\"], [\"3\"], \"$laureates.share\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Organizing prizes\n",
    "\n",
    "# Count prizes awarded (at least partly) to organizations as a sum over sizes of \"prizes\" arrays.\n",
    "pipeline = [\n",
    "    {'$match': {'gender': \"org\"}},\n",
    "    {\"$project\": {\"n_prizes\": {\"$size\": '$prizes'}}},\n",
    "    {\"$group\": {\"_id\": None, \"n_prizes_total\": {\"$sum\": '$n_prizes'}}}\n",
    "]\n",
    "\n",
    "print(list(db.laureates.aggregate(pipeline)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Gap years, aggregated\n",
    "\n",
    "from collections import OrderedDict\n",
    "\n",
    "original_categories = sorted(set(db.prizes.distinct(\"category\", {\"year\": \"1901\"})))\n",
    "pipeline = [\n",
    "    {\"$match\": {\"category\": {\"$in\": original_categories}}},\n",
    "    {\"$project\": {\"category\": 1, \"year\": 1}},\n",
    "    \n",
    "    # Collect the set of category values for each prize year.\n",
    "    {\"$group\": {\"_id\": '$year', \"categories\": {\"$addToSet\": \"$category\"}}},\n",
    "    \n",
    "    # Project categories *not* awarded (i.e., that are missing this year).\n",
    "    {\"$project\": {\"missing\": {\"$setDifference\": [original_categories, '$categories']}}},\n",
    "    \n",
    "    # Only include years with at least one missing category\n",
    "    {\"$match\": {\"missing.0\": {\"$exists\": True}}},\n",
    "    \n",
    "    # Sort in reverse chronological order. Note that \"_id\" is a distinct year at this stage.\n",
    "    {\"$sort\": OrderedDict([(\"_id\", -1)])},\n",
    "]\n",
    "for doc in db.prizes.aggregate(pipeline):\n",
    "    print(\"{year}: {missing}\".format(year=doc[\"_id\"],missing=\", \".join(sorted(doc[\"missing\"]))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Here and elsewhere\n",
    "\n",
    "key_ac = \"prizes.affiliations.country\"\n",
    "key_bc = \"bornCountry\"\n",
    "pipeline = [\n",
    "    {\"$project\": {key_bc: 1, key_ac: 1}},\n",
    "\n",
    "    # Ensure a single prize affiliation country per pipeline document\n",
    "    {'$unwind': \"$prizes\"},\n",
    "    {'$unwind': \"$prizes.affiliations\"},\n",
    "\n",
    "    # Ensure values in the list of distinct values (so not empty)\n",
    "    {\"$match\": {key_ac: {'$in': db.laureates.distinct(key_ac)}}},\n",
    "    {\"$project\": {\"affilCountrySameAsBorn\": {\n",
    "        \"$gte\": [{\"$indexOfBytes\": [\"$\"+key_ac, \"$\"+key_bc]}, 0]}}},\n",
    "\n",
    "    # Count by \"$affilCountrySameAsBorn\" value (True or False)\n",
    "    {\"$group\": {\"_id\": \"$affilCountrySameAsBorn\",\n",
    "                \"count\": {\"$sum\": 1}}},\n",
    "]\n",
    "for doc in db.laureates.aggregate(pipeline): print(doc)\n",
    "\n",
    "# <script.py> output:\n",
    "# {'count': 477, '_id': True}\n",
    "# {'count': 261, '_id': False}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Countries of birth by prize category\n",
    "\n",
    "pipeline = [\n",
    "    # Unwind the laureates array\n",
    "    {'$unwind': \"$laureates\"},\n",
    "    {\"$lookup\": {\n",
    "        \"from\": \"laureates\", \"foreignField\": \"id\",\n",
    "        \"localField\": \"laureates.id\", \"as\": \"laureate_bios\"}},\n",
    "\n",
    "    # Unwind the new laureate_bios array\n",
    "    {\"$unwind\": \"$laureate_bios\"},\n",
    "    {\"$project\": {\"category\": 1,\n",
    "                  \"bornCountry\": \"$laureate_bios.bornCountry\"}},\n",
    "\n",
    "    # Collect bornCountry values associated with each prize category\n",
    "    {\"$group\": {\"_id\": \"$category\",\n",
    "                \"bornCountries\": {\"$addToSet\": \"$bornCountry\"}}},\n",
    "\n",
    "    # Project out the size of each category's (set of) bornCountries\n",
    "    {\"$project\": {\"category\": 1,\n",
    "                  \"nBornCountries\": {\"$size\": \"$bornCountries\"}}},\n",
    "    {\"$sort\": {\"nBornCountries\": -1}},\n",
    "]\n",
    "for doc in db.prizes.aggregate(pipeline): print(doc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## How many prizes were awarded to immigrants?\n",
    "\n",
    "pipeline = [\n",
    "    # Limit results to people; project needed fields; unwind prizes\n",
    "    {\"$match\": {\"gender\": {\"$ne\": \"org\"}}},\n",
    "    {\"$project\": {\"bornCountry\": 1, \"prizes.affiliations.country\": 1}},\n",
    "    {\"$unwind\": \"$prizes\"},\n",
    "  \n",
    "    # Count prizes with no country-of-birth affiliation\n",
    "    {\"$addFields\": {\"bornCountryInAffiliations\": {\"$in\": [\"$bornCountry\", \"$prizes.affiliations.country\"]}}},\n",
    "    {\"$match\": {\"bornCountryInAffiliations\": False}},\n",
    "    {\"$count\": \"awardedElsewhere\"},\n",
    "]\n",
    "\n",
    "print(list(db.laureates.aggregate(pipeline)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Refinement: filter out \"unaffiliated\" people\n",
    "\n",
    "pipeline = [\n",
    "    {\"$match\": {\"gender\": {\"$ne\": \"org\"}}},\n",
    "    {\"$project\": {\"bornCountry\": 1, \"prizes.affiliations.country\": 1}},\n",
    "    {\"$unwind\": \"$prizes\"},\n",
    "    {\"$addFields\": {\"bornCountryInAffiliations\": {\"$in\": [\"$bornCountry\", \"$prizes.affiliations.country\"]}}},\n",
    "    {\"$match\": {\"bornCountryInAffiliations\": False}},\n",
    "    {\"$count\": \"awardedElsewhere\"},\n",
    "]\n",
    "\n",
    "# Construct the additional filter stage\n",
    "added_stage = {\"$match\": {\"prizes.affiliations.country\" : {'$in': db.laureates.distinct(\"prizes.affiliations.country\")}}}\n",
    "\n",
    "# Insert this stage into the pipeline\n",
    "pipeline.insert(3, added_stage)\n",
    "print(list(db.laureates.aggregate(pipeline)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tf-gpu-2.0",
   "language": "python",
   "name": "tf_gpu2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}