SFO_forecast_EDA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1202,
   "id": "52a338fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "import plotly.express as px\n",
    "from sklearn.linear_model import LinearRegression, ElasticNet\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from xgboost import XGBRegressor\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1203,
   "id": "97467200",
   "metadata": {},
   "outputs": [],
   "source": [
    "faa = pd.read_csv(\"data/FAA/FAA_WEB-Report-22188.csv\")\n",
    "bls_oak = pd.read_excel(\"data/BLS/SeriesReport-OAK-HWD-BKL.xlsx\", skiprows=10)\n",
    "bls_sf = pd.read_excel(\"data/BLS/SeriesReport-SF-RWC-SSF.xlsx\", skiprows=10)\n",
    "bls_sj = pd.read_excel(\"data/BLS/SeriesReport-SJ-SVL-SNC.xlsx\", skiprows=10)\n",
    "covid_h = pd.read_csv(\"data/CitySF/COVID-19_Hospitalizations_Over_Time_20240830.csv\")\n",
    "covid_v = pd.read_csv(\"data/CitySF/ARCHIVED__COVID-19_Vaccinations_Given_to_SF_Residents_Over_Time_20240830.csv\")\n",
    "pop = pd.read_csv(\"data/FRED/CASANF0POP.csv\")\n",
    "pba2050 = pd.read_csv(\"data/PBA2050/PBA_growth_profiles_normalized_2022.csv\")\n",
    "taf2023 = pd.read_excel(\"data/FAA/AirportsOperations_SFO.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ece2fd6",
   "metadata": {},
   "source": [
    "**Subset FAA data for SFO commercial operations only**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1204,
   "id": "f015b3a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "faa_sfo = faa.copy().loc[faa.Airport==\"SFO\"]\n",
    "faa_sfo['Commercial_Ops'] = faa_sfo['AirCarrier']+faa_sfo['AirTaxi']\n",
    "faa_sfo = faa_sfo.copy()[['Year','Commercial_Ops']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c057b6ee",
   "metadata": {},
   "source": [
    "**Aggregate BLS data for three metropolitan areas for total Bay Area employment**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1205,
   "id": "1ee6b576",
   "metadata": {},
   "outputs": [],
   "source": [
    "def bls_avg(bls):\n",
    "    '''\n",
    "    Calculates average employment count for each year of BLS data.\n",
    "    '''\n",
    "    bls_melt = bls.melt(id_vars=['Year'], var_name='Month', value_name='Employment')\n",
    "    bls_avg = bls_melt.groupby(['Year']).mean().reset_index()\n",
    "    \n",
    "    return bls_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1206,
   "id": "e48a6922",
   "metadata": {},
   "outputs": [],
   "source": [
    "bls_oak_avg = bls_avg(bls_oak)\n",
    "bls_sf_avg = bls_avg(bls_sf)\n",
    "bls_sj_avg = bls_avg(bls_sj)\n",
    "bls_all = bls_oak_avg.merge(bls_sf_avg, on='Year')\n",
    "bls_all = bls_all.merge(bls_sj_avg, on='Year')\n",
    "bls_all['Employment'] = bls_all['Employment_x']+bls_all['Employment_y']+bls_all['Employment']\n",
    "bls_all.drop(columns=['Employment_x','Employment_y'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57a356a2",
   "metadata": {},
   "source": [
    "**Aggregate COVID-19 hospitalizations and vaccinations by year**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1207,
   "id": "2720a5cf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "covid_h['reportdate'] = pd.to_datetime(covid_h['reportdate'], format=\"%Y/%m/%d\")\n",
    "covid_h['Year'] = covid_h['reportdate'].apply(lambda x:x.year)\n",
    "covid_h['patientcount'] = covid_h['patientcount']/covid_h['hospitalreportingpct']\n",
    "covid_h_sum = covid_h.groupby(['Year']).sum()[['patientcount']].reset_index()\n",
    "covid_h_sum.rename(columns={'patientcount':'covid_h'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1208,
   "id": "0d31b638",
   "metadata": {},
   "outputs": [],
   "source": [
    "covid_v = covid_v.loc[covid_v['administering_provider_type']==\"All Providers\"]\n",
    "covid_v['date_administered'] = pd.to_datetime(covid_v['date_administered'], format=\"%Y/%m/%d\")\n",
    "covid_v['Year'] = covid_v['date_administered'].apply(lambda x:x.year)\n",
    "covid_v_avg = covid_v.groupby(['Year']).mean()[['cumulative_recipients']].reset_index()\n",
    "covid_v_avg.rename(columns={'cumulative_recipients':'covid_v'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2424d36c",
   "metadata": {},
   "source": [
    "**Reformat population data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1209,
   "id": "faa9f6a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pop['DATE'] = pd.to_datetime(pop['DATE'], format=\"%Y-%m-%d\")\n",
    "pop['Year'] = pop['DATE'].apply(lambda x:x.year)\n",
    "pop['Pop'] = pop['CASANF0POP']*1000\n",
    "pop = pop[['Year','Pop']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59963112",
   "metadata": {},
   "source": [
    "**Merge datasets and output correlations with Commercial_Ops**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1210,
   "id": "6bb82cc7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data = faa_sfo.merge(bls_all, on='Year')\n",
    "data = data.merge(covid_h_sum, on='Year', how='left')\n",
    "data = data.merge(covid_v_avg, on='Year', how='left')\n",
    "data = data.merge(pop, on='Year', how='left')\n",
    "data = data.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bf332c8",
   "metadata": {},
   "source": [
    "**Create new variable for non-vaccinated population**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1211,
   "id": "d2e253f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['non_vax_pop'] = data['Pop']-data['covid_v']\n",
    "data['non_vax_pop'] = data.apply(lambda x:0 if x.Year<2020 else x.non_vax_pop, axis=1)\n",
    "data['non_vax_pop'] = data['non_vax_pop'].apply(lambda x:0 if x<0 else x)\n",
    "data.drop(columns=['covid_v','Pop'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5cc3364",
   "metadata": {},
   "source": [
    "**Add dummy variable for post-9/11 effects and output correlations to Commercial_Ops**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1212,
   "id": "18038958",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['dummy_911'] = data.apply(lambda x:1 if x.Year in [2001,2002,2003] else 0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1213,
   "id": "c1277037",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['dummy_covid'] = data.apply(lambda x:1 if x.Year in [2020,2021] else 0, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14e6c22b",
   "metadata": {},
   "source": [
    "**Output correlations of variables with Commercial_Ops**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1214,
   "id": "c1187f83",
   "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>Commercial_Ops</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <td>-0.140069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Commercial_Ops</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Employment</th>\n",
       "      <td>0.084124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covid_h</th>\n",
       "      <td>-0.593244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>non_vax_pop</th>\n",
       "      <td>-0.669679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dummy_911</th>\n",
       "      <td>-0.257498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dummy_covid</th>\n",
       "      <td>-0.714581</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Commercial_Ops\n",
       "Year                 -0.140069\n",
       "Commercial_Ops        1.000000\n",
       "Employment            0.084124\n",
       "covid_h              -0.593244\n",
       "non_vax_pop          -0.669679\n",
       "dummy_911            -0.257498\n",
       "dummy_covid          -0.714581"
      ]
     },
     "execution_count": 1214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.corr()[['Commercial_Ops']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebc712ca",
   "metadata": {},
   "source": [
    "**Correlations for data prior to 2019 only**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1215,
   "id": "053dedfd",
   "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>Commercial_Ops</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <td>0.313257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Commercial_Ops</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Employment</th>\n",
       "      <td>0.589422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covid_h</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>non_vax_pop</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dummy_911</th>\n",
       "      <td>-0.484711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dummy_covid</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Commercial_Ops\n",
       "Year                  0.313257\n",
       "Commercial_Ops        1.000000\n",
       "Employment            0.589422\n",
       "covid_h                    NaN\n",
       "non_vax_pop                NaN\n",
       "dummy_911            -0.484711\n",
       "dummy_covid                NaN"
      ]
     },
     "execution_count": 1215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2019 = data.loc[data.Year<=2019]\n",
    "data_2019.corr()[['Commercial_Ops']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66fb56dd",
   "metadata": {},
   "source": [
    "**Correlations for data prior to 2019 and not including 2001-2003**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1216,
   "id": "29d046a3",
   "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>Commercial_Ops</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <td>0.32121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Commercial_Ops</th>\n",
       "      <td>1.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Employment</th>\n",
       "      <td>0.65008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>covid_h</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>non_vax_pop</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dummy_911</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dummy_covid</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Commercial_Ops\n",
       "Year                   0.32121\n",
       "Commercial_Ops         1.00000\n",
       "Employment             0.65008\n",
       "covid_h                    NaN\n",
       "non_vax_pop                NaN\n",
       "dummy_911                  NaN\n",
       "dummy_covid                NaN"
      ]
     },
     "execution_count": 1216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_normal_yr = data_2019.loc[~data.Year.isin([2001,2002,2003])]\n",
    "data_normal_yr.corr()[['Commercial_Ops']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1c582ee",
   "metadata": {},
   "source": [
    "**Create train/test sets, use years after 2000 (results in better regression)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1217,
   "id": "38ad0532",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.loc[data.Year >= 2000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1218,
   "id": "74d6db12",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_years = [1991,1994,1996,1999,2002,2005,2008,2011,2014,2017,2022]\n",
    "data_test = data.loc[data.Year.isin(test_years)]\n",
    "data_train = data.loc[~data.Year.isin(test_years)]\n",
    "X_cols = ['Year','Employment','covid_h','non_vax_pop','dummy_911','dummy_covid']\n",
    "y_col = ['Commercial_Ops']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1219,
   "id": "ee701132",
   "metadata": {},
   "outputs": [],
   "source": [
    "X = data[X_cols]\n",
    "y = np.ravel(data[y_col])\n",
    "X_train = data_train[X_cols]\n",
    "X_test = data_test[X_cols]\n",
    "y_train = np.ravel(data_train[y_col])\n",
    "y_test = np.ravel(data_test[y_col])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2136eae9",
   "metadata": {},
   "source": [
    "**Train and score regression model**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1220,
   "id": "31990423",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "R2 on train set: 0.988\n",
      "R2 on test set: 0.976\n"
     ]
    }
   ],
   "source": [
    "reg = LinearRegression().fit(X_train,y_train)\n",
    "print(\"R2 on train set: {}\".format(round(reg.score(X_train,y_train),3)))\n",
    "print(\"R2 on test set: {}\".format(round(reg.score(X_test,y_test),3)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6f82eca",
   "metadata": {},
   "source": [
    "**Plot actual vs. predicted operations**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1221,
   "id": "a8ff8cd9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "hovertemplate": "Variable=Commercial_Ops_actual<br>Year=%{x}<br>Operations=%{y}<extra></extra>",
         "legendgroup": "Commercial_Ops_actual",
         "line": {
          "color": "#636efa",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "Commercial_Ops_actual",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          2000,
          2001,
          2002,
          2003,
          2004,
          2005,
          2006,
          2007,
          2008,
          2009,
          2010,
          2011,
          2012,
          2013,
          2014,
          2015,
          2016,
          2017,
          2018,
          2019,
          2020,
          2021,
          2022,
          2023
         ],
         "xaxis": "x",
         "y": [
          401249,
          367471,
          331880,
          315185,
          332818,
          331515,
          338147,
          357717,
          369877,
          364242,
          371890,
          386941,
          406650,
          405331,
          416342,
          414300,
          435129,
          445842,
          455798,
          444786,
          223098,
          255427,
          343045,
          375078
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "Variable=Commercial_Ops_predicted<br>Year=%{x}<br>Operations=%{y}<extra></extra>",
         "legendgroup": "Commercial_Ops_predicted",
         "line": {
          "color": "#EF553B",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "Commercial_Ops_predicted",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          2000,
          2001,
          2002,
          2003,
          2004,
          2005,
          2006,
          2007,
          2008,
          2009,
          2010,
          2011,
          2012,
          2013,
          2014,
          2015,
          2016,
          2017,
          2018,
          2019,
          2020,
          2021,
          2022,
          2023
         ],
         "xaxis": "x",
         "y": [
          400340.00329554407,
          363405.28844146384,
          341900.0179514345,
          319250.7115585357,
          340018.0468237363,
          333911.1592326388,
          335176.3035916765,
          345821.3100391077,
          359251.29370150715,
          363190.1584899728,
          384826.5174729582,
          389163.13398029143,
          400590.62386912806,
          404661.339042963,
          413627.5728368466,
          425964.8487227531,
          436699.49142660666,
          441310.0413215137,
          443719.4783157436,
          447048.8789098086,
          223097.99999999953,
          255427,
          337719.33814110653,
          375078.00000000047
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "legend": {
         "title": {
          "text": "Variable"
         },
         "tracegroupgap": 0
        },
        "margin": {
         "t": 60
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Year"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Operations"
         }
        }
       }
      },
      "text/html": [
       "<div>                            <div id=\"1ca43aa1-5ef2-40f2-ad28-70b19790a4fb\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>            <script type=\"text/javascript\">                require([\"plotly\"], function(Plotly) {                    window.PLOTLYENV=window.PLOTLYENV || {};                                    if (document.getElementById(\"1ca43aa1-5ef2-40f2-ad28-70b19790a4fb\")) {                    Plotly.newPlot(                        \"1ca43aa1-5ef2-40f2-ad28-70b19790a4fb\",                        [{\"hovertemplate\":\"Variable=Commercial_Ops_actual<br>Year=%{x}<br>Operations=%{y}<extra></extra>\",\"legendgroup\":\"Commercial_Ops_actual\",\"line\":{\"color\":\"#636efa\",\"dash\":\"solid\"},\"marker\":{\"symbol\":\"circle\"},\"mode\":\"lines\",\"name\":\"Commercial_Ops_actual\",\"orientation\":\"v\",\"showlegend\":true,\"type\":\"scatter\",\"x\":[2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023],\"xaxis\":\"x\",\"y\":[401249.0,367471.0,331880.0,315185.0,332818.0,331515.0,338147.0,357717.0,369877.0,364242.0,371890.0,386941.0,406650.0,405331.0,416342.0,414300.0,435129.0,445842.0,455798.0,444786.0,223098.0,255427.0,343045.0,375078.0],\"yaxis\":\"y\"},{\"hovertemplate\":\"Variable=Commercial_Ops_predicted<br>Year=%{x}<br>Operations=%{y}<extra></extra>\",\"legendgroup\":\"Commercial_Ops_predicted\",\"line\":{\"color\":\"#EF553B\",\"dash\":\"solid\"},\"marker\":{\"symbol\":\"circle\"},\"mode\":\"lines\",\"name\":\"Commercial_Ops_predicted\",\"orientation\":\"v\",\"showlegend\":true,\"type\":\"scatter\",\"x\":[2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023],\"xaxis\":\"x\",\"y\":[400340.00329554407,363405.28844146384,341900.0179514345,319250.7115585357,340018.0468237363,333911.1592326388,335176.3035916765,345821.3100391077,359251.29370150715,363190.1584899728,384826.5174729582,389163.13398029143,400590.62386912806,404661.339042963,413627.5728368466,425964.8487227531,436699.49142660666,441310.0413215137,443719.4783157436,447048.8789098086,223097.99999999953,255427.0,337719.33814110653,375078.00000000047],\"yaxis\":\"y\"}],                        {\"legend\":{\"title\":{\"text\":\"Variable\"},\"tracegroupgap\":0},\"margin\":{\"t\":60},\"template\":{\"data\":{\"bar\":[{\"error_x\":{\"color\":\"#2a3f5f\"},\"error_y\":{\"color\":\"#2a3f5f\"},\"marker\":{\"line\":{\"color\":\"#E5ECF6\",\"width\":0.5},\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"bar\"}],\"barpolar\":[{\"marker\":{\"line\":{\"color\":\"#E5ECF6\",\"width\":0.5},\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"barpolar\"}],\"carpet\":[{\"aaxis\":{\"endlinecolor\":\"#2a3f5f\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"minorgridcolor\":\"white\",\"startlinecolor\":\"#2a3f5f\"},\"baxis\":{\"endlinecolor\":\"#2a3f5f\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"minorgridcolor\":\"white\",\"startlinecolor\":\"#2a3f5f\"},\"type\":\"carpet\"}],\"choropleth\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"type\":\"choropleth\"}],\"contour\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"contour\"}],\"contourcarpet\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"type\":\"contourcarpet\"}],\"heatmap\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"heatmap\"}],\"heatmapgl\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"heatmapgl\"}],\"histogram\":[{\"marker\":{\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"histogram\"}],\"histogram2d\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"histogram2d\"}],\"histogram2dcontour\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"histogram2dcontour\"}],\"mesh3d\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"type\":\"mesh3d\"}],\"parcoords\":[{\"line\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"parcoords\"}],\"pie\":[{\"automargin\":true,\"type\":\"pie\"}],\"scatter\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatter\"}],\"scatter3d\":[{\"line\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatter3d\"}],\"scattercarpet\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattercarpet\"}],\"scattergeo\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattergeo\"}],\"scattergl\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattergl\"}],\"scattermapbox\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattermapbox\"}],\"scatterpolar\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatterpolar\"}],\"scatterpolargl\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatterpolargl\"}],\"scatterternary\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatterternary\"}],\"surface\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"surface\"}],\"table\":[{\"cells\":{\"fill\":{\"color\":\"#EBF0F8\"},\"line\":{\"color\":\"white\"}},\"header\":{\"fill\":{\"color\":\"#C8D4E3\"},\"line\":{\"color\":\"white\"}},\"type\":\"table\"}]},\"layout\":{\"annotationdefaults\":{\"arrowcolor\":\"#2a3f5f\",\"arrowhead\":0,\"arrowwidth\":1},\"autotypenumbers\":\"strict\",\"coloraxis\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"colorscale\":{\"diverging\":[[0,\"#8e0152\"],[0.1,\"#c51b7d\"],[0.2,\"#de77ae\"],[0.3,\"#f1b6da\"],[0.4,\"#fde0ef\"],[0.5,\"#f7f7f7\"],[0.6,\"#e6f5d0\"],[0.7,\"#b8e186\"],[0.8,\"#7fbc41\"],[0.9,\"#4d9221\"],[1,\"#276419\"]],\"sequential\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"sequentialminus\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]},\"colorway\":[\"#636efa\",\"#EF553B\",\"#00cc96\",\"#ab63fa\",\"#FFA15A\",\"#19d3f3\",\"#FF6692\",\"#B6E880\",\"#FF97FF\",\"#FECB52\"],\"font\":{\"color\":\"#2a3f5f\"},\"geo\":{\"bgcolor\":\"white\",\"lakecolor\":\"white\",\"landcolor\":\"#E5ECF6\",\"showlakes\":true,\"showland\":true,\"subunitcolor\":\"white\"},\"hoverlabel\":{\"align\":\"left\"},\"hovermode\":\"closest\",\"mapbox\":{\"style\":\"light\"},\"paper_bgcolor\":\"white\",\"plot_bgcolor\":\"#E5ECF6\",\"polar\":{\"angularaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"bgcolor\":\"#E5ECF6\",\"radialaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"}},\"scene\":{\"xaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"gridwidth\":2,\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\"},\"yaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"gridwidth\":2,\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\"},\"zaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"gridwidth\":2,\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\"}},\"shapedefaults\":{\"line\":{\"color\":\"#2a3f5f\"}},\"ternary\":{\"aaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"baxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"bgcolor\":\"#E5ECF6\",\"caxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"}},\"title\":{\"x\":0.05},\"xaxis\":{\"automargin\":true,\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\",\"title\":{\"standoff\":15},\"zerolinecolor\":\"white\",\"zerolinewidth\":2},\"yaxis\":{\"automargin\":true,\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\",\"title\":{\"standoff\":15},\"zerolinecolor\":\"white\",\"zerolinewidth\":2}}},\"xaxis\":{\"anchor\":\"y\",\"domain\":[0.0,1.0],\"title\":{\"text\":\"Year\"}},\"yaxis\":{\"anchor\":\"x\",\"domain\":[0.0,1.0],\"title\":{\"text\":\"Operations\"}}},                        {\"responsive\": true}                    ).then(function(){\n",
       "                            \n",
       "var gd = document.getElementById('1ca43aa1-5ef2-40f2-ad28-70b19790a4fb');\n",
       "var x = new MutationObserver(function (mutations, observer) {{\n",
       "        var display = window.getComputedStyle(gd).display;\n",
       "        if (!display || display === 'none') {{\n",
       "            console.log([gd, 'removed!']);\n",
       "            Plotly.purge(gd);\n",
       "            observer.disconnect();\n",
       "        }}\n",
       "}});\n",
       "\n",
       "// Listen for the removal of the full notebook cells\n",
       "var notebookContainer = gd.closest('#notebook-container');\n",
       "if (notebookContainer) {{\n",
       "    x.observe(notebookContainer, {childList: true});\n",
       "}}\n",
       "\n",
       "// Listen for the clearing of the current output cell\n",
       "var outputEl = gd.closest('.output');\n",
       "if (outputEl) {{\n",
       "    x.observe(outputEl, {childList: true});\n",
       "}}\n",
       "\n",
       "                        })                };                });            </script>        </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "y_pred = reg.predict(X)\n",
    "y_df = pd.DataFrame()\n",
    "y_df['Year'] = data['Year']\n",
    "y_df['Commercial_Ops_actual'] = y\n",
    "y_df['Commercial_Ops_predicted'] = y_pred\n",
    "y_df = y_df.melt(id_vars=['Year'], value_name='Operations', var_name='Variable')\n",
    "y_df.rename(columns={'value':'Operations'}, inplace=True)\n",
    "fig = px.line(y_df, x='Year', y='Operations', color='Variable')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e951c1f",
   "metadata": {},
   "source": [
    "**Use PBA2050 employment projections for future data to fit model predictions**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1222,
   "id": "a17a6f38",
   "metadata": {},
   "outputs": [],
   "source": [
    "totemp = pba2050.loc[(pba2050.variable==\"TOTEMP\") & (pba2050.cnty_abbr==\"TOT\")]\n",
    "totemp.drop(columns=['gpf_id','variable','cnty_abbr'], inplace=True)\n",
    "for col in totemp.columns:\n",
    "    totemp[col] = totemp[col]/totemp['2023'].values[0]\n",
    "totemp = totemp.melt(var_name=\"Year\",value_name=\"growth\")\n",
    "totemp['Year'] = totemp['Year'].apply(lambda x:int(x))\n",
    "totemp = totemp.loc[totemp.Year >= 2023]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e997596e",
   "metadata": {},
   "source": [
    "**Set COVID hospitalizations for 2024 to 12500 (5154 hospitalizations in first 5 months of year, extrapolated to 12 months), assume 40% decrease in hospitalizations each subsequent year based on trend from 2022-2024**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1223,
   "id": "20dc0288",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_fc = pd.DataFrame(columns=data.columns)\n",
    "data_fc['Year'] = np.linspace(2024,2050,2050-2024+1)\n",
    "data_fc['covid_h'] = [int(12500*0.6**n) for n in range(0,2050-2024+1)]\n",
    "data_fc['non_vax_pop'] = 0\n",
    "data_fc['dummy_911'] = 0\n",
    "data_fc['dummy_covid'] = 0\n",
    "emp_2023 = data.loc[data['Year'] == 2023]['Employment'].values[0]\n",
    "data_fc = data_fc.merge(totemp, on=['Year'], how='left')\n",
    "data_fc['Employment'] = emp_2023*data_fc['growth']\n",
    "data_fc.drop(columns=['Commercial_Ops','growth'], inplace=True)\n",
    "data_fc = data_fc[X_cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8bd284c6",
   "metadata": {},
   "source": [
    "**Use regression model to predict operations from 2024-2050**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1224,
   "id": "571fbc7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_proj = reg.predict(data_fc)\n",
    "y_df_proj = pd.DataFrame()\n",
    "y_df_proj['Year'] = data_fc['Year']\n",
    "y_df_proj['Commercial_Ops_actual'] = np.nan\n",
    "y_df_proj['Commercial_Ops_predicted'] = y_proj\n",
    "y_df_proj = y_df_proj.melt(id_vars=['Year'], value_name='Operations', var_name='Variable')\n",
    "y_df_all = pd.concat([y_df, y_df_proj])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4166b245",
   "metadata": {},
   "source": [
    "**Set upper bound on predictions based on SFO capacity (506,600 total operations)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1225,
   "id": "afc55a91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "hovertemplate": "Variable=Commercial_Ops_actual<br>Year=%{x}<br>Operations=%{y}<extra></extra>",
         "legendgroup": "Commercial_Ops_actual",
         "line": {
          "color": "#636efa",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "Commercial_Ops_actual",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          2000,
          2001,
          2002,
          2003,
          2004,
          2005,
          2006,
          2007,
          2008,
          2009,
          2010,
          2011,
          2012,
          2013,
          2014,
          2015,
          2016,
          2017,
          2018,
          2019,
          2020,
          2021,
          2022,
          2023,
          2024,
          2025,
          2026,
          2027,
          2028,
          2029,
          2030,
          2031,
          2032,
          2033,
          2034,
          2035,
          2036,
          2037,
          2038,
          2039,
          2040,
          2041,
          2042,
          2043,
          2044,
          2045,
          2046,
          2047,
          2048,
          2049,
          2050
         ],
         "xaxis": "x",
         "y": [
          401249,
          367471,
          331880,
          315185,
          332818,
          331515,
          338147,
          357717,
          369877,
          364242,
          371890,
          386941,
          406650,
          405331,
          416342,
          414300,
          435129,
          445842,
          455798,
          444786,
          223098,
          255427,
          343045,
          375078,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null,
          null
         ],
         "yaxis": "y"
        },
        {
         "hovertemplate": "Variable=Commercial_Ops_predicted<br>Year=%{x}<br>Operations=%{y}<extra></extra>",
         "legendgroup": "Commercial_Ops_predicted",
         "line": {
          "color": "#EF553B",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "Commercial_Ops_predicted",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          2000,
          2001,
          2002,
          2003,
          2004,
          2005,
          2006,
          2007,
          2008,
          2009,
          2010,
          2011,
          2012,
          2013,
          2014,
          2015,
          2016,
          2017,
          2018,
          2019,
          2020,
          2021,
          2022,
          2023,
          2024,
          2025,
          2026,
          2027,
          2028,
          2029,
          2030,
          2031,
          2032,
          2033,
          2034,
          2035,
          2036,
          2037,
          2038,
          2039,
          2040,
          2041,
          2042,
          2043,
          2044,
          2045,
          2046,
          2047,
          2048,
          2049,
          2050
         ],
         "xaxis": "x",
         "y": [
          400340.00329554407,
          363405.28844146384,
          341900.0179514345,
          319250.7115585357,
          340018.0468237363,
          333911.1592326388,
          335176.3035916765,
          345821.3100391077,
          359251.29370150715,
          363190.1584899728,
          384826.5174729582,
          389163.13398029143,
          400590.62386912806,
          404661.339042963,
          413627.5728368466,
          425964.8487227531,
          436699.49142660666,
          441310.0413215137,
          443719.4783157436,
          447048.8789098086,
          223097.99999999953,
          255427,
          337719.33814110653,
          375078.00000000047,
          407356.9598910473,
          424024.19489300624,
          436031.78453479987,
          445245.91678317636,
          452777.91705653165,
          459308.09357745247,
          465230.186378872,
          470793.48648755765,
          476137.7832643171,
          481351.60997099057,
          486486.2227065414,
          491574.23898849124,
          492547,
          492489,
          492431,
          492372,
          492313,
          492254,
          492195,
          492135,
          492075,
          492014,
          491954,
          491893,
          491832,
          491770,
          491708
         ],
         "yaxis": "y"
        }
       ],
       "layout": {
        "legend": {
         "title": {
          "text": "Variable"
         },
         "tracegroupgap": 0
        },
        "margin": {
         "t": 60
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Year"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "Operations"
         }
        }
       }
      },
      "text/html": [
       "<div>                            <div id=\"706d8229-172f-4554-b73c-b9c3b261517e\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>            <script type=\"text/javascript\">                require([\"plotly\"], function(Plotly) {                    window.PLOTLYENV=window.PLOTLYENV || {};                                    if (document.getElementById(\"706d8229-172f-4554-b73c-b9c3b261517e\")) {                    Plotly.newPlot(                        \"706d8229-172f-4554-b73c-b9c3b261517e\",                        [{\"hovertemplate\":\"Variable=Commercial_Ops_actual<br>Year=%{x}<br>Operations=%{y}<extra></extra>\",\"legendgroup\":\"Commercial_Ops_actual\",\"line\":{\"color\":\"#636efa\",\"dash\":\"solid\"},\"marker\":{\"symbol\":\"circle\"},\"mode\":\"lines\",\"name\":\"Commercial_Ops_actual\",\"orientation\":\"v\",\"showlegend\":true,\"type\":\"scatter\",\"x\":[2000.0,2001.0,2002.0,2003.0,2004.0,2005.0,2006.0,2007.0,2008.0,2009.0,2010.0,2011.0,2012.0,2013.0,2014.0,2015.0,2016.0,2017.0,2018.0,2019.0,2020.0,2021.0,2022.0,2023.0,2024.0,2025.0,2026.0,2027.0,2028.0,2029.0,2030.0,2031.0,2032.0,2033.0,2034.0,2035.0,2036.0,2037.0,2038.0,2039.0,2040.0,2041.0,2042.0,2043.0,2044.0,2045.0,2046.0,2047.0,2048.0,2049.0,2050.0],\"xaxis\":\"x\",\"y\":[401249.0,367471.0,331880.0,315185.0,332818.0,331515.0,338147.0,357717.0,369877.0,364242.0,371890.0,386941.0,406650.0,405331.0,416342.0,414300.0,435129.0,445842.0,455798.0,444786.0,223098.0,255427.0,343045.0,375078.0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],\"yaxis\":\"y\"},{\"hovertemplate\":\"Variable=Commercial_Ops_predicted<br>Year=%{x}<br>Operations=%{y}<extra></extra>\",\"legendgroup\":\"Commercial_Ops_predicted\",\"line\":{\"color\":\"#EF553B\",\"dash\":\"solid\"},\"marker\":{\"symbol\":\"circle\"},\"mode\":\"lines\",\"name\":\"Commercial_Ops_predicted\",\"orientation\":\"v\",\"showlegend\":true,\"type\":\"scatter\",\"x\":[2000.0,2001.0,2002.0,2003.0,2004.0,2005.0,2006.0,2007.0,2008.0,2009.0,2010.0,2011.0,2012.0,2013.0,2014.0,2015.0,2016.0,2017.0,2018.0,2019.0,2020.0,2021.0,2022.0,2023.0,2024.0,2025.0,2026.0,2027.0,2028.0,2029.0,2030.0,2031.0,2032.0,2033.0,2034.0,2035.0,2036.0,2037.0,2038.0,2039.0,2040.0,2041.0,2042.0,2043.0,2044.0,2045.0,2046.0,2047.0,2048.0,2049.0,2050.0],\"xaxis\":\"x\",\"y\":[400340.00329554407,363405.28844146384,341900.0179514345,319250.7115585357,340018.0468237363,333911.1592326388,335176.3035916765,345821.3100391077,359251.29370150715,363190.1584899728,384826.5174729582,389163.13398029143,400590.62386912806,404661.339042963,413627.5728368466,425964.8487227531,436699.49142660666,441310.0413215137,443719.4783157436,447048.8789098086,223097.99999999953,255427.0,337719.33814110653,375078.00000000047,407356.9598910473,424024.19489300624,436031.78453479987,445245.91678317636,452777.91705653165,459308.09357745247,465230.186378872,470793.48648755765,476137.7832643171,481351.60997099057,486486.2227065414,491574.23898849124,492547.0,492489.0,492431.0,492372.0,492313.0,492254.0,492195.0,492135.0,492075.0,492014.0,491954.0,491893.0,491832.0,491770.0,491708.0],\"yaxis\":\"y\"}],                        {\"legend\":{\"title\":{\"text\":\"Variable\"},\"tracegroupgap\":0},\"margin\":{\"t\":60},\"template\":{\"data\":{\"bar\":[{\"error_x\":{\"color\":\"#2a3f5f\"},\"error_y\":{\"color\":\"#2a3f5f\"},\"marker\":{\"line\":{\"color\":\"#E5ECF6\",\"width\":0.5},\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"bar\"}],\"barpolar\":[{\"marker\":{\"line\":{\"color\":\"#E5ECF6\",\"width\":0.5},\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"barpolar\"}],\"carpet\":[{\"aaxis\":{\"endlinecolor\":\"#2a3f5f\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"minorgridcolor\":\"white\",\"startlinecolor\":\"#2a3f5f\"},\"baxis\":{\"endlinecolor\":\"#2a3f5f\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"minorgridcolor\":\"white\",\"startlinecolor\":\"#2a3f5f\"},\"type\":\"carpet\"}],\"choropleth\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"type\":\"choropleth\"}],\"contour\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"contour\"}],\"contourcarpet\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"type\":\"contourcarpet\"}],\"heatmap\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"heatmap\"}],\"heatmapgl\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"heatmapgl\"}],\"histogram\":[{\"marker\":{\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"histogram\"}],\"histogram2d\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"histogram2d\"}],\"histogram2dcontour\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"histogram2dcontour\"}],\"mesh3d\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"type\":\"mesh3d\"}],\"parcoords\":[{\"line\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"parcoords\"}],\"pie\":[{\"automargin\":true,\"type\":\"pie\"}],\"scatter\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatter\"}],\"scatter3d\":[{\"line\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatter3d\"}],\"scattercarpet\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattercarpet\"}],\"scattergeo\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattergeo\"}],\"scattergl\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattergl\"}],\"scattermapbox\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scattermapbox\"}],\"scatterpolar\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatterpolar\"}],\"scatterpolargl\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatterpolargl\"}],\"scatterternary\":[{\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"type\":\"scatterternary\"}],\"surface\":[{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"type\":\"surface\"}],\"table\":[{\"cells\":{\"fill\":{\"color\":\"#EBF0F8\"},\"line\":{\"color\":\"white\"}},\"header\":{\"fill\":{\"color\":\"#C8D4E3\"},\"line\":{\"color\":\"white\"}},\"type\":\"table\"}]},\"layout\":{\"annotationdefaults\":{\"arrowcolor\":\"#2a3f5f\",\"arrowhead\":0,\"arrowwidth\":1},\"autotypenumbers\":\"strict\",\"coloraxis\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"colorscale\":{\"diverging\":[[0,\"#8e0152\"],[0.1,\"#c51b7d\"],[0.2,\"#de77ae\"],[0.3,\"#f1b6da\"],[0.4,\"#fde0ef\"],[0.5,\"#f7f7f7\"],[0.6,\"#e6f5d0\"],[0.7,\"#b8e186\"],[0.8,\"#7fbc41\"],[0.9,\"#4d9221\"],[1,\"#276419\"]],\"sequential\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"sequentialminus\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]},\"colorway\":[\"#636efa\",\"#EF553B\",\"#00cc96\",\"#ab63fa\",\"#FFA15A\",\"#19d3f3\",\"#FF6692\",\"#B6E880\",\"#FF97FF\",\"#FECB52\"],\"font\":{\"color\":\"#2a3f5f\"},\"geo\":{\"bgcolor\":\"white\",\"lakecolor\":\"white\",\"landcolor\":\"#E5ECF6\",\"showlakes\":true,\"showland\":true,\"subunitcolor\":\"white\"},\"hoverlabel\":{\"align\":\"left\"},\"hovermode\":\"closest\",\"mapbox\":{\"style\":\"light\"},\"paper_bgcolor\":\"white\",\"plot_bgcolor\":\"#E5ECF6\",\"polar\":{\"angularaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"bgcolor\":\"#E5ECF6\",\"radialaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"}},\"scene\":{\"xaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"gridwidth\":2,\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\"},\"yaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"gridwidth\":2,\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\"},\"zaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"gridwidth\":2,\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\"}},\"shapedefaults\":{\"line\":{\"color\":\"#2a3f5f\"}},\"ternary\":{\"aaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"baxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"bgcolor\":\"#E5ECF6\",\"caxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"}},\"title\":{\"x\":0.05},\"xaxis\":{\"automargin\":true,\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\",\"title\":{\"standoff\":15},\"zerolinecolor\":\"white\",\"zerolinewidth\":2},\"yaxis\":{\"automargin\":true,\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\",\"title\":{\"standoff\":15},\"zerolinecolor\":\"white\",\"zerolinewidth\":2}}},\"xaxis\":{\"anchor\":\"y\",\"domain\":[0.0,1.0],\"title\":{\"text\":\"Year\"}},\"yaxis\":{\"anchor\":\"x\",\"domain\":[0.0,1.0],\"title\":{\"text\":\"Operations\"}}},                        {\"responsive\": true}                    ).then(function(){\n",
       "                            \n",
       "var gd = document.getElementById('706d8229-172f-4554-b73c-b9c3b261517e');\n",
       "var x = new MutationObserver(function (mutations, observer) {{\n",
       "        var display = window.getComputedStyle(gd).display;\n",
       "        if (!display || display === 'none') {{\n",
       "            console.log([gd, 'removed!']);\n",
       "            Plotly.purge(gd);\n",
       "            observer.disconnect();\n",
       "        }}\n",
       "}});\n",
       "\n",
       "// Listen for the removal of the full notebook cells\n",
       "var notebookContainer = gd.closest('#notebook-container');\n",
       "if (notebookContainer) {{\n",
       "    x.observe(notebookContainer, {childList: true});\n",
       "}}\n",
       "\n",
       "// Listen for the clearing of the current output cell\n",
       "var outputEl = gd.closest('.output');\n",
       "if (outputEl) {{\n",
       "    x.observe(outputEl, {childList: true});\n",
       "}}\n",
       "\n",
       "                        })                };                });            </script>        </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "taf_sfo = taf2023.loc[taf2023.ayear >= 2000]\n",
    "taf_sfo['Total'] = taf_sfo[['itn_Ac','itn_at','itn_ga','itn_mil']].sum(axis=1)\n",
    "taf_sfo['itn_mil'] = taf_sfo['itn_mil'].apply(lambda x:2400 if x>2400 else x)\n",
    "taf_sfo['itn_ga'] = taf_sfo['itn_ga'].apply(lambda x:12900 if x>12900 else x)\n",
    "taf_sfo['comm_ops_max'] = 506600-taf_sfo['itn_mil']-taf_sfo['itn_ga']\n",
    "taf_sfo.rename(columns={'ayear':'Year'}, inplace=True)\n",
    "y_df_all = y_df_all.merge(taf_sfo[['Year','comm_ops_max']], on=['Year'], how='left')\n",
    "y_df_all['Operations'] = y_df_all.apply(lambda x:x.comm_ops_max if x.Operations>x.comm_ops_max else x.Operations, axis=1)\n",
    "y_df_all.drop(columns=['comm_ops_max'], inplace=True)\n",
    "fig = px.line(y_df_all, x='Year', y='Operations', color='Variable')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "191f49d3",
   "metadata": {},
   "source": [
    "**Output projection to send to CARB**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1226,
   "id": "ed197db0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sfo_carb_2022 = taf2023.loc[taf2023.ayear.isin([2022,2023])]\n",
    "sfo_carb = y_df_all.loc[y_df_all.Variable==\"Commercial_Ops_predicted\"][['Year','Operations']]\n",
    "sfo_carb = sfo_carb.loc[sfo_carb.Year>=2024]\n",
    "sfo_carb = sfo_carb.merge(taf_sfo, on='Year', how='left')\n",
    "sfo_carb['itn_at'] = sfo_carb['itn_at'].apply(lambda x:21100 if x>21100 else x)\n",
    "sfo_carb['itn_Ac'] = sfo_carb['Operations']-sfo_carb['itn_at']\n",
    "sfo_carb['itn_Ac'] = sfo_carb['itn_Ac'].apply(lambda x:int(x))\n",
    "sfo_carb.drop(columns=['Operations','comm_ops_max'], inplace=True)\n",
    "sfo_carb.rename(columns={'Year':'ayear'}, inplace=True)\n",
    "sfo_carb = sfo_carb[['locid','scenario','ayear','itn_Ac', 'itn_at', 'itn_ga','itn_mil', 'loc_ga', 'loc_mil', 'tot_overs']]\n",
    "sfo_carb = pd.concat([sfo_carb_2022, sfo_carb])\n",
    "sfo_carb.to_excel(\"output/SFO_projection_to_CARB.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5714228",
   "metadata": {},
   "source": [
    "**Output forecast profile for inventory**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1227,
   "id": "592a010d",
   "metadata": {},
   "outputs": [],
   "source": [
    "sfo_forecast = y_df_all.loc[y_df_all.Year>=2022]\n",
    "sfo_forecast = sfo_forecast.loc[sfo_forecast.Variable==\"Commercial_Ops_predicted\"]\n",
    "sfo_forecast['Normalized'] = sfo_forecast['Operations']/sfo_forecast.loc[sfo_forecast.Year==2022]['Operations'].values[0]\n",
    "sfo_forecast['Operations'] = sfo_forecast['Operations'].apply(lambda x:int(x))\n",
    "sfo_forecast.drop(columns=['Variable'], inplace=True)\n",
    "sfo_forecast.to_csv(\"output/SFO_forecast_profile.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b60732f",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}