Reading a SQL Lite file.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e2ab45e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "import os\n",
    "os.chdir('C:/Users/j_hor/Documents/Coderhouse/Data Science/Data/Raw')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b98eb860",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write SQL querys as string\n",
    "# Check database table's names\n",
    "sql1 = \"SELECT name FROM sqlite_master WHERE type ='table'\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4a0d21b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    name\n",
      "0  NBA_season1718_salary\n",
      "1          Seasons_Stats\n"
     ]
    }
   ],
   "source": [
    "# Create connection and query sqlite database table's names\n",
    "connection = sqlite3.connect('nba_salary.sqlite')\n",
    "df = pd.read_sql_query(sql1, connection)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6fe7afb2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(122, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X1</th>\n",
       "      <th>Player</th>\n",
       "      <th>Tm</th>\n",
       "      <th>season17_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Stephen Curry</td>\n",
       "      <td>GSW</td>\n",
       "      <td>34682550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>LeBron James</td>\n",
       "      <td>CLE</td>\n",
       "      <td>33285709.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Paul Millsap</td>\n",
       "      <td>DEN</td>\n",
       "      <td>31269231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>Gordon Hayward</td>\n",
       "      <td>BOS</td>\n",
       "      <td>29727900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>Blake Griffin</td>\n",
       "      <td>DET</td>\n",
       "      <td>29512900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    X1          Player   Tm  season17_18\n",
       "0  1.0   Stephen Curry  GSW   34682550.0\n",
       "1  2.0    LeBron James  CLE   33285709.0\n",
       "2  3.0    Paul Millsap  DEN   31269231.0\n",
       "3  4.0  Gordon Hayward  BOS   29727900.0\n",
       "4  5.0   Blake Griffin  DET   29512900.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Players with Salaries over u$d 10 million\n",
    "sql2 = \"SELECT * FROM NBA_season1718_salary WHERE season17_18 > 10000000\"\n",
    "df = pd.read_sql_query(sql2, connection)\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d81f170e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(134, 57)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X1</th>\n",
       "      <th>Player</th>\n",
       "      <th>Tm</th>\n",
       "      <th>season17_18</th>\n",
       "      <th>X1</th>\n",
       "      <th>Year</th>\n",
       "      <th>Player</th>\n",
       "      <th>Pos</th>\n",
       "      <th>Age</th>\n",
       "      <th>Tm</th>\n",
       "      <th>...</th>\n",
       "      <th>FT%</th>\n",
       "      <th>ORB</th>\n",
       "      <th>DRB</th>\n",
       "      <th>TRB</th>\n",
       "      <th>AST</th>\n",
       "      <th>STL</th>\n",
       "      <th>BLK</th>\n",
       "      <th>TOV</th>\n",
       "      <th>PF</th>\n",
       "      <th>PTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32.0</td>\n",
       "      <td>Steven Adams</td>\n",
       "      <td>OKC</td>\n",
       "      <td>22471910.0</td>\n",
       "      <td>24100.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Steven Adams</td>\n",
       "      <td>C</td>\n",
       "      <td>23.0</td>\n",
       "      <td>OKC</td>\n",
       "      <td>...</td>\n",
       "      <td>0.611</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>615.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>195.0</td>\n",
       "      <td>905.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>36.0</td>\n",
       "      <td>LaMarcus Aldridge</td>\n",
       "      <td>SAS</td>\n",
       "      <td>21461010.0</td>\n",
       "      <td>24104.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>LaMarcus Aldridge</td>\n",
       "      <td>PF</td>\n",
       "      <td>31.0</td>\n",
       "      <td>SAS</td>\n",
       "      <td>...</td>\n",
       "      <td>0.812</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>524.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>158.0</td>\n",
       "      <td>1243.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>42.0</td>\n",
       "      <td>Ryan Anderson</td>\n",
       "      <td>HOU</td>\n",
       "      <td>19578455.0</td>\n",
       "      <td>24114.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Ryan Anderson</td>\n",
       "      <td>PF</td>\n",
       "      <td>28.0</td>\n",
       "      <td>HOU</td>\n",
       "      <td>...</td>\n",
       "      <td>0.860</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>330.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>142.0</td>\n",
       "      <td>979.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33.0</td>\n",
       "      <td>Giannis Antetokounmpo</td>\n",
       "      <td>MIL</td>\n",
       "      <td>22471910.0</td>\n",
       "      <td>24115.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Giannis Antetokounmpo</td>\n",
       "      <td>SF</td>\n",
       "      <td>22.0</td>\n",
       "      <td>MIL</td>\n",
       "      <td>...</td>\n",
       "      <td>0.770</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>700.0</td>\n",
       "      <td>434.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>246.0</td>\n",
       "      <td>1832.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12.0</td>\n",
       "      <td>Carmelo Anthony</td>\n",
       "      <td>OKC</td>\n",
       "      <td>26243760.0</td>\n",
       "      <td>24116.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Carmelo Anthony</td>\n",
       "      <td>SF</td>\n",
       "      <td>32.0</td>\n",
       "      <td>NYK</td>\n",
       "      <td>...</td>\n",
       "      <td>0.833</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>436.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>198.0</td>\n",
       "      <td>1659.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 57 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     X1                 Player   Tm  season17_18       X1    Year  \\\n",
       "0  32.0           Steven Adams  OKC   22471910.0  24100.0  2017.0   \n",
       "1  36.0      LaMarcus Aldridge  SAS   21461010.0  24104.0  2017.0   \n",
       "2  42.0          Ryan Anderson  HOU   19578455.0  24114.0  2017.0   \n",
       "3  33.0  Giannis Antetokounmpo  MIL   22471910.0  24115.0  2017.0   \n",
       "4  12.0        Carmelo Anthony  OKC   26243760.0  24116.0  2017.0   \n",
       "\n",
       "                  Player Pos   Age   Tm  ...    FT%   ORB   DRB    TRB    AST  \\\n",
       "0           Steven Adams   C  23.0  OKC  ...  0.611  None  None  615.0   86.0   \n",
       "1      LaMarcus Aldridge  PF  31.0  SAS  ...  0.812  None  None  524.0  139.0   \n",
       "2          Ryan Anderson  PF  28.0  HOU  ...  0.860  None  None  330.0   68.0   \n",
       "3  Giannis Antetokounmpo  SF  22.0  MIL  ...  0.770  None  None  700.0  434.0   \n",
       "4        Carmelo Anthony  SF  32.0  NYK  ...  0.833  None  None  436.0  213.0   \n",
       "\n",
       "    STL   BLK   TOV     PF     PTS  \n",
       "0  None  None  None  195.0   905.0  \n",
       "1  None  None  None  158.0  1243.0  \n",
       "2  None  None  None  142.0   979.0  \n",
       "3  None  None  None  246.0  1832.0  \n",
       "4  None  None  None  198.0  1659.0  \n",
       "\n",
       "[5 rows x 57 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Players Stats from 2017 Season (only players with Salaries over u$d 10 million during 17-18 season)\n",
    "sql3 = \"SELECT * FROM NBA_season1718_salary as salaries INNER JOIN Seasons_Stats as stats ON salaries.Player = stats.Player WHERE salaries.season17_18 > 10000000 AND stats.Year = 2017\"\n",
    "df = pd.read_sql_query(sql3, connection)\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f54d48b4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Player</th>\n",
       "      <th>Tm</th>\n",
       "      <th>Salary_17/18_Season</th>\n",
       "      <th>Year</th>\n",
       "      <th>Player</th>\n",
       "      <th>Pos</th>\n",
       "      <th>Age</th>\n",
       "      <th>Tm</th>\n",
       "      <th>G</th>\n",
       "      <th>GS</th>\n",
       "      <th>...</th>\n",
       "      <th>FT%</th>\n",
       "      <th>ORB</th>\n",
       "      <th>DRB</th>\n",
       "      <th>TRB</th>\n",
       "      <th>AST</th>\n",
       "      <th>STL</th>\n",
       "      <th>BLK</th>\n",
       "      <th>TOV</th>\n",
       "      <th>PF</th>\n",
       "      <th>PTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Steven Adams</td>\n",
       "      <td>OKC</td>\n",
       "      <td>22471910.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Steven Adams</td>\n",
       "      <td>C</td>\n",
       "      <td>23.0</td>\n",
       "      <td>OKC</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.611</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>615.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>195.0</td>\n",
       "      <td>905.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LaMarcus Aldridge</td>\n",
       "      <td>SAS</td>\n",
       "      <td>21461010.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>LaMarcus Aldridge</td>\n",
       "      <td>PF</td>\n",
       "      <td>31.0</td>\n",
       "      <td>SAS</td>\n",
       "      <td>72.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.812</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>524.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>158.0</td>\n",
       "      <td>1243.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ryan Anderson</td>\n",
       "      <td>HOU</td>\n",
       "      <td>19578455.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Ryan Anderson</td>\n",
       "      <td>PF</td>\n",
       "      <td>28.0</td>\n",
       "      <td>HOU</td>\n",
       "      <td>72.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.860</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>330.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>142.0</td>\n",
       "      <td>979.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Giannis Antetokounmpo</td>\n",
       "      <td>MIL</td>\n",
       "      <td>22471910.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Giannis Antetokounmpo</td>\n",
       "      <td>SF</td>\n",
       "      <td>22.0</td>\n",
       "      <td>MIL</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.770</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>700.0</td>\n",
       "      <td>434.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>246.0</td>\n",
       "      <td>1832.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Carmelo Anthony</td>\n",
       "      <td>OKC</td>\n",
       "      <td>26243760.0</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>Carmelo Anthony</td>\n",
       "      <td>SF</td>\n",
       "      <td>32.0</td>\n",
       "      <td>NYK</td>\n",
       "      <td>74.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.833</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>436.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>198.0</td>\n",
       "      <td>1659.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 55 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Player   Tm  Salary_17/18_Season    Year  \\\n",
       "0           Steven Adams  OKC           22471910.0  2017.0   \n",
       "1      LaMarcus Aldridge  SAS           21461010.0  2017.0   \n",
       "2          Ryan Anderson  HOU           19578455.0  2017.0   \n",
       "3  Giannis Antetokounmpo  MIL           22471910.0  2017.0   \n",
       "4        Carmelo Anthony  OKC           26243760.0  2017.0   \n",
       "\n",
       "                  Player Pos   Age   Tm     G  GS  ...    FT%   ORB   DRB  \\\n",
       "0           Steven Adams   C  23.0  OKC  80.0 NaN  ...  0.611  None  None   \n",
       "1      LaMarcus Aldridge  PF  31.0  SAS  72.0 NaN  ...  0.812  None  None   \n",
       "2          Ryan Anderson  PF  28.0  HOU  72.0 NaN  ...  0.860  None  None   \n",
       "3  Giannis Antetokounmpo  SF  22.0  MIL  80.0 NaN  ...  0.770  None  None   \n",
       "4        Carmelo Anthony  SF  32.0  NYK  74.0 NaN  ...  0.833  None  None   \n",
       "\n",
       "     TRB    AST   STL   BLK   TOV     PF     PTS  \n",
       "0  615.0   86.0  None  None  None  195.0   905.0  \n",
       "1  524.0  139.0  None  None  None  158.0  1243.0  \n",
       "2  330.0   68.0  None  None  None  142.0   979.0  \n",
       "3  700.0  434.0  None  None  None  246.0  1832.0  \n",
       "4  436.0  213.0  None  None  None  198.0  1659.0  \n",
       "\n",
       "[5 rows x 55 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.rename(columns={'season17_18':'Salary_17/18_Season'})\n",
    "del df['X1']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "85b13e01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>points</th>\n",
       "      <th>assists</th>\n",
       "      <th>personal_fouls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>510.11635</td>\n",
       "      <td>114.852623</td>\n",
       "      <td>116.339222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      points     assists  personal_fouls\n",
       "0  510.11635  114.852623      116.339222"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Average of PTS, AST and PF from Season_Stats table (historical averages)\n",
    "sql4 = \"SELECT AVG(PTS) as points, AVG(AST) as assists, AVG(PF) as personal_fouls FROM Seasons_Stats\"\n",
    "df = pd.read_sql_query(sql4, connection)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e99e4fc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}