{ "cells": [ { "cell_type": "markdown", "id": "aacf6221-15ab-4ca1-b905-d0940640c955", "metadata": {}, "source": [ "# Rollup support" ] }, { "cell_type": "code", "execution_count": 1, "id": "828baba8-de63-4b3b-bb7b-38af896541ff", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from pandas import DataFrame\n", "from data201 import db_connection, df_query\n", "\n", "pd.set_option('display.max_rows', None)" ] }, { "cell_type": "code", "execution_count": 2, "id": "301bb68a-fc48-4225-864a-202bb4b0f4e3", "metadata": {}, "outputs": [], "source": [ "conn = db_connection(config_file='students.ini')\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "4541be68-935e-4e0a-bcab-f8fac574f3d8", "metadata": {}, "source": [ "## Goal\n", "#### We want to generate a table like the following. The number of stars after each label indicates its subtotal rank, from lowest to highest:\n", "```\n", "DEPARTMENT *\n", " SCHOOL **\n", " SEMESTER ***\n", " YEARLY ****\n", " GRAND *****\n", "```\n", "#### Examples:\n", "- #### Line 12 `SCHOOL **` is the sum of line 9 `DEPARTMENT *` and line 11 `DEPARTMENT *`: 161 = 76 + 85\n", "- #### Line 13 `SEMESTER ***` is the sum of Line 3 `SCHOOL **` and line 6 `SCHOOL **` and line 12 `SCHOOL **`: 352 = 88 + 103 + 161\n", "```\n", " school_year semester school department level TOTAL LABEL\n", " ---------------------------------------------------------------------------------------------\n", "0 2022-2023 fall Engineering Electrical Engineering graduate 41.0 \n", "1 2022-2023 fall Engineering Electrical Engineering undergraduate 47.0 \n", "2 88.0 DEPARTMENT *\n", "3 88.0 SCHOOL **\n", "4 2022-2023 fall Information Data Science graduate 103.0 \n", "5 103.0 DEPARTMENT *\n", "6 103.0 SCHOOL **\n", "7 2022-2023 fall Science Computer Science graduate 27.0 \n", "8 2022-2023 fall Science Computer Science undergraduate 49.0 \n", "9 76.0 DEPARTMENT *\n", "10 2022-2023 fall Science Physics and Astronomy undergraduate 85.0 \n", "11 85.0 DEPARTMENT *\n", "12 161.0 SCHOOL **\n", "13 352.0 SEMESTER ***\n", "14 2022-2023 spring Engineering Electrical Engineering graduate 38.0 \n", "15 2022-2023 spring Engineering Electrical Engineering undergraduate 51.0 \n", "16 89.0 DEPARTMENT *\n", "17 89.0 SCHOOL **\n", "18 2022-2023 spring Information Data Science graduate 99.0 \n", "19 99.0 DEPARTMENT *\n", "20 99.0 SCHOOL **\n", "21 2022-2023 spring Science Computer Science graduate 14.0 \n", "22 2022-2023 spring Science Computer Science undergraduate 60.0 \n", "23 74.0 DEPARTMENT *\n", "24 2022-2023 spring Science Physics and Astronomy undergraduate 82.0 \n", "25 82.0 DEPARTMENT *\n", "26 156.0 SCHOOL **\n", "27 344.0 SEMESTER ***\n", "28 696.0 YEARLY ****\n", "29 2023-2024 fall Engineering Electrical Engineering graduate 44.0 \n", "30 2023-2024 fall Engineering Electrical Engineering undergraduate 35.0 \n", "31 79.0 DEPARTMENT *\n", "32 79.0 SCHOOL **\n", "33 2023-2024 fall Information Data Science graduate 63.0 \n", "34 63.0 DEPARTMENT *\n", "35 63.0 SCHOOL **\n", "36 2023-2024 fall Science Computer Science graduate 44.0 \n", "37 2023-2024 fall Science Computer Science undergraduate 41.0 \n", "38 85.0 DEPARTMENT *\n", "39 2023-2024 fall Science Physics and Astronomy undergraduate 44.0 \n", "40 44.0 DEPARTMENT *\n", "41 129.0 SCHOOL **\n", "42 271.0 SEMESTER ***\n", "43 271.0 YEARLY ****\n", "44 967.0 GRAND *****\n", "```" ] }, { "cell_type": "markdown", "id": "cca89b38-212b-495b-97bb-b91aaafc9f42", "metadata": { "tags": [] }, "source": [ "## Window functions\n", "#### We can use window functions to display all the subtotals and the grand total." ] }, { "cell_type": "code", "execution_count": 3, "id": "6ff7e815-c2c5-4ce4-b1d8-2683932ca98c", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
school_yearsemesterschooldepartmentlevelgrand_totalyearly_totalsemester_totalschool_totaldept_totallevel_total
02022-2023fallEngineeringElectrical Engineeringgraduate967.0696.0352.088.088.041.0
12022-2023fallEngineeringElectrical Engineeringundergraduate967.0696.0352.088.088.047.0
22022-2023fallInformationData Sciencegraduate967.0696.0352.0103.0103.0103.0
32022-2023fallScienceComputer Sciencegraduate967.0696.0352.0161.076.027.0
42022-2023fallScienceComputer Scienceundergraduate967.0696.0352.0161.076.049.0
52022-2023fallSciencePhysics and Astronomyundergraduate967.0696.0352.0161.085.085.0
62022-2023springEngineeringElectrical Engineeringgraduate967.0696.0344.089.089.038.0
72022-2023springEngineeringElectrical Engineeringundergraduate967.0696.0344.089.089.051.0
82022-2023springInformationData Sciencegraduate967.0696.0344.099.099.099.0
92022-2023springScienceComputer Sciencegraduate967.0696.0344.0156.074.014.0
102022-2023springScienceComputer Scienceundergraduate967.0696.0344.0156.074.060.0
112022-2023springSciencePhysics and Astronomyundergraduate967.0696.0344.0156.082.082.0
122023-2024fallEngineeringElectrical Engineeringgraduate967.0271.0271.079.079.044.0
132023-2024fallEngineeringElectrical Engineeringundergraduate967.0271.0271.079.079.035.0
142023-2024fallInformationData Sciencegraduate967.0271.0271.063.063.063.0
152023-2024fallScienceComputer Sciencegraduate967.0271.0271.0129.085.044.0
162023-2024fallScienceComputer Scienceundergraduate967.0271.0271.0129.085.041.0
172023-2024fallSciencePhysics and Astronomyundergraduate967.0271.0271.0129.044.044.0
\n", "
" ], "text/plain": [ " school_year semester school department level \\\n", "0 2022-2023 fall Engineering Electrical Engineering graduate \n", "1 2022-2023 fall Engineering Electrical Engineering undergraduate \n", "2 2022-2023 fall Information Data Science graduate \n", "3 2022-2023 fall Science Computer Science graduate \n", "4 2022-2023 fall Science Computer Science undergraduate \n", "5 2022-2023 fall Science Physics and Astronomy undergraduate \n", "6 2022-2023 spring Engineering Electrical Engineering graduate \n", "7 2022-2023 spring Engineering Electrical Engineering undergraduate \n", "8 2022-2023 spring Information Data Science graduate \n", "9 2022-2023 spring Science Computer Science graduate \n", "10 2022-2023 spring Science Computer Science undergraduate \n", "11 2022-2023 spring Science Physics and Astronomy undergraduate \n", "12 2023-2024 fall Engineering Electrical Engineering graduate \n", "13 2023-2024 fall Engineering Electrical Engineering undergraduate \n", "14 2023-2024 fall Information Data Science graduate \n", "15 2023-2024 fall Science Computer Science graduate \n", "16 2023-2024 fall Science Computer Science undergraduate \n", "17 2023-2024 fall Science Physics and Astronomy undergraduate \n", "\n", " grand_total yearly_total semester_total school_total dept_total \\\n", "0 967.0 696.0 352.0 88.0 88.0 \n", "1 967.0 696.0 352.0 88.0 88.0 \n", "2 967.0 696.0 352.0 103.0 103.0 \n", "3 967.0 696.0 352.0 161.0 76.0 \n", "4 967.0 696.0 352.0 161.0 76.0 \n", "5 967.0 696.0 352.0 161.0 85.0 \n", "6 967.0 696.0 344.0 89.0 89.0 \n", "7 967.0 696.0 344.0 89.0 89.0 \n", "8 967.0 696.0 344.0 99.0 99.0 \n", "9 967.0 696.0 344.0 156.0 74.0 \n", "10 967.0 696.0 344.0 156.0 74.0 \n", "11 967.0 696.0 344.0 156.0 82.0 \n", "12 967.0 271.0 271.0 79.0 79.0 \n", "13 967.0 271.0 271.0 79.0 79.0 \n", "14 967.0 271.0 271.0 63.0 63.0 \n", "15 967.0 271.0 271.0 129.0 85.0 \n", "16 967.0 271.0 271.0 129.0 85.0 \n", "17 967.0 271.0 271.0 129.0 44.0 \n", "\n", " level_total \n", "0 41.0 \n", "1 47.0 \n", "2 103.0 \n", "3 27.0 \n", "4 49.0 \n", "5 85.0 \n", "6 38.0 \n", "7 51.0 \n", "8 99.0 \n", "9 14.0 \n", "10 60.0 \n", "11 82.0 \n", "12 44.0 \n", "13 35.0 \n", "14 63.0 \n", "15 44.0 \n", "16 41.0 \n", "17 44.0 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_query(conn, \n", " \"\"\"\n", " SELECT DISTINCT \n", " school_year, semester, school, department, level,\n", " SUM(count) OVER() \n", " AS grand_total,\n", " SUM(count) OVER(PARTITION BY school_year) \n", " AS yearly_total,\n", " SUM(count) OVER(PARTITION BY school_year, semester) \n", " AS semester_total,\n", " SUM(count) OVER(PARTITION BY school_year, semester, school) \n", " AS school_total,\n", " SUM(count) OVER(PARTITION BY school_year, semester, \n", " school, department) \n", " AS dept_total,\n", " SUM(count) OVER(PARTITION BY school_year, semester, \n", " school, department, level) \n", " AS level_total\n", " FROM counts\n", " JOIN calendar\n", " ON counts.when = calendar.key\n", " JOIN classes\n", " ON counts.which = classes.key\n", " ORDER BY school_year, semester, school, department, level\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "f205cf8c-ae58-49d8-ad48-67dd2280100d", "metadata": {}, "source": [ "## Problems with using the window functions\n", "- #### Extra columns\n", "- #### Repeated subtotals values\n", "- #### Hard to read" ] }, { "cell_type": "markdown", "id": "dc940543-d7cb-46d5-ad1c-9bdb48a47c5e", "metadata": {}, "source": [ "## `GROUP BY ... WITH ROLLUP`\n", "#### The `GROUP BY` clause creates the drill-down hierarchy `school_year`, `semester`, `school`, `department`, `level`, and `class`. Adding `WITH ROLLUP` to the clause tells `SUM(count)` to calculate subtotals by drilling up the hierarchy ranks: level, department, school, semester, and year. In a row that contains a subtotal at a particular hierarchy rank, the field value whose subtotals were added are next to the leftmost None in the dataframe. Add the subtotals of the rows whose leftmost None is one column to the right, and are therefore one rank lower. Examples from the calculated dataframe displayed below:\n", "- #### Line 6: Electrical Engineering is next to the leftmost None. 88 = 41 + 47 (lines 2 and 5) is the subtotal of all the Electrical Engineering classes.\n", "- #### Line 25: Science is next to the leftmost None. 161 = 76 + 85 (lines 20 and 24) is the subtotal of the School of Science." ] }, { "cell_type": "code", "execution_count": 4, "id": "b7f949c4-be3a-41e8-a421-917a0d515210", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
school_yearsemesterschooldepartmentlevelclassSUBTOTALS
02022-2023fallEngineeringElectrical EngineeringgraduateEE 21024.0
12022-2023fallEngineeringElectrical EngineeringgraduateEE 29717.0
22022-2023fallEngineeringElectrical EngineeringgraduateNone41.0
32022-2023fallEngineeringElectrical EngineeringundergraduateEE 11825.0
42022-2023fallEngineeringElectrical EngineeringundergraduateEE 12022.0
52022-2023fallEngineeringElectrical EngineeringundergraduateNone47.0
62022-2023fallEngineeringElectrical EngineeringNoneNone88.0
72022-2023fallEngineeringNoneNoneNone88.0
82022-2023fallInformationData SciencegraduateDATA 20035.0
92022-2023fallInformationData SciencegraduateDATA 20140.0
102022-2023fallInformationData SciencegraduateDATA 20228.0
112022-2023fallInformationData SciencegraduateNone103.0
122022-2023fallInformationData ScienceNoneNone103.0
132022-2023fallInformationNoneNoneNone103.0
142022-2023fallScienceComputer SciencegraduateCS 24615.0
152022-2023fallScienceComputer SciencegraduateCS 25412.0
162022-2023fallScienceComputer SciencegraduateNone27.0
172022-2023fallScienceComputer ScienceundergraduateCS 14926.0
182022-2023fallScienceComputer ScienceundergraduateCS 15323.0
192022-2023fallScienceComputer ScienceundergraduateNone49.0
202022-2023fallScienceComputer ScienceNoneNone76.0
212022-2023fallSciencePhysics and AstronomyundergraduatePHYS 11235.0
222022-2023fallSciencePhysics and AstronomyundergraduatePHYS 4950.0
232022-2023fallSciencePhysics and AstronomyundergraduateNone85.0
242022-2023fallSciencePhysics and AstronomyNoneNone85.0
252022-2023fallScienceNoneNoneNone161.0
262022-2023fallNoneNoneNoneNone352.0
272022-2023springEngineeringElectrical EngineeringgraduateEE 21023.0
282022-2023springEngineeringElectrical EngineeringgraduateEE 29715.0
292022-2023springEngineeringElectrical EngineeringgraduateNone38.0
302022-2023springEngineeringElectrical EngineeringundergraduateEE 11824.0
312022-2023springEngineeringElectrical EngineeringundergraduateEE 12027.0
322022-2023springEngineeringElectrical EngineeringundergraduateNone51.0
332022-2023springEngineeringElectrical EngineeringNoneNone89.0
342022-2023springEngineeringNoneNoneNone89.0
352022-2023springInformationData SciencegraduateDATA 20031.0
362022-2023springInformationData SciencegraduateDATA 20136.0
372022-2023springInformationData SciencegraduateDATA 20232.0
382022-2023springInformationData SciencegraduateNone99.0
392022-2023springInformationData ScienceNoneNone99.0
402022-2023springInformationNoneNoneNone99.0
412022-2023springScienceComputer SciencegraduateCS 25414.0
422022-2023springScienceComputer SciencegraduateNone14.0
432022-2023springScienceComputer ScienceundergraduateCS 14933.0
442022-2023springScienceComputer ScienceundergraduateCS 15327.0
452022-2023springScienceComputer ScienceundergraduateNone60.0
462022-2023springScienceComputer ScienceNoneNone74.0
472022-2023springSciencePhysics and AstronomyundergraduatePHYS 11239.0
482022-2023springSciencePhysics and AstronomyundergraduatePHYS 4943.0
492022-2023springSciencePhysics and AstronomyundergraduateNone82.0
502022-2023springSciencePhysics and AstronomyNoneNone82.0
512022-2023springScienceNoneNoneNone156.0
522022-2023springNoneNoneNoneNone344.0
532022-2023NoneNoneNoneNoneNone696.0
542023-2024fallEngineeringElectrical EngineeringgraduateEE 21021.0
552023-2024fallEngineeringElectrical EngineeringgraduateEE 29723.0
562023-2024fallEngineeringElectrical EngineeringgraduateNone44.0
572023-2024fallEngineeringElectrical EngineeringundergraduateEE 11815.0
582023-2024fallEngineeringElectrical EngineeringundergraduateEE 12020.0
592023-2024fallEngineeringElectrical EngineeringundergraduateNone35.0
602023-2024fallEngineeringElectrical EngineeringNoneNone79.0
612023-2024fallEngineeringNoneNoneNone79.0
622023-2024fallInformationData SciencegraduateDATA 20025.0
632023-2024fallInformationData SciencegraduateDATA 20120.0
642023-2024fallInformationData SciencegraduateDATA 20218.0
652023-2024fallInformationData SciencegraduateNone63.0
662023-2024fallInformationData ScienceNoneNone63.0
672023-2024fallInformationNoneNoneNone63.0
682023-2024fallScienceComputer SciencegraduateCS 24618.0
692023-2024fallScienceComputer SciencegraduateCS 25426.0
702023-2024fallScienceComputer SciencegraduateNone44.0
712023-2024fallScienceComputer ScienceundergraduateCS 14922.0
722023-2024fallScienceComputer ScienceundergraduateCS 15319.0
732023-2024fallScienceComputer ScienceundergraduateNone41.0
742023-2024fallScienceComputer ScienceNoneNone85.0
752023-2024fallSciencePhysics and AstronomyundergraduatePHYS 11220.0
762023-2024fallSciencePhysics and AstronomyundergraduatePHYS 4924.0
772023-2024fallSciencePhysics and AstronomyundergraduateNone44.0
782023-2024fallSciencePhysics and AstronomyNoneNone44.0
792023-2024fallScienceNoneNoneNone129.0
802023-2024fallNoneNoneNoneNone271.0
812023-2024NoneNoneNoneNoneNone271.0
82NoneNoneNoneNoneNoneNone967.0
\n", "
" ], "text/plain": [ " school_year semester school department level \\\n", "0 2022-2023 fall Engineering Electrical Engineering graduate \n", "1 2022-2023 fall Engineering Electrical Engineering graduate \n", "2 2022-2023 fall Engineering Electrical Engineering graduate \n", "3 2022-2023 fall Engineering Electrical Engineering undergraduate \n", "4 2022-2023 fall Engineering Electrical Engineering undergraduate \n", "5 2022-2023 fall Engineering Electrical Engineering undergraduate \n", "6 2022-2023 fall Engineering Electrical Engineering None \n", "7 2022-2023 fall Engineering None None \n", "8 2022-2023 fall Information Data Science graduate \n", "9 2022-2023 fall Information Data Science graduate \n", "10 2022-2023 fall Information Data Science graduate \n", "11 2022-2023 fall Information Data Science graduate \n", "12 2022-2023 fall Information Data Science None \n", "13 2022-2023 fall Information None None \n", "14 2022-2023 fall Science Computer Science graduate \n", "15 2022-2023 fall Science Computer Science graduate \n", "16 2022-2023 fall Science Computer Science graduate \n", "17 2022-2023 fall Science Computer Science undergraduate \n", "18 2022-2023 fall Science Computer Science undergraduate \n", "19 2022-2023 fall Science Computer Science undergraduate \n", "20 2022-2023 fall Science Computer Science None \n", "21 2022-2023 fall Science Physics and Astronomy undergraduate \n", "22 2022-2023 fall Science Physics and Astronomy undergraduate \n", "23 2022-2023 fall Science Physics and Astronomy undergraduate \n", "24 2022-2023 fall Science Physics and Astronomy None \n", "25 2022-2023 fall Science None None \n", "26 2022-2023 fall None None None \n", "27 2022-2023 spring Engineering Electrical Engineering graduate \n", "28 2022-2023 spring Engineering Electrical Engineering graduate \n", "29 2022-2023 spring Engineering Electrical Engineering graduate \n", "30 2022-2023 spring Engineering Electrical Engineering undergraduate \n", "31 2022-2023 spring Engineering Electrical Engineering undergraduate \n", "32 2022-2023 spring Engineering Electrical Engineering undergraduate \n", "33 2022-2023 spring Engineering Electrical Engineering None \n", "34 2022-2023 spring Engineering None None \n", "35 2022-2023 spring Information Data Science graduate \n", "36 2022-2023 spring Information Data Science graduate \n", "37 2022-2023 spring Information Data Science graduate \n", "38 2022-2023 spring Information Data Science graduate \n", "39 2022-2023 spring Information Data Science None \n", "40 2022-2023 spring Information None None \n", "41 2022-2023 spring Science Computer Science graduate \n", "42 2022-2023 spring Science Computer Science graduate \n", "43 2022-2023 spring Science Computer Science undergraduate \n", "44 2022-2023 spring Science Computer Science undergraduate \n", "45 2022-2023 spring Science Computer Science undergraduate \n", "46 2022-2023 spring Science Computer Science None \n", "47 2022-2023 spring Science Physics and Astronomy undergraduate \n", "48 2022-2023 spring Science Physics and Astronomy undergraduate \n", "49 2022-2023 spring Science Physics and Astronomy undergraduate \n", "50 2022-2023 spring Science Physics and Astronomy None \n", "51 2022-2023 spring Science None None \n", "52 2022-2023 spring None None None \n", "53 2022-2023 None None None None \n", "54 2023-2024 fall Engineering Electrical Engineering graduate \n", "55 2023-2024 fall Engineering Electrical Engineering graduate \n", "56 2023-2024 fall Engineering Electrical Engineering graduate \n", "57 2023-2024 fall Engineering Electrical Engineering undergraduate \n", "58 2023-2024 fall Engineering Electrical Engineering undergraduate \n", "59 2023-2024 fall Engineering Electrical Engineering undergraduate \n", "60 2023-2024 fall Engineering Electrical Engineering None \n", "61 2023-2024 fall Engineering None None \n", "62 2023-2024 fall Information Data Science graduate \n", "63 2023-2024 fall Information Data Science graduate \n", "64 2023-2024 fall Information Data Science graduate \n", "65 2023-2024 fall Information Data Science graduate \n", "66 2023-2024 fall Information Data Science None \n", "67 2023-2024 fall Information None None \n", "68 2023-2024 fall Science Computer Science graduate \n", "69 2023-2024 fall Science Computer Science graduate \n", "70 2023-2024 fall Science Computer Science graduate \n", "71 2023-2024 fall Science Computer Science undergraduate \n", "72 2023-2024 fall Science Computer Science undergraduate \n", "73 2023-2024 fall Science Computer Science undergraduate \n", "74 2023-2024 fall Science Computer Science None \n", "75 2023-2024 fall Science Physics and Astronomy undergraduate \n", "76 2023-2024 fall Science Physics and Astronomy undergraduate \n", "77 2023-2024 fall Science Physics and Astronomy undergraduate \n", "78 2023-2024 fall Science Physics and Astronomy None \n", "79 2023-2024 fall Science None None \n", "80 2023-2024 fall None None None \n", "81 2023-2024 None None None None \n", "82 None None None None None \n", "\n", " class SUBTOTALS \n", "0 EE 210 24.0 \n", "1 EE 297 17.0 \n", "2 None 41.0 \n", "3 EE 118 25.0 \n", "4 EE 120 22.0 \n", "5 None 47.0 \n", "6 None 88.0 \n", "7 None 88.0 \n", "8 DATA 200 35.0 \n", "9 DATA 201 40.0 \n", "10 DATA 202 28.0 \n", "11 None 103.0 \n", "12 None 103.0 \n", "13 None 103.0 \n", "14 CS 246 15.0 \n", "15 CS 254 12.0 \n", "16 None 27.0 \n", "17 CS 149 26.0 \n", "18 CS 153 23.0 \n", "19 None 49.0 \n", "20 None 76.0 \n", "21 PHYS 112 35.0 \n", "22 PHYS 49 50.0 \n", "23 None 85.0 \n", "24 None 85.0 \n", "25 None 161.0 \n", "26 None 352.0 \n", "27 EE 210 23.0 \n", "28 EE 297 15.0 \n", "29 None 38.0 \n", "30 EE 118 24.0 \n", "31 EE 120 27.0 \n", "32 None 51.0 \n", "33 None 89.0 \n", "34 None 89.0 \n", "35 DATA 200 31.0 \n", "36 DATA 201 36.0 \n", "37 DATA 202 32.0 \n", "38 None 99.0 \n", "39 None 99.0 \n", "40 None 99.0 \n", "41 CS 254 14.0 \n", "42 None 14.0 \n", "43 CS 149 33.0 \n", "44 CS 153 27.0 \n", "45 None 60.0 \n", "46 None 74.0 \n", "47 PHYS 112 39.0 \n", "48 PHYS 49 43.0 \n", "49 None 82.0 \n", "50 None 82.0 \n", "51 None 156.0 \n", "52 None 344.0 \n", "53 None 696.0 \n", "54 EE 210 21.0 \n", "55 EE 297 23.0 \n", "56 None 44.0 \n", "57 EE 118 15.0 \n", "58 EE 120 20.0 \n", "59 None 35.0 \n", "60 None 79.0 \n", "61 None 79.0 \n", "62 DATA 200 25.0 \n", "63 DATA 201 20.0 \n", "64 DATA 202 18.0 \n", "65 None 63.0 \n", "66 None 63.0 \n", "67 None 63.0 \n", "68 CS 246 18.0 \n", "69 CS 254 26.0 \n", "70 None 44.0 \n", "71 CS 149 22.0 \n", "72 CS 153 19.0 \n", "73 None 41.0 \n", "74 None 85.0 \n", "75 PHYS 112 20.0 \n", "76 PHYS 49 24.0 \n", "77 None 44.0 \n", "78 None 44.0 \n", "79 None 129.0 \n", "80 None 271.0 \n", "81 None 271.0 \n", "82 None 967.0 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_query(conn, \n", " \"\"\"\n", " SELECT school_year, semester, school, department, level, class, \n", " SUM(count) AS SUBTOTALS\n", " FROM counts\n", " JOIN calendar\n", " ON counts.when = calendar.key\n", " JOIN classes\n", " ON counts.which = classes.key\n", " GROUP BY school_year, semester, school, department, level, class\n", " WITH ROLLUP\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "37c15b50-86de-44f2-a4e4-1b29e20c9d8d", "metadata": {}, "source": [ "## `GROUPING()` function\n", "#### The `GROUPING()` function takes a field as its argument, and it returns 1 if the field value is `NULL` due to `ROLLUP` subtotaling. Otherwise, it return 0 for any other value, including `NULL` ***not*** generated by subtotaling. We can use these 0 and 1 values in `IF` tests to enhance the dataframe display.\n", "#### The calculated dataframe displayed below shows the values of the `GROUPING()` function. Each 1 corresponds to a `NULL` generated by subtotaling. Note that a `NULL` is represented by `None` in a dataframe display." ] }, { "cell_type": "code", "execution_count": 5, "id": "5034bc2b-0bc4-4a73-82c0-9ebda549c162", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
school_yearsemesterschooldepartmentlevelGRANDYEARSEMESTERSCHOOLDEPT
02022-2023fallEngineeringElectrical Engineeringgraduate00000
12022-2023fallEngineeringElectrical Engineeringundergraduate00000
22022-2023fallEngineeringElectrical EngineeringNone00001
32022-2023fallEngineeringNoneNone00011
42022-2023fallInformationData Sciencegraduate00000
52022-2023fallInformationData ScienceNone00001
62022-2023fallInformationNoneNone00011
72022-2023fallScienceComputer Sciencegraduate00000
82022-2023fallScienceComputer Scienceundergraduate00000
92022-2023fallScienceComputer ScienceNone00001
102022-2023fallSciencePhysics and Astronomyundergraduate00000
112022-2023fallSciencePhysics and AstronomyNone00001
122022-2023fallScienceNoneNone00011
132022-2023fallNoneNoneNone00111
142022-2023springEngineeringElectrical Engineeringgraduate00000
152022-2023springEngineeringElectrical Engineeringundergraduate00000
162022-2023springEngineeringElectrical EngineeringNone00001
172022-2023springEngineeringNoneNone00011
182022-2023springInformationData Sciencegraduate00000
192022-2023springInformationData ScienceNone00001
202022-2023springInformationNoneNone00011
212022-2023springScienceComputer Sciencegraduate00000
222022-2023springScienceComputer Scienceundergraduate00000
232022-2023springScienceComputer ScienceNone00001
242022-2023springSciencePhysics and Astronomyundergraduate00000
252022-2023springSciencePhysics and AstronomyNone00001
262022-2023springScienceNoneNone00011
272022-2023springNoneNoneNone00111
282022-2023NoneNoneNoneNone01111
292023-2024fallEngineeringElectrical Engineeringgraduate00000
302023-2024fallEngineeringElectrical Engineeringundergraduate00000
312023-2024fallEngineeringElectrical EngineeringNone00001
322023-2024fallEngineeringNoneNone00011
332023-2024fallInformationData Sciencegraduate00000
342023-2024fallInformationData ScienceNone00001
352023-2024fallInformationNoneNone00011
362023-2024fallScienceComputer Sciencegraduate00000
372023-2024fallScienceComputer Scienceundergraduate00000
382023-2024fallScienceComputer ScienceNone00001
392023-2024fallSciencePhysics and Astronomyundergraduate00000
402023-2024fallSciencePhysics and AstronomyNone00001
412023-2024fallScienceNoneNone00011
422023-2024fallNoneNoneNone00111
432023-2024NoneNoneNoneNone01111
44NoneNoneNoneNoneNone11111
\n", "
" ], "text/plain": [ " school_year semester school department level \\\n", "0 2022-2023 fall Engineering Electrical Engineering graduate \n", "1 2022-2023 fall Engineering Electrical Engineering undergraduate \n", "2 2022-2023 fall Engineering Electrical Engineering None \n", "3 2022-2023 fall Engineering None None \n", "4 2022-2023 fall Information Data Science graduate \n", "5 2022-2023 fall Information Data Science None \n", "6 2022-2023 fall Information None None \n", "7 2022-2023 fall Science Computer Science graduate \n", "8 2022-2023 fall Science Computer Science undergraduate \n", "9 2022-2023 fall Science Computer Science None \n", "10 2022-2023 fall Science Physics and Astronomy undergraduate \n", "11 2022-2023 fall Science Physics and Astronomy None \n", "12 2022-2023 fall Science None None \n", "13 2022-2023 fall None None None \n", "14 2022-2023 spring Engineering Electrical Engineering graduate \n", "15 2022-2023 spring Engineering Electrical Engineering undergraduate \n", "16 2022-2023 spring Engineering Electrical Engineering None \n", "17 2022-2023 spring Engineering None None \n", "18 2022-2023 spring Information Data Science graduate \n", "19 2022-2023 spring Information Data Science None \n", "20 2022-2023 spring Information None None \n", "21 2022-2023 spring Science Computer Science graduate \n", "22 2022-2023 spring Science Computer Science undergraduate \n", "23 2022-2023 spring Science Computer Science None \n", "24 2022-2023 spring Science Physics and Astronomy undergraduate \n", "25 2022-2023 spring Science Physics and Astronomy None \n", "26 2022-2023 spring Science None None \n", "27 2022-2023 spring None None None \n", "28 2022-2023 None None None None \n", "29 2023-2024 fall Engineering Electrical Engineering graduate \n", "30 2023-2024 fall Engineering Electrical Engineering undergraduate \n", "31 2023-2024 fall Engineering Electrical Engineering None \n", "32 2023-2024 fall Engineering None None \n", "33 2023-2024 fall Information Data Science graduate \n", "34 2023-2024 fall Information Data Science None \n", "35 2023-2024 fall Information None None \n", "36 2023-2024 fall Science Computer Science graduate \n", "37 2023-2024 fall Science Computer Science undergraduate \n", "38 2023-2024 fall Science Computer Science None \n", "39 2023-2024 fall Science Physics and Astronomy undergraduate \n", "40 2023-2024 fall Science Physics and Astronomy None \n", "41 2023-2024 fall Science None None \n", "42 2023-2024 fall None None None \n", "43 2023-2024 None None None None \n", "44 None None None None None \n", "\n", " GRAND YEAR SEMESTER SCHOOL DEPT \n", "0 0 0 0 0 0 \n", "1 0 0 0 0 0 \n", "2 0 0 0 0 1 \n", "3 0 0 0 1 1 \n", "4 0 0 0 0 0 \n", "5 0 0 0 0 1 \n", "6 0 0 0 1 1 \n", "7 0 0 0 0 0 \n", "8 0 0 0 0 0 \n", "9 0 0 0 0 1 \n", "10 0 0 0 0 0 \n", "11 0 0 0 0 1 \n", "12 0 0 0 1 1 \n", "13 0 0 1 1 1 \n", "14 0 0 0 0 0 \n", "15 0 0 0 0 0 \n", "16 0 0 0 0 1 \n", "17 0 0 0 1 1 \n", "18 0 0 0 0 0 \n", "19 0 0 0 0 1 \n", "20 0 0 0 1 1 \n", "21 0 0 0 0 0 \n", "22 0 0 0 0 0 \n", "23 0 0 0 0 1 \n", "24 0 0 0 0 0 \n", "25 0 0 0 0 1 \n", "26 0 0 0 1 1 \n", "27 0 0 1 1 1 \n", "28 0 1 1 1 1 \n", "29 0 0 0 0 0 \n", "30 0 0 0 0 0 \n", "31 0 0 0 0 1 \n", "32 0 0 0 1 1 \n", "33 0 0 0 0 0 \n", "34 0 0 0 0 1 \n", "35 0 0 0 1 1 \n", "36 0 0 0 0 0 \n", "37 0 0 0 0 0 \n", "38 0 0 0 0 1 \n", "39 0 0 0 0 0 \n", "40 0 0 0 0 1 \n", "41 0 0 0 1 1 \n", "42 0 0 1 1 1 \n", "43 0 1 1 1 1 \n", "44 1 1 1 1 1 " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_query(conn, \n", " \"\"\"\n", " SELECT school_year, semester,\n", " school, department, level,\n", " GROUPING(school_year) AS GRAND,\n", " GROUPING(semester) AS YEAR,\n", " GROUPING(school) AS SEMESTER,\n", " GROUPING(department) AS SCHOOL,\n", " GROUPING(level) AS DEPT\n", " FROM counts\n", " JOIN calendar\n", " ON counts.when = calendar.key\n", " JOIN classes\n", " ON counts.which = classes.key\n", " GROUP BY school_year, semester, school, department, level \n", " WITH ROLLUP\n", " \"\"\"\n", ")" ] }, { "cell_type": "markdown", "id": "333c96c6-5a6d-4d03-a740-8147be0dbd64", "metadata": {}, "source": [ "## Total student counts with embedded labels\n", "#### We use the results of calling the `GROUPING()` function to display subtotal labels. In a row with more than one label, the leftmost label says at which hierarchy rank the subtotal was calculated. Examples from the dataframe displayed below:\n", "- #### Line 6: 103 is the subtotal of the Information school.\n", "- #### Line 13: 352 is the subtotal of the fall semester." ] }, { "cell_type": "code", "execution_count": 6, "id": "bc41a690-5a85-49ea-8d83-6355ee2c5f76", "metadata": {}, "outputs": [], "source": [ "labels = (' GRAND *****', \n", " ' YEARLY **** ', \n", " ' SEMESTER *** ', \n", " ' SCHOOL ** ', \n", " 'DEPARTMENT * ')\n", "\n", "grand, yearly, semester, school, department = \\\n", " (label for label in labels)" ] }, { "cell_type": "code", "execution_count": 7, "id": "443b069c-2384-4e1b-bee7-415fa9539e39", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
 school_yearsemesterschooldepartmentleveltotal
02022-2023fallEngineeringElectrical Engineeringgraduate41.000000
12022-2023fallEngineeringElectrical Engineeringundergraduate47.000000
22022-2023fallEngineeringElectrical EngineeringDEPARTMENT * 88.000000
32022-2023fallEngineering SCHOOL ** DEPARTMENT * 88.000000
42022-2023fallInformationData Sciencegraduate103.000000
52022-2023fallInformationData ScienceDEPARTMENT * 103.000000
62022-2023fallInformation SCHOOL ** DEPARTMENT * 103.000000
72022-2023fallScienceComputer Sciencegraduate27.000000
82022-2023fallScienceComputer Scienceundergraduate49.000000
92022-2023fallScienceComputer ScienceDEPARTMENT * 76.000000
102022-2023fallSciencePhysics and Astronomyundergraduate85.000000
112022-2023fallSciencePhysics and AstronomyDEPARTMENT * 85.000000
122022-2023fallScience SCHOOL ** DEPARTMENT * 161.000000
132022-2023fall SEMESTER *** SCHOOL ** DEPARTMENT * 352.000000
142022-2023springEngineeringElectrical Engineeringgraduate38.000000
152022-2023springEngineeringElectrical Engineeringundergraduate51.000000
162022-2023springEngineeringElectrical EngineeringDEPARTMENT * 89.000000
172022-2023springEngineering SCHOOL ** DEPARTMENT * 89.000000
182022-2023springInformationData Sciencegraduate99.000000
192022-2023springInformationData ScienceDEPARTMENT * 99.000000
202022-2023springInformation SCHOOL ** DEPARTMENT * 99.000000
212022-2023springScienceComputer Sciencegraduate14.000000
222022-2023springScienceComputer Scienceundergraduate60.000000
232022-2023springScienceComputer ScienceDEPARTMENT * 74.000000
242022-2023springSciencePhysics and Astronomyundergraduate82.000000
252022-2023springSciencePhysics and AstronomyDEPARTMENT * 82.000000
262022-2023springScience SCHOOL ** DEPARTMENT * 156.000000
272022-2023spring SEMESTER *** SCHOOL ** DEPARTMENT * 344.000000
282022-2023 YEARLY **** SEMESTER *** SCHOOL ** DEPARTMENT * 696.000000
292023-2024fallEngineeringElectrical Engineeringgraduate44.000000
302023-2024fallEngineeringElectrical Engineeringundergraduate35.000000
312023-2024fallEngineeringElectrical EngineeringDEPARTMENT * 79.000000
322023-2024fallEngineering SCHOOL ** DEPARTMENT * 79.000000
332023-2024fallInformationData Sciencegraduate63.000000
342023-2024fallInformationData ScienceDEPARTMENT * 63.000000
352023-2024fallInformation SCHOOL ** DEPARTMENT * 63.000000
362023-2024fallScienceComputer Sciencegraduate44.000000
372023-2024fallScienceComputer Scienceundergraduate41.000000
382023-2024fallScienceComputer ScienceDEPARTMENT * 85.000000
392023-2024fallSciencePhysics and Astronomyundergraduate44.000000
402023-2024fallSciencePhysics and AstronomyDEPARTMENT * 44.000000
412023-2024fallScience SCHOOL ** DEPARTMENT * 129.000000
422023-2024fall SEMESTER *** SCHOOL ** DEPARTMENT * 271.000000
432023-2024 YEARLY **** SEMESTER *** SCHOOL ** DEPARTMENT * 271.000000
44 GRAND ***** YEARLY **** SEMESTER *** SCHOOL ** DEPARTMENT * 967.000000
\n" ], "text/plain": [ "" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = df_query(conn, \n", " f\"\"\"\n", " SELECT \n", " -- --------- ---- -----\n", " -- CONDITION TRUE FALSE\n", " -- --------- ---- -----\n", " IF (GROUPING(school_year), '{grand}', school_year) \n", " AS school_year,\n", " IF (GROUPING(semester), '{yearly}', semester) \n", " AS semester,\n", " IF (GROUPING(school), '{semester}', school) \n", " AS school,\n", " IF (GROUPING(department), '{school}', department) \n", " AS department,\n", " IF (GROUPING(level), '{department}', level) \n", " AS level,\n", " SUM(count) AS total\n", " FROM counts\n", " JOIN calendar\n", " ON counts.when = calendar.key\n", " JOIN classes\n", " ON counts.which = classes.key\n", " GROUP BY school_year, semester, school, department, level \n", " WITH ROLLUP\n", " \"\"\"\n", ")\n", "\n", "df.style.format( {'LABEL' : '{:16s}'} )" ] }, { "cell_type": "markdown", "id": "952c3cac-9226-4b8e-be7e-ec251b12af89", "metadata": {}, "source": [ "## Total student counts with side labels\n", "#### The dataframe displayed below contains the same information as the table above, except that it puts the subtotal labels in a column at the right. All the fields of a subtotal row are blank." ] }, { "cell_type": "code", "execution_count": 8, "id": "3462d47b-c435-49d0-aad4-a5ee5a1c9405", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
school_yearsemesterschooldepartmentlevelSUBTOTALLABEL
02022-2023fallEngineeringElectrical Engineeringgraduate41.0
12022-2023fallEngineeringElectrical Engineeringundergraduate47.0
288.0DEPARTMENT *
388.0SCHOOL **
42022-2023fallInformationData Sciencegraduate103.0
5103.0DEPARTMENT *
6103.0SCHOOL **
72022-2023fallScienceComputer Sciencegraduate27.0
82022-2023fallScienceComputer Scienceundergraduate49.0
976.0DEPARTMENT *
102022-2023fallSciencePhysics and Astronomyundergraduate85.0
1185.0DEPARTMENT *
12161.0SCHOOL **
13352.0SEMESTER ***
142022-2023springEngineeringElectrical Engineeringgraduate38.0
152022-2023springEngineeringElectrical Engineeringundergraduate51.0
1689.0DEPARTMENT *
1789.0SCHOOL **
182022-2023springInformationData Sciencegraduate99.0
1999.0DEPARTMENT *
2099.0SCHOOL **
212022-2023springScienceComputer Sciencegraduate14.0
222022-2023springScienceComputer Scienceundergraduate60.0
2374.0DEPARTMENT *
242022-2023springSciencePhysics and Astronomyundergraduate82.0
2582.0DEPARTMENT *
26156.0SCHOOL **
27344.0SEMESTER ***
28696.0YEARLY ****
292023-2024fallEngineeringElectrical Engineeringgraduate44.0
302023-2024fallEngineeringElectrical Engineeringundergraduate35.0
3179.0DEPARTMENT *
3279.0SCHOOL **
332023-2024fallInformationData Sciencegraduate63.0
3463.0DEPARTMENT *
3563.0SCHOOL **
362023-2024fallScienceComputer Sciencegraduate44.0
372023-2024fallScienceComputer Scienceundergraduate41.0
3885.0DEPARTMENT *
392023-2024fallSciencePhysics and Astronomyundergraduate44.0
4044.0DEPARTMENT *
41129.0SCHOOL **
42271.0SEMESTER ***
43271.0YEARLY ****
44967.0GRAND *****
\n", "
" ], "text/plain": [ " school_year semester school department level \\\n", "0 2022-2023 fall Engineering Electrical Engineering graduate \n", "1 2022-2023 fall Engineering Electrical Engineering undergraduate \n", "2 \n", "3 \n", "4 2022-2023 fall Information Data Science graduate \n", "5 \n", "6 \n", "7 2022-2023 fall Science Computer Science graduate \n", "8 2022-2023 fall Science Computer Science undergraduate \n", "9 \n", "10 2022-2023 fall Science Physics and Astronomy undergraduate \n", "11 \n", "12 \n", "13 \n", "14 2022-2023 spring Engineering Electrical Engineering graduate \n", "15 2022-2023 spring Engineering Electrical Engineering undergraduate \n", "16 \n", "17 \n", "18 2022-2023 spring Information Data Science graduate \n", "19 \n", "20 \n", "21 2022-2023 spring Science Computer Science graduate \n", "22 2022-2023 spring Science Computer Science undergraduate \n", "23 \n", "24 2022-2023 spring Science Physics and Astronomy undergraduate \n", "25 \n", "26 \n", "27 \n", "28 \n", "29 2023-2024 fall Engineering Electrical Engineering graduate \n", "30 2023-2024 fall Engineering Electrical Engineering undergraduate \n", "31 \n", "32 \n", "33 2023-2024 fall Information Data Science graduate \n", "34 \n", "35 \n", "36 2023-2024 fall Science Computer Science graduate \n", "37 2023-2024 fall Science Computer Science undergraduate \n", "38 \n", "39 2023-2024 fall Science Physics and Astronomy undergraduate \n", "40 \n", "41 \n", "42 \n", "43 \n", "44 \n", "\n", " SUBTOTAL LABEL \n", "0 41.0 \n", "1 47.0 \n", "2 88.0 DEPARTMENT * \n", "3 88.0 SCHOOL ** \n", "4 103.0 \n", "5 103.0 DEPARTMENT * \n", "6 103.0 SCHOOL ** \n", "7 27.0 \n", "8 49.0 \n", "9 76.0 DEPARTMENT * \n", "10 85.0 \n", "11 85.0 DEPARTMENT * \n", "12 161.0 SCHOOL ** \n", "13 352.0 SEMESTER *** \n", "14 38.0 \n", "15 51.0 \n", "16 89.0 DEPARTMENT * \n", "17 89.0 SCHOOL ** \n", "18 99.0 \n", "19 99.0 DEPARTMENT * \n", "20 99.0 SCHOOL ** \n", "21 14.0 \n", "22 60.0 \n", "23 74.0 DEPARTMENT * \n", "24 82.0 \n", "25 82.0 DEPARTMENT * \n", "26 156.0 SCHOOL ** \n", "27 344.0 SEMESTER *** \n", "28 696.0 YEARLY **** \n", "29 44.0 \n", "30 35.0 \n", "31 79.0 DEPARTMENT * \n", "32 79.0 SCHOOL ** \n", "33 63.0 \n", "34 63.0 DEPARTMENT * \n", "35 63.0 SCHOOL ** \n", "36 44.0 \n", "37 41.0 \n", "38 85.0 DEPARTMENT * \n", "39 44.0 \n", "40 44.0 DEPARTMENT * \n", "41 129.0 SCHOOL ** \n", "42 271.0 SEMESTER *** \n", "43 271.0 YEARLY **** \n", "44 967.0 GRAND ***** " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_query(conn, \n", " f\"\"\"\n", " SELECT \n", " -- For the first 5 columns, print blanks if any colummn value \n", " -- is a NULL due to ROLLUP subtotaling.\n", " \n", " -- --------- ---- -----\n", " -- CONDITION TRUE FALSE\n", " -- --------- ---- -----\n", " IF ( GROUPING(school_year)\n", " OR GROUPING(semester) \n", " OR GROUPING(school) \n", " OR GROUPING(department) \n", " OR GROUPING(level), '', school_year) AS school_year,\n", " \n", " IF ( GROUPING(school_year)\n", " OR GROUPING(semester) \n", " OR GROUPING(school) \n", " OR GROUPING(department) \n", " OR GROUPING(level), '', semester) AS semester,\n", " \n", " IF ( GROUPING(school_year)\n", " OR GROUPING(semester) \n", " OR GROUPING(school) \n", " OR GROUPING(department) \n", " OR GROUPING(level), '', school) AS school,\n", "\n", " IF ( GROUPING(school_year)\n", " OR GROUPING(semester) \n", " OR GROUPING(school) \n", " OR GROUPING(department) \n", " OR GROUPING(level), '', department) AS department,\n", " \n", " IF ( GROUPING(school_year)\n", " OR GROUPING(semester) \n", " OR GROUPING(school) \n", " OR GROUPING(department) \n", " OR GROUPING(level), '', level) AS level,\n", "\n", " -- The 6th column is subtotals.\n", " --\n", " SUM(count) AS SUBTOTAL,\n", "\n", " -- The last column is an appropriate label.\n", " --\n", " IF (GROUPING(school_year), '{grand}',\n", " IF (GROUPING(semester), '{yearly}',\n", " IF (GROUPING(school), '{semester}',\n", " IF (GROUPING(department), '{school}',\n", " IF (GROUPING(level), '{department}',\n", " ''))))) AS LABEL\n", " \n", " FROM counts\n", " JOIN calendar\n", " ON counts.when = calendar.key\n", " JOIN classes\n", " ON counts.which = classes.key\n", " GROUP BY school_year, semester, school, department, level \n", " WITH ROLLUP\n", " \"\"\"\n", ")" ] }, { "cell_type": "code", "execution_count": 9, "id": "3c1f3d0b-4929-4695-a728-493f0d9998b0", "metadata": {}, "outputs": [], "source": [ "cursor.close()\n", "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "1aad8eb9-c1c8-43ee-9095-12ceeefdad76", "metadata": {}, "outputs": [], "source": [] } ], "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.12.4" } }, "nbformat": 4, "nbformat_minor": 5 }