StarLift Continued.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **StarLift Airlines Trips**\n",
    "As an introductory problem to data and network optimization using Python, we'll go over this problem with a made-up airline company trying to find the best setup for their flights. The original dataset can be found here: https://raw.githubusercontent.com/jpatokal/openflights/master/data/routes.dat. Note that how we use it here is (most likely) out of its original context."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import statements\n",
    "import pandas as pd # Pandas library that allows us to use dataframes\n",
    "import matplotlib.pyplot as plt # This library allows us to visualize our data\n",
    "import math\n",
    "import numpy as np\n",
    "\n",
    "# Get our custom functions\n",
    "import sys # Allows us to import functions from a different directory\n",
    "sys.path.append('./Course Resources/') # Add the directory that has our functions file\n",
    "import functions as fun # Import our functions from the functions.py file. Go check it out in the Course Resources folder!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **StarLift Airlines Analysis - Static**<br>\n",
    "The goal of this analysis is to analyze the structure of a commercial airline's flight system. Let's call the company StarLift Airlines and we'll say they fly all over the world. StarLift has several airports they fly to and many different routes between airports, and they want to find what the most profitable setup is at different price points and airport fees. They want to find out if they should remove airports and, if so, how many they should remove. Your task is to do an analysis of the trip data provided to find the number of airports they fly to/from, the most used airports, and create a model showing the relationship between potential profits and airports removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our dataframe from the csv file\n",
    "df = pd.read_csv('./Data/airplaneRoutes.csv') # Read in the data from the csv file\n",
    "df.columns = ['source_airport', 'dest_airport'] # Traditional python naming conventions for columns\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initial assumptions: variables we will use to change projections\n",
    "PROFIT_PER_TRIP = 10000\n",
    "FEE_PER_AIRPORT = 200000\n",
    "\n",
    "# Store the number of trips in a variable that we'll use in our projections\n",
    "N_TRIPS = df.shape[0]\n",
    "\n",
    "# Output to make sure we got the right numbers\n",
    "print(f'Charge per trip: ${PROFIT_PER_TRIP}\\nFee per airport: ${FEE_PER_AIRPORT}\\nNumber of trips: {N_TRIPS}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Separate the source airports and destination airports into separate dataframes. Get unique values, sort them, and reset the index\n",
    "unique_source_airports = df[['source_airport']].drop_duplicates().sort_values(by='source_airport').reset_index(drop=True)\n",
    "unique_dest_airports = df[['dest_airport']].drop_duplicates().sort_values(by='dest_airport').reset_index(drop=True)\n",
    "\n",
    "fun.compare_dataframes(unique_source_airports, unique_dest_airports) # Compare the dataframes\n",
    "\n",
    "# Check if all of the source airports are also in the destination airports\n",
    "unique_dest_airports['dest_airport'].isin(unique_source_airports['source_airport']).value_counts()\n",
    "\n",
    "# Get the values that are not in source_airports and append them to the end of the dataframe\n",
    "missing_airports = unique_dest_airports[~unique_dest_airports['dest_airport'].isin(unique_source_airports['source_airport'])]\n",
    "\n",
    "# Rename the columns to be identical\n",
    "missing_airports.columns = ['source_airport']\n",
    "\n",
    "# Combine the dataframes to get all the unique values\n",
    "unique_airports = pd.concat([unique_source_airports, missing_airports])\n",
    "unique_airports.columns = ['airportId']\n",
    "unique_airports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an array of unique airports in our dataset. Store the number of airports\n",
    "N_AIRPORTS = unique_airports.shape[0]\n",
    "\n",
    "# Output the number of airports\n",
    "print(f'Number of airports: {N_AIRPORTS}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the counts of each unique airport from the original dataframe and sort by airportId\n",
    "source_airports = pd.DataFrame(df['source_airport'].value_counts().sort_index())\n",
    "dest_airports = pd.DataFrame(df['dest_airport'].value_counts().sort_index())\n",
    "\n",
    "# Combine the two dataframes to the unique_airports dataframe to display counts\n",
    "unique_airports = pd.merge(unique_airports, source_airports, left_on='airportId', right_index=True, how='left')\n",
    "unique_airports = pd.merge(unique_airports, dest_airports, left_on='airportId', right_index=True, how='left')\n",
    "\n",
    "# Rename the columns to be more descriptive\n",
    "unique_airports.columns = ['airportId', 'source_flights', 'dest_flights']\n",
    "\n",
    "# Fill in the NaN values with 0\n",
    "unique_airports.fillna(0, inplace=True)\n",
    "\n",
    "# Change the columns to integers\n",
    "unique_airports['source_flights'] = unique_airports['source_flights'].astype(int)\n",
    "unique_airports['dest_flights'] = unique_airports['dest_flights'].astype(int)\n",
    "\n",
    "# Create column to keep track of double counts\n",
    "unique_airports['double_counts'] = 0\n",
    "\n",
    "# Make the index the airportId\n",
    "unique_airports.set_index('airportId', inplace=True)\n",
    "\n",
    "# Output the dataframe\n",
    "unique_airports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check if there are any trips where the source and destination are the same\n",
    "print(df[df['source_airport'] == df['dest_airport']])\n",
    "# There is one trip where the source and destination are the same\n",
    "\n",
    "''' INSTEAD OF RUNNING THE BELOW CODE...\n",
    "# Use loop to count double counted trips for each airport\n",
    "for index, row in source_airports.iterrows():\n",
    "    airport_id = row.iloc[0] # Get the airport id\n",
    "    print(airport_id)\n",
    "    # Count double counted trips where source airport is equal to dest airport\n",
    "    unique_airports.loc[airport_id, 'double_counts'] = ((df['source_airport'] == airport_id) & (df['dest_airport'] == airport_id)).sum()\n",
    "\n",
    "# Add start and ends and subtract double.counts to get the total number of times each airport was involved in a trip\n",
    "unique_airports['total'] = unique_airports['source_flights'] + unique_airports['dest_flights'] - unique_airports['double_counts'] # Add the two columns together\n",
    "\n",
    "# Output\n",
    "print(\"start + end - double_counts = total\")\n",
    "\n",
    "# Sort the rows by 'total' from least to greatest\n",
    "unique_airports = unique_airports.sort_values(by='total')\n",
    "\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ...Let's just add one to PKN's double counts\n",
    "unique_airports.loc[unique_airports.index == 'PKN', 'double_counts'] = 1\n",
    "unique_airports['total'] = unique_airports['source_flights'] + unique_airports['dest_flights'] - unique_airports['double_counts'] # Add the two columns together\n",
    "unique_airports = unique_airports.sort_values(by='total')\n",
    "\n",
    "unique_airports.loc['PKN']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initialize some variables to use in the loop\n",
    "airports_removed = 0\n",
    "df_copy = df.copy() # Create a copy of the dataframe to work with. This will be empty by the end of the loop\n",
    "\n",
    "# Create a dataframe to store the profits for the number of airports removed. Set index equal to the number of airports removed\n",
    "profitsStatic = pd.DataFrame(index=range(N_AIRPORTS + 1), columns=['profit']).rename_axis('airports_removed')\n",
    "\n",
    "# Set the first profit to the profit with no airports removed\n",
    "profitsStatic.at[0, 'profit'] = PROFIT_PER_TRIP * N_TRIPS - FEE_PER_AIRPORT * N_AIRPORTS\n",
    "profitsStatic.at[0, 'airport_removed'] = \"--\"\n",
    "\n",
    "# Iterate through each airport, removing one each iteration, to compare profits and find the max from the \n",
    "# formula profits = (PROFIT_PER_TRIP * trips_remaining) - FEE_PER_AIRPORT * airports_remaining\n",
    "for index, row in unique_airports.iterrows():\n",
    "    airport_id = index # Get the airport id\n",
    "    airports_removed += 1 # Increment the number of airports removed\n",
    "    airports_remaining = N_AIRPORTS - airports_removed # Calculate the number of airports remaining\n",
    "    \n",
    "    # Find all instances of the airport in the dataframe, as the source or dest airport, and remove them\n",
    "    df_copy = df_copy[(df_copy['source_airport'] != airport_id) & (df_copy['dest_airport'] != airport_id)]\n",
    "    trips_remaining = df_copy.shape[0]\n",
    "    \n",
    "    # Calculate the profit for the number of airports removed\n",
    "    airport_profit = (PROFIT_PER_TRIP * trips_remaining) - (FEE_PER_AIRPORT * airports_remaining)\n",
    "    \n",
    "    # Store the profit in the dataframe\n",
    "    profitsStatic.at[airports_removed, 'profit'] = airport_profit\n",
    "    profitsStatic.at[airports_removed, 'airport_removed'] = airport_id # Store the airport that was removed\n",
    "    \n",
    "# Output the max profit and the airport that was removed\n",
    "max_profit = profitsStatic['profit'].max() # Find the max profit\n",
    "max_profit_index = profitsStatic['profit'].idxmax() # Find the index of the max profit\n",
    "print(f'Max profit of ${max_profit} at {max_profit_index} airports removed from the system')\n",
    "\n",
    "profitsStatic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a plot of the profits\n",
    "plt.plot(profitsStatic.index, profitsStatic['profit'])\n",
    "plt.xlabel('Airports Removed')\n",
    "plt.ylabel('Profit')\n",
    "plt.title('Profit vs. Airports Removed')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **StarLift Airlines Analysis - Dynamic Update**\n",
    "In the static example, the counts for each airport were taken and sorted once and that was our final list for removal. To get a more accurate count, though, we want to get the counts for each airport every time we remove an airport since then the counts might change slightly and our order may be different."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our dataframe from the csv file\n",
    "df = pd.read_csv('./Data/airplaneRoutes.csv') # Read in the data from the csv file\n",
    "df.columns = ['source_airport', 'dest_airport'] # Traditional python naming conventions for columns\n",
    "\n",
    "# Initial assumptions\n",
    "PROFIT_PER_TRIP = 10000\n",
    "FEE_PER_AIRPORT = 200000\n",
    "N_TRIPS = df.shape[0]\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Static function for just this problem\n",
    "def get_airport_counts (df, double_counts=True, ascending=True):\n",
    "    import pandas as pd\n",
    "    # set the index to be the unique values of the source_airport\n",
    "    counts = df['source_airport'].unique()\n",
    "    \n",
    "    # Get the unique values of dest_airport that are not found in source_airport/already in the dataframe\n",
    "    missing_airports = df['dest_airport'][~df['dest_airport'].isin(counts)].unique()\n",
    "    \n",
    "    counts = np.append(counts, missing_airports)\n",
    "    \n",
    "    df_counts = pd.DataFrame(index=counts) # Create a new dataframe with the index set to the unique values of source_airport\n",
    "    \n",
    "    df_counts['source_airport'] = df['source_airport'].value_counts() # set the first row to the source_airport counts\n",
    "    df_counts['dest_airport'] = df['dest_airport'].value_counts() # set the second row to the dest_airport counts\n",
    "    \n",
    "    df_counts.fillna(0, inplace=True) # Fill in the NaN values with 0\n",
    "    df_counts['source_airport'] = df_counts['source_airport'].astype(int) # convert the columns to integers\n",
    "    df_counts['dest_airport'] = df_counts['dest_airport'].astype(int)\n",
    "    \n",
    "    # Double counts: the only row in our dataset where the source and destination are the same is PKN, and it only occurs once.\n",
    "    # Check first if PKN exists in our df_counts dataframe\n",
    "    if double_counts:\n",
    "        df_counts['double_counts'] = 0 # Create a column to store the double counts\n",
    "        if 'PKN' in df_counts.index:\n",
    "            df_counts.loc['PKN', 'double_counts'] = 1\n",
    "    \n",
    "    # Get the total number of trips for each airport\n",
    "    df_counts['total'] = df_counts['source_airport'] + df_counts['dest_airport'] - df_counts['double_counts']\n",
    "    \n",
    "    # Sort the dataframe by the total column\n",
    "    df_counts = df_counts.sort_values(by='total', ascending=ascending)\n",
    "    \n",
    "    return df_counts\n",
    "\n",
    "df_hardcoded = get_airport_counts(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dynamic function to get the counts of each unique ID from each column in the original dataframe\n",
    "def get_counts(df, tableId, col=[], double_counts=\"none\", ascending=True):\n",
    "    import pandas as pd\n",
    "    df_counts = pd.DataFrame() # Create an empty dataframe to store the counts\n",
    "    col_unique_vals = [] # Create an empty list to store the unique values\n",
    "    \n",
    "    # GET ALL THE UNIQUE ID'S FOR EVERY COLUMN IN THE DATAFRAME\n",
    "    for column in df.columns:\n",
    "        col_unique_vals = df[column].unique()\n",
    "        \n",
    "        # If it's not the first column, remove the values that are already in the dataframe\n",
    "        if column != df.columns[0]:\n",
    "            # Assign the values that are not in the dataframe to col_unique_vals\n",
    "            col_unique_vals = [val for val in col_unique_vals if val not in df_counts.iloc[:, 0].values]\n",
    "            \n",
    "            # Make a temporary dataframe to store the unique values\n",
    "            temp_df = pd.DataFrame(col_unique_vals)\n",
    "        \n",
    "            # Concatenate temp_df with df_counts\n",
    "            df_counts = pd.concat([df_counts, temp_df], ignore_index=True)\n",
    "        else:\n",
    "            # This is the first column, so create the dataframe based on the unique values in the column\n",
    "            df_counts = pd.DataFrame(col_unique_vals)\n",
    "            \n",
    "        col_unique_vals = [] # Reset the list of unique values\n",
    "    \n",
    "    # RENAME THE COLUMNS TO BE MORE DESCRIPTIVE. THIS WILL LATER BE SET TO THE INDEX\n",
    "    df_counts.columns = [tableId]\n",
    "    \n",
    "    # GET THE COUNTS OF EACH UNIQUE ID FROM EACH COLUMN IN THE ORIGINAL DATAFRAME\n",
    "    i = 1\n",
    "    for column in df.columns:\n",
    "        col_counts = pd.DataFrame(df[column].value_counts())\n",
    "        col_name = f'col_{i}'  # Create custom column name\n",
    "        col_counts.columns = [col_name]  # Assign custom column name\n",
    "        df_counts = pd.merge(df_counts, col_counts, left_on=tableId, right_index=True, how='left')\n",
    "        i += 1\n",
    "\n",
    "    # SET THE INDEX TO THE ID COLUMN\n",
    "    df_counts.set_index(tableId, inplace=True)\n",
    "    \n",
    "    # DYNAMICALLY ASSIGN COLUMN NAMES. FIRST CHECK IF THE USER PROVIDED A VALID LIST OF COLUMN NAMES. IF NOT, CREATE DEFAULT COLUMN NAMES\n",
    "    if len(col) < len(df_counts.columns):\n",
    "        num_missing_cols = len(df_counts.columns) - len(col)\n",
    "        default_cols = [f'col_{i+1}' for i in range(len(col), len(col) + num_missing_cols)]\n",
    "        col.extend(default_cols)\n",
    "    # Create a dictionary to map existing column names to new column names\n",
    "    column_mapping = {old_col: new_col for old_col, new_col in zip(df_counts.columns, col)}\n",
    "    df_counts.rename(columns=column_mapping, inplace=True)\n",
    "    \n",
    "    # REPLACE NaN VALUES WITH 0 AND CHANGE THE COLUMNS TO INTEGERS\n",
    "    df_counts.fillna(0, inplace=True)\n",
    "    for column in df_counts.columns:\n",
    "        df_counts[column] = df_counts[column].astype(int)\n",
    "    \n",
    "    # DOUBLE COUNTING\n",
    "    if double_counts == \"byrow\": # If there is at least two of one value in a row, count it as one\n",
    "        df_counts['double_counts'] = 0 # Create a column to store the double counts\n",
    "        columnNamesOfOriginalData = df.columns # Get the column names of the original data (used in the loop below)\n",
    "        \n",
    "        # Tally the double counts dynamically\n",
    "        for index in df_counts.index:\n",
    "            df_counts.at[index, 'double_counts'] = ((df[columnNamesOfOriginalData] == index).sum(axis=1) == len(col)).sum()\n",
    "            \n",
    "        # Add together all the rows (except for double_counts), subtracting double counts at the end\n",
    "        df_counts['total'] = df_counts.drop(columns='double_counts').sum(axis=1) - df_counts['double_counts']\n",
    "    elif double_counts == \"individual\": # Count every repeated value individually for each row\n",
    "        df_counts['total'] = df_counts.sum(axis=1) # need to figure out how to implement this\n",
    "    else:\n",
    "        # Otherwise, just add all the rows together\n",
    "        df_counts['total'] = df_counts.sum(axis=1)\n",
    "    \n",
    "    # SORT THE ROWS BY 'total' BASED ON THE ASCENDING PARAMETER\n",
    "    df_counts = df_counts.sort_values(by='total', ascending=ascending)\n",
    "        \n",
    "    return df_counts\n",
    "\n",
    "df_new = get_counts(df, \"airportId\", col=['source_flights', 'dest_flights'], double_counts=\"byrow\")\n",
    "df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fun.compare_dataframes(df_new, df_hardcoded) # Compare the dataframes\n",
    "\n",
    "# Locate where double counts are greater than 0\n",
    "print(df_new.loc[df_new['double_counts'] > 0])\n",
    "\n",
    "# Find the row where PKN is the source_airport and the dest_airport\n",
    "df.loc[(df['source_airport'] == 'PKN') & (df['dest_airport'] == 'PKN')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initialize some variables to use in the loop\n",
    "airports_removed = 0\n",
    "df_copy = df.copy() # Create a copy of the dataframe to work with. This will be empty by the end of the loop\n",
    "airport_counts = get_airport_counts(df_copy, double_counts=True) # Initialize airport_counts\n",
    "\n",
    "N_AIRPORTS = airport_counts.shape[0] # Initialize the number of airports\n",
    "\n",
    "# Create a dataframe to store the profits for the number of airports removed. Set index equal to the number of airports removed\n",
    "profitsDynamic = pd.DataFrame(index=range(airport_counts.shape[0] + 1), columns=['profit']).rename_axis('airports_removed')\n",
    "\n",
    "# Set the first profit to the profit with no airports removed\n",
    "profitsDynamic.at[0, 'profit'] = PROFIT_PER_TRIP * N_TRIPS - FEE_PER_AIRPORT * N_AIRPORTS\n",
    "profitsDynamic.at[0, 'airport_removed'] = \"--\"\n",
    "    \n",
    "while airport_counts.shape[0] > 0:\n",
    "    airport_id = airport_counts.index[0] # Get the airport id\n",
    "    \n",
    "    # Find all instances of the airport in the dataframe, as the source or dest airport, and remove them\n",
    "    df_copy = df_copy[(df_copy['source_airport'] != airport_id) & (df_copy['dest_airport'] != airport_id)]\n",
    "    trips_remaining = df_copy.shape[0] # Calculate the number of trips remaining\n",
    "    \n",
    "    # Recalculate the airport counts\n",
    "    airport_counts = get_airport_counts(df_copy)\n",
    "\n",
    "    airports_removed += 1 # Increment the number of airports removed\n",
    "    airports_remaining = airport_counts.shape[0] # Calculate the number of airports remaining\n",
    "    \n",
    "    # Calculate the profit for the number of airports removed\n",
    "    airport_profit = (PROFIT_PER_TRIP * trips_remaining) - (FEE_PER_AIRPORT * airports_remaining)\n",
    "    \n",
    "    # Store the profit in the dataframe\n",
    "    profitsDynamic.at[airports_removed, 'profit'] = airport_profit\n",
    "    profitsDynamic.at[airports_removed, 'airport_removed'] = airport_id\n",
    "    \n",
    "# Get rid of rows in the dataframe that are all NaN\n",
    "profitsDynamic = profitsDynamic.dropna(how='all')\n",
    "    \n",
    "# Output the max profit and the airport that was removed\n",
    "max_profit = profitsDynamic['profit'].max() # Find the max profit\n",
    "max_profit_index = profitsDynamic['profit'].idxmax() # Find the index of the max profit\n",
    "print(f'Max profit of ${max_profit} at {max_profit_index} airports removed from the system')\n",
    "\n",
    "profitsDynamic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a plot of the profits\n",
    "plt.plot(profitsDynamic.index, profitsDynamic['profit'])\n",
    "plt.xlabel('Airports Removed')\n",
    "plt.ylabel('Profit')\n",
    "plt.title('Profit vs. Airports Removed')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Compare Static vs. Dynamic**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine the dataframes for a side-by-side comparison\n",
    "profitsCombined = pd.concat([profitsStatic, profitsDynamic], axis=1)\n",
    "profitsCombined.columns = ['static_profit', 'static_airport_removed', 'dynamic_profit', 'dynamic_airport_removed']\n",
    "# Add to the dataframe a column for the difference between the static and dynamic profits\n",
    "profitsCombined['difference_in_profit'] = profitsCombined['dynamic_profit'] - profitsCombined['static_profit']\n",
    "profitsCombined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare the max profits between the static and dynamic methods\n",
    "max_profit_static = profitsStatic['profit'].max()\n",
    "max_profit_dynamic = profitsDynamic['profit'].max()\n",
    "max_profit_difference = max_profit_dynamic - max_profit_static\n",
    "print(f'Max profit difference between static and dynamic methods: ${max_profit_difference}')\n",
    "print(f'Static: ${max_profit_static}\\nDynamic: ${max_profit_dynamic}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the two profits on the same graph, also plot the difference in profits\n",
    "plt.plot(profitsCombined.index, profitsCombined['static_profit'], label='Static')\n",
    "plt.plot(profitsCombined.index, profitsCombined['dynamic_profit'], label='Dynamic')\n",
    "plt.plot(profitsCombined.index, profitsCombined['difference_in_profit'], label='Difference')\n",
    "plt.xlabel('Airports Removed')\n",
    "plt.ylabel('Profit')\n",
    "plt.title('Profit vs. Airports Removed')\n",
    "plt.legend() # Add a legend to the graph\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A more clear graph of the difference in profits\n",
    "plt.plot(profitsCombined.index, profitsCombined['difference_in_profit'], label='Difference')\n",
    "plt.xlabel('Airports Removed')\n",
    "plt.ylabel('Difference in Profit')\n",
    "plt.title('Profit vs. Airports Removed')\n",
    "plt.show()\n",
    "\n",
    "# Get the max difference in profits and the number of airports removed\n",
    "max_diff = profitsCombined['difference_in_profit'].max()\n",
    "max_diff_index = profitsCombined['difference_in_profit'].idxmax()\n",
    "print(f'Max difference in profit of ${max_diff} at {max_diff_index} airpots removed from the system')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **StarLift Airlines Analysis - Random Selection w/ Dynamic Update (Monte Carlo)**\n",
    "Next, we theorize that even though one airport has more/less trips than another, we don't know how many other airports will be impacted by the removal of the one we intend to remove, and if one airport might have a bigger impact by its removal than another on different airports. Another simulation may be needed to test different methods of airport removal to see if we can get an even higher max profit using some elements of randomness in our process. This next section is just to introduct the idea of random selection even though a Monte Carlo simulation is not intended for such a simple model. Here is a good summary of the methodology: https://aws.amazon.com/what-is/monte-carlo-simulation/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create our dataframe from the csv file\n",
    "df = pd.read_csv('./Data/airplaneRoutes.csv') # Read in the data from the csv file\n",
    "df.columns = ['source_airport', 'dest_airport'] # Traditional python naming conventions for columns\n",
    "\n",
    "# Initial assumptions\n",
    "PROFIT_PER_TRIP = 10000\n",
    "FEE_PER_AIRPORT = 200000\n",
    "N_TRIPS = df.shape[0]\n",
    "N_AIRPORTS = df['source_airport'].nunique() + df['dest_airport'][~df['dest_airport'].isin(df['source_airport'])].nunique()\n",
    "M_CARLO_WINDOWS = [int(round(N_AIRPORTS*0.05)), \n",
    "                   int(round(N_AIRPORTS*0.1)), \n",
    "                   int(round(N_AIRPORTS*0.2))] # 5%, 10%, and 20% of the total number of trips\n",
    "M_CARLO_RUNS = 10\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def MonteCarlo(df, charge, fee, numAirports, numTrips, mCarloWindows, runs, messages='all', profitToBeat=[0, 0]):\n",
    "    import time\n",
    "    start_time = time.time() # Get the start time; keep track of runtime\n",
    "\n",
    "    if profitToBeat[1] == 0:\n",
    "        profitToBeat[1] = numAirports\n",
    "\n",
    "    # Store the max we find. Initialize the dataframe\n",
    "    the_max_df = pd.DataFrame(index=range(numAirports + 1), columns=['profit']).rename_axis('airports_removed')\n",
    "    the_max_profit = profitToBeat[0]\n",
    "    the_max_airport_removed = profitToBeat[1]\n",
    "\n",
    "    # Seed the random number generator with the current time. This will make sure we get different results each time we run the code\n",
    "    np.random.seed()\n",
    "\n",
    "    for window in mCarloWindows:\n",
    "        iterations = math.ceil(numAirports / window) + 1 # Calculate the number of iterations\n",
    "        if messages in ['simple', 'all']: print(f'Window: {window}, Runs: {runs}, Iterations: {iterations}')\n",
    "        \n",
    "        for x in range(runs):\n",
    "            # Initialize some variables to use in the loop\n",
    "            airports_removed = 0\n",
    "            df_copy = df.copy() # Create a copy of the dataframe to work with. This will be empty by the end of the loop\n",
    "            airport_counts = get_airport_counts(df_copy, double_counts=True) # Initialize airport_counts\n",
    "            \n",
    "            # Create a dataframe to store the profits for the number of airports removed. Set index equal to the number of airports removed\n",
    "            profitsRandomSelection = pd.DataFrame(index=range(airport_counts.shape[0] + 1),\\\n",
    "                                                  columns=['profit']).rename_axis('airports_removed')\n",
    "\n",
    "            # Set the first profit to the profit with no airports removed\n",
    "            profitsRandomSelection.at[0, 'profit'] = charge * numTrips - fee * numAirports\n",
    "            profitsRandomSelection.at[0, 'airport_removed'] = \"--\"\n",
    "            \n",
    "            for i in range(iterations): # Iterate through the number of iterations\n",
    "                airports_in_window = airport_counts.index[:window] # Get airports in the window\n",
    "                \n",
    "                for j in airports_in_window: # Iterate through the window\n",
    "                    # Get a random airport from the window\n",
    "                    airport_id = airports_in_window[math.floor(len(airports_in_window) * np.random.random())]\n",
    "                        \n",
    "                    # Remove the airport from the airports_in_window\n",
    "                    airports_in_window = airports_in_window[airports_in_window != airport_id]\n",
    "                    \n",
    "                    # Find all instances of the airport in the dataframe, as the source or dest airports, and remove them\n",
    "                    df_copy = df_copy[(df_copy['source_airport'] != airport_id) & (df_copy['dest_airport'] != airport_id)]\n",
    "                    trips_remaining = df_copy.shape[0] # Calculate the number of trips remaining\n",
    "                    \n",
    "                    # Recalculate the airport counts\n",
    "                    airport_counts = get_airport_counts(df_copy)\n",
    "                \n",
    "                    airports_removed += 1 # Increment the number of airports removed\n",
    "                    airports_remaining = airport_counts.shape[0] # Calculate the number of airports remaining\n",
    "                    \n",
    "                    # Calculate the profit for the number of airports removed\n",
    "                    airport_profit = (charge * trips_remaining) - (fee * airports_remaining)\n",
    "                    \n",
    "                    # Store the profit in the dataframe\n",
    "                    profitsRandomSelection.at[airports_removed, 'profit'] = airport_profit\n",
    "                    profitsRandomSelection.at[airports_removed, 'airport_removed'] = airport_id\n",
    "                \n",
    "            # Output the max profit and the airport that was removed\n",
    "            max_profit = profitsRandomSelection['profit'].max() # Find the max profit\n",
    "            max_profit_index = profitsRandomSelection['profit'].idxmax() # Find the index of the max profit\n",
    "            \n",
    "            # Get how far into the runtime we are in minutes and seconds\n",
    "            elapsed_time = time.time() - start_time \n",
    "            elapsed_minutes = int(elapsed_time // 60)\n",
    "            elapsed_seconds = int(elapsed_time % 60)\n",
    "            \n",
    "            if messages in ['all']: print(f'{x + 1}: Max profit of ${max_profit} at {max_profit_index} airports removed ({elapsed_minutes} min {elapsed_seconds} sec)')\n",
    "\n",
    "            # If the max profit is greater than what we have, record it. If it's the same but at a lower count fo airports removed, record\n",
    "            if max_profit > the_max_profit:\n",
    "                if messages in ['simple', 'all']: print(f\">> The max profit changed from {the_max_profit} to {max_profit} at {max_profit_index} airports removed\")\n",
    "                the_max_df = profitsRandomSelection.copy()\n",
    "                the_max_profit = max_profit\n",
    "                the_max_airport_removed = max_profit_index\n",
    "            elif max_profit == the_max_profit and max_profit_index < the_max_airport_removed:\n",
    "                if messages in ['simple', 'all']: print(f\"The max profit stayed the same at {max_profit}, but was achieved with {max_profit_index} airports removed\")\n",
    "                the_max_df = profitsRandomSelection.copy()\n",
    "                the_max_profit = max_profit\n",
    "                the_max_airport_removed = max_profit_index\n",
    "                \n",
    "        if messages in ['simple', 'all']: print() # Add a space between each window\n",
    "        \n",
    "    # Output total runtime  \n",
    "    elapsed_time = time.time() - start_time # Get how far into the runtime we are in minutes and seconds\n",
    "    elapsed_minutes = int(elapsed_time // 60)\n",
    "    elapsed_seconds = int(elapsed_time % 60) \n",
    "    if messages in ['simple', 'all']: print(f'\\nTotal runtime: {elapsed_minutes} min {elapsed_seconds} sec')\n",
    "    \n",
    "    # Output a graph of the max profit and the_max_df\n",
    "    plt.plot(the_max_df.index, the_max_df['profit'])\n",
    "    plt.xlabel('Airports Removed')\n",
    "    plt.ylabel('Profit')\n",
    "    plt.title('Max Profit vs. Airports Removed')\n",
    "    plt.show()\n",
    "    \n",
    "    return the_max_df, the_max_profit, the_max_airport_removed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clear some variables we don't need and won't use again before running to make sure it runs as quickly as possible\n",
    "del airport_counts, airport_id, airport_profit, airports_remaining, airports_removed, dest_airports, df_copy, df_hardcoded, df_new,\\\n",
    "    index, max_diff, max_diff_index, max_profit, max_profit_difference, max_profit_dynamic, max_profit_index, max_profit_static, \\\n",
    "    missing_airports, profitsDynamic, profitsStatic, row, source_airports, trips_remaining, unique_airports, unique_dest_airports, \\\n",
    "    unique_source_airports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "profitsMonteCarlo, max_profit_mc, max_airports_rem_mc = MonteCarlo(df, PROFIT_PER_TRIP, FEE_PER_AIRPORT, N_AIRPORTS, N_TRIPS, M_CARLO_WINDOWS, M_CARLO_RUNS, messages='all')\n",
    "profitsMonteCarlo\n",
    "# Max profit calculated was with dynamic at $359,360,000 with 2567 airports removed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Compare Monte Carlo vs. Static and Dynamic**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare the max profits between the static and dynamic methods\n",
    "max_profit_dynamic = profitsCombined['dynamic_profit'].max()\n",
    "max_profit_difference = max_profit_dynamic - max_profit_mc\n",
    "print(f'Max profit difference between static and dynamic methods: ${max_profit_difference}')\n",
    "print(f'Monte Carlo: ${max_profit_mc}\\nDynamic: ${max_profit_dynamic}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine the dataframes for a side-by-side comparison\n",
    "profitsCombined = pd.concat([profitsCombined, profitsMonteCarlo], axis=1)\n",
    "profitsCombined.columns = ['static_profit', 'static_airport_removed', 'dynamic_profit', 'dynamic_airport_removed', 'difference_in_profit', 'mc_profit', 'mc_airport_removed']\n",
    "# Add to the dataframe a column for the difference between the static and dynamic profits\n",
    "profitsCombined['mc_difference_in_profit'] = profitsCombined['mc_profit'] - profitsCombined['dynamic_profit']\n",
    "profitsCombined['mc_w_static_difference'] = profitsCombined['mc_profit'] - profitsCombined['static_profit']\n",
    "profitsCombined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# A graph of the difference in profits\n",
    "plt.plot(profitsCombined.index, profitsCombined['mc_difference_in_profit'], label='M-Carlo and Dynamic')\n",
    "plt.plot(profitsCombined.index, profitsCombined['difference_in_profit'], label='Dynamic and Static')\n",
    "plt.plot(profitsCombined.index, profitsCombined['mc_w_static_difference'], label='M-Carlo and Static')\n",
    "plt.xlabel('Airports Removed')\n",
    "plt.ylabel('Profit')\n",
    "plt.title('Profit vs. Airports Removed')\n",
    "plt.legend()\n",
    "plt.show()\n",
    "\n",
    "# Get the max difference in profits and the number of airports removed\n",
    "max_diff = profitsCombined['mc_difference_in_profit'].max()\n",
    "max_diff_index = profitsCombined['mc_difference_in_profit'].idxmax()\n",
    "print(f'Max difference in profit of ${max_diff} at {max_diff_index} airports removed from the system')"
   ]
  }
 ],
 "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}