04_data_exploration_p2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "00d40d28-393d-4317-86cd-9f76b198e313",
   "metadata": {},
   "source": [
    "# 04. Data Exploration of the NBA PosgreSQL database (collected over API)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc4035ef-b506-492d-af5f-a9fa7a472703",
   "metadata": {},
   "source": [
    "Use DBeaver to generate ER diagram for the NBA PosgreSQL database:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed283e69-91b9-4fce-9185-cbaa5da5f462",
   "metadata": {},
   "source": [
    "<div>\n",
    "    <img src=\"figures/2.1 ER diagram.png\" alt=\"Fig. 4.1. The nba PostgreSQL ER diagram.\" style=\"display: block; margin: 0 auto;\">\n",
    "    <p style=\"text-align: center;\">Fig. 4.1. The nba PostgreSQL ER diagram.</p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b07ba85-7485-4852-8762-437ad15b00c8",
   "metadata": {},
   "source": [
    "As we can see from the ER diagram:\n",
    "\n",
    "- 4 tables;\n",
    "- the tables have between 5 and 23 columns;\n",
    "- the columns have TEXT, NUMERIC, INTEGER, TIMESTAMP types;\n",
    "- no views;\n",
    "- relationships between tables on ER diargam."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acd2581b-556a-499f-997c-5a9e38e91ac5",
   "metadata": {},
   "source": [
    "Import libraries, import auxilliary functions, set pandas, and set logger:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "87692858-d6dd-493f-98fb-51f4ad29488e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import the standard libraries.\n",
    "import os\n",
    "\n",
    "# Import the third party libraries.\n",
    "from dotenv import load_dotenv\n",
    "import psycopg\n",
    "\n",
    "# Import the local/project packages, modules, and fucntions.\n",
    "from utils.data_exploration_p2 import (\n",
    "    get_db_info,\n",
    "    plot_mpl_bars,\n",
    "    print_db_info,\n",
    "    create_reports\n",
    ")\n",
    "# Set environment.\n",
    "load_dotenv()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17b2b51f-5e06-4b64-a524-c3711c879a90",
   "metadata": {},
   "source": [
    "Establish a connection to our database and create a cursor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9bbd9ad3-f303-4893-8b23-bc16e797dd58",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the PostgreSQL DB.\n",
    "conn = psycopg.connect(\n",
    "    dbname=\"nba\",\n",
    "    user=os.environ.get(\"user\"),\n",
    "    password=os.environ.get(\"password\"),\n",
    "    host=os.environ.get(\"host\"),\n",
    "    port=os.environ.get(\"port\")\n",
    ")\n",
    "# Create a cursor object.\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d6f4aaf-a232-478d-b2e6-affde1ac8eb6",
   "metadata": {},
   "source": [
    "Return a dictionary of list for every table in DB and a number a rows for every table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "07140ec7-2a49-4419-b921-32b175fc503c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-08-15 16:45:52,696 | utils.data_exploration_p2 | INFO | The list of dictionaries db_info has been created.\n"
     ]
    }
   ],
   "source": [
    "db_info, num_rows = get_db_info(cur)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05bcd6b6-e71d-40f2-9560-4fefb6f9f502",
   "metadata": {},
   "source": [
    "The num_rows is a DataFrame that have \"Table Name\" and \"A num of rows columns\". Let's visualize it ([see Python code](utils/data_exploration_p2.py)):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "55ca3c87-2177-4b7c-89b9-c61756b4d676",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Table Name  A num of rows\n",
      "0      teams             45\n",
      "1      games          62939\n",
      "2    players           4979\n",
      "3      stats        1038274\n"
     ]
    }
   ],
   "source": [
    "print(num_rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9bf4b3ce-b852-411e-91f1-0ecc4372c8ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot_mpl_bars(num_rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6494853-19b9-4396-9627-1544bd4fa611",
   "metadata": {},
   "source": [
    "<div>\n",
    "    <img src=\"figures/4.2 A num of rows in tables.png\" alt=\"Fig. 4.2. A num of rows in tables.\" style=\"display: block; margin: 0 auto;\">\n",
    "    <p style=\"text-align: center;\">Fig. 4.2. A num of rows in tables.</p>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7796bbf6-5588-4f19-b052-a8b954818a88",
   "metadata": {},
   "source": [
    "As we can see there is the play_by_play table that has significantly more rows than other tables. The play_by_play table has more than 1 million rows. Using functions like pandas.describe() or ydata-profiling with this table can take several hours on an average computer.\n",
    "\n",
    "Print a list of table names and main data about columns ([see Python code](utils/data_exploration_p2.py)):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "94e490e1-59d4-4527-98bb-99b285a23cca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table name: teams\n",
      "Table columns:\n",
      "      Column Name     Type IS_NULLABLE DFLT_VALUE \n",
      "ID                                               \n",
      "1             id  integer          NO        None\n",
      "2   abbreviation     text         YES        None\n",
      "3           city     text         YES        None\n",
      "4     conference     text         YES        None\n",
      "5       division     text         YES        None\n",
      "6      full_name     text         YES        None\n",
      "7           name     text         YES        None\n",
      "\n",
      "Table name: games\n",
      "Table columns:\n",
      "            Column Name                         Type IS_NULLABLE DFLT_VALUE \n",
      "ID                                                                         \n",
      "1                   id                      integer          NO        None\n",
      "2                 date  timestamp without time zone         YES        None\n",
      "3         home_team_id                      integer         YES        None\n",
      "4      home_team_score                      integer         YES        None\n",
      "5               season                      integer         YES        None\n",
      "6      visitor_team_id                      integer         YES        None\n",
      "7   visitor_team_score                      integer         YES        None\n",
      "\n",
      "Table name: players\n",
      "Table columns:\n",
      "    Column Name     Type IS_NULLABLE DFLT_VALUE \n",
      "ID                                             \n",
      "1           id  integer          NO        None\n",
      "5      team_id  integer         YES        None\n",
      "2   first_name     text         YES        None\n",
      "3    last_name     text         YES        None\n",
      "4     position     text         YES        None\n",
      "\n",
      "Table name: stats\n",
      "Table columns:\n",
      "    Column Name     Type IS_NULLABLE DFLT_VALUE \n",
      "ID                                             \n",
      "23    turnover  integer         YES        None\n",
      "2      game_id  integer         YES        None\n",
      "3      team_id  integer         YES        None\n",
      "4    player_id  integer         YES        None\n",
      "5          ast  integer         YES        None\n",
      "6          blk  integer         YES        None\n",
      "7         dreb  integer         YES        None\n",
      "8      fg3_pct  numeric         YES        None\n",
      "9         fg3a  integer         YES        None\n",
      "10        fg3m  integer         YES        None\n",
      "11      fg_pct  numeric         YES        None\n",
      "12         fga  integer         YES        None\n",
      "13         fgm  integer         YES        None\n",
      "14      ft_pct  numeric         YES        None\n",
      "15         fta  integer         YES        None\n",
      "16         ftm  integer         YES        None\n",
      "1           id  integer          NO        None\n",
      "18        oreb  integer         YES        None\n",
      "19          pf  integer         YES        None\n",
      "20         pts  integer         YES        None\n",
      "21         reb  integer         YES        None\n",
      "22         stl  integer         YES        None\n",
      "17         min     text         YES        None\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print_db_info(db_info)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8259fef9-bf47-487b-a3d7-34b63ffe7efe",
   "metadata": {},
   "source": [
    "Generate a report for each table using the ydata-profiling library. We will pass a list of \"excluded\" tables to the create_reports function, which can be found [here](utils/data_exploration_p2.py). It means a list of \"big\" tables that are similar to the play_by_play table. For these 'excluded' tables, we will randomly select 100,000 rows (by default) for initial exploration. The reports are saved in the reports directory and outputted to widgets in Jupyter Lab notebook.  It is a more comfortabe way to explore the reports in a browser."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "417d90d7-1391-4e51-b7c0-981fb303a486",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-08-15 16:45:55,152 | utils.data_exploration_p2 | INFO | Generating the profile report for table teams...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/lex/Sync/AI/My_projects/NBA/utils/data_exploration_p2.py:145: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql_query(\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f64860af07654b96a7aad8dd8c95ede4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Summarize dataset:   0%|          | 0/5 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "161b34df769a42d2acfccbbe2878b818",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Render widgets:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4a7521e3e5914fb28306a9e88293968b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Tab(children=(Tab(children=(GridBox(children=(VBox(children=(GridspecLayout(children=(HTML(valu…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a52628c3aea84083a08f718ebfe67938",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Render HTML:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6143ef8e641749b897d6c1d66cb8e748",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Export report to file:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/lex/Sync/AI/My_projects/NBA/utils/data_exploration_p2.py:145: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql_query(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-08-15 16:46:00,348 | utils.data_exploration_p2 | INFO | Generating the profile report for table games...\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9cf5ea7661c143dfa227a2ce02ec19a1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Summarize dataset:   0%|          | 0/5 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "27b9c97962bf4100bc561ad4e2cb1807",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "87fa129a13a54428905336758c5d1de7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Render widgets:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3bc590bfe08f4d9182e51ca2dd3fc526",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Export report to file:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-08-15 16:46:02,435 | utils.data_exploration_p2 | INFO | Generating the profile report for table players...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/lex/Sync/AI/My_projects/NBA/utils/data_exploration_p2.py:145: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql_query(\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6091d0f368ef48429aa027af3a4aadbc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Summarize dataset:   0%|          | 0/5 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "16ac3ab694d24c879920730a72532e72",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f1b8a12da4004984a669a628791d526f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Render widgets:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4597b9defe4147cc88795f0656e76c1f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Export report to file:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/lex/Sync/AI/My_projects/NBA/utils/data_exploration_p2.py:145: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql_query(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-08-15 16:46:11,403 | utils.data_exploration_p2 | INFO | Generating the profile report for table stats...\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "51857b5c56f0449c9db03a63cbc639e6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Summarize dataset:   0%|          | 0/5 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "362eff536a51428ba871c9121e369fb4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Render widgets:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "40ea54974cf34572a59d9f30380065b9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Tab(children=(Tab(children=(GridBox(children=(VBox(children=(GridspecLayout(children=(HTML(valu…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "64dc239124d14ea38678b79edd15bb36",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Render HTML:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e33543c0192a4384bca7b990b2fd8c79",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Export report to file:   0%|          | 0/1 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-08-15 16:46:20,186 | utils.data_exploration_p2 | INFO | The profile reports have been generated.\n",
      "The following tables have been excluded (several million lines take a few hours to create the report):\n",
      "[]\n",
      "The empty dataframes for tables:\n",
      "[]\n"
     ]
    }
   ],
   "source": [
    "create_reports(db_info, conn, excluded_tables=[])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9 (env_mc_tf)",
   "language": "python",
   "name": "env_mc_tf"
  },
  "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}