analyze_excess_mortality.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84b71a8b-57f9-433a-813c-2efe85ca1d78",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "-- Note: Unless you save your query, these tabs will NOT persist if you clear your cookies or change browsers.\n",
    "\n",
    "SELECT deaths,\n",
    "CASE WHEN age < 20 THEN '<20'\n",
    "     WHEN age >=20 and age < 30  THEN '20-29'\n",
    "     WHEN age >=30 and age < 40  THEN '30-39'\n",
    "     WHEN age >=40 and age < 50  THEN '40-49'\n",
    "     WHEN age >=50 and age < 60  THEN '50-59'\n",
    "     WHEN age >=60 and age < 70  THEN '60-69'\n",
    "     WHEN age >=70 and age < 80  THEN '70-79'\n",
    "     ELSE '>=80'\n",
    "END as age_group,\n",
    "age,\n",
    "to_date(to_char(year::NUMERIC,'9999D99S'),'YYYY') AS new_year from data_result\n",
    "\"\"\"\n",
    "\n",
    "sql = \"\"\"\n",
    "select \n",
    "    sum(bev002__gestorbene__anzahl) as deaths,\n",
    "    cast(\n",
    "        (case 4_auspraegung_label when 'unter 1 Jahr' then '0' else split(4_auspraegung_label,'-')[0] end) \n",
    "    as int) as age,\n",
    "    Zeit as year,\n",
    "    from df \n",
    "    where 4_auspraegung_label not in (\n",
    "        'Alter unbekannt',\n",
    "        'Insgesamt',\n",
    "        '100 Jahre und mehr') \n",
    "    group by 4_auspraegung_label, Zeit \n",
    "    order by zeit asc\n",
    "\"\"\"\n",
    "sql = sql.replace(\"\\n\", \" \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1354f4c9-95b1-4cd2-b799-3b063cdcbee3",
   "metadata": {},
   "outputs": [],
   "source": [
    "!ipython component-library/transform/spark-sql.ipynb data_dir=/home/jovyan/work/claimed/data/ sql=\"$sql\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b58343a-05f9-4a94-973a-c0ac1cfbc2c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "host=''\n",
    "port=5432\n",
    "database='postgresdb'\n",
    "user='postgresadmin'\n",
    "password='admin123'\n",
    "schema='public'\n",
    "table='data_result'\n",
    "data_csv='data_result.csv'\n",
    "truncate=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92aa8c84-7745-4123-a45a-7732cc1565dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "!ipython component-library/output/output-rdbms-sqlalchemy.ipynb data_dir=/home/jovyan/work/claimed/data/ host=\"$host\" port=\"$port\" database=\"$database\" user=\"$user\" password=\"$password\" schema=\"$schema\" table=\"$table\" data_csv=\"$data_csv\" truncate=\"$truncate\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17cd0713-6ebc-4738-be0c-ba5a82481bf8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}