{ "cells": [ { "cell_type": "code", "execution_count": 147, "id": "f0b96187", "metadata": {}, "outputs": [], "source": [ "import os\n", "import math\n", "import time\n", "from datetime import date, timedelta\n", "\n", "import pandas as pd\n", "import requests\n", "from tqdm import tqdm\n", "\n", "pd.set_option(\"display.max_columns\", 100)" ] }, { "cell_type": "code", "execution_count": 149, "id": "f9fcf1e6", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "30" ] }, "execution_count": 149, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cities = [\n", " {\"city\":\"Almaty\", \"country\":\"KZ\", \"lat\":43.238949, \"lon\":76.889709, \"region\":\"Central Asia\"},\n", " {\"city\":\"Astana\", \"country\":\"KZ\", \"lat\":51.169392, \"lon\":71.449074, \"region\":\"Central Asia\"},\n", " {\"city\":\"Shymkent\", \"country\":\"KZ\", \"lat\":42.3176, \"lon\":69.5901, \"region\":\"Central Asia\"},\n", " {\"city\":\"Tashkent\", \"country\":\"UZ\", \"lat\":41.2995, \"lon\":69.2401, \"region\":\"Central Asia\"},\n", " {\"city\":\"Bishkek\", \"country\":\"KG\", \"lat\":42.8746, \"lon\":74.5698, \"region\":\"Central Asia\"},\n", " {\"city\":\"Dubai\", \"country\":\"AE\", \"lat\":25.2048, \"lon\":55.2708, \"region\":\"Middle East\"},\n", " {\"city\":\"Riyadh\", \"country\":\"SA\", \"lat\":24.7136, \"lon\":46.6753, \"region\":\"Middle East\"},\n", " {\"city\":\"Istanbul\", \"country\":\"TR\", \"lat\":41.0082, \"lon\":28.9784, \"region\":\"Europe/Asia\"},\n", " {\"city\":\"London\", \"country\":\"GB\", \"lat\":51.5072, \"lon\":-0.1276, \"region\":\"Europe\"},\n", " {\"city\":\"Paris\", \"country\":\"FR\", \"lat\":48.8566, \"lon\":2.3522, \"region\":\"Europe\"},\n", " {\"city\":\"Berlin\", \"country\":\"DE\", \"lat\":52.52, \"lon\":13.405, \"region\":\"Europe\"},\n", " {\"city\":\"Rome\", \"country\":\"IT\", \"lat\":41.9028, \"lon\":12.4964, \"region\":\"Europe\"},\n", " {\"city\":\"Madrid\", \"country\":\"ES\", \"lat\":40.4168, \"lon\":-3.7038, \"region\":\"Europe\"},\n", " {\"city\":\"New York\", \"country\":\"US\", \"lat\":40.7128, \"lon\":-74.0060, \"region\":\"North America\"},\n", " {\"city\":\"Los Angeles\", \"country\":\"US\", \"lat\":34.0522, \"lon\":-118.2437, \"region\":\"North America\"},\n", " {\"city\":\"Chicago\", \"country\":\"US\", \"lat\":41.8781, \"lon\":-87.6298, \"region\":\"North America\"},\n", " {\"city\":\"Toronto\", \"country\":\"CA\", \"lat\":43.6532, \"lon\":-79.3832, \"region\":\"North America\"},\n", " {\"city\":\"Mexico City\", \"country\":\"MX\", \"lat\":19.4326, \"lon\":-99.1332, \"region\":\"North America\"},\n", " {\"city\":\"São Paulo\", \"country\":\"BR\", \"lat\":-23.5505, \"lon\":-46.6333, \"region\":\"South America\"},\n", " {\"city\":\"Buenos Aires\", \"country\":\"AR\", \"lat\":-34.6037, \"lon\":-58.3816, \"region\":\"South America\"},\n", " {\"city\":\"Tokyo\", \"country\":\"JP\", \"lat\":35.6762, \"lon\":139.6503, \"region\":\"Asia\"},\n", " {\"city\":\"Seoul\", \"country\":\"KR\", \"lat\":37.5665, \"lon\":126.9780, \"region\":\"Asia\"},\n", " {\"city\":\"Singapore\", \"country\":\"SG\", \"lat\":1.3521, \"lon\":103.8198, \"region\":\"Asia\"},\n", " {\"city\":\"Bangkok\", \"country\":\"TH\", \"lat\":13.7563, \"lon\":100.5018, \"region\":\"Asia\"},\n", " {\"city\":\"Jakarta\", \"country\":\"ID\", \"lat\":-6.2088, \"lon\":106.8456, \"region\":\"Asia\"},\n", " {\"city\":\"Sydney\", \"country\":\"AU\", \"lat\":-33.8688, \"lon\":151.2093, \"region\":\"Oceania\"},\n", " {\"city\":\"Melbourne\", \"country\":\"AU\", \"lat\":-37.8136, \"lon\":144.9631, \"region\":\"Oceania\"},\n", " {\"city\":\"Cape Town\", \"country\":\"ZA\", \"lat\":-33.9249, \"lon\":18.4241, \"region\":\"Africa\"},\n", " {\"city\":\"Nairobi\", \"country\":\"KE\", \"lat\":-1.2921, \"lon\":36.8219, \"region\":\"Africa\"},\n", " {\"city\":\"Cairo\", \"country\":\"EG\", \"lat\":30.0444, \"lon\":31.2357, \"region\":\"Africa\"},\n", "]\n", "\n", "len(cities)" ] }, { "cell_type": "code", "execution_count": 151, "id": "aff4af88", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(datetime.date(2024, 1, 22), datetime.date(2026, 1, 21))" ] }, "execution_count": 151, "metadata": {}, "output_type": "execute_result" } ], "source": [ "end_date = date.today() - timedelta(days=1)\n", "start_date = end_date - timedelta(days=730)\n", "\n", "start_date, end_date" ] }, { "cell_type": "code", "execution_count": 153, "id": "7f48f752", "metadata": {}, "outputs": [], "source": [ "def fetch_open_meteo_daily(lat: float, lon: float, start: date, end: date) -> pd.DataFrame:\n", " url = \"https://archive-api.open-meteo.com/v1/archive\"\n", " params = {\n", " \"latitude\": lat,\n", " \"longitude\": lon,\n", " \"start_date\": start.isoformat(),\n", " \"end_date\": end.isoformat(),\n", " \"daily\": \"temperature_2m_max,temperature_2m_min,precipitation_sum,windspeed_10m_max\",\n", " \"timezone\": \"UTC\"\n", " }\n", " r = requests.get(url, params=params, timeout=60)\n", " r.raise_for_status()\n", " data = r.json()\n", "\n", " daily = data.get(\"daily\", {})\n", " if not daily or \"time\" not in daily:\n", " return pd.DataFrame()\n", "\n", " df = pd.DataFrame({\n", " \"date\": daily.get(\"time\", []),\n", " \"tmax_c\": daily.get(\"temperature_2m_max\", []),\n", " \"tmin_c\": daily.get(\"temperature_2m_min\", []),\n", " \"precip_mm\": daily.get(\"precipitation_sum\", []),\n", " \"windmax_kmh\": daily.get(\"windspeed_10m_max\", []),\n", " })\n", " return df" ] }, { "cell_type": "code", "execution_count": 155, "id": "9e8d359a", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "Fetching cities: 100%|██████████| 30/30 [00:24<00:00, 1.21it/s]\n" ] }, { "data": { "text/plain": [ "((21199, 10), 1)" ] }, "execution_count": 155, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = []\n", "errors = []\n", "\n", "for c in tqdm(cities, desc=\"Fetching cities\"):\n", " try:\n", " df_city = fetch_open_meteo_daily(c[\"lat\"], c[\"lon\"], start_date, end_date)\n", " if df_city.empty:\n", " errors.append((c[\"city\"], \"Empty response\"))\n", " continue\n", "\n", " df_city[\"city\"] = c[\"city\"]\n", " df_city[\"country\"] = c[\"country\"]\n", " df_city[\"region\"] = c[\"region\"]\n", " df_city[\"lat\"] = c[\"lat\"]\n", " df_city[\"lon\"] = c[\"lon\"]\n", "\n", " rows.append(df_city)\n", " time.sleep(0.2)\n", " except Exception as e:\n", " errors.append((c[\"city\"], repr(e)))\n", "\n", "raw = pd.concat(rows, ignore_index=True)\n", "raw.shape, len(errors)" ] }, { "cell_type": "code", "execution_count": 156, "id": "d282ff52", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datetmax_ctmin_cprecip_mmwindmax_kmhcitycountryregionlatlon
02024-01-22-6.5-13.60.013.5AlmatyKZCentral Asia43.23894976.889709
12024-01-232.6-10.90.08.2AlmatyKZCentral Asia43.23894976.889709
22024-01-240.9-5.00.07.2AlmatyKZCentral Asia43.23894976.889709
32024-01-252.1-6.70.411.8AlmatyKZCentral Asia43.23894976.889709
42024-01-26-0.3-9.63.010.6AlmatyKZCentral Asia43.23894976.889709
\n", "
" ], "text/plain": [ " date tmax_c tmin_c precip_mm windmax_kmh city country \\\n", "0 2024-01-22 -6.5 -13.6 0.0 13.5 Almaty KZ \n", "1 2024-01-23 2.6 -10.9 0.0 8.2 Almaty KZ \n", "2 2024-01-24 0.9 -5.0 0.0 7.2 Almaty KZ \n", "3 2024-01-25 2.1 -6.7 0.4 11.8 Almaty KZ \n", "4 2024-01-26 -0.3 -9.6 3.0 10.6 Almaty KZ \n", "\n", " region lat lon \n", "0 Central Asia 43.238949 76.889709 \n", "1 Central Asia 43.238949 76.889709 \n", "2 Central Asia 43.238949 76.889709 \n", "3 Central Asia 43.238949 76.889709 \n", "4 Central Asia 43.238949 76.889709 " ] }, "execution_count": 156, "metadata": {}, "output_type": "execute_result" } ], "source": [ "raw.head()" ] }, { "cell_type": "code", "execution_count": 157, "id": "1533c482", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(21199, 21199, 0)" ] }, "execution_count": 157, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = raw.copy()\n", "\n", "df[\"date\"] = pd.to_datetime(df[\"date\"], errors=\"coerce\").dt.date\n", "\n", "num_cols = [\"tmax_c\",\"tmin_c\",\"precip_mm\",\"windmax_kmh\",\"lat\",\"lon\"]\n", "for col in num_cols:\n", " df[col] = pd.to_numeric(df[col], errors=\"coerce\")\n", "\n", "before = len(df)\n", "df = df.drop_duplicates(subset=[\"city\",\"country\",\"date\"])\n", "after = len(df)\n", "\n", "before, after, before-after" ] }, { "cell_type": "code", "execution_count": 158, "id": "611b93c4", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "date 0\n", "tmax_c 0\n", "tmin_c 0\n", "precip_mm 0\n", "windmax_kmh 0\n", "city 0\n", "country 0\n", "region 0\n", "lat 0\n", "lon 0\n", "dtype: int64" ] }, "execution_count": 158, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = df.dropna(subset=[\"date\",\"city\",\"country\"])\n", "\n", "for col in [\"tmax_c\",\"tmin_c\",\"precip_mm\",\"windmax_kmh\"]:\n", " df[col] = df.groupby(\"city\")[col].transform(lambda s: s.fillna(s.median()))\n", "\n", "df.isna().sum()" ] }, { "cell_type": "code", "execution_count": 159, "id": "543482bc", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datetmax_ctmin_cprecip_mmwindmax_kmhcitycountryregionlatlontavg_ctemp_range_cis_rain_daytavg_ftmax_ftmin_f
02024-01-22-6.5-13.60.013.5AlmatyKZCentral Asia43.23894976.889709-10.057.1013.9120.307.52
12024-01-232.6-10.90.08.2AlmatyKZCentral Asia43.23894976.889709-4.1513.5024.5336.6812.38
22024-01-240.9-5.00.07.2AlmatyKZCentral Asia43.23894976.889709-2.055.9028.3133.6223.00
32024-01-252.1-6.70.411.8AlmatyKZCentral Asia43.23894976.889709-2.308.8127.8635.7819.94
42024-01-26-0.3-9.63.010.6AlmatyKZCentral Asia43.23894976.889709-4.959.3123.0931.4614.72
\n", "
" ], "text/plain": [ " date tmax_c tmin_c precip_mm windmax_kmh city country \\\n", "0 2024-01-22 -6.5 -13.6 0.0 13.5 Almaty KZ \n", "1 2024-01-23 2.6 -10.9 0.0 8.2 Almaty KZ \n", "2 2024-01-24 0.9 -5.0 0.0 7.2 Almaty KZ \n", "3 2024-01-25 2.1 -6.7 0.4 11.8 Almaty KZ \n", "4 2024-01-26 -0.3 -9.6 3.0 10.6 Almaty KZ \n", "\n", " region lat lon tavg_c temp_range_c is_rain_day \\\n", "0 Central Asia 43.238949 76.889709 -10.05 7.1 0 \n", "1 Central Asia 43.238949 76.889709 -4.15 13.5 0 \n", "2 Central Asia 43.238949 76.889709 -2.05 5.9 0 \n", "3 Central Asia 43.238949 76.889709 -2.30 8.8 1 \n", "4 Central Asia 43.238949 76.889709 -4.95 9.3 1 \n", "\n", " tavg_f tmax_f tmin_f \n", "0 13.91 20.30 7.52 \n", "1 24.53 36.68 12.38 \n", "2 28.31 33.62 23.00 \n", "3 27.86 35.78 19.94 \n", "4 23.09 31.46 14.72 " ] }, "execution_count": 159, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df[\"tavg_c\"] = (df[\"tmax_c\"] + df[\"tmin_c\"]) / 2.0\n", "df[\"temp_range_c\"] = (df[\"tmax_c\"] - df[\"tmin_c\"]).abs()\n", "df[\"is_rain_day\"] = (df[\"precip_mm\"] > 0).astype(int)\n", "\n", "df[\"tavg_f\"] = df[\"tavg_c\"] * 9/5 + 32\n", "df[\"tmax_f\"] = df[\"tmax_c\"] * 9/5 + 32\n", "df[\"tmin_f\"] = df[\"tmin_c\"] * 9/5 + 32\n", "\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 160, "id": "eaeff1dd", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citycountrymonthdaystavg_c_meanprecip_mm_sumwindmax_kmh_meanrain_days
0AlmatyKZ2024-0110-4.6050006.611.7100005
1AlmatyKZ2024-0229-6.51379338.211.3310349
2AlmatyKZ2024-03313.96451679.411.42580615
3AlmatyKZ2024-043011.550000103.410.04666715
4AlmatyKZ2024-053116.65161386.110.98064512
\n", "
" ], "text/plain": [ " city country month days tavg_c_mean precip_mm_sum \\\n", "0 Almaty KZ 2024-01 10 -4.605000 6.6 \n", "1 Almaty KZ 2024-02 29 -6.513793 38.2 \n", "2 Almaty KZ 2024-03 31 3.964516 79.4 \n", "3 Almaty KZ 2024-04 30 11.550000 103.4 \n", "4 Almaty KZ 2024-05 31 16.651613 86.1 \n", "\n", " windmax_kmh_mean rain_days \n", "0 11.710000 5 \n", "1 11.331034 9 \n", "2 11.425806 15 \n", "3 10.046667 15 \n", "4 10.980645 12 " ] }, "execution_count": 160, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df[\"year\"] = pd.to_datetime(df[\"date\"]).dt.year\n", "df[\"month\"] = pd.to_datetime(df[\"date\"]).dt.to_period(\"M\").astype(str)\n", "\n", "city_month = (\n", " df.groupby([\"city\",\"country\",\"month\"], as_index=False)\n", " .agg(\n", " days=(\"date\",\"count\"),\n", " tavg_c_mean=(\"tavg_c\",\"mean\"),\n", " precip_mm_sum=(\"precip_mm\",\"sum\"),\n", " windmax_kmh_mean=(\"windmax_kmh\",\"mean\"),\n", " rain_days=(\"is_rain_day\",\"sum\"),\n", " )\n", ")\n", "\n", "city_month.head()" ] }, { "cell_type": "code", "execution_count": 161, "id": "091bae56", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
regionmonthcitiestavg_c_meanprecip_mm_sumrain_days
0Africa2024-01221.7300005.610
1Africa2024-02221.81982839.531
2Africa2024-03221.28064561.529
3Africa2024-04219.365000219.140
4Africa2024-05218.12903299.137
\n", "
" ], "text/plain": [ " region month cities tavg_c_mean precip_mm_sum rain_days\n", "0 Africa 2024-01 2 21.730000 5.6 10\n", "1 Africa 2024-02 2 21.819828 39.5 31\n", "2 Africa 2024-03 2 21.280645 61.5 29\n", "3 Africa 2024-04 2 19.365000 219.1 40\n", "4 Africa 2024-05 2 18.129032 99.1 37" ] }, "execution_count": 161, "metadata": {}, "output_type": "execute_result" } ], "source": [ "region_month = (\n", " df.groupby([\"region\",\"month\"], as_index=False)\n", " .agg(\n", " cities=(\"city\",\"nunique\"),\n", " tavg_c_mean=(\"tavg_c\",\"mean\"),\n", " precip_mm_sum=(\"precip_mm\",\"sum\"),\n", " rain_days=(\"is_rain_day\",\"sum\"),\n", " )\n", ")\n", "\n", "region_month.head()" ] }, { "cell_type": "code", "execution_count": 162, "id": "f88de596", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "((21199, 19), (4386, 19), (10585, 19))" ] }, "execution_count": 162, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df[\"date_iso\"] = pd.to_datetime(df[\"date\"]).dt.strftime(\"%Y-%m-%d\")\n", "\n", "df_europe = df[df[\"region\"].str.contains(\"Europe\", na=False)]\n", "df_last_365 = df[pd.to_datetime(df[\"date\"]) >= (pd.Timestamp.today().normalize() - pd.Timedelta(days=365))]\n", "\n", "df.shape, df_europe.shape, df_last_365.shape" ] }, { "cell_type": "code", "execution_count": 163, "id": "bf76a443", "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import create_engine\n", "\n", "PGHOST = \"localhost\"\n", "PGPORT = \"5433\"\n", "PGDATABASE = \"airflow\"\n", "PGUSER = \"airflow\"\n", "PGPASSWORD = \"airflow\"\n", "\n", "engine = create_engine(\n", " f\"postgresql+psycopg2://{PGUSER}:{PGPASSWORD}@{PGHOST}:{PGPORT}/{PGDATABASE}\"\n", ")" ] }, { "cell_type": "code", "execution_count": 164, "id": "c669f239", "metadata": {}, "outputs": [], "source": [ "create_weather_daily_sql = \"\"\"\n", "CREATE TABLE IF NOT EXISTS weather_daily (\n", " date DATE NOT NULL,\n", " city TEXT NOT NULL,\n", " country TEXT NOT NULL,\n", " region TEXT,\n", " lat DOUBLE PRECISION,\n", " lon DOUBLE PRECISION,\n", " tmax_c DOUBLE PRECISION,\n", " tmin_c DOUBLE PRECISION,\n", " tavg_c DOUBLE PRECISION,\n", " temp_range_c DOUBLE PRECISION,\n", " precip_mm DOUBLE PRECISION,\n", " windmax_kmh DOUBLE PRECISION,\n", " is_rain_day INTEGER,\n", " tmax_f DOUBLE PRECISION,\n", " tmin_f DOUBLE PRECISION,\n", " tavg_f DOUBLE PRECISION,\n", " PRIMARY KEY (date, city, country)\n", ");\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 165, "id": "133e2020-8448-46b9-9a40-9660216e5250", "metadata": {}, "outputs": [], "source": [ "create_city_month_sql = \"\"\"\n", "CREATE TABLE IF NOT EXISTS city_month_agg (\n", " month TEXT NOT NULL,\n", " city TEXT NOT NULL,\n", " country TEXT NOT NULL,\n", " days INTEGER,\n", " tavg_c_mean DOUBLE PRECISION,\n", " precip_mm_sum DOUBLE PRECISION,\n", " windmax_kmh_mean DOUBLE PRECISION,\n", " rain_days INTEGER,\n", " PRIMARY KEY (month, city, country)\n", ");\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 166, "id": "1ccb8fb9-9659-4572-819c-45038f28c2e7", "metadata": {}, "outputs": [], "source": [ "create_region_month_sql = \"\"\"\n", "CREATE TABLE IF NOT EXISTS region_month_agg (\n", " month TEXT NOT NULL,\n", " region TEXT NOT NULL,\n", " cities INTEGER,\n", " tavg_c_mean DOUBLE PRECISION,\n", " precip_mm_sum DOUBLE PRECISION,\n", " rain_days INTEGER,\n", " PRIMARY KEY (month, region)\n", ");\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 167, "id": "e9d18206-7c72-4ce9-9a5b-d29ad659ecd6", "metadata": {}, "outputs": [], "source": [ "with engine.begin() as conn:\n", " conn.execute(text(create_weather_daily_sql))\n", " conn.execute(text(create_city_month_sql))\n", " conn.execute(text(create_region_month_sql))" ] }, { "cell_type": "code", "execution_count": 168, "id": "74357793", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'OK'" ] }, "execution_count": 168, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def upsert_dataframe(df_in: pd.DataFrame, table_name: str, pk_cols: list[str], conn):\n", " \"\"\"Load df_in into a staging table, then UPSERT into table_name.\"\"\"\n", " staging = f\"stg_{table_name}\"\n", " df_in.to_sql(staging, conn, if_exists=\"replace\", index=False)\n", "\n", " cols = list(df_in.columns)\n", " col_list = \",\".join([f'\"{c}\"' for c in cols])\n", " pk_list = \",\".join([f'\"{c}\"' for c in pk_cols])\n", " update_set = \",\".join([f'\"{c}\"=EXCLUDED.\"{c}\"' for c in cols if c not in pk_cols])\n", "\n", " sql = f'''\n", " INSERT INTO {table_name} ({col_list})\n", " SELECT {col_list} FROM {staging}\n", " ON CONFLICT ({pk_list}) DO UPDATE SET\n", " {update_set};\n", " DROP TABLE {staging};\n", " '''\n", " conn.execute(text(sql))\n", "\n", "weather_daily_cols = [\n", " \"date\",\"city\",\"country\",\"region\",\"lat\",\"lon\",\"tmax_c\",\"tmin_c\",\"tavg_c\",\"temp_range_c\",\n", " \"precip_mm\",\"windmax_kmh\",\"is_rain_day\",\"tmax_f\",\"tmin_f\",\"tavg_f\"\n", "]\n", "weather_daily = df[weather_daily_cols].copy()\n", "\n", "try:\n", " with engine.begin() as conn:\n", " upsert_dataframe(weather_daily, \"weather_daily\", [\"date\",\"city\",\"country\"], conn)\n", " upsert_dataframe(city_month, \"city_month_agg\", [\"month\",\"city\",\"country\"], conn)\n", " upsert_dataframe(region_month, \"region_month_agg\", [\"month\",\"region\"], conn)\n", " load_status = \"OK\"\n", "except Exception as e:\n", " load_status = f\"FAILED: {e!r}\"\n", "\n", "load_status" ] }, { "cell_type": "code", "execution_count": 169, "id": "a754ff75", "metadata": {}, "outputs": [], "source": [ "def sql_df(q: str) -> pd.DataFrame:\n", " with engine.begin() as conn:\n", " return pd.read_sql(text(q), conn)" ] }, { "cell_type": "code", "execution_count": 170, "id": "b8a83be3-216f-46fa-a54b-5f94d2c4d6ac", "metadata": {}, "outputs": [], "source": [ "q1 = \"\"\"\n", "SELECT\n", " COUNT(*) AS rows,\n", " MIN(date) AS min_date,\n", " MAX(date) AS max_date,\n", " COUNT(DISTINCT city) AS cities\n", "FROM weather_daily;\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 171, "id": "a1092c63-b3b5-45ec-b9ac-5b9ef2999112", "metadata": {}, "outputs": [], "source": [ "\n", "q2 = \"\"\"\n", "SELECT month, city, country, precip_mm_sum, rain_days, tavg_c_mean\n", "FROM city_month_agg\n", "ORDER BY precip_mm_sum DESC\n", "LIMIT 10;\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 172, "id": "e407f55c-4248-432a-887e-61c9c2a1a291", "metadata": {}, "outputs": [], "source": [ "q4 = \"\"\"\n", "SELECT city, country,\n", " AVG(tavg_c) AS avg_temp_c,\n", " SUM(is_rain_day) AS rain_days\n", "FROM weather_daily\n", "WHERE date >= (CURRENT_DATE - INTERVAL '30 days')\n", "GROUP BY city, country\n", "ORDER BY avg_temp_c DESC\n", "LIMIT 15;\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 173, "id": "026b3911-17f2-4853-a705-728d41b991d4", "metadata": {}, "outputs": [], "source": [ "q3 = \"\"\"\n", "WITH latest AS (\n", " SELECT MAX(month) AS m FROM region_month_agg\n", ")\n", "SELECT r.month, r.region, r.cities, r.tavg_c_mean, r.precip_mm_sum, r.rain_days\n", "FROM region_month_agg r\n", "JOIN latest ON r.month = latest.m\n", "ORDER BY r.tavg_c_mean DESC;\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 174, "id": "b2712203-ec0d-46ae-8548-1a0ce4b538c2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
rowsmin_datemax_datecities
0211992024-01-222026-01-2129
\n", "
" ], "text/plain": [ " rows min_date max_date cities\n", "0 21199 2024-01-22 2026-01-21 29" ] }, "execution_count": 174, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_df(q1)" ] }, { "cell_type": "code", "execution_count": 175, "id": "b127f5a3", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
monthcitycountryprecip_mm_sumrain_daystavg_c_mean
02024-11SingaporeSG536.73026.741667
12024-07SeoulKR522.63025.772581
22024-05SingaporeSG441.33027.793548
32025-01JakartaID419.43127.075806
42025-01SingaporeSG414.82926.251613
52025-04SingaporeSG388.32927.308333
62024-02Los AngelesUS341.11712.986207
72025-06Mexico CityMX338.82818.078333
82025-03JakartaID336.33127.622581
92024-07Mexico CityMX334.73117.737097
\n", "
" ], "text/plain": [ " month city country precip_mm_sum rain_days tavg_c_mean\n", "0 2024-11 Singapore SG 536.7 30 26.741667\n", "1 2024-07 Seoul KR 522.6 30 25.772581\n", "2 2024-05 Singapore SG 441.3 30 27.793548\n", "3 2025-01 Jakarta ID 419.4 31 27.075806\n", "4 2025-01 Singapore SG 414.8 29 26.251613\n", "5 2025-04 Singapore SG 388.3 29 27.308333\n", "6 2024-02 Los Angeles US 341.1 17 12.986207\n", "7 2025-06 Mexico City MX 338.8 28 18.078333\n", "8 2025-03 Jakarta ID 336.3 31 27.622581\n", "9 2024-07 Mexico City MX 334.7 31 17.737097" ] }, "execution_count": 175, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_df(q2)" ] }, { "cell_type": "code", "execution_count": 176, "id": "136463bd", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
monthregioncitiestavg_c_meanprecip_mm_sumrain_days
02026-01South America223.440476234.626
12026-01Oceania222.000000160.529
22026-01Africa221.39404820.715
32026-01Middle East217.9714295.73
42026-01Asia516.320952379.551
52026-01Europe/Asia17.08095297.014
62026-01North America54.938571204.959
72026-01Europe54.306190322.462
82026-01Central Asia5-2.68428694.135
\n", "
" ], "text/plain": [ " month region cities tavg_c_mean precip_mm_sum rain_days\n", "0 2026-01 South America 2 23.440476 234.6 26\n", "1 2026-01 Oceania 2 22.000000 160.5 29\n", "2 2026-01 Africa 2 21.394048 20.7 15\n", "3 2026-01 Middle East 2 17.971429 5.7 3\n", "4 2026-01 Asia 5 16.320952 379.5 51\n", "5 2026-01 Europe/Asia 1 7.080952 97.0 14\n", "6 2026-01 North America 5 4.938571 204.9 59\n", "7 2026-01 Europe 5 4.306190 322.4 62\n", "8 2026-01 Central Asia 5 -2.684286 94.1 35" ] }, "execution_count": 176, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_df(q3)" ] }, { "cell_type": "code", "execution_count": 177, "id": "fceb9a2f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citycountryavg_temp_crain_days
0BangkokTH27.3466675
1JakartaID26.89000030
2SingaporeSG26.73833328
3Buenos AiresAR25.0433338
4São PauloBR24.61833330
5Cape TownZA21.9800006
6SydneyAU21.58833323
7NairobiKE20.90833319
8MelbourneAU20.48833317
9DubaiAE20.2900003
10RiyadhSA16.1266672
11Mexico CityMX15.01833312
12Los AngelesUS14.26833313
13RomeIT8.91333313
14IstanbulTR6.85000022
\n", "
" ], "text/plain": [ " city country avg_temp_c rain_days\n", "0 Bangkok TH 27.346667 5\n", "1 Jakarta ID 26.890000 30\n", "2 Singapore SG 26.738333 28\n", "3 Buenos Aires AR 25.043333 8\n", "4 São Paulo BR 24.618333 30\n", "5 Cape Town ZA 21.980000 6\n", "6 Sydney AU 21.588333 23\n", "7 Nairobi KE 20.908333 19\n", "8 Melbourne AU 20.488333 17\n", "9 Dubai AE 20.290000 3\n", "10 Riyadh SA 16.126667 2\n", "11 Mexico City MX 15.018333 12\n", "12 Los Angeles US 14.268333 13\n", "13 Rome IT 8.913333 13\n", "14 Istanbul TR 6.850000 22" ] }, "execution_count": 177, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_df(q4)" ] } ], "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.12.7" } }, "nbformat": 4, "nbformat_minor": 5 }