<jupyter_start><jupyter_text>Sequencing stagesHere is a cursor, followed by four aggregation pipeline stages:```cursor = (db.laureates.find(    projection={"firstname": 1, "prizes.year": 1, "_id": 0},    filter={"gender": "org"}) .limit(3).sort("prizes.year", -1))project_stage = {"$project": {"firstname": 1, "prizes.year": 1, "_id": 0}}match_stage = {"$match": {"gender": "org"}}limit_stage = {"$limit": 3}sort_stage = {"$sort": {"prizes.year": -1}}```What sequence pipeline of the above four stages can produce a cursor db.laureates.aggregate(pipeline) equivalent to cursor above?<jupyter_code>from pymongo import MongoClient

client = MongoClient('mongodb://localhost:27017')

# Connect to the "nobel" database
db = client.nobel
cursor = (db.laureates.find(
    projection={"firstname": 1, "prizes.year": 1, "_id": 0},
    filter={"gender": "org"})
 .limit(3).sort("prizes.year", -1))

cursor
project_stage = {"$project": {"firstname": 1, "prizes.year": 1, "_id": 0}}
match_stage = {"$match": {"gender": "org"}}
limit_stage = {"$limit": 3}
sort_stage = {"$sort": {"prizes.year": -1}}<jupyter_output><empty_output><jupyter_text>Aggregating a few individuals' country dataThe following query cursor yields birth-country and prize-affiliation-country information for three non-organization laureates:```cursor = (db.laureates.find(    {"gender": {"$ne": "org"}},    ["bornCountry", "prizes.affiliations.country"]).limit(3))```<jupyter_code># Translate cursor to aggregation pipeline
pipeline = [
    {'$match': {'gender': {'$ne': 'org'}}},
    {'$project': {'bornCountry': 1, 'prizes.affiliations.country': 1}},
    {'$limit': 3}
]

for doc in db.laureates.aggregate(pipeline):
    print("{bornCountry}: {prizes}".format(**doc))<jupyter_output>the Netherlands: [{'affiliations': [{'country': 'the Netherlands'}]}]
USA: [{'affiliations': [{'country': 'USA'}]}]
USA: [{'affiliations': [{'country': 'USA'}]}]<jupyter_text>Passing the aggregation baton to PythonConstruct an aggregation pipeline to collect, in reverse chronological order (i.e., descending year), prize documents for all original categories (that is, $in categories awarded in 1901). Project only the prize year and category (including document _id is fine).The aggregation cursor will be fed to Python's itertools.groupby function to group prizes by year. For each year that at least one of the original prize categories was missing, a line with all missing categories for that year will be printed.<jupyter_code>from collections import OrderedDict
from itertools import groupby
from operator import itemgetter

original_categories = set(db.prizes.distinct("category", {"year": "1901"}))

# Save an pipeline to collect original-category prizes
pipeline = [
    {"$match": {"category": {"$in": list(original_categories)}}},
    {"$project": {"category": 1, "year": 1}},
    {"$sort": OrderedDict([("year", -1)])}
]
cursor = db.prizes.aggregate(pipeline)
for key, group in groupby(cursor, key=itemgetter("year")):
    missing = original_categories - {doc["category"] for doc in group}
    if missing:
        print("{year}: {missing}".format(
            year=key, missing=", ".join(sorted(missing))))<jupyter_output>2018: literature
1972: peace
1967: peace
1966: peace
1956: peace
1955: peace
1948: peace
1943: literature, peace
1939: peace
1935: literature
1934: physics
1933: chemistry
1932: peace
1931: physics
1928: peace
1925: medicine
1924: chemistry, peace
1923: peace
1921: medicine
1919: chemistry
1918: literature, medicine, peace
1917: chemistry, medicine
1916: chemistry, medicine, peace, physics
1915: medicine, peace
1914: literature, peace<jupyter_text>Field Paths and SetsPreviously, we confirmed -- via a Python loop -- that for each prize, either all laureates have a 1/3 share, or none do. Now, let's do this via an aggregation (result should be an empty list):```list(db.prizes.aggregate([    {"$project": {"allThree": {"$setEquals": [____, ____]},                  "noneThree": {"$not": {"$setIsSubset": [____, ____]}}}},    {"$match": {"$nor": [{"allThree": True}, {"noneThree": True}]}}]))Which values fill the blanks?```<jupyter_code>list(db.prizes.aggregate([
    {"$project": {"allThree": {"$setEquals": ["$laureates.share", ["3"]]},
                  "noneThree": {"$not": {"$setIsSubset": [["3"], "$laureates.share"]}}}},
    {"$match": {"$nor": [{"allThree": True}, {"noneThree": True}]}}]))<jupyter_output><empty_output><jupyter_text>Organizing prizesIn the slides at the beginning of this lesson, we saw a two-stage aggregation pipeline to determine the number of prizes awarded in total. How many prizes were awarded (at least partly) to organizations?<jupyter_code># Count prizes awarded (at least partly) to organizations as a sum over sizes of "prizes" arrays.
