Calc-Descriptive-Stats .ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How to use this notebook:\n",
    "1. Input the directory containing the model run folders containing SE files.\n",
    "2. The script will identify any folders not beginning with a \"_\" as a run folder\n",
    "3. The script will output the mean, min-mix, and standard deviation of all model runs in the directory specified earlier "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import glob\n",
    "\n",
    "# show all columns\n",
    "pd.options.display.max_columns = None"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update this path!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Directory that contains SE files from multiple runs\n",
    "data_folder = r\"\\\\server1\\Volumef\\SHARED\\Josh\\scenario_no_center_v3\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Environment Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# variables to add back\n",
    "t832_vars = pd.read_csv(r\".\\Inputs\\TAZ832_Extra_Variables.csv\")\n",
    "t900_vars = pd.read_csv(r\".\\Inputs\\TAZ900_Extra_Variables.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['\\\\\\\\server1\\\\Volumef\\\\SHARED\\\\Josh\\\\scenario_no_center_v3\\\\mq1028',\n",
       " '\\\\\\\\server1\\\\Volumef\\\\SHARED\\\\Josh\\\\scenario_no_center_v3\\\\mq1029',\n",
       " '\\\\\\\\server1\\\\Volumef\\\\SHARED\\\\Josh\\\\scenario_no_center_v3\\\\mq946',\n",
       " '\\\\\\\\server1\\\\Volumef\\\\SHARED\\\\Josh\\\\scenario_no_center_v3\\\\mq955',\n",
       " '\\\\\\\\server1\\\\Volumef\\\\SHARED\\\\Josh\\\\scenario_no_center_v3\\\\mq956',\n",
       " '\\\\\\\\server1\\\\Volumef\\\\SHARED\\\\Josh\\\\scenario_no_center_v3\\\\mq999']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the folders that contain SE data, this  will skip any beginning with an underscore\n",
    "runs =  [name for name in os.listdir(data_folder) if name.startswith('_')==False and  os.path.isdir(os.path.join(data_folder, name))]\n",
    "run_paths = [os.path.join(data_folder, x) for x in runs]\n",
    "n_runs = len(runs)\n",
    "run_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mean\n",
    "avg_folder = os.path.join(data_folder, '_Averaged_Results')\n",
    "if not os.path.exists(avg_folder):\n",
    "    os.makedirs(avg_folder)\n",
    "\n",
    "avg_v900_folder = os.path.join(avg_folder, 'v900_' + f'{n_runs}runs')\n",
    "if not os.path.exists(avg_v900_folder):\n",
    "    os.makedirs(avg_v900_folder)\n",
    "\n",
    "avg_v832_folder = os.path.join(avg_folder, 'v832_'+ f'{n_runs}runs')\n",
    "if not os.path.exists(avg_v832_folder):\n",
    "    os.makedirs(avg_v832_folder)\n",
    "\n",
    "#----------\n",
    "\n",
    "# min max\n",
    "minmax_folder = os.path.join(data_folder, '_Min_Max_Results')\n",
    "if not os.path.exists(avg_folder):\n",
    "    os.makedirs(avg_folder)\n",
    "\n",
    "\n",
    "minmax_v900_folder = os.path.join(minmax_folder, 'v900_'+ f'{n_runs}runs')\n",
    "if not os.path.exists(minmax_v900_folder):\n",
    "    os.makedirs(minmax_v900_folder)\n",
    "\n",
    "minmax_v832_folder = os.path.join(minmax_folder, 'v832_' + f'{n_runs}runs')\n",
    "if not os.path.exists(minmax_v832_folder):\n",
    "    os.makedirs(minmax_v832_folder)\n",
    "\n",
    "#----------\n",
    "\n",
    "# std\n",
    "std_folder = os.path.join(data_folder, '_Std_Dev_Results')\n",
    "if not os.path.exists(std_folder):\n",
    "    os.makedirs(std_folder)\n",
    "\n",
    "std_v900_folder = os.path.join(std_folder, 'v900_'+ f'{n_runs}runs')\n",
    "if not os.path.exists(std_v900_folder):\n",
    "    os.makedirs(std_v900_folder)\n",
    "\n",
    "std_v832_folder = os.path.join(std_folder, 'v832_' + f'{n_runs}runs')\n",
    "if not os.path.exists(std_v832_folder):\n",
    "    os.makedirs(std_v832_folder)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_se_file_for_year(path, version, year):\n",
    "    if version == '832':\n",
    "        csv = os.path.join(path, f'TAZ832_SE_{year}.csv')\n",
    "    if version == '900':\n",
    "        csv = os.path.join(path, f'SE_{year}.csv')\n",
    "    \n",
    "    df = pd.read_csv(csv)\n",
    "    df = df.set_index(';TAZID')\n",
    "    del df['CO_TAZID']\n",
    "    del df['CO_FIPS']\n",
    "    del df['CO_NAME']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def recalc_fields(df):\n",
    "    df['HHSIZE'] = 0\n",
    "    df.loc[df['TOTHH'] > 0, 'HHSIZE'] = df.HHPOP[df.TOTHH > 0]/df.TOTHH[df.TOTHH > 0]\n",
    "    df['ALLEMP'] = (df['RETL']+df['FOOD']+df['MANU']+df['WSLE']+df['OFFI']+df['GVED']+df['HLTH']+\n",
    "                            df['OTHR']+df['FM_AGRI']+df['FM_MING']+df['FM_CONS']+df['HBJ'])\n",
    "    df['RETEMP'] = df['RETL'] + df['FOOD']\n",
    "    df['INDEMP'] = df['MANU'] + df['WSLE']\n",
    "    df['OTHEMP'] = df['OFFI'] + df['GVED'] + df['HLTH'] + df['OTHR']\n",
    "    df['TOTEMP'] = df['RETEMP'] + df['INDEMP'] + df['OTHEMP']\n",
    "    return pd.DataFrame(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_stats(read_se_output, version):\n",
    "    \n",
    "    data_stack = pd.concat(read_se_output)\n",
    "\n",
    "    # average\n",
    "    average = data_stack.groupby(data_stack.index).mean().reset_index()\n",
    "    average = recalc_fields(average)\n",
    "  \n",
    "    # min - max\n",
    "    min = data_stack.groupby(data_stack.index).min().reset_index()\n",
    "    min = recalc_fields(min)\n",
    "    cols = min.set_index(';TAZID').columns\n",
    "    cols_new = [col + '_Min' for col in cols]\n",
    "    replace_dict = dict(zip(cols, cols_new))\n",
    "    min = min.rename(replace_dict, axis=1)\n",
    "    max = data_stack.groupby(data_stack.index).max().reset_index()\n",
    "    max = recalc_fields(max)\n",
    "    cols = max.set_index(';TAZID').columns\n",
    "    cols_new = [col + '_Max' for col in cols]\n",
    "    replace_dict = dict(zip(cols, cols_new))\n",
    "    max = max.rename(replace_dict, axis=1)\n",
    "    min_max = min.merge(max, on=';TAZID' ,how='left')\n",
    "    min_max = min_max.reindex(sorted(min_max.columns), axis=1)\n",
    "\n",
    "    # std\n",
    "    std = data_stack.groupby(data_stack.index).std().reset_index()\n",
    "\n",
    "    # join the geog vars back\n",
    "    if version == '832': extra_vars = t832_vars\n",
    "    if version == '900': extra_vars = t900_vars\n",
    "    average = average.merge(extra_vars, on=';TAZID', how='left')\n",
    "    min_max = min_max.merge(extra_vars, on=';TAZID', how='left')\n",
    "    std = std.merge(extra_vars, on=';TAZID', how='left')\n",
    "\n",
    "    # reorder columns\n",
    "    cols_ordered = [';TAZID', 'CO_TAZID', 'TOTHH', 'HHPOP', 'HHSIZE', 'TOTEMP', 'RETEMP',\n",
    "       'INDEMP', 'OTHEMP', 'ALLEMP', 'RETL', 'FOOD', 'MANU', 'WSLE', 'OFFI',\n",
    "       'GVED', 'HLTH', 'OTHR', 'FM_AGRI', 'FM_MING', 'FM_CONS', 'HBJ',\n",
    "       'AVGINCOME', 'Enrol_Elem', 'Enrol_Midl', 'Enrol_High', 'CO_FIPS',\n",
    "       'CO_NAME']\n",
    "    average = average[cols_ordered]\n",
    "    std = std[cols_ordered]\n",
    "\n",
    "\n",
    "    stats = [average, min_max, std]\n",
    "    return stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def export_se_tables(calc_stats_output, version, year):\n",
    "    year = str(year)\n",
    "    avg = calc_stats_output[0]\n",
    "    mm =calc_stats_output[1]\n",
    "    std = calc_stats_output[2]\n",
    "\n",
    "    if version == '832':\n",
    "        avg.to_csv(os.path.join(avg_v832_folder , f'SE_{year}.csv'), index=False)\n",
    "        mm.to_csv(os.path.join(minmax_v832_folder , f'Min_Max_SE_{year}.csv'), index=False)\n",
    "        std.to_csv(os.path.join(std_v832_folder , f'Std_Dev_SE_{year}.csv'), index=False)\n",
    "\n",
    "    if version == '900':\n",
    "        avg.to_csv(os.path.join(avg_v900_folder , f'SE_{year}.csv'), index=False)\n",
    "        mm.to_csv(os.path.join(minmax_v900_folder , f'Min_Max_SE_{year}.csv'), index=False)\n",
    "        std.to_csv(os.path.join(std_v900_folder , f'Std_Dev_SE_{year}.csv'), index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## __main__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "for year in range(2019,2051):\n",
    "\n",
    "    dataframes_900 = [read_se_file_for_year(path, '900', year) for path in run_paths]\n",
    "    dataframes_with_stats_900 = calculate_stats(dataframes_900, '900')\n",
    "    export_se_tables(dataframes_with_stats_900, '900', year)\n",
    "\n",
    "    # dataframes_832 = [read_se_file_for_year(path, '832', year) for path in run_paths]\n",
    "    # dataframes_with_stats_832 = calculate_stats(dataframes_832, '832')\n",
    "    # export_se_tables(dataframes_with_stats_832, '832', year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dfs_900_2019 = [read_se_file_for_year(path, '900', 2019) for path in run_paths]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_with_stats = calculate_stats(dfs_900_2019, '900')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export_se_tables(df_with_stats, '900', 2019)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # compare results of new script and old script\n",
    "# new = pd.read_csv(r\"\\\\server1\\Volumef\\SHARED\\Andy\\_FromJosh\\New REMM Results\\result_20221216\\_Averaged_Results\\v900_6runs\\SE_2050.csv\")\n",
    "# old = pd.read_csv(r\"\\\\server1\\Volumef\\SHARED\\Andy\\_FromJosh\\New REMM Results\\result_20221216\\_Averaged_Results_\\v900\\6runs\\SE_2050.csv\")\n",
    "# new.round(2).compare(old.round(2))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.11",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.11"
  },
  "vscode": {
   "interpreter": {
    "hash": "3245673af07dcc28bdd829afb187282e9288a1f8195a5928b70ecba6e5973721"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}