airports.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bbd24237",
   "metadata": {},
   "source": [
    "![OurAirports logo](https://ourairports.com/images/logo-196px.png \"OurAirports logo\")\n",
    "<div align=\"center\">\n",
    "\n",
    "## Scraping world airports data\n",
    "</div>\n",
    "\n",
    "Document explaining how world airports data is processed and saved in PostgreSQL database.\n",
    "\n",
    "Link to data from Our Airports: [airports data data](https://davidmegginson.github.io/ourairports-data/airports.csv)\n",
    "\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "234fe7c4",
   "metadata": {},
   "source": [
    "# 1. Download and load data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5932173",
   "metadata": {},
   "source": [
    "## Data is in csv format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfbd53bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b0ac8d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_link = \"https://davidmegginson.github.io/ourairports-data/airports.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "891ff9bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_csv = pd.read_csv(airport_link)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61f0bd35",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_csv.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bfcd2ba2",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e89bf6c",
   "metadata": {},
   "source": [
    "## Modify dataframe to display only columns that are valuable for our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83087bc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_csv.columns.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0cc9c73",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_airports = airport_csv[\n",
    "    [\n",
    "        \"id\",\n",
    "        \"name\",\n",
    "        \"type\",\n",
    "        \"elevation_ft\",\n",
    "        \"iso_country\",\n",
    "        \"iso_region\",\n",
    "        \"gps_code\",\n",
    "        \"iata_code\",\n",
    "        \"local_code\",\n",
    "        \"home_link\",\n",
    "        \"wikipedia_link\",\n",
    "        \"latitude_deg\",\n",
    "        \"longitude_deg\",\n",
    "    ]\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ad7cdcb",
   "metadata": {},
   "source": [
    "## Change NaN values to None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70014656",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_airports = filter_airports.where(pd.notnull(filter_airports), None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1c2b723",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_airports"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa9f6ff2",
   "metadata": {},
   "source": [
    "# 3. Create foreign key for airport type if not in db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b38b9a95",
   "metadata": {},
   "outputs": [],
   "source": [
    "from apps.civic_structure.models import Airport, AirportType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a86d5ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_types = set(filter_airports[\"type\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b98d221",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "airport_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f8c92b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "[AirportType.objects.get_or_create(name=item) for item in airport_types if item]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76a99af3",
   "metadata": {},
   "source": [
    "# 4. Save in database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6546dae",
   "metadata": {},
   "source": [
    "## Check state and country iso codes in database and airport for differences to prevent errors saving PK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14117f80",
   "metadata": {},
   "outputs": [],
   "source": [
    "from apps.administrative_area.models import Country, State"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb5f535d",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_iso = set(Country.objects.order_by().values_list('iso2', flat=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6fed44b",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_country_iso = set(filter_airports[\"iso_country\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f7f1366",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_iso = set(State.objects.order_by().values_list('iso_3166_1_2', flat=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f8d4dce",
   "metadata": {},
   "outputs": [],
   "source": [
    "airport_state_iso = set(filter_airports[\"iso_region\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87fbf956",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_diff = airport_country_iso.difference(country_iso)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df09e3b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_diff = airport_state_iso.difference(state_iso)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "771f76ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cd1a218",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_diff"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbc547bc",
   "metadata": {},
   "source": [
    "## Iterate through dataset then save or update rows in database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b678541",
   "metadata": {},
   "outputs": [],
   "source": [
    "from django.contrib.gis.geos import fromstr, MultiPoint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7d4a346",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in filter_airports.itertuples(index=False, name='Pandas'):\n",
    "    \n",
    "    if row.iso_country in country_diff:\n",
    "        country = None\n",
    "    else:\n",
    "        country = Country.objects.filter(iso2=row.iso_country)[0]\n",
    "\n",
    "    if row.iso_region in state_diff:\n",
    "        state = None\n",
    "    else:\n",
    "        state = State.objects.filter(iso_3166_1_2=row.iso_region)[0]\n",
    "\n",
    "    airport_type = AirportType.objects.filter(name=row.type)\n",
    "    airport_type = airport_type[0] if airport_type else None\n",
    "    \n",
    "    point = MultiPoint(fromstr(f'POINT({row.longitude_deg} {row.latitude_deg})', srid=4326))\n",
    "\n",
    "    updated_values = {\n",
    "        \"name\": row.name,\n",
    "        \"airport_type\": airport_type,\n",
    "        \"point\": point,\n",
    "        \"state\": state,\n",
    "        \"country\": country,\n",
    "        \"iata_code\": row.iata_code,\n",
    "        \"gps_code\": row.gps_code,\n",
    "        \"local_code\": row.local_code,\n",
    "        \"elevation_ft\": row.elevation_ft,\n",
    "        \"url\": row.home_link,\n",
    "        \"wikipedia_url\": row.wikipedia_link,\n",
    "    }\n",
    "    \n",
    "    # Update base on our_airports ID code\n",
    "    Airport.objects.update_or_create(\n",
    "        our_airports_id=row.id,\n",
    "        defaults=updated_values,\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c12a55fc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}