SQLite_App.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python SQL toolkit and Object Relational Mapper\n",
    "import sqlalchemy\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "from sqlalchemy.orm import Session\n",
    "from sqlalchemy import create_engine, inspect, func\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create engine using the `demographics.sqlite` database file\n",
    "engine = create_engine(\"sqlite:///USEnergy.sqlite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Declare a Base using `automap_base()`\n",
    "Base = automap_base()\n",
    "# Use the Base class to reflect the database tables\n",
    "Base.prepare(engine, reflect=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "# Print all of the classes mapped to the Base\n",
    "print(Base.classes.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AK Generation', 'AL Generation', 'AR Generation', 'AZ Generation', 'Average Energy Prices', 'CA Generation', 'CO Generation', 'CT Generation', 'DC Generation', 'DE Generation', 'Electric Power Consumption', 'End User Consumption', 'FL Generation', 'GA Generation', 'IA Generation', 'ID Generation', 'IL Generation', 'IN Generation', 'KS Generation', 'KY Generation', 'LA Generation', 'MA Generation', 'MD Generation', 'ME Generation', 'MI Generation', 'MN Generation', 'MO Generation', 'MS Generation', 'MT Generation', 'NC Generation', 'ND Generation', 'NE Generation', 'NH Generation', 'NJ Generation', 'NM Generation', 'NV Generation', 'NY Generation', 'OH Generation', 'OK Generation', 'OR Generation', 'PA Generation', 'RI Generation', 'SC Generation', 'SD Generation', 'TN Generation', 'TX Generation', 'UT Generation', 'VA Generation', 'VT Generation', 'WA Generation', 'WI Generation', 'WV Generation', 'WY Generation']\n"
     ]
    }
   ],
   "source": [
    "# Use the Inspector to explore the database and print the table names\n",
    "inspector = inspect(engine)\n",
    "tables = inspector.get_table_names()\n",
    "print(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Natural Gas         Coal  Petroleum Liquids  Petroleum Coke  \\\n",
      "index                                                                \n",
      "2001    9612.68200  72198.11400          434.54900               0   \n",
      "2002   15904.06500  72041.66400          312.04800               0   \n",
      "2003   12243.59800  76696.39100          336.64400               0   \n",
      "2004   15977.01923  74831.62435          295.02495               0   \n",
      "2005   13869.45085  78128.45747          333.11034               0   \n",
      "2006   19407.22170  78108.66818          180.16601               0   \n",
      "2007   23231.77629  77993.76036          156.92188               0   \n",
      "2008   22362.91875  74604.64796          204.26488               0   \n",
      "2009   31617.08268  55608.72382          219.27368               0   \n",
      "2010   39235.01714  63050.41062          199.85355               0   \n",
      "2011   47681.05394  56807.35302          120.38403               0   \n",
      "2012   55705.00044  45606.91978          109.58316               0   \n",
      "2013   46586.38525  47050.48393           74.13097               0   \n",
      "2014   48270.07438  47301.62625           98.09998               0   \n",
      "2015   55844.45670  41410.42949           84.20802               0   \n",
      "2016   57803.61849  34258.40552           45.83237               0   \n",
      "2017   52983.66286  31440.40111           35.70787               0   \n",
      "2018   58800.41864  31777.52001           65.70354               0   \n",
      "2019   56628.34875  26694.35624           19.06368               0   \n",
      "\n",
      "       Other Gasses  Nuclear  Hydroelectric  Wind  \\\n",
      "index                                               \n",
      "2001              0        0              0     0   \n",
      "2002              0        0              0     0   \n",
      "2003              0        0              0     0   \n",
      "2004              0        0              0     0   \n",
      "2005              0        0              0     0   \n",
      "2006              0        0              0     0   \n",
      "2007              0        0              0     0   \n",
      "2008              0        0              0     0   \n",
      "2009              0        0              0     0   \n",
      "2010              0        0              0     0   \n",
      "2011              0        0              0     0   \n",
      "2012              0        0              0     0   \n",
      "2013              0        0              0     0   \n",
      "2014              0        0              0     0   \n",
      "2015              0        0              0     0   \n",
      "2016              0        0              0     0   \n",
      "2017              0        0              0     0   \n",
      "2018              0        0              0     0   \n",
      "2019              0        0              0     0   \n",
      "\n",
      "       Wood and Wood Derived Fuels  Other Biomass  Geothermal  \\\n",
      "index                                                           \n",
      "2001                             0              0           0   \n",
      "2002                             0              0           0   \n",
      "2003                             0              0           0   \n",
      "2004                             0              0           0   \n",
      "2005                             0              0           0   \n",
      "2006                             0              0           0   \n",
      "2007                             0              0           0   \n",
      "2008                             0              0           0   \n",
      "2009                             0              0           0   \n",
      "2010                             0              0           0   \n",
      "2011                             0              0           0   \n",
      "2012                             0              0           0   \n",
      "2013                             0              0           0   \n",
      "2014                             0              0           0   \n",
      "2015                             0              0           0   \n",
      "2016                             0              0           0   \n",
      "2017                             0              0           0   \n",
      "2018                             0              0           0   \n",
      "2019                             0              0           0   \n",
      "\n",
      "       All Other Renewables  All Solar  Other  Utility Scale Thermal  \\\n",
      "index                                                                  \n",
      "2001                      0          0      0                      0   \n",
      "2002                      0          0      0                      0   \n",
      "2003                      0          0      0                      0   \n",
      "2004                      0          0      0                      0   \n",
      "2005                      0          0      0                      0   \n",
      "2006                      0          0      0                      0   \n",
      "2007                      0          0      0                      0   \n",
      "2008                      0          0      0                      0   \n",
      "2009                      0          0      0                      0   \n",
      "2010                      0          0      0                      0   \n",
      "2011                      0          0      0                      0   \n",
      "2012                      0          0      0                      0   \n",
      "2013                      0          0      0                      0   \n",
      "2014                      0          0      0                      0   \n",
      "2015                      0          0      0                      0   \n",
      "2016                      0          0      0                      0   \n",
      "2017                      0          0      0                      0   \n",
      "2018                      0          0      0                      0   \n",
      "2019                      0          0      0                      0   \n",
      "\n",
      "       Hydroelectic Pumped Storage  \n",
      "index                               \n",
      "2001                             0  \n",
      "2002                             0  \n",
      "2003                             0  \n",
      "2004                             0  \n",
      "2005                             0  \n",
      "2006                             0  \n",
      "2007                             0  \n",
      "2008                             0  \n",
      "2009                             0  \n",
      "2010                             0  \n",
      "2011                             0  \n",
      "2012                             0  \n",
      "2013                             0  \n",
      "2014                             0  \n",
      "2015                             0  \n",
      "2016                             0  \n",
      "2017                             0  \n",
      "2018                             0  \n",
      "2019                             0  \n"
     ]
    }
   ],
   "source": [
    "state= \"AL\"\n",
    "tables=pd.read_sql_query(f\"SELECT * from '{state} Generation'\", con=engine) #State Initials to display table.\n",
    "tables.set_index(\"index\",inplace = True)\n",
    "print(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['index', 'AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY']\n"
     ]
    }
   ],
   "source": [
    "columns = inspector.get_columns('Average Energy Prices')    \n",
    "states = []\n",
    "for column in columns:\n",
    "    states.append(column[\"name\"])\n",
    "print(states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>index</th>\n",
       "      <th>AL</th>\n",
       "      <th>AK</th>\n",
       "      <th>AZ</th>\n",
       "      <th>AR</th>\n",
       "      <th>CA</th>\n",
       "      <th>CO</th>\n",
       "      <th>CT</th>\n",
       "      <th>DC</th>\n",
       "      <th>DE</th>\n",
       "      <th>...</th>\n",
       "      <th>SD</th>\n",
       "      <th>TN</th>\n",
       "      <th>TX</th>\n",
       "      <th>UT</th>\n",
       "      <th>VT</th>\n",
       "      <th>VA</th>\n",
       "      <th>WA</th>\n",
       "      <th>WV</th>\n",
       "      <th>WI</th>\n",
       "      <th>WY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018</td>\n",
       "      <td>9.63</td>\n",
       "      <td>19.36</td>\n",
       "      <td>10.85</td>\n",
       "      <td>7.78</td>\n",
       "      <td>16.58</td>\n",
       "      <td>10.02</td>\n",
       "      <td>18.41</td>\n",
       "      <td>12.03</td>\n",
       "      <td>10.55</td>\n",
       "      <td>...</td>\n",
       "      <td>9.97</td>\n",
       "      <td>9.58</td>\n",
       "      <td>8.48</td>\n",
       "      <td>8.21</td>\n",
       "      <td>15.13</td>\n",
       "      <td>9.48</td>\n",
       "      <td>8</td>\n",
       "      <td>8.72</td>\n",
       "      <td>10.58</td>\n",
       "      <td>8.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 52 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  index    AL     AK     AZ    AR     CA     CO     CT     DC     DE  ...  \\\n",
       "1  2018  9.63  19.36  10.85  7.78  16.58  10.02  18.41  12.03  10.55  ...   \n",
       "\n",
       "     SD    TN    TX    UT     VT    VA  WA    WV     WI    WY  \n",
       "1  9.97  9.58  8.48  8.21  15.13  9.48   8  8.72  10.58  8.09  \n",
       "\n",
       "[1 rows x 52 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "year = 2018\n",
    "prices = pd.read_sql_query(\"SELECT * from 'Average Energy Prices'\",con = engine)\n",
    "#prices.rename(columns = {\"index\": \"Years\"},  \n",
    "          #inplace = True) \n",
    "year_df = prices.loc[prices['index'] == str(year)]\n",
    "year_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index\n",
       "2019    9.88\n",
       "2018    9.63\n",
       "2017    9.83\n",
       "2016    9.56\n",
       "2015    9.33\n",
       "2014    9.27\n",
       "2013    9.02\n",
       "2012    9.18\n",
       "2011    9.10\n",
       "2010    8.89\n",
       "2009    8.83\n",
       "2008    8.59\n",
       "2007    7.57\n",
       "2006    7.07\n",
       "2005    6.46\n",
       "2004    6.08\n",
       "2003    5.88\n",
       "2002    5.71\n",
       "2001    5.60\n",
       "Name: AL, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = \"AL\"\n",
    "prices = pd.read_sql_query(\"SELECT * from 'Average Energy Prices'\",con = engine)\n",
    "prices.set_index(\"index\",inplace =True)\n",
    "state_df = prices.loc[:,state]\n",
    "state_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>index</th>\n",
       "      <th>Coal</th>\n",
       "      <th>Naturalgas</th>\n",
       "      <th>Petroleum</th>\n",
       "      <th>Renewableenergy</th>\n",
       "      <th>Electricity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>4.3</td>\n",
       "      <td>23.3</td>\n",
       "      <td>38.9</td>\n",
       "      <td>11.6</td>\n",
       "      <td>21.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>1.2</td>\n",
       "      <td>55.8</td>\n",
       "      <td>37.8</td>\n",
       "      <td>1.6</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>0.7</td>\n",
       "      <td>10.7</td>\n",
       "      <td>58.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>27.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>0.5</td>\n",
       "      <td>26.2</td>\n",
       "      <td>43.0</td>\n",
       "      <td>9.3</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>0.5</td>\n",
       "      <td>24.7</td>\n",
       "      <td>57.7</td>\n",
       "      <td>3.4</td>\n",
       "      <td>13.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "index  Coal  Naturalgas  Petroleum  Renewableenergy  Electricity\n",
       "AL      4.3        23.3       38.9             11.6         21.9\n",
       "AK      1.2        55.8       37.8              1.6          3.5\n",
       "AZ      0.7        10.7       58.0              3.3         27.4\n",
       "AR      0.5        26.2       43.0              9.3         21.0\n",
       "CA      0.5        24.7       57.7              3.4         13.7"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_df = pd.read_sql_query(\"SELECT * from 'End User Consumption'\",con = engine)\n",
    "total_df.set_index(\"index\",inplace=True)\n",
    "df=total_df.T\n",
    "df[\"Coal\"] = list(map(lambda x: x[1:-2], df[\"Coal\"].values))\n",
    "df['Coal'] = pd.to_numeric(df.Coal)\n",
    "df[\"Naturalgas\"] = list(map(lambda x: x[1:-2], df[\"Naturalgas\"].values))\n",
    "df['Naturalgas'] = pd.to_numeric(df.Naturalgas)\n",
    "df[\"Petroleum\"] = list(map(lambda x: x[1:-2], df[\"Petroleum\"].values))\n",
    "df['Petroleum'] = pd.to_numeric(df.Petroleum)\n",
    "df[\"Renewableenergy\"] = list(map(lambda x: x[1:-2], df[\"Renewableenergy\"].values))\n",
    "df['Renewableenergy'] = pd.to_numeric(df.Renewableenergy)\n",
    "df[\"Electricity\"] = list(map(lambda x: x[1:-2], df[\"Electricity\"].values))\n",
    "df['Electricity'] = pd.to_numeric(df.Electricity)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>index</th>\n",
       "      <th>Coal</th>\n",
       "      <th>Naturalgas</th>\n",
       "      <th>Petroleum</th>\n",
       "      <th>Renewableenergy</th>\n",
       "      <th>Nuclearpower</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>24.7</td>\n",
       "      <td>34.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>8.4</td>\n",
       "      <td>32.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>18.7</td>\n",
       "      <td>45.6</td>\n",
       "      <td>8.7</td>\n",
       "      <td>26.9</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>30.5</td>\n",
       "      <td>27.8</td>\n",
       "      <td>0.1</td>\n",
       "      <td>11.1</td>\n",
       "      <td>30.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>48.3</td>\n",
       "      <td>25.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>5.2</td>\n",
       "      <td>21.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>39.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>48.7</td>\n",
       "      <td>11.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "index  Coal  Naturalgas  Petroleum  Renewableenergy  Nuclearpower\n",
       "AL     24.7        34.6        0.1              8.4          32.2\n",
       "AK     18.7        45.6        8.7             26.9           0.0\n",
       "AZ     30.5        27.8        0.1             11.1          30.5\n",
       "AR     48.3        25.1        0.1              5.2          21.4\n",
       "CA      0.0        39.4        0.0             48.7          11.8"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_df = pd.read_sql_query(\"SELECT * from 'Electric Power Consumption'\",con = engine)\n",
    "total_df.set_index(\"index\",inplace=True)\n",
    "df=total_df.T\n",
    "df[\"Coal\"] = list(map(lambda x: x[1:-2], df[\"Coal\"].values))\n",
    "df['Coal'] = pd.to_numeric(df.Coal)\n",
    "df[\"Naturalgas\"] = list(map(lambda x: x[1:-2], df[\"Naturalgas\"].values))\n",
    "df['Naturalgas'] = pd.to_numeric(df.Naturalgas)\n",
    "df[\"Petroleum\"] = list(map(lambda x: x[1:-2], df[\"Petroleum\"].values))\n",
    "df['Petroleum'] = pd.to_numeric(df.Petroleum)\n",
    "df[\"Renewableenergy\"] = list(map(lambda x: x[1:-2], df[\"Renewableenergy\"].values))\n",
    "df['Renewableenergy'] = pd.to_numeric(df.Renewableenergy)\n",
    "df[\"Nuclearpower\"] = list(map(lambda x: x[1:-2], df[\"Nuclearpower\"].values))\n",
    "df['Nuclearpower'] = pd.to_numeric(df.Nuclearpower)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}