lambda_function_with_flights_and _weather_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import re\n",
    "import json\n",
    "from datetime import datetime, timedelta, date\n",
    "import sqlalchemy\n",
    "\n",
    "# creating a function for weather data (dynamic sql table) that takes a list of city names as an argument\n",
    "def get_wea(cities:list):\n",
    "\n",
    "    API_key = \"XXX\" # API key generated by OpenWeatherMap for 5 day weather forecast\n",
    "\n",
    "    # creating the weather dictionary with needed columns for the SQL table\n",
    "    wea_dict = {\"city_id\": [],\n",
    "                \"forecast_time\": [],\n",
    "                \"weather_outlook\": [],\n",
    "                \"temperature\": [],\n",
    "                \"feels_like_temperature\": [],\n",
    "                \"wind_speed\": [],\n",
    "                \"risk_of_rain\": []\n",
    "                }\n",
    "\n",
    "    for row in cities.itertuples():\n",
    "        url = f\"http://api.openweathermap.org/data/2.5/forecast?q={row.city}&appid={API_key}&units=metric\"\n",
    "        response = requests.get(url)\n",
    "        wea_resp = response.json()\n",
    "\n",
    "        for wea in wea_resp[\"list\"]:\n",
    "            wea_dict[\"city_id\"].append(row.city_id)\n",
    "            wea_dict[\"forecast_time\"].append(wea[\"dt_txt\"])\n",
    "            wea_dict[\"weather_outlook\"].append(wea[\"weather\"][0][\"main\"])\n",
    "            wea_dict[\"temperature\"].append(wea[\"main\"][\"temp\"])\n",
    "            wea_dict[\"feels_like_temperature\"].append(wea[\"main\"][\"feels_like\"])\n",
    "            wea_dict[\"wind_speed\"].append(wea[\"wind\"][\"speed\"])\n",
    "            wea_dict[\"risk_of_rain\"].append(wea[\"pop\"])\n",
    "\n",
    "    return pd.DataFrame(wea_dict)\n",
    "    \n",
    "# creating a function for flights data (dynamic sql table)\n",
    "def tomorrows_flight_arrivals(icao_list):\n",
    "\n",
    "    today = datetime.now().date()\n",
    "    tomorrow = today + timedelta(days=1)\n",
    "\n",
    "    list_for_arrivals_df = []\n",
    "\n",
    "    for icao in icao_list:\n",
    "        times = [[\"00:00\",\"11:59\"],[\"12:00\",\"23:59\"]]\n",
    "\n",
    "        for time in times:\n",
    "            url = f\"https://aerodatabox.p.rapidapi.com/flights/airports/icao/{icao}/{tomorrow}T{time[0]}/{tomorrow}T{time[1]}\"\n",
    "\n",
    "            querystring = {\"direction\":\"Arrival\",\"withCancelled\":\"false\"}\n",
    "\n",
    "            headers = {\n",
    "                \"X-RapidAPI-Key\": \"0b7cffd425mshbf932b1b5f7e633p187a96jsna5aedb4ce276\",\n",
    "                \"X-RapidAPI-Host\": \"aerodatabox.p.rapidapi.com\"\n",
    "                }\n",
    "\n",
    "            response = requests.request(\"GET\", url, headers=headers, params=querystring)\n",
    "            flights_resp = response.json()\n",
    "\n",
    "            arrivals_df = pd.json_normalize(flights_resp[\"arrivals\"])[[\"number\", \"airline.name\", \"movement.scheduledTime.local\", \"movement.terminal\", \"movement.airport.name\", \"movement.airport.icao\"]]\n",
    "            arrivals_df = arrivals_df.rename(columns={\"number\": \"flight_number\", \"airline.name\": \"airline\", \"movement.scheduledTime.local\": \"arrival_time\", \"movement.terminal\": \"arrival_terminal\", \"movement.airport.name\": \"departure_city\", \"movement.airport.icao\": \"departure_airport_icao\"})\n",
    "            arrivals_df['arrival_airport_icao'] = icao\n",
    "            arrivals_df['data_retrieved_on'] = datetime.now().date()\n",
    "            arrivals_df = arrivals_df[[\"flight_number\", \"departure_airport_icao\", \"arrival_airport_icao\", \"arrival_time\"]]\n",
    "            arrivals_df[\"arrival_time\"] = arrivals_df[\"arrival_time\"].str[:-6]\n",
    "            list_for_arrivals_df.append(arrivals_df)\n",
    "\n",
    "    return pd.concat(list_for_arrivals_df, ignore_index=True)\n",
    "\n",
    "# creating the lambda function on AWS Lambda\n",
    "def lambda_handler(event, context):\n",
    "    schema=\"gans\"\n",
    "    host=\"wbs-project3-db.cunuvsto5hvy.us-east-1.rds.amazonaws.com\"\n",
    "    user=\"admin\"\n",
    "    password=\"XXX\"\n",
    "    port=3306\n",
    "    con = f'mysql+pymysql://{user}:{password}@{host}:{port}/{schema}'\n",
    "  \n",
    "    city_df = pd.read_sql('cities',con=con)\n",
    "  \n",
    "    df_weathers = get_wea(city_df)\n",
    "    df_weathers.to_sql('weathers',if_exists='append',con=con,index=False)\n",
    "    \n",
    "    df_flights = tomorrows_flight_arrivals([\"EDDB\", \"EDDF\", \"EDDH\", \"EDDK\", \"EDDM\"])\n",
    "    df_flights.to_sql('flights',if_exists='append',con=con,index=False)\n",
    "  \n",
    "    return {\n",
    "    'statusCode': 200,\n",
    "    'body': json.dumps('Hello from Lambda!')}"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DataScience_Bootcamp",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}