pipeline = [
    {'$match': {'gender': "org"}},
    {"$project": {"n_prizes": {"$size": '$prizes'}}},
    {"$group": {"_id": None, "n_prizes_total": {"$sum": '$n_prizes'}}}
]

print(list(db.laureates.aggregate(pipeline)))<jupyter_output>[{'_id': None, 'n_prizes_total': 27}]<jupyter_text>Gap years, aggregatedIn a previous exercise, you collected instances of prize categories not being awarded in particular years. You implemented this using a for loop in Python. You will now implement this as an aggregation pipeline that:1. Filters for original prize categories (i.e. sans economics),2. Projects category and year,3. Groups distinct prize categories awarded by year,4. Projects prize categories not awarded by year,5. Filters for years with missing prize categories, and6. Returns a cursor of documents in reverse chronological order, one per year, each with a list of missing prize categories for that year.Remember to use field paths (precede field names with "$") to extract field values in expressions.<jupyter_code>from collections import OrderedDict

original_categories = sorted(set(db.prizes.distinct("category", {"year": "1901"})))
pipeline = [
    {"$match": {"category": {"$in": original_categories}}},
    {"$project": {"category": 1, "year": 1}},

    # Collect the set of category values for each prize year.
    {"$group": {"_id": '$year', "categories": {"$addToSet": "$category"}}},

    # Project categories *not* awarded (i.e., that are missing this year).
    {"$project": {"missing": {"$setDifference": [
        original_categories, '$categories']}}},

    # Only include years with at least one missing category
    {"$match": {"missing.0": {"$exists": True}}},

    # Sort in reverse chronological order. Note that "_id" is a distinct year at this stage.
    {"$sort": OrderedDict([("_id", -1)])},
]
for doc in db.prizes.aggregate(pipeline):
    print("{year}: {missing}".format(
        year=doc["_id"], missing=", ".join(sorted(doc["missing"]))))<jupyter_output>2018: literature
1972: peace
1967: peace
1966: peace
1956: peace
1955: peace
1948: peace
1943: literature, peace
1939: peace
1935: literature
1934: physics
1933: chemistry
1932: peace
1931: physics
1928: peace
1925: medicine
1924: chemistry, peace
1923: peace
1921: medicine
1919: chemistry
1918: literature, medicine, peace
1917: chemistry, medicine
1916: chemistry, medicine, peace, physics
1915: medicine, peace
1914: literature, peace<jupyter_text>Embedding aggregation expressionsThe $expr operator allows embedding of aggregation expressions in a normal query (or in a $match stage). Which of the following expressions counts the number of laureate documents with string-valued bornCountries when passed to db.laureates.count_documents?You can assume (and check!) that the following is true:```assert all(isinstance(v, str) for v in db.laureates.distinct("bornCountry"))```  Here and elsewhereWhat proportion of laureates won a prize while affiliated with an institution in their country of birth? Build an aggregation pipeline to get the count of laureates who either did or did not win a prize with an affiliation country that is a substring of their country of birth -- for example, the prize affiliation country "Germany" should match the country of birth "Prussia (now Germany)".<jupyter_code>key_ac = "prizes.affiliations.country"
key_bc = "bornCountry"
pipeline = [
    {"$project": {key_bc: 1, key_ac: 1}},

    # Ensure a single prize affiliation country per pipeline document
    {'$unwind': "$prizes"},
    {'$unwind': "$prizes.affiliations"},

    # Ensure values in the list of distinct values (so not empty)
    {"$match": {key_ac: {'$in': db.laureates.distinct(key_ac)}}},
    {"$project": {"affilCountrySameAsBorn": {
        "$gte": [{"$indexOfBytes": ["$"+key_ac, "$"+key_bc]}, 0]}}},

    # Count by "$affilCountrySameAsBorn" value (True or False)
    {"$group": {"_id": "$affilCountrySameAsBorn",
                "count": {"$sum": 1}}},
]

