Convert.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "ce0e475c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "4384adb2",
   "metadata": {
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "configuration_path = \"data/lyon_configuration.json\"\n",
    "scenario_path = \"input/scenario_baseline_2022.json\"\n",
    "solution_path = \"input/solution_baseline_2022.json\"\n",
    "output_path = \"output/copert.xlsx\"\n",
    "year = 2022"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c661d655",
   "metadata": {},
   "source": [
    "## Read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "95862b39",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(solution_path) as f:\n",
    "    solution = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "2290f678",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(scenario_path) as f:\n",
    "    scenario = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "ccf50616",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(configuration_path) as f:\n",
    "    configuration = json.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18ec3368",
   "metadata": {},
   "source": [
    "## Prepare general data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "2d02cbab",
   "metadata": {},
   "outputs": [],
   "source": [
    "months = [\n",
    "    \"January\", \"February\", \"March\", \"April\", \"May\", \"June\", \"July\", \n",
    "    \"August\", \"September\", \"October\", \"November\", \"December\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "86347515",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_min_temperature = pd.DataFrame.from_records([\n",
    "    { \"Month\": month, str(year): temperature }\n",
    "    for temperature, month in zip(configuration[\"temperature\"][\"min\"], months)\n",
    "])\n",
    "\n",
    "df_max_temperature = pd.DataFrame.from_records([\n",
    "    { \"Month\": month, str(year): temperature }\n",
    "    for temperature, month in zip(configuration[\"temperature\"][\"max\"], months)\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "86106acd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_humidity = pd.DataFrame.from_records([\n",
    "    { \"Month\": month, str(year): humidity }\n",
    "    for humidity, month in zip(configuration[\"humidity\"], months)\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "61cddfea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sheets = pd.DataFrame.from_records([\n",
    "    { \"SHEET_NAME\": \"STOCK\", \"Unit\": \"[n]\" },\n",
    "    { \"SHEET_NAME\": \"MEAN_ACTIVITY\", \"Unit\": \"[km]\" },\n",
    "    { \"SHEET_NAME\": \"URBAN_OFF_PEAK_SPEED\", \"Unit\": \"[km/h]\" },\n",
    "    { \"SHEET_NAME\": \"URBAN_PEAK_SPEED\", \"Unit\": \"[km/h]\" },\n",
    "    { \"SHEET_NAME\": \"URBAN_OFF_PEAK_SHARE\", \"Unit\": \"[%]\" },\n",
    "    { \"SHEET_NAME\": \"URBAN_PEAK_SHARE\", \"Unit\": \"[%]\" },\n",
    "    { \"SHEET_NAME\": \"MIN_TEMPERATURE\", \"Unit\": \"[°C]\" },\n",
    "    { \"SHEET_NAME\": \"MAX_TEMPERATURE\", \"Unit\": \"[°C]\" },\n",
    "    { \"SHEET_NAME\": \"HUMIDITY\", \"Unit\": \"[%]\" },\n",
    "    { \"SHEET_NAME\": \"FUEL_TANK_SIZE\", \"Unit\": \"[l]\" },\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb01f589",
   "metadata": {},
   "source": [
    "## By vehicle type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "bc180dcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "vehicle_speeds = {}\n",
    "vehicle_counts = {}\n",
    "vehicle_distances = {}\n",
    "\n",
    "for vt in scenario[\"vehicle_types\"]:\n",
    "    vehicle_speeds[vt[\"id\"]] = vt[\"speed_km_h\"]\n",
    "    vehicle_counts[vt[\"id\"]] = 0\n",
    "    vehicle_distances[vt[\"id\"]] = 0.0\n",
    "    \n",
    "for route in solution[\"routes\"]:\n",
    "    vehicle_counts[route[\"vehicle_type\"]] += 1\n",
    "    vehicle_distances[route[\"vehicle_type\"]] += route[\"distance_km\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "id": "dbad5f39",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_stock = []\n",
    "df_mean_activity = []\n",
    "\n",
    "df_peak_speed = []\n",
    "df_offpeak_speed = []\n",
    "\n",
    "df_peak_share = []\n",
    "df_offpeak_share = []\n",
    "\n",
    "df_fuel_tank_size = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "id": "b64cfdb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "for vt, config in configuration[\"vehicle_type_mapping\"].items():\n",
    "    assert vt in vehicle_counts\n",
    "    \n",
    "    common = {\n",
    "        \"Category\": config[\"category\"],\n",
    "        \"Fuel\": config[\"fuel\"],\n",
    "        \"Segment\": config[\"segment\"],\n",
    "        \"Euro Standard\": config[\"euro_standard\"]\n",
    "    }\n",
    "    \n",
    "    peak_share = {}\n",
    "    peak_share.update(common)\n",
    "    peak_share[str(year)] = configuration[\"peak_share\"] * 100.0\n",
    "    df_peak_share.append(peak_share)\n",
    "    \n",
    "    offpeak_share = {}\n",
    "    offpeak_share.update(common)\n",
    "    offpeak_share[str(year)] = (1.0 - configuration[\"peak_share\"]) * 100.0\n",
    "    df_offpeak_share.append(offpeak_share)\n",
    "    \n",
    "    peak_speed = {}\n",
    "    peak_speed.update(common)\n",
    "    peak_speed[str(year)] = vehicle_speeds[vt] * configuration[\"peak_speed_factor\"]\n",
    "    df_peak_speed.append(peak_speed)\n",
    "    \n",
    "    offpeak_speed = {}\n",
    "    offpeak_speed.update(common)\n",
    "    offpeak_speed[str(year)] = vehicle_speeds[vt]\n",
    "    df_offpeak_speed.append(offpeak_speed)\n",
    "    \n",
    "    fuel_tank_size = {}\n",
    "    fuel_tank_size.update(common)\n",
    "    fuel_tank_size[str(year)] = config[\"fuel_tank_size\"]\n",
    "    df_fuel_tank_size.append(fuel_tank_size)\n",
    "    \n",
    "    stock = {}\n",
    "    stock.update(common)\n",
    "    stock[str(year)] = vehicle_counts[vt]\n",
    "    df_stock.append(stock)\n",
    "    \n",
    "    mean_activity = {}\n",
    "    mean_activity.update(common)\n",
    "    mean_activity[str(year)] = vehicle_distances[vt]\n",
    "    df_mean_activity.append(mean_activity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "43c5e57d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_peak_share = pd.DataFrame.from_records(df_peak_share)\n",
    "df_offpeak_share = pd.DataFrame.from_records(df_offpeak_share)\n",
    "df_peak_speed= pd.DataFrame.from_records(df_peak_speed)\n",
    "df_offpeak_speed = pd.DataFrame.from_records(df_offpeak_speed)\n",
    "df_fuel_tank_size = pd.DataFrame.from_records(df_fuel_tank_size)\n",
    "df_stock = pd.DataFrame.from_records(df_stock)\n",
    "df_mean_activity = pd.DataFrame.from_records(df_mean_activity)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "813739a2",
   "metadata": {},
   "source": [
    "## Output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "id": "ae8a7606",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(output_path, engine=\"openpyxl\") as writer:\n",
    "    df_sheets.to_excel(writer, index = False, sheet_name = \"SHEETS\")\n",
    "    df_stock.to_excel(writer, index = False, sheet_name = \"STOCK\")\n",
    "    df_mean_activity.to_excel(writer, index = False, sheet_name = \"MEAN_ACTIVITY\")\n",
    "    df_offpeak_speed.to_excel(writer, index = False, sheet_name = \"URBAN_OFFPEAK_SPEED\")\n",
    "    df_peak_speed.to_excel(writer, index = False, sheet_name = \"URBAN_PEAK_SPEED\")\n",
    "    df_offpeak_share.to_excel(writer, index = False, sheet_name = \"URBAN_OFFPEAK_SHARE\")\n",
    "    df_peak_share.to_excel(writer, index = False, sheet_name = \"URBAN_PEAK_SHARE\")\n",
    "    df_min_temperature.to_excel(writer, index = False, sheet_name = \"MIN_TEMPERATURE\")\n",
    "    df_max_temperature.to_excel(writer, index = False, sheet_name = \"MAX_TEMPERATURE\")\n",
    "    df_humidity.to_excel(writer, index = False, sheet_name = \"HUMIDITY\")\n",
    "    df_fuel_tank_size.to_excel(writer, index = False, sheet_name = \"FUEL_TANK_SIZE\")"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}