03 - Playing with PyPI.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c70e0e54-1d74-4247-a748-8383b8a25825",
   "metadata": {},
   "source": [
    "# Playing with PyPI (data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30d6cf48-6dba-4fa8-bb9f-f6ec08194536",
   "metadata": {},
   "source": [
    "Many thanks to Seth M. Larson for making these available: https://github.com/sethmlarson/pypi-data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f33f080-893a-4505-aa45-38eab303d3d4",
   "metadata": {},
   "source": [
    "## Download data\n",
    "\n",
    "Note that `DuckDB` supports reading directly from remote parquet files (and performs column-pruning and other optimizations to reduce network load) but we'll be executing several queries against these data and so we're better off downloading the files to begin with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d68d87a1-51da-4f81-a722-9b75ab656eb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "\n",
    "filenames = [\n",
    "    \"deps.parquet\",\n",
    "    \"maintainers.parquet\",\n",
    "    \"package_urls.parquet\",\n",
    "    \"packages.parquet\",\n",
    "    \"scorecard_checks.parquet\",\n",
    "    \"wheels.parquet\",\n",
    "]\n",
    "\n",
    "folder = Path(\"pypi\")\n",
    "folder.mkdir(exist_ok=True)\n",
    "\n",
    "for filename in filenames:\n",
    "    path = folder / filename\n",
    "    if not path.exists():\n",
    "        import urllib.request\n",
    "\n",
    "        urllib.request.urlretrieve(\n",
    "            f\"https://storage.googleapis.com/ibis-tutorial-data/pypi/{filename}\",\n",
    "            path,\n",
    "        )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a768a549",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "\n",
    "ibis.options.interactive = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72eac630-fd7a-48ac-94ab-141b58f27adf",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = ibis.duckdb.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b27aed9-d181-4b09-b29d-47f710093b75",
   "metadata": {},
   "outputs": [],
   "source": [
    "for filename in filenames:\n",
    "    path = folder / filename\n",
    "    con.read_parquet(path, table_name=filename.split(\".\")[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19e1a36e",
   "metadata": {},
   "outputs": [],
   "source": [
    "con.list_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b063ad0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "deps = con.tables.deps\n",
    "\n",
    "deps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3fa22cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "maintainers = con.tables.maintainers\n",
    "\n",
    "maintainers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d1c2451",
   "metadata": {},
   "outputs": [],
   "source": [
    "packages = con.tables.packages\n",
    "\n",
    "packages"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22901bc3-172d-4c1f-82b4-eb7fcc47e2dd",
   "metadata": {},
   "source": [
    "## Using `.sql` as an escape hatch\n",
    "\n",
    "Ibis is pretty good about abstracting away the underlying execution engine, but sometimes you _want_ to talk directly to the engine.\n",
    "\n",
    "Maybe you:\n",
    "- have an existing SQL query from a data engineer that you want to build on\n",
    "- need to access a backend-specific function that isn't exposed in Ibis\n",
    "- really want to write some SQL\n",
    "\n",
    "If you do, then you can call `.sql` on a connection (or an existing expression!) and write out your SQL query.\n",
    "\n",
    "(You can even pass a `dialect` flag if you are more comfortable writing SQL in a dialect different than the one your engine expects!)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70bf4cdc-7735-4beb-982f-fa093187f95f",
   "metadata": {},
   "source": [
    "### Looking for typo squatters"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd14d7df-17ac-4f29-b6c1-cb5a96f1fb7c",
   "metadata": {},
   "source": [
    "DuckDB has a `jaccard` function for doing Jaccard similarity checks on strings.  But there's no `jaccard` method in `ibis` -- what to do?  \n",
    "\n",
    "We'll write a short SQL query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b53ed9e-617c-428b-9e4e-4862d4d1e370",
   "metadata": {},
   "outputs": [],
   "source": [
    "# packages.name.jaccard(\"pandas\")  # <--this is not a thing (yet!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97f39f71-1d38-4f55-b301-c081427b03b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "expr = packages.sql(\n",
    "    \"SELECT name, version, jaccard(packages.name, 'pandas') as sim from packages\",\n",
    "    dialect=\"duckdb\",\n",
    ")  # the dialect keyword is redundant here\n",
    "\n",
    "expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "029d4a97-c3e4-4bf8-89bb-7c51796e3fee",
   "metadata": {},
   "source": [
    "We should probably sort that `sim` column to see which packages have higher similarity.\n",
    "\n",
    "Even though we called out to `sql` for the Jaccard function call, we don't need to stay in SQL-land moving forward, just chain as usual!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac1a57b4-17c4-414f-b467-26665fa2c636",
   "metadata": {},
   "outputs": [],
   "source": [
    "expr.order_by(_.sim.desc())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baadb23f-ad1e-45ea-a6d8-cac67727f393",
   "metadata": {},
   "source": [
    "### Exercise 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35cb267a-35b8-443e-89ef-aec0801740e4",
   "metadata": {},
   "source": [
    "Try swapping out `jaccard` for a different similarity function available in DuckDB (https://duckdb.org/docs/sql/functions/char#text-similarity-functions) -- or you can try looking for similar PyPI usernames using the `maintainers` table!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ce713d2-db8a-4c25-8f5d-2c012fe5b10b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6f17cf18-6e76-4bc5-ab8e-802722042b25",
   "metadata": {},
   "source": [
    "### Solution\n",
    "\n",
    "One possible solution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7eb1e37-7a0b-451e-bdc6-db6f3f7a34d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex01-jaro.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2b130a1-47d5-4576-beb3-1ea84e5cd98d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex01-maint.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2313e236-0f1d-40e2-ae50-3780d3aa4973",
   "metadata": {},
   "source": [
    "## Integration with other tools"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0c856ae-af06-444b-8e2b-fd208e7fd122",
   "metadata": {},
   "source": [
    "Plotting data is quite helpful!  Recent support for the `__dataframe__` protocol has made it easier than ever to use Ibis with various plotting libraries."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8683c77d-8952-4849-aac8-6ef61eb9f175",
   "metadata": {},
   "source": [
    "### Histogram of maintainer count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8f38839-6bc5-4983-92ce-dcfc6192d1af",
   "metadata": {},
   "source": [
    "We'll start by computing the number of packages that have `n` maintainers, as defined by having write permissions on PyPI."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a491e55a",
   "metadata": {},
   "outputs": [],
   "source": [
    "maintainer_counts = (\n",
    "    maintainers.group_by(\"package_name\")\n",
    "    .agg(maintainers=_.count())\n",
    "    .group_by(\"maintainers\")\n",
    "    .agg(count=_.count())\n",
    "    .order_by(_.maintainers)\n",
    ")\n",
    "\n",
    "maintainer_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc416f11-e7d3-41c7-9c0a-ddc64b4e012d",
   "metadata": {},
   "source": [
    "We'll plot a histogram of the maintainer counts using `altair`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b8cfd93-1eff-4416-93b7-6eefb9ae8aa4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import altair as alt\n",
    "\n",
    "alt.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1469acd-40d7-41fd-937f-df0bd94feaa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "ibis.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9d5df9f-d2a4-4394-8254-0484145b78d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "chart = (\n",
    "    alt.Chart(maintainer_counts)\n",
    "    .mark_bar()\n",
    "    .encode(x=\"maintainers\", y=alt.Y(\"count\", scale=alt.Scale(type=\"log\")))\n",
    ")\n",
    "\n",
    "chart"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39a693ee-b678-4e01-8091-d6cbc411ab5c",
   "metadata": {},
   "source": [
    "We can also use `plotnine`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "996e2da6-5408-44ac-b799-01cb05d97a7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from plotnine import aes, geom_histogram, ggplot, scale_y_log10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b880c7e-830f-449b-8dd5-827d0072d6a4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "(\n",
    "    ggplot(maintainer_counts, aes(x=\"maintainers\", y=\"count\"))\n",
    "    + geom_bar(stat=\"identity\")\n",
    "    + scale_y_log10()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fc0a9c6-2ce3-4c8a-8844-6f11d7434f54",
   "metadata": {},
   "source": [
    "Finally, since we can always call `to_pandas` or `to_pyarrow` -- it's easy to connect an Ibis result to your favorite PyData tools."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efd97851-2342-46c7-890e-8d99e525daf6",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "\n",
    "What's the deal with that spike at 12 maintainers?\n",
    "\n",
    "**Note**: if you would like to return more rows in interactive mode, you can set the row-count e.g. `ibis.options.repr.interactive.max_rows = 20`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3019139-9a04-4200-b936-6acfdf792290",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "19434df7-2ccb-4429-9c07-0d8eb0bb6a6b",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d98f270-bb05-458a-9602-ab86a081f1c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex02-counts.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f8b295b-0950-46c1-9809-8f2bc17a9ca9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex02-ftw.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e822c044-4d1b-48c9-a22e-3d0264eb7588",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex02-ftw-maintainers.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad487fbc-e79d-4b32-acd6-9dab9dbf43fd",
   "metadata": {},
   "source": [
    "## UDFs\n",
    "\n",
    "Modern SQL engines have a _lot_ of features, but there's always going to be _something_ you want to do that isn't built-in.  Maybe you have a text classifier you want to run over a dataset?\n",
    "\n",
    "We'll tackle something a little less ML-related, and figure out if we need to update our PyPI data pull."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e48eaafc-1062-4f67-b66d-65f97f0cf64d",
   "metadata": {},
   "outputs": [],
   "source": [
    "pp = (\n",
    "    packages.order_by(_.downloads.desc())\n",
    "    .select(\"name\", \"version\", \"downloads\")\n",
    "    .limit(10)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "511d9233-4de5-4759-b2ee-8edb1434e88e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6cad273-3412-4c48-b8c8-9270264a6d78",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ibis.expr.operations import udf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a56ac5d-dee8-4266-9ea9-f566544d107e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "import requests\n",
    "\n",
    "\n",
    "@udf.scalar.python\n",
    "def check_version(name: str, version: str) -> bool:\n",
    "    r = requests.get(f\"https://pypi.org/pypi/{name}/json\")\n",
    "    return (\n",
    "        version == sorted(list(json.loads(r.content.decode())[\"releases\"].keys()))[-1]\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69879f34-7df4-4f59-97e7-8b4f6d24de60",
   "metadata": {},
   "outputs": [],
   "source": [
    "pp.mutate(is_latest=check_version(pp.name, pp.version))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6f8f2b0-1014-42f3-858c-d1141f8c030e",
   "metadata": {},
   "source": [
    "### Exercise 3\n",
    "\n",
    "Above we used a UDF to look up whether the version of a given package in our dataset was the latest release on PyPI. \n",
    "\n",
    "Let's add another UDF to check whether any of the packages in our top-10 downloads have known vulnerabilities.\n",
    "\n",
    "The API endpoint for checking vulnerabilities is `\"https://pypi.org/pypi/{name}/{version}/json\"` -- if you load in the JSON from that `GET`, there is a `vulnerabilities` key that will be a list of known vulnerabilities.\n",
    "\n",
    "Write a UDF that grabs this information, and if it is present, pull out the `aliases` and `fixed_in` subfields.\n",
    "\n",
    "Here is a sample API response for a known vulnerability:\n",
    "\n",
    "```\n",
    "GET /pypi/Django/3.0.2/json HTTP/1.1\n",
    "Host: pypi.org\n",
    "Accept: application/json\n",
    "\n",
    "{\n",
    "    \"info\": {},\n",
    "    \"last_serial\": 12089094,\n",
    "    \"releases\": {},\n",
    "    \"urls\": [],\n",
    "    \"vulnerabilities\": [\n",
    "        {\n",
    "            \"aliases\": [\n",
    "                \"CVE-2021-3281\"\n",
    "            ],\n",
    "            \"details\": \"In Django 2.2 before 2.2.18, 3.0 before 3.0.12, and 3.1 before 3.1.6, the django.utils.archive.extract method (used by \\\"startapp --template\\\" and \\\"startproject --template\\\") allows directory traversal via an archive with absolute paths or relative paths with dot segments.\",\n",
    "            \"summary\": \"A shorter summary of the vulnerability\",\n",
    "            \"fixed_in\": [\n",
    "                \"2.2.18\",\n",
    "                \"3.0.12\",\n",
    "                \"3.1.6\"\n",
    "            ],\n",
    "            \"id\": \"PYSEC-2021-9\",\n",
    "            \"link\": \"https://osv.dev/vulnerability/PYSEC-2021-9\",\n",
    "            \"source\": \"osv\",\n",
    "            \"withdrawn\": null\n",
    "        },\n",
    "    ]\n",
    "}\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "724f42d6-3dd2-4fbc-9741-1cf366428258",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "bb89afb8-1985-44df-84a6-23fddf5c65dc",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6176fa2-0fd7-4344-939d-85ebe4b44032",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex03.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed919462-6ccf-4e66-879d-ad7592d90669",
   "metadata": {},
   "source": [
    "## More exercises / questions:\n",
    "\n",
    "If you've finished all the exercises and still have some time or want to explore using Ibis further, here are some more questions to try to answer using the PyPI data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18f28863",
   "metadata": {},
   "source": [
    "### Exercise 4: Which maintainers have the most downloads?\n",
    "\n",
    "Which package maintainers on PyPI have the most downloads across all the packages they maintain?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b170ac60-9026-4f76-9eac-a5704f9ad48b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2640de91-973a-421f-b9b3-e3139c7be2d0",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11134b38",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex04.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7659d798",
   "metadata": {},
   "source": [
    "### Exercise 5: What packages depend on `ibis-framework`?\n",
    "\n",
    "Or fill in your package name of choice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "659860fe-0583-41b3-8112-0d9ed780700e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "99a7002a-ccd6-4ac1-b6fe-07d7441f9704",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ad82673",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex05.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e82f5d53",
   "metadata": {},
   "source": [
    "### Exercise 6: What packages depend on things I maintain?\n",
    "\n",
    "Do you publish any PyPI packages?  Try looking up which packages depend on those packages.\n",
    "Or you can try looking up some of the Ibis maintainers dependency graphs (`gforsyth`, `jcrist`, `Charles.Cloud`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9499173-7797-484a-8202-7207d0d5debe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e4c4e04b-e65c-43b7-8c06-3e2273e35227",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce1c5c4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex06.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c299bf0d",
   "metadata": {},
   "source": [
    "### Exercise 7: What are the top pytest extensions?\n",
    "\n",
    "We love `pytest` and so do a bunch of other people.  Their plugins/extensions also follow a naming convention that they start with `pytest-`, so try to find out which `pytest` extensions are the most depended on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "389cc99c-9d5b-45f2-ae56-12aed8863ecc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ddc29932-2fcd-4634-905c-3ca8a2db9f94",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c518d66e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex07.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c06e557",
   "metadata": {},
   "source": [
    "### Exercise 8: Find the top 20 most depended on packages that have only one maintainer\n",
    "\n",
    "Sometimes people refer to bus factors, other times we think of someone winning the lottery and moving to a remote cabin in the woods (this is my plan). Which packages would be left without an active maintainer if one person is no longer available to publish?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3dbbe168-7a2e-4c63-8c60-093c303873cb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ef831ff5-a674-494d-a4a8-ec50d78b3f4c",
   "metadata": {},
   "source": [
    "### Solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46fc05d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/nb03-ex08.py"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}