for doc in db.laureates.aggregate(pipeline): print(doc)<jupyter_output>{'_id': False, 'count': 261}
{'_id': True, 'count': 477}<jupyter_text>Countries of birth by prize categorySome prize categories have laureates hailing from a greater number of countries than do other categories. You will build an aggregation pipeline for the prizes collection to collect these numbers, using a $lookup stage to obtain laureate countries of birth.<jupyter_code>pipeline = [
    # Unwind the laureates array
    {'$unwind': "$laureates"},
    {"$lookup": {
        "from": "laureates", "foreignField": "id",
        "localField": "laureates.id", "as": "laureate_bios"}},

    # Unwind the new laureate_bios array
    {"$unwind": "$laureate_bios"},
    {"$project": {"category": 1,
                  "bornCountry": "$laureate_bios.bornCountry"}},

    # Collect bornCountry values associated with each prize category
    {"$group": {"_id": "$category",
                "bornCountries": {"$addToSet": "$bornCountry"}}},

    # Project out the size of each category's (set of) bornCountries
    {"$project": {"category": 1,
                  "nBornCountries": {"$size": "$bornCountries"}}},
    {"$sort": {"nBornCountries": -1}},
]

for doc in db.prizes.aggregate(pipeline): print(doc)<jupyter_output>{'_id': 'literature', 'nBornCountries': 55}
{'_id': 'peace', 'nBornCountries': 50}
{'_id': 'chemistry', 'nBornCountries': 48}
{'_id': 'medicine', 'nBornCountries': 44}
{'_id': 'physics', 'nBornCountries': 44}
{'_id': 'economics', 'nBornCountries': 21}<jupyter_text>"...it's the life in your years"For the pipeline we developed in the last slide deck, I want you to replace the last ($bucket) stage with one such that, given the documents docs collected, we can get the following output:```from operator import itemgetterprint(max(docs, key=itemgetter("years")))print(min(docs, key=itemgetter("years"))){'firstname': 'Rita', 'surname': 'Levi-Montalcini', 'years': 103.0}{'firstname': '<NAME>', 'surname': '<NAME>.', 'years': 39.0}```You may assume that any earlier $project stage has been replaced by an equivalent $addFields stage.<jupyter_code>{"$project": {"years": 1, "firstname": 1, "surname": 1, "_id": 0}}<jupyter_output><empty_output><jupyter_text>How many prizes were awarded to immigrants?How many prizes were awarded to people who had no affiliation in their country of birth at the time of the award?<jupyter_code>pipeline = [
    # Limit results to people; project needed fields; unwind prizes
    {"$match": {
        "gender": {"$ne": "org"}}},
    {"$project": {
        "bornCountry": 1, "prizes.affiliations.country": 1}},
    {"$unwind": "$prizes"},

    # Count prizes with no country-of-birth affiliation
    {"$addFields": {"bornCountryInAffiliations": {
        "$in": [
            "$bornCountry", 
            "$prizes.affiliations.country"
            ]}}},
    {"$match": {
        "bornCountryInAffiliations": False}},
    {"$count": "awardedElsewhere"},
]

print(list(db.laureates.aggregate(pipeline)))<jupyter_output>[{'awardedElsewhere': 478}]<jupyter_text>Refinement: filter out "unaffiliated" peopleIn the previous exercise, we counted prizes awarded to people without an affiliation in their "bornCountry". However, hundreds of prizes were awarded to people without recorded affiliations; sure, their "bornCountry" is technically not the "country" of any of their affiliations, but there are no "country" values to compare against!<jupyter_code>pipeline = [
    {"$match": {
        "gender": {
            "$ne": "org"
        }
    }},
    {"$project": {
        "bornCountry": 1, 
        "prizes.affiliations.country": 1
        }
    },
    {
        "$unwind": "$prizes"
    },
    {"$addFields": {"bornCountryInAffiliations": {
        "$in": ["$bornCountry", "$prizes.affiliations.country"]}}},
    {"$match": {"bornCountryInAffiliations": False}},
    {"$count": "awardedElsewhere"},
]

# Construct the additional filter stage
added_stage = {"$match": {"prizes.affiliations.country": {
    '$in': db.laureates.distinct("prizes.affiliations.country")}}}

# Insert this stage into the pipeline
pipeline.insert(3, added_stage)
print(list(db.laureates.aggregate(pipeline)))<jupyter_output>[{'awardedElsewhere': 252}]
{'fraction_non_alphanumeric': 0.15445773832497106, 'fraction_numerical': 0.023697414125820146, 'mean_word_length': 5.04573028464769, 'pattern_counts': {'":': 143, '<': 39, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '138536', 'license': 'MIT', 'n_tokens_mistral': 4231, 'n_tokens_neox': 3875, 'n_words': 1297, 'path': 'introduction-to-mongodb-in-python/4. Get Only What You Need, and Fast/notebook_section_4.ipynb', 'repo_name': 'nhutnamhcmus/datacamp-playground', 'size': '12963'}