database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = duckdb.connect('mta_data.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌────────────┬────────────────────────────────────┬───────────────────────────────────────────┬──────────────────────────────────┬─────────────────────────────────────────┬─────────────────────────────────┬────────────────────────────────────────┬────────────────────────────────────────┬───────────────────────────────────────────────┬──────────────────────────────────────┬─────────────────────────────────────────────────┬────────────────────────────────────┬───────────────────────────────────────────────────────┬──────────────────────────────────────────────────┬─────────────────────────────────────────────────────────┐\n",
      "│    Date    │ Subways: Total Estimated Ridership │ Subways: % of Comparable Pre-Pandemic Day │ Buses: Total Estimated Ridership │ Buses: % of Comparable Pre-Pandemic Day │ LIRR: Total Estimated Ridership │ LIRR: % of Comparable Pre-Pandemic Day │ Metro-North: Total Estimated Ridership │ Metro-North: % of Comparable Pre-Pandemic Day │ Access-A-Ride: Total Scheduled Trips │ Access-A-Ride: % of Comparable Pre-Pandemic Day │ Bridges and Tunnels: Total Traffic │ Bridges and Tunnels: % of Comparable Pre-Pandemic Day │ Staten Island Railway: Total Estimated Ridership │ Staten Island Railway: % of Comparable Pre-Pandemic Day │\n",
      "│    date    │               int64                │                  double                   │              int64               │                 double                  │              int64              │                 double                 │                 int64                  │                    double                     │                int64                 │                     double                      │               int64                │                        double                         │                      int64                       │                         double                          │\n",
      "├────────────┼────────────────────────────────────┼───────────────────────────────────────────┼──────────────────────────────────┼─────────────────────────────────────────┼─────────────────────────────────┼────────────────────────────────────────┼────────────────────────────────────────┼───────────────────────────────────────────────┼──────────────────────────────────────┼─────────────────────────────────────────────────┼────────────────────────────────────┼───────────────────────────────────────────────────────┼──────────────────────────────────────────────────┼─────────────────────────────────────────────────────────┤\n",
      "│ 2020-03-01 │                            2212965 │                                      0.97 │                           984908 │                                    0.99 │                           86790 │                                    1.0 │                                  55825 │                                          0.59 │                                19922 │                                            1.13 │                             786960 │                                                  0.98 │                                             1636 │                                                    0.52 │\n",
      "│ 2020-03-02 │                            5329915 │                                      0.96 │                          2209066 │                                    0.99 │                          321569 │                                   1.03 │                                 180701 │                                          0.66 │                                30338 │                                            1.02 │                             874619 │                                                  0.95 │                                            17140 │                                                    1.07 │\n",
      "│ 2020-03-03 │                            5481103 │                                      0.98 │                          2228608 │                                    0.99 │                          319727 │                                   1.02 │                                 190648 │                                          0.69 │                                32767 │                                             1.1 │                             882175 │                                                  0.96 │                                            17453 │                                                    1.09 │\n",
      "│ 2020-03-04 │                            5498809 │                                      0.99 │                          2177165 │                                    0.97 │                          311662 │                                   0.99 │                                 192689 │                                           0.7 │                                34297 │                                            1.15 │                             905558 │                                                  0.98 │                                            17136 │                                                    1.07 │\n",
      "│ 2020-03-05 │                            5496453 │                                      0.99 │                          2244515 │                                     1.0 │                          307597 │                                   0.98 │                                 194386 │                                           0.7 │                                33209 │                                            1.12 │                             929298 │                                                  1.01 │                                            17203 │                                                    1.08 │\n",
      "│ 2020-03-06 │                            5189447 │                                      0.93 │                          2066743 │                                    0.92 │                          289171 │                                   0.92 │                                 205056 │                                          0.74 │                                30970 │                                            1.04 │                             945408 │                                                  1.03 │                                            15285 │                                                    0.96 │\n",
      "│ 2020-03-07 │                            2814637 │                                      0.92 │                          1249085 │                                    0.94 │                          106058 │                                   0.98 │                                  75838 │                                          0.56 │                                18117 │                                            1.07 │                             827907 │                                                  0.95 │                                             2445 │                                                    0.48 │\n",
      "│ 2020-03-08 │                            2120656 │                                      0.93 │                           957163 │                                    0.96 │                           81565 │                                   0.94 │                                  60800 │                                          0.64 │                                19477 │                                            1.11 │                             765083 │                                                  0.95 │                                             1672 │                                                    0.53 │\n",
      "│ 2020-03-09 │                            4973513 │                                      0.89 │                          2124770 │                                    0.95 │                          277001 │                                   0.88 │                                 183953 │                                          0.67 │                                29609 │                                             1.0 │                             860073 │                                                  0.93 │                                            16122 │                                                    1.01 │\n",
      "│ 2020-03-10 │                            4867818 │                                      0.87 │                          2111989 │                                    0.94 │                          259324 │                                   0.83 │                                 179050 │                                          0.65 │                                31315 │                                            1.05 │                             855585 │                                                  0.93 │                                            15805 │                                                    0.99 │\n",
      "│     ·      │                               ·    │                                        ·  │                             ·    │                                      ·  │                             ·   │                                     ·  │                                    ·   │                                            ·  │                                  ·   │                                              ·  │                                ·   │                                                    ·  │                                               ·  │                                                      ·  │\n",
      "│     ·      │                               ·    │                                        ·  │                             ·    │                                      ·  │                             ·   │                                     ·  │                                    ·   │                                            ·  │                                  ·   │                                              ·  │                                ·   │                                                    ·  │                                               ·  │                                                      ·  │\n",
      "│     ·      │                               ·    │                                        ·  │                             ·    │                                      ·  │                             ·   │                                     ·  │                                    ·   │                                            ·  │                                  ·   │                                              ·  │                                ·   │                                                    ·  │                                               ·  │                                                      ·  │\n",
      "│ 2024-11-04 │                            3949644 │                                       0.7 │                          1450618 │                                    0.66 │                          243884 │                                   0.74 │                                 216014 │                                          0.75 │                                36716 │                                            1.18 │                             908080 │                                                  0.96 │                                             7765 │                                                    0.46 │\n",
      "│ 2024-11-05 │                            3640162 │                                      0.65 │                          1321363 │                                     0.6 │                          244078 │                                   0.74 │                                 213516 │                                          0.75 │                                36106 │                                            1.16 │                             858375 │                                                  0.91 │                                             6691 │                                                    0.39 │\n",
      "│ 2024-11-06 │                            4113280 │                                      0.73 │                          1488751 │                                    0.68 │                          256311 │                                   0.78 │                                 221461 │                                          0.77 │                                40044 │                                            1.28 │                             928979 │                                                  0.98 │                                             8324 │                                                    0.49 │\n",
      "│ 2024-11-07 │                            4231978 │                                      0.75 │                          1497504 │                                    0.68 │                          259228 │                                   0.79 │                                 226007 │                                          0.79 │                                39248 │                                            1.26 │                             958652 │                                                  1.02 │                                             8176 │                                                    0.48 │\n",
      "│ 2024-11-08 │                            3971545 │                                       0.7 │                          1425759 │                                    0.65 │                          245087 │                                   0.75 │                                 213611 │                                          0.75 │                                38062 │                                            1.22 │                             990743 │                                                  1.05 │                                             7038 │                                                    0.41 │\n",
      "│ 2024-11-09 │                            2769307 │                                      0.88 │                           917222 │                                     0.7 │                          133329 │                                   1.16 │                                 134651 │                                          0.89 │                                24198 │                                            1.43 │                             924905 │                                                  1.02 │                                             3270 │                                                    0.94 │\n",
      "│ 2024-11-10 │                            2116197 │                                      0.84 │                           711123 │                                    0.71 │                          105256 │                                   1.11 │                                 103048 │                                          0.99 │                                23972 │                                            1.27 │                             870080 │                                                  1.06 │                                             2320 │                                                    0.76 │\n",
      "│ 2024-11-11 │                            3276120 │                                       1.3 │                          1166004 │                                    1.17 │                          236005 │                                    2.5 │                                 206579 │                                          1.98 │                                28032 │                                            1.49 │                             864822 │                                                  1.05 │                                             5532 │                                                     1.8 │\n",
      "│ 2024-11-12 │                            4259620 │                                      0.76 │                          1463667 │                                    0.67 │                          264372 │                                   0.81 │                                 234752 │                                          0.82 │                                39828 │                                            1.28 │                             921273 │                                                  0.98 │                                             8568 │                                                     0.5 │\n",
      "│ 2024-11-13 │                            4301105 │                                      0.76 │                          1401832 │                                    0.64 │                          257349 │                                   0.78 │                                 229455 │                                           0.8 │                                41143 │                                            1.32 │                             932923 │                                                  0.99 │                                             8122 │                                                    0.48 │\n",
      "├────────────┴────────────────────────────────────┴───────────────────────────────────────────┴──────────────────────────────────┴─────────────────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────┴────────────────────────────────────────┴───────────────────────────────────────────────┴──────────────────────────────────────┴─────────────────────────────────────────────────┴────────────────────────────────────┴───────────────────────────────────────────────────────┴──────────────────────────────────────────────────┴─────────────────────────────────────────────────────────┤\n",
      "│ 1719 rows (20 shown)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 15 columns │\n",
      "└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "con.table('mta').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Date</th>\n",
       "      <th>Subways: Total Estimated Ridership</th>\n",
       "      <th>Subways: % of Comparable Pre-Pandemic Day</th>\n",
       "      <th>Buses: Total Estimated Ridership</th>\n",
       "      <th>Buses: % of Comparable Pre-Pandemic Day</th>\n",
       "      <th>LIRR: Total Estimated Ridership</th>\n",
       "      <th>LIRR: % of Comparable Pre-Pandemic Day</th>\n",
       "      <th>Metro-North: Total Estimated Ridership</th>\n",
       "      <th>Metro-North: % of Comparable Pre-Pandemic Day</th>\n",
       "      <th>Access-A-Ride: Total Scheduled Trips</th>\n",
       "      <th>Access-A-Ride: % of Comparable Pre-Pandemic Day</th>\n",
       "      <th>Bridges and Tunnels: Total Traffic</th>\n",
       "      <th>Bridges and Tunnels: % of Comparable Pre-Pandemic Day</th>\n",
       "      <th>Staten Island Railway: Total Estimated Ridership</th>\n",
       "      <th>Staten Island Railway: % of Comparable Pre-Pandemic Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>2212965</td>\n",
       "      <td>0.97</td>\n",
       "      <td>984908</td>\n",
       "      <td>0.99</td>\n",
       "      <td>86790</td>\n",
       "      <td>1.00</td>\n",
       "      <td>55825</td>\n",
       "      <td>0.59</td>\n",
       "      <td>19922</td>\n",
       "      <td>1.13</td>\n",
       "      <td>786960</td>\n",
       "      <td>0.98</td>\n",
       "      <td>1636</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>5329915</td>\n",
       "      <td>0.96</td>\n",
       "      <td>2209066</td>\n",
       "      <td>0.99</td>\n",
       "      <td>321569</td>\n",
       "      <td>1.03</td>\n",
       "      <td>180701</td>\n",
       "      <td>0.66</td>\n",
       "      <td>30338</td>\n",
       "      <td>1.02</td>\n",
       "      <td>874619</td>\n",
       "      <td>0.95</td>\n",
       "      <td>17140</td>\n",
       "      <td>1.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>5481103</td>\n",
       "      <td>0.98</td>\n",
       "      <td>2228608</td>\n",
       "      <td>0.99</td>\n",
       "      <td>319727</td>\n",
       "      <td>1.02</td>\n",
       "      <td>190648</td>\n",
       "      <td>0.69</td>\n",
       "      <td>32767</td>\n",
       "      <td>1.10</td>\n",
       "      <td>882175</td>\n",
       "      <td>0.96</td>\n",
       "      <td>17453</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>5498809</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2177165</td>\n",
       "      <td>0.97</td>\n",
       "      <td>311662</td>\n",
       "      <td>0.99</td>\n",
       "      <td>192689</td>\n",
       "      <td>0.70</td>\n",
       "      <td>34297</td>\n",
       "      <td>1.15</td>\n",
       "      <td>905558</td>\n",
       "      <td>0.98</td>\n",
       "      <td>17136</td>\n",
       "      <td>1.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-05</td>\n",
       "      <td>5496453</td>\n",
       "      <td>0.99</td>\n",
       "      <td>2244515</td>\n",
       "      <td>1.00</td>\n",
       "      <td>307597</td>\n",
       "      <td>0.98</td>\n",
       "      <td>194386</td>\n",
       "      <td>0.70</td>\n",
       "      <td>33209</td>\n",
       "      <td>1.12</td>\n",
       "      <td>929298</td>\n",
       "      <td>1.01</td>\n",
       "      <td>17203</td>\n",
       "      <td>1.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  Subways: Total Estimated Ridership  \\\n",
       "0 2020-03-01                             2212965   \n",
       "1 2020-03-02                             5329915   \n",
       "2 2020-03-03                             5481103   \n",
       "3 2020-03-04                             5498809   \n",
       "4 2020-03-05                             5496453   \n",
       "\n",
       "   Subways: % of Comparable Pre-Pandemic Day  \\\n",
       "0                                       0.97   \n",
       "1                                       0.96   \n",
       "2                                       0.98   \n",
       "3                                       0.99   \n",
       "4                                       0.99   \n",
       "\n",
       "   Buses: Total Estimated Ridership  Buses: % of Comparable Pre-Pandemic Day  \\\n",
       "0                            984908                                     0.99   \n",
       "1                           2209066                                     0.99   \n",
       "2                           2228608                                     0.99   \n",
       "3                           2177165                                     0.97   \n",
       "4                           2244515                                     1.00   \n",
       "\n",
       "   LIRR: Total Estimated Ridership  LIRR: % of Comparable Pre-Pandemic Day  \\\n",
       "0                            86790                                    1.00   \n",
       "1                           321569                                    1.03   \n",
       "2                           319727                                    1.02   \n",
       "3                           311662                                    0.99   \n",
       "4                           307597                                    0.98   \n",
       "\n",
       "   Metro-North: Total Estimated Ridership  \\\n",
       "0                                   55825   \n",
       "1                                  180701   \n",
       "2                                  190648   \n",
       "3                                  192689   \n",
       "4                                  194386   \n",
       "\n",
       "   Metro-North: % of Comparable Pre-Pandemic Day  \\\n",
       "0                                           0.59   \n",
       "1                                           0.66   \n",
       "2                                           0.69   \n",
       "3                                           0.70   \n",
       "4                                           0.70   \n",
       "\n",
       "   Access-A-Ride: Total Scheduled Trips  \\\n",
       "0                                 19922   \n",
       "1                                 30338   \n",
       "2                                 32767   \n",
       "3                                 34297   \n",
       "4                                 33209   \n",
       "\n",
       "   Access-A-Ride: % of Comparable Pre-Pandemic Day  \\\n",
       "0                                             1.13   \n",
       "1                                             1.02   \n",
       "2                                             1.10   \n",
       "3                                             1.15   \n",
       "4                                             1.12   \n",
       "\n",
       "   Bridges and Tunnels: Total Traffic  \\\n",
       "0                              786960   \n",
       "1                              874619   \n",
       "2                              882175   \n",
       "3                              905558   \n",
       "4                              929298   \n",
       "\n",
       "   Bridges and Tunnels: % of Comparable Pre-Pandemic Day  \\\n",
       "0                                               0.98       \n",
       "1                                               0.95       \n",
       "2                                               0.96       \n",
       "3                                               0.98       \n",
       "4                                               1.01       \n",
       "\n",
       "   Staten Island Railway: Total Estimated Ridership  \\\n",
       "0                                              1636   \n",
       "1                                             17140   \n",
       "2                                             17453   \n",
       "3                                             17136   \n",
       "4                                             17203   \n",
       "\n",
       "   Staten Island Railway: % of Comparable Pre-Pandemic Day  \n",
       "0                                               0.52        \n",
       "1                                               1.07        \n",
       "2                                               1.09        \n",
       "3                                               1.07        \n",
       "4                                               1.08        "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_df = con.table('mta').to_df()\n",
    "mta_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────┬───────┬──────────┐\n",
      "│ username │  age  │ country  │\n",
      "│ varchar  │ int32 │ varchar  │\n",
      "├──────────┼───────┼──────────┤\n",
      "│ Will)    │    30 │ USA      │\n",
      "│ Will)    │    30 │ USA      │\n",
      "│ Roberto  │    33 │ Chile    │\n",
      "│ Roberto  │    33 │ Chile    │\n",
      "│ Sam      │    26 │ USA      │\n",
      "│ Roberto  │    33 │ Chile    │\n",
      "│ Roberto  │    33 │ Chile    │\n",
      "│ Roberto  │    33 │ Chile    │\n",
      "│ Roberto  │    33 │ Chile    │\n",
      "│ Pedro    │    26 │ Colombia │\n",
      "│ Will     │    30 │ USA      │\n",
      "├──────────┴───────┴──────────┤\n",
      "│ 11 rows           3 columns │\n",
      "└─────────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "username = 'Will'\n",
    "age = 30\n",
    "country = 'USA'\n",
    "\n",
    "\n",
    "con = duckdb.connect('mta_data.db')\n",
    "con.sql(\"CREATE TABLE IF NOT EXISTS users(username TEXT, age INT, country TEXT)\")\n",
    "\n",
    "con.sql(f\"INSERT INTO users VALUES ('{username}','{age}','{country}')\")\n",
    "\n",
    "\n",
    "con.table('users').show()\n",
    "con.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}