SPENSER_notebook_3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import csv\n",
    "import sys\n",
    "\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read CSV file containing the persons from the North East of England\n",
    "df_persons_NE_Household_composition__dir = r'C:\\Users\\b9055315\\Documents\\PhD_PROJECT\\Synthetic_population_developement\\SPENSER\\Data\\NE_only' # use your path\n",
    "df_persons_NE_Household_composition__file = os.path.join(df_persons_NE_Household_composition__dir, \"df_persons_NE_clean.csv\")\n",
    "df_persons_NE_Household_composition = pd.read_csv(df_persons_NE_Household_composition__file, index_col=None, header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read CSV file containing the households from the North East of England\n",
    "df_households_NE_extended__dir = r'C:\\Users\\b9055315\\Documents\\PhD_PROJECT\\Synthetic_population_developement\\SPENSER\\Data\\NE_only' # use your path\n",
    "df_households_NE_extended_file = os.path.join(df_households_NE_extended__dir, \"df_households_NE_clean.csv\")\n",
    "df_households_NE_extended = pd.read_csv(df_households_NE_extended_file, index_col=None, header=0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read CSV file containing the classification of the OA:\n",
    "Rural_Urban_OA_dir = r'C:\\Users\\b9055315\\Documents\\PhD_PROJECT\\Synthetic_population_developement\\Data\\ONS\\Rural_Urban_OAlevels\\RUC11_OA11_EW' # use your path\n",
    "df_Rural_Urban_OA_file = os.path.join(Rural_Urban_OA_dir, \"RUC11_OA11_EW.csv\")\n",
    "df_Rural_Urban_OA = pd.read_csv(df_Rural_Urban_OA_file, index_col=None, header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Join df_persons_NE_Household_composition with df_Rural_Urban_OA in order to get the type of rural/urban area per OA area\n",
    "df_persons_NE_Household_composition = df_persons_NE_Household_composition.merge(df_Rural_Urban_OA, left_on='Area_OA_x', right_on='OA11CD', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove unnecesary columns\n",
    "df_persons_NE_Household_composition= df_persons_NE_Household_composition.drop(['OA11CD', 'RUC11CD', 'BOUND_CHGIND', 'ASSIGN_CHGIND', 'ASSIGN_CHREASON'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Urban major conurbation                                     1152170\n",
       "Urban city and town                                          993870\n",
       "Rural town and fringe                                        311330\n",
       "Rural village                                                 65767\n",
       "Rural hamlets and isolated dwellings                          42269\n",
       "Rural town and fringe in a sparse setting                     28963\n",
       "Rural village in a sparse setting                             19501\n",
       "Rural hamlets and isolated dwellings in a sparse setting      18721\n",
       "Urban city and town in a sparse setting                       12926\n",
       "Name: RUC11, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Calculate the number of different urban classifications values\n",
    "df_persons_NE_Household_composition['RUC11'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replace RUC11 values for the ones that appear in the nts9901.ods table \n",
    "#(Full car driving licence holders by gender, region and Rural-Urban Classification1: 17 years old and over, England, 2002/03 and 2020)\n",
    "#(4 categories only Urban Conurbation, Urban city and town, Rural Village, Hamlet and Isolated Dwelling, Rural town and fringe)\n",
    "\n",
    "#Urban Conurbation\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Urban major conurbation', 'RUC11'] = 'Urban_Conurbation'\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Urban minor conurbation', 'RUC11'] = 'Urban_Conurbation'\n",
    "\n",
    "#Urban city and town\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Urban city and town', 'RUC11'] = 'Urban_city_and_town'\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Urban city and town in a sparse setting', 'RUC11'] = 'Urban_city_and_town'\n",
    "\n",
    "#Rural Village, Hamlet and Isolated Dwelling\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Rural hamlets and isolated dwellings', 'RUC11'] = 'Rural_Village_Hamlet_and_Isolated_Dwelling'\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Rural hamlets and isolated dwellings in a sparse setting', 'RUC11'] = 'Rural_Village_Hamlet_and_Isolated_Dwelling'\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Rural village', 'RUC11'] = 'Rural_Village_Hamlet_and_Isolated_Dwelling'\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Rural village in a sparse setting', 'RUC11'] = 'Rural_Village_Hamlet_and_Isolated_Dwelling'\n",
    "\n",
    "#Rural town and fringe\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Rural town and fringe', 'RUC11'] = 'Rural_town_and_fringe'\n",
    "df_persons_NE_Household_composition.loc[df_persons_NE_Household_composition.RUC11 == 'Rural town and fringe in a sparse setting', 'RUC11'] = 'Rural_town_and_fringe'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Urban_Conurbation                             1152170\n",
       "Urban_city_and_town                           1006796\n",
       "Rural_town_and_fringe                          340293\n",
       "Rural_Village_Hamlet_and_Isolated_Dwelling     146258\n",
       "Name: RUC11, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# AGAIN:\n",
    "#Calculate the number of different urban classifications values\n",
    "df_persons_NE_Household_composition['RUC11'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new empty column for the driving license (bool)\n",
    "df_persons_NE_Household_composition[\"Driving_license\"] = np.nan\n",
    "df_persons_NE_Household_composition[\"Driving_license\"] = df_persons_NE_Household_composition[\"Driving_license\"].astype('bool')   \n",
    "df_persons_NE_Household_composition[\"Driving_license\"] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "## FORCE ONLY ONE PERSON PER HOUSEHOLD TO BE ASSIGNED A DRIVING LICENCE IF HE/SHE LIVES IN A HOUSEHOLD WITH AT LEAST ONE CAR\n",
    "# Create a dataframe only containing people older than 17 and with at least one car in the hosuehold\n",
    "df_persons_household_car_17plus = df_persons_NE_Household_composition.loc[(df_persons_NE_Household_composition['LC4202_C_CARSNO_x'] > 1) & (df_persons_NE_Household_composition['Age'] > 17)]\n",
    "df_persons_household_car_17plus.sample(frac=1)\n",
    "\n",
    "# Create a dataframe containing only one person per household (based on 'VALUES FROM NTS0201' PK value) with at least one car\n",
    "df_persons_driving_licence_forced = df_persons_household_car_17plus.drop_duplicates(subset = 'HID_AreaOA_x')\n",
    "\n",
    "\n",
    "\n",
    "## SELECT THE REMAINING DRIVING LICENCE TO PEOPLE BASED ON NATIONAL TRAVEL SURVEY TABLES (NTS9901 AND NTS0201)\n",
    "# List with the gender values (1:male, 2:female)\n",
    "gender_list = [1,2]\n",
    "\n",
    "## VALUES FROM NTS9901\n",
    "# Save in a list the different values of urban/rural areas\n",
    "#rural_urban_areas_names_list = ['Urban_Conurbation', 'Urban_city_and_town', 'Rural_town_and_fringe', 'Rural_Village_Hamlet_and_Isolated_Dwelling']\n",
    "\n",
    "## VALUES FROM NTS0201\n",
    "#List containg the range values of groups of age\n",
    "age_range_list = [(18,20), (21,29), (30,39), (40,49), (50,59), (60,69), (70,120)]\n",
    "\n",
    "# Values given by me to force to choose specific people when there is not a car, one car and more than one car available in the household\n",
    "household_car_weight_list= [0.2,0.3,0.5]\n",
    "\n",
    "## VALUES FROM NTS0201\n",
    "# % of men by age that have a driving licence\n",
    "men_driving_percentage_list = [0.34,0.65,0.83,0.89,0.89,0.90,0.81]\n",
    "\n",
    "## VALUES FROM NTS0201\n",
    "# % of women by age that have a driving licence [ADAPTED TO MATCH THE % IN THE NORTH EAST!!]\n",
    "women_driving_percentage_list = [0.31,0.54,0.66,0.74,0.74,0.70,0.49]\n",
    "\n",
    "#Create an empty list where the small blocks of dataframes will be stored\n",
    "df_persons_temp = []\n",
    "\n",
    "\n",
    "\n",
    "for gender in gender_list:\n",
    "    \n",
    "    # create a dataframe only containing males (1) or females (2)\n",
    "    if gender ==1:\n",
    "        df_gender = df_persons_NE_Household_composition.loc[(df_persons_NE_Household_composition['Sex'] == gender)]\n",
    "\n",
    "    elif gender == 2:\n",
    "        df_gender = df_persons_NE_Household_composition.loc[(df_persons_NE_Household_composition['Sex'] == gender)]\n",
    "        \n",
    "    \n",
    "    for age_range in age_range_list:\n",
    "            \n",
    "        # Choose the values of the percentage of people driving based on their age and GENDER:\n",
    "        if age_range == (18,20):\n",
    "            if gender == 1:\n",
    "                driving_percentage = men_driving_percentage_list[0]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[0]\n",
    "        elif age_range == (21,29):\n",
    "            if gender == 1:\n",
    "                driving_percentage = men_driving_percentage_list[1]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[1]\n",
    "        elif age_range == (30,39):\n",
    "            if gender == 1:\n",
    "                driving_percentage = men_driving_percentage_list[2]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[2]\n",
    "        elif age_range == (40,49):\n",
    "            if gender == 1:\n",
    "                driving_percentage = men_driving_percentage_list[3]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[3]\n",
    "        elif age_range == (50,59):\n",
    "            if gender == 1:\n",
    "                driving_percentage = men_driving_percentage_list[4]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[4]\n",
    "        elif age_range == (60,69):\n",
    "            if gender == 1:\n",
    "                driving_percentage = men_driving_percentage_list[5]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[5]\n",
    "        elif age_range == (70,120):\n",
    "            if gender == 1: \n",
    "                driving_percentage = men_driving_percentage_list[6]\n",
    "            else:\n",
    "                driving_percentage = women_driving_percentage_list[6] \n",
    "                \n",
    "                \n",
    "        # Create a dataframe containing only the previous selected gender and one specific rural/urban area\n",
    "        globals()[f\"df_{gender}\"] = df_gender\n",
    "        \n",
    "        # Create a dataframe containing only the previous selected gender and rural/urban area with a specific range of age (e.g. 17-20) \n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"] = globals()[f\"df_{gender}\"].loc[((globals()[f\"df_{gender}\"]['Age'] >= age_range[0])) &((globals()[f\"df_{gender}\"]['Age'] <= age_range[1]))]\n",
    "            \n",
    "            \n",
    "        # Dataframe containing only those people assigned a driving licence (forced) in a specific range of age (e.g. 17-20), sex and urban/rural location\n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"] = df_persons_driving_licence_forced.loc[(df_persons_driving_licence_forced['Age'] >= age_range[0]) & (df_persons_driving_licence_forced['Age'] <= age_range[1]) & (df_persons_driving_licence_forced['Sex'] == gender)]\n",
    "            \n",
    "            \n",
    "        # Remove those forced people to have a driving licence and keep only those who can be assigned a driving licence based on age, sex and urban/rural location attribute values\n",
    "        # Concatenate the dataframe containing all persons from the same sex, urban/rural area and range of year, with the ones from these categories that have been selected to get a driving licence    \n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"] = (pd.concat([globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"], globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"]]))\n",
    "\n",
    "        # Remove duplicates and keep only those who were not assigned a driving licence\n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"] = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"].drop_duplicates(keep = False)\n",
    "            \n",
    "        #print(len(globals()[f\"df_{gender}_{area}_{age_range[0]}_{age_range[1]}_remaining\"]))\n",
    "              \n",
    "            \n",
    "        # Split the previous dataframe in three: people with no car in the household, people with one car and people with more than one\n",
    "            \n",
    "        ## No car\n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_nocar\"] = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"].loc[((globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"]['LC4202_C_CARSNO_x'] == 1))]\n",
    "            \n",
    "        ##One car\n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecar\"] = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"].loc[((globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"]['LC4202_C_CARSNO_x'] == 2))]\n",
    "            \n",
    "        ##One car plus\n",
    "        globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecarplus\"] = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"].loc[((globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining\"]['LC4202_C_CARSNO_x'] == 3))]\n",
    "                           \n",
    "            \n",
    "\n",
    "        if int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[0]*driving_percentage - len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"]), 0)) > 0:\n",
    "                \n",
    "            # Choose a specific number of people who live in households without car based on values from household_car_weight_list MINUS the number of forced people assigned with a driving licence previously gropued by sex, age and urban/rural area (dataframe: \"df_persons_driving_licence_forced\"), (just because they live in a household with at least one car)\n",
    "            if int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[0]*driving_percentage - len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"])/3, 0)) <= len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_nocar\"]):\n",
    "                df_no_car_chosen = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_nocar\"].sample(int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[0]*driving_percentage, 0)) - int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"])/3, 0)))\n",
    "            else:\n",
    "                df_no_car_chosen = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_nocar\"]\n",
    "            \n",
    "        else:\n",
    "            df_no_car_chosen = pd.DataFrame()\n",
    "\n",
    "            \n",
    "        if int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[1]*driving_percentage - len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"]), 0)) > 0:\n",
    "\n",
    "        # Choose a specific number of people who live in households with ONE car based on values from household_car_weight_list\n",
    "            if int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[1]*driving_percentage - len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"])/3, 0)) <= len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecar\"]):\n",
    "                df_no_car_chosen = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecar\"].sample(int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[1]*driving_percentage, 0)) - int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"])/3, 0))) \n",
    "            else:\n",
    "                df_one_car_chosen = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecar\"]\n",
    "  \n",
    "        else:\n",
    "            df_one_car_chosen = pd.DataFrame()\n",
    "            \n",
    "        if int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[2]*driving_percentage - len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"]), 0)) > 0:\n",
    "                \n",
    "        # Choose a specific number of people who live in households with more than one car based on values from household_car_weight_list\n",
    "            if int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[2]*driving_percentage - len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"])/3, 0)) <= len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecarplus\"]):\n",
    "                df_one_car_plus_chosen = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecarplus\"].sample(int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"])*household_car_weight_list[2]*driving_percentage, 0)) - int(round(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"])/3, 0))) \n",
    "            else:\n",
    "                df_one_car_plus_chosen = globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_remaining_onecarplus\"]\n",
    "                                                                                                                 \n",
    "        else:\n",
    "            df_one_car_plus_chosen = pd.DataFrame()\n",
    "\n",
    "\n",
    "\n",
    "        # Concatenate the previous selected people AND the forced people\n",
    "        # THESE ARE THE ONES SELECTED BASED (N SEX, LOCATION AND AGE) AND THE PREVIOUS FORCED ONES\n",
    "        # THESE ARE THE ONES THAT WILL HAVE A DRIVING LICENCE (UP TO NOW)\n",
    "        df_persons_driving_selection = (pd.concat([df_no_car_chosen, df_one_car_chosen, df_one_car_plus_chosen, globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}_forced\"]]))\n",
    "\n",
    "                                             \n",
    "        # Check the number of chosen people to be assigned and compared to the value that should be reached.\n",
    "        # If the value is lower, the remaining should be collected randomly from the dataframe that contains people with the \n",
    "        # same gender and age range\n",
    "        if int(round(len(df_persons_driving_selection),0)) < int(round((len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"]) * driving_percentage),0)):\n",
    "            leftovers = int(len(globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"]) * driving_percentage) - len(df_persons_driving_selection)\n",
    "         \n",
    "        # Concatenate the dataframe containing all persons from the same sex, urban/rural area and range of year, with the ones from these categories that have been selected to get a driving licence (forced and chosen)\n",
    "        df_with_duplicates = (pd.concat([globals()[f\"df_{gender}_{age_range[0]}_{age_range[1]}\"], df_persons_driving_selection]))\n",
    "            \n",
    "        # Remove duplicates and keep only those who were not assigned a driving licence\n",
    "        df_leftovers = df_with_duplicates.drop_duplicates(keep = False)\n",
    "            \n",
    "        # If leftover value is > 0, then new drivers (value of leftovers) have to be selected and assigne then a driving licence\n",
    "        if int(len(df_leftovers)) > 0:\n",
    "            df_leftovers_driving = df_leftovers.sample(int(leftovers))\n",
    "                \n",
    "            # Concatenate the dataframe containing all persons from the same sex, urban/rural area and range of year, with the ones from these categories that have been selected to get a driving licence\n",
    "            df_leftovers_with_duplicates = (pd.concat([df_leftovers, df_leftovers_driving]))\n",
    "                \n",
    "            # Remove duplicates and keep only those who were not assigned a driving licence\n",
    "            df_people_no_driving_licence = df_leftovers_with_duplicates.drop_duplicates(keep = False)\n",
    "            \n",
    "            # Concatenate this new drivers with the previous ones:\n",
    "            df_persons_driving_selection = (pd.concat([df_persons_driving_selection, df_leftovers_driving]))\n",
    "                \n",
    "            # Update attribute value \"Driving_licence\" = True to those who have been assigned a driving licence before\n",
    "            df_persons_driving_selection[\"Driving_license\"] = True\n",
    "        else:\n",
    "            df_persons_driving_selection[\"Driving_license\"] = True\n",
    "                \n",
    "                     \n",
    "        #Concatenate the df_persons_driving_selection and the 'leftovers' that were not assigned a driving licence\n",
    "        # This new dataframe should have all persons of the specific gender, urban/rural area and range of age as the orignal dataframe\n",
    "        df_persons = (pd.concat([df_persons_driving_selection, df_people_no_driving_licence]))\n",
    "            \n",
    "        #Append the dataframe into the temporal list\n",
    "        df_persons_temp.append(df_persons)\n",
    "        \n",
    "#concatenate all persons (lists of the 'df_persons_NE_OA_HID_temp' list) in one dataframe\n",
    "df_persons_NE_after_driving = pd.concat(df_persons_temp, axis=0, ignore_index=True)\n",
    "\n",
    "# Create a new dataframe with only the people under 18 years old \n",
    "#(this group has not been considered before and has to be included (without driving licence) in order to \n",
    "# have the total number of people in the final dataset)\n",
    "df_people_under18 = df_persons_NE_Household_composition.loc[(df_persons_NE_Household_composition['Age']< 18)]                \n",
    "\n",
    "# Concatenate previous dataframe generated with the people under 18:\n",
    "df_persons_NE_after_driving = (pd.concat([df_persons_NE_after_driving, df_people_under18]))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1529912"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of people with driving licence\n",
    "aa = df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Driving_license'] == True)]\n",
    "\n",
    "len(aa)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "persons WITH driving license and at least one car in the household:  1227728\n",
      "persons WITHOUT driving license and at least one car in the household:  246908\n",
      "person WITH driving license but NO car in the household:  302184\n",
      "TOTAL people with driving license:  1529912\n",
      "% persons WITH driving license and at least one car in the household:  80.24827571781906\n",
      "% person WITH driving license but NO car in the household:  19.751724282180934\n"
     ]
    }
   ],
   "source": [
    "# ANALYSIS of people with/without driving licence and access to car in the household\n",
    "\n",
    "df_persons_car_and_driving_licence = df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Age'] > 17) & (df_persons_NE_after_driving['LC4202_C_CARSNO_x'] > 1) &(df_persons_NE_after_driving['Driving_license'] == True)]\n",
    "a = len(df_persons_car_and_driving_licence)\n",
    "\n",
    "\n",
    "df_persons_car_and_NOdriving_licence = df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Age'] > 17) & (df_persons_NE_after_driving['LC4202_C_CARSNO_x'] > 1) &(df_persons_NE_after_driving['Driving_license'] == False)]\n",
    "b = len(df_persons_car_and_NOdriving_licence)\n",
    "\n",
    "df_persons_Nocar_and_driving_licence = df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Age'] > 17) & (df_persons_NE_after_driving['LC4202_C_CARSNO_x'] == 1) &(df_persons_NE_after_driving['Driving_license'] == True)]\n",
    "c = len(df_persons_Nocar_and_driving_licence)\n",
    "\n",
    "df_persons_drivingLicence = df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Age'] > 17) & (df_persons_NE_after_driving['Driving_license'] == True)]\n",
    "d = len(df_persons_drivingLicence)\n",
    "\n",
    "print('persons WITH driving license and at least one car in the household: ', a)\n",
    "print('persons WITHOUT driving license and at least one car in the household: ', b)\n",
    "print('person WITH driving license but NO car in the household: ', c)\n",
    "print('TOTAL people with driving license: ', d)\n",
    "\n",
    "\n",
    "perct_people_with_drivingLicence_AND_car_in_household = a/d *100\n",
    "perct_people_with_drivingLicence_BUT_NO_car_in_household = c/d *100\n",
    "\n",
    "print('% persons WITH driving license and at least one car in the household: ', perct_people_with_drivingLicence_AND_car_in_household)\n",
    "print('% person WITH driving license but NO car in the household: ', perct_people_with_drivingLicence_BUT_NO_car_in_household)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "15591 106250 135124 135113 162177 141735 131633 30266 57213 27677 16700 20045 15749 30810\n"
     ]
    }
   ],
   "source": [
    "a= len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 18) & (df_persons_NE_after_driving['Age'] <= 20) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "b=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 21) & (df_persons_NE_after_driving['Age'] <= 29) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "c=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 30) & (df_persons_NE_after_driving['Age'] <= 39) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "d=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 40) & (df_persons_NE_after_driving['Age'] <= 49) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "e=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 50) & (df_persons_NE_after_driving['Age'] <= 59) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "f=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 60) & (df_persons_NE_after_driving['Age'] <= 69) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "g=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] > 70) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "\n",
    "aa= len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 18) & (df_persons_NE_after_driving['Age'] <= 20) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "bb=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 21) & (df_persons_NE_after_driving['Age'] <= 29) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "cc=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 30) & (df_persons_NE_after_driving['Age'] <= 39) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "dd=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 40) & (df_persons_NE_after_driving['Age'] <= 49) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "ee=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 50) & (df_persons_NE_after_driving['Age'] <= 59) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "ff=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] >= 60) & (df_persons_NE_after_driving['Age'] <= 69) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "gg=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 1) & (df_persons_NE_after_driving['Age'] > 70) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "\n",
    "print(a,b,c,d,e,f,g,aa,bb,cc,dd,ee,ff,gg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13495 83874 110998 117755 142343 116379 99522 30040 71450 57182 41374 50013 49877 101423\n"
     ]
    }
   ],
   "source": [
    "a= len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 18) & (df_persons_NE_after_driving['Age'] <= 20) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "b=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 21) & (df_persons_NE_after_driving['Age'] <= 29) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "c=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 30) & (df_persons_NE_after_driving['Age'] <= 39) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "d=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 40) & (df_persons_NE_after_driving['Age'] <= 49) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "e=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 50) & (df_persons_NE_after_driving['Age'] <= 59) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "f=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 60) & (df_persons_NE_after_driving['Age'] <= 69) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "g=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] > 70) & (df_persons_NE_after_driving['Driving_license'] == True)])\n",
    "\n",
    "\n",
    "aa= len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 18) & (df_persons_NE_after_driving['Age'] <= 20) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "bb=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 21) & (df_persons_NE_after_driving['Age'] <= 29) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "cc=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 30) & (df_persons_NE_after_driving['Age'] <= 39) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "dd=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 40) & (df_persons_NE_after_driving['Age'] <= 49) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "ee=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 50) & (df_persons_NE_after_driving['Age'] <= 59) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "ff=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] >= 60) & (df_persons_NE_after_driving['Age'] <= 69) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "gg=len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['Age'] > 70) & (df_persons_NE_after_driving['Driving_license'] == False)])\n",
    "\n",
    "\n",
    "print(a,b,c,d,e,f,g,aa,bb,cc,dd,ee,ff,gg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "289194 190563 479757\n"
     ]
    }
   ],
   "source": [
    "a= len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['RUC11'] == 'Urban_Conurbation') & (df_persons_NE_after_driving['Driving_license'] == True) & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "b = len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['RUC11'] == 'Urban_Conurbation') & (df_persons_NE_after_driving['Driving_license'] == False) & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "c = len(df_persons_NE_after_driving.loc[(df_persons_NE_after_driving['Sex'] == 2) & (df_persons_NE_after_driving['RUC11'] == 'Urban_Conurbation') & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "print(a, b, c)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "207767 70291 278058\n"
     ]
    }
   ],
   "source": [
    "aaa = len(df_persons_NE_after_driving.loc[ (df_persons_NE_after_driving['RUC11'] == 'Rural_town_and_fringe') & (df_persons_NE_after_driving['Driving_license'] == True) & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "bbb = len(df_persons_NE_after_driving.loc[ (df_persons_NE_after_driving['RUC11'] == 'Rural_town_and_fringe') & (df_persons_NE_after_driving['Driving_license'] == False) & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "ccc = len(df_persons_NE_after_driving.loc[ (df_persons_NE_after_driving['RUC11'] == 'Rural_town_and_fringe') & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "print(aaa, bbb, ccc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1529912 611530\n"
     ]
    }
   ],
   "source": [
    "rr = len(df_persons_NE_after_driving.loc[ (df_persons_NE_after_driving['Driving_license'] == True) & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "ss = len(df_persons_NE_after_driving.loc[ (df_persons_NE_after_driving['Driving_license'] == False) & (df_persons_NE_after_driving['Age'] > 17)])\n",
    "\n",
    "print(rr,ss) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Export the df_persons_NE_household_composition_updated\n",
    "\n",
    "df_persons_NE_drivinglicence_20222306_correct = df_persons_NE_after_driving\n",
    "\n",
    "df_persons_NE_drivinglicence_20222306_correct.to_csv(r'C:\\Users\\b9055315\\Documents\\PhD_PROJECT\\Synthetic_population_developement\\SPENSER\\Data\\2019\\Driving_licence\\df_persons_NE_drivinglicence_20222306_correct.csv', encoding='utf-8', header=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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": 2
}