db_info/test_connection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test Connection to DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%pip install -q sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Successfully connected to the PostgreSQL database\n"
     ]
    }
   ],
   "source": [
    "# for postgreSQL database credentials can be written as \n",
    "user = 'myuser'\n",
    "password = 'mypassword'\n",
    "host = 'localhost'\n",
    "port = '5432' # 5433\n",
    "database = 'eie'\n",
    "# for creating connection string\n",
    "connection_str = f\"postgresql://{user}:{password}@{host}:{port}/{database}\"\n",
    "# SQLAlchemy engine\n",
    "engine = create_engine(connection_str)\n",
    "# you can test if the connection is made or not\n",
    "try:\n",
    "    with engine.connect() as connection_str:\n",
    "        print('Successfully connected to the PostgreSQL database')\n",
    "except Exception as ex:\n",
    "    print(f'Sorry failed to connect: {ex}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query Examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>years</th>\n",
       "      <th>sextypename</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016</td>\n",
       "      <td>Female</td>\n",
       "      <td>141626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016</td>\n",
       "      <td>Male</td>\n",
       "      <td>126376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>Female</td>\n",
       "      <td>127637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>Male</td>\n",
       "      <td>113252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>Female</td>\n",
       "      <td>168582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018</td>\n",
       "      <td>Male</td>\n",
       "      <td>167105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019</td>\n",
       "      <td>Female</td>\n",
       "      <td>175647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019</td>\n",
       "      <td>Male</td>\n",
       "      <td>178166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020</td>\n",
       "      <td>Female</td>\n",
       "      <td>187488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020</td>\n",
       "      <td>Male</td>\n",
       "      <td>191811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2021</td>\n",
       "      <td>Female</td>\n",
       "      <td>193073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2021</td>\n",
       "      <td>Male</td>\n",
       "      <td>196250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022</td>\n",
       "      <td>Female</td>\n",
       "      <td>122311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022</td>\n",
       "      <td>Male</td>\n",
       "      <td>111793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2023</td>\n",
       "      <td>Female</td>\n",
       "      <td>148941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2023</td>\n",
       "      <td>Male</td>\n",
       "      <td>139994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    years sextypename     num\n",
       "0    2016      Female  141626\n",
       "1    2016        Male  126376\n",
       "2    2017      Female  127637\n",
       "3    2017        Male  113252\n",
       "4    2018      Female  168582\n",
       "5    2018        Male  167105\n",
       "6    2019      Female  175647\n",
       "7    2019        Male  178166\n",
       "8    2020      Female  187488\n",
       "9    2020        Male  191811\n",
       "10   2021      Female  193073\n",
       "11   2021        Male  196250\n",
       "12   2022      Female  122311\n",
       "13   2022        Male  111793\n",
       "14   2023      Female  148941\n",
       "15   2023        Male  139994"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of students that took test by year and by gender\n",
    "# Needed columns:outid, sextypename, year -> from students\n",
    "query = \"\"\"\n",
    "SELECT years, sextypename,  COUNT(outid) as NUM FROM students\n",
    "GROUP BY years, sextypename\n",
    "ORDER BY years, sextypename;\n",
    "\"\"\"\n",
    "df = pd.read_sql(query, engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>region</th>\n",
       "      <th>years</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cherkaska</td>\n",
       "      <td>2016</td>\n",
       "      <td>8078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cherkaska</td>\n",
       "      <td>2017</td>\n",
       "      <td>7234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cherkaska</td>\n",
       "      <td>2018</td>\n",
       "      <td>8733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cherkaska</td>\n",
       "      <td>2019</td>\n",
       "      <td>9099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cherkaska</td>\n",
       "      <td>2020</td>\n",
       "      <td>11216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>Zhytomyrskа</td>\n",
       "      <td>2019</td>\n",
       "      <td>11757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>Zhytomyrskа</td>\n",
       "      <td>2020</td>\n",
       "      <td>12508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>Zhytomyrskа</td>\n",
       "      <td>2021</td>\n",
       "      <td>12933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Zhytomyrskа</td>\n",
       "      <td>2022</td>\n",
       "      <td>8108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>Zhytomyrskа</td>\n",
       "      <td>2023</td>\n",
       "      <td>10402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>200 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          region  years  count\n",
       "0      Cherkaska   2016   8078\n",
       "1      Cherkaska   2017   7234\n",
       "2      Cherkaska   2018   8733\n",
       "3      Cherkaska   2019   9099\n",
       "4      Cherkaska   2020  11216\n",
       "..           ...    ...    ...\n",
       "195  Zhytomyrskа   2019  11757\n",
       "196  Zhytomyrskа   2020  12508\n",
       "197  Zhytomyrskа   2021  12933\n",
       "198  Zhytomyrskа   2022   8108\n",
       "199  Zhytomyrskа   2023  10402\n",
       "\n",
       "[200 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of students that took tests by region by year\n",
    "query = \"\"\"\n",
    "SELECT region, years, COUNT(outid) FROM (\n",
    "    SELECT students.outid, students.years, locations.english_name as region FROM students\n",
    "    INNER JOIN locations ON CONCAT(SUBSTRING(students.KATOTTG_2023_school, 3, 2), '00000000')=locations.KOATUU_2020\n",
    "    WHERE starts_with(students.KATOTTG_2023_school, 'UA')\n",
    ")\n",
    "GROUP BY region, years\n",
    "ORDER BY region, years;\n",
    "\"\"\"\n",
    "df = pd.read_sql(query, engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>years</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>2021</th>\n",
       "      <th>2022</th>\n",
       "      <th>2023</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cherkaska</th>\n",
       "      <td>8078</td>\n",
       "      <td>7234</td>\n",
       "      <td>8733</td>\n",
       "      <td>9099</td>\n",
       "      <td>11216</td>\n",
       "      <td>10853</td>\n",
       "      <td>6967</td>\n",
       "      <td>8962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chernihivska</th>\n",
       "      <td>7557</td>\n",
       "      <td>6777</td>\n",
       "      <td>8533</td>\n",
       "      <td>8471</td>\n",
       "      <td>9047</td>\n",
       "      <td>8978</td>\n",
       "      <td>6054</td>\n",
       "      <td>7155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chernivetska</th>\n",
       "      <td>6804</td>\n",
       "      <td>6095</td>\n",
       "      <td>8981</td>\n",
       "      <td>8980</td>\n",
       "      <td>9457</td>\n",
       "      <td>9603</td>\n",
       "      <td>5153</td>\n",
       "      <td>6869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dnipropetrovska</th>\n",
       "      <td>21725</td>\n",
       "      <td>19207</td>\n",
       "      <td>24800</td>\n",
       "      <td>29263</td>\n",
       "      <td>31833</td>\n",
       "      <td>32479</td>\n",
       "      <td>20859</td>\n",
       "      <td>23610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Donetska</th>\n",
       "      <td>11427</td>\n",
       "      <td>10238</td>\n",
       "      <td>14365</td>\n",
       "      <td>15098</td>\n",
       "      <td>16235</td>\n",
       "      <td>16211</td>\n",
       "      <td>6284</td>\n",
       "      <td>5240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ivano-Frankivska</th>\n",
       "      <td>9381</td>\n",
       "      <td>8396</td>\n",
       "      <td>12948</td>\n",
       "      <td>13556</td>\n",
       "      <td>14770</td>\n",
       "      <td>15484</td>\n",
       "      <td>9161</td>\n",
       "      <td>12023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kharkivska</th>\n",
       "      <td>16901</td>\n",
       "      <td>15329</td>\n",
       "      <td>21251</td>\n",
       "      <td>21644</td>\n",
       "      <td>23706</td>\n",
       "      <td>24311</td>\n",
       "      <td>13715</td>\n",
       "      <td>16305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Khersonska</th>\n",
       "      <td>8190</td>\n",
       "      <td>7032</td>\n",
       "      <td>9190</td>\n",
       "      <td>9781</td>\n",
       "      <td>10248</td>\n",
       "      <td>10352</td>\n",
       "      <td>2300</td>\n",
       "      <td>2529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Khmelnytska</th>\n",
       "      <td>8896</td>\n",
       "      <td>7918</td>\n",
       "      <td>11756</td>\n",
       "      <td>11641</td>\n",
       "      <td>11917</td>\n",
       "      <td>12641</td>\n",
       "      <td>8345</td>\n",
       "      <td>10820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kirovohradska</th>\n",
       "      <td>6652</td>\n",
       "      <td>5797</td>\n",
       "      <td>7889</td>\n",
       "      <td>8026</td>\n",
       "      <td>8779</td>\n",
       "      <td>8255</td>\n",
       "      <td>5560</td>\n",
       "      <td>6701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kyiv</th>\n",
       "      <td>20086</td>\n",
       "      <td>19604</td>\n",
       "      <td>28432</td>\n",
       "      <td>30202</td>\n",
       "      <td>32407</td>\n",
       "      <td>33735</td>\n",
       "      <td>24428</td>\n",
       "      <td>30568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kyivska</th>\n",
       "      <td>12385</td>\n",
       "      <td>10651</td>\n",
       "      <td>12972</td>\n",
       "      <td>13543</td>\n",
       "      <td>15331</td>\n",
       "      <td>16676</td>\n",
       "      <td>12550</td>\n",
       "      <td>14253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Luhanska</th>\n",
       "      <td>4314</td>\n",
       "      <td>3909</td>\n",
       "      <td>5880</td>\n",
       "      <td>5828</td>\n",
       "      <td>6079</td>\n",
       "      <td>5941</td>\n",
       "      <td>1528</td>\n",
       "      <td>1155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lvivska</th>\n",
       "      <td>17365</td>\n",
       "      <td>16125</td>\n",
       "      <td>21393</td>\n",
       "      <td>25537</td>\n",
       "      <td>27063</td>\n",
       "      <td>28250</td>\n",
       "      <td>18857</td>\n",
       "      <td>23216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mykolaivska</th>\n",
       "      <td>7509</td>\n",
       "      <td>6784</td>\n",
       "      <td>9689</td>\n",
       "      <td>9614</td>\n",
       "      <td>10357</td>\n",
       "      <td>10492</td>\n",
       "      <td>5466</td>\n",
       "      <td>6914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Odeska</th>\n",
       "      <td>15920</td>\n",
       "      <td>14799</td>\n",
       "      <td>20651</td>\n",
       "      <td>22313</td>\n",
       "      <td>23545</td>\n",
       "      <td>24569</td>\n",
       "      <td>15195</td>\n",
       "      <td>18263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poltavska</th>\n",
       "      <td>9937</td>\n",
       "      <td>8839</td>\n",
       "      <td>11749</td>\n",
       "      <td>12470</td>\n",
       "      <td>13077</td>\n",
       "      <td>12800</td>\n",
       "      <td>8501</td>\n",
       "      <td>11311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rivnenska</th>\n",
       "      <td>10408</td>\n",
       "      <td>9287</td>\n",
       "      <td>12883</td>\n",
       "      <td>12765</td>\n",
       "      <td>13837</td>\n",
       "      <td>14657</td>\n",
       "      <td>8903</td>\n",
       "      <td>11081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sumska</th>\n",
       "      <td>7372</td>\n",
       "      <td>6468</td>\n",
       "      <td>9334</td>\n",
       "      <td>9334</td>\n",
       "      <td>9798</td>\n",
       "      <td>10083</td>\n",
       "      <td>6634</td>\n",
       "      <td>8308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ternopilska</th>\n",
       "      <td>6719</td>\n",
       "      <td>5785</td>\n",
       "      <td>10278</td>\n",
       "      <td>10367</td>\n",
       "      <td>10754</td>\n",
       "      <td>11139</td>\n",
       "      <td>7227</td>\n",
       "      <td>9247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vinnytska</th>\n",
       "      <td>11084</td>\n",
       "      <td>9368</td>\n",
       "      <td>14671</td>\n",
       "      <td>15584</td>\n",
       "      <td>16209</td>\n",
       "      <td>16250</td>\n",
       "      <td>10512</td>\n",
       "      <td>12876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Volynska</th>\n",
       "      <td>8245</td>\n",
       "      <td>7256</td>\n",
       "      <td>11192</td>\n",
       "      <td>11295</td>\n",
       "      <td>11925</td>\n",
       "      <td>12664</td>\n",
       "      <td>7728</td>\n",
       "      <td>10411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zakarpatska</th>\n",
       "      <td>9478</td>\n",
       "      <td>9176</td>\n",
       "      <td>11824</td>\n",
       "      <td>12293</td>\n",
       "      <td>12916</td>\n",
       "      <td>13327</td>\n",
       "      <td>6711</td>\n",
       "      <td>8406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zaporizka</th>\n",
       "      <td>12001</td>\n",
       "      <td>10597</td>\n",
       "      <td>14585</td>\n",
       "      <td>15352</td>\n",
       "      <td>16285</td>\n",
       "      <td>16640</td>\n",
       "      <td>7358</td>\n",
       "      <td>8616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zhytomyrskа</th>\n",
       "      <td>9568</td>\n",
       "      <td>8218</td>\n",
       "      <td>11708</td>\n",
       "      <td>11757</td>\n",
       "      <td>12508</td>\n",
       "      <td>12933</td>\n",
       "      <td>8108</td>\n",
       "      <td>10402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "years              2016   2017   2018   2019   2020   2021   2022   2023\n",
       "region                                                                  \n",
       "Cherkaska          8078   7234   8733   9099  11216  10853   6967   8962\n",
       "Chernihivska       7557   6777   8533   8471   9047   8978   6054   7155\n",
       "Chernivetska       6804   6095   8981   8980   9457   9603   5153   6869\n",
       "Dnipropetrovska   21725  19207  24800  29263  31833  32479  20859  23610\n",
       "Donetska          11427  10238  14365  15098  16235  16211   6284   5240\n",
       "Ivano-Frankivska   9381   8396  12948  13556  14770  15484   9161  12023\n",
       "Kharkivska        16901  15329  21251  21644  23706  24311  13715  16305\n",
       "Khersonska         8190   7032   9190   9781  10248  10352   2300   2529\n",
       "Khmelnytska        8896   7918  11756  11641  11917  12641   8345  10820\n",
       "Kirovohradska      6652   5797   7889   8026   8779   8255   5560   6701\n",
       "Kyiv              20086  19604  28432  30202  32407  33735  24428  30568\n",
       "Kyivska           12385  10651  12972  13543  15331  16676  12550  14253\n",
       "Luhanska           4314   3909   5880   5828   6079   5941   1528   1155\n",
       "Lvivska           17365  16125  21393  25537  27063  28250  18857  23216\n",
       "Mykolaivska        7509   6784   9689   9614  10357  10492   5466   6914\n",
       "Odeska            15920  14799  20651  22313  23545  24569  15195  18263\n",
       "Poltavska          9937   8839  11749  12470  13077  12800   8501  11311\n",
       "Rivnenska         10408   9287  12883  12765  13837  14657   8903  11081\n",
       "Sumska             7372   6468   9334   9334   9798  10083   6634   8308\n",
       "Ternopilska        6719   5785  10278  10367  10754  11139   7227   9247\n",
       "Vinnytska         11084   9368  14671  15584  16209  16250  10512  12876\n",
       "Volynska           8245   7256  11192  11295  11925  12664   7728  10411\n",
       "Zakarpatska        9478   9176  11824  12293  12916  13327   6711   8406\n",
       "Zaporizka         12001  10597  14585  15352  16285  16640   7358   8616\n",
       "Zhytomyrskа        9568   8218  11708  11757  12508  12933   8108  10402"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pandas transfor table to pivot table\n",
    "df.pivot(index='region', columns='years', values='count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}