death_month_age.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "77faf879-4dc0-4efc-b453-269e62646fa5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "openjdk version \"11.0.18\" 2023-01-17\r\n",
      "OpenJDK Runtime Environment (build 11.0.18+10-post-Ubuntu-0ubuntu120.04.1)\r\n",
      "OpenJDK 64-Bit Server VM (build 11.0.18+10-post-Ubuntu-0ubuntu120.04.1, mixed mode, sharing)\r\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import tabula\n",
    "import numpy as np\n",
    "\n",
    "!java -version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8df3773d-6d7c-4548-ba8c-02bf279bff7b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Got stderr: März 24, 2023 7:08:57 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "März 24, 2023 7:08:58 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# ab page 77 bis 80\n",
    "page_number = 77\n",
    "url = 'https://www.destatis.de/DE/Themen/Gesellschaft-Umwelt/Bevoelkerung/Sterbefaelle-Lebenserwartung/Tabellen/sonderauswertung-sterbefaelle-pdf.pdf;jsessionid=3625BB3642C6C5041DA068AE79C45426.live741?__blob=publicationFile'\n",
    "#df = tabula.read_pdf('death.pdf', stream = True, pages = page_number)[0]\n",
    "\n",
    "df = tabula.read_pdf(url, stream = True, pages = page_number)[0]\n",
    "\n",
    "df.drop(columns = ['Lfd.', 'Insgesamt'], inplace = True)\n",
    "\n",
    "df.replace('\\.', '', regex = True, inplace = True)\n",
    "\n",
    "\n",
    "df.rename(columns = df.iloc[0], inplace = True)\n",
    "df.replace(' ', '', regex = True, inplace = True)\n",
    "\n",
    "\n",
    "df.drop(index = 0, inplace = True)\n",
    "df.iloc[:,6] = df.iloc[:,7]\n",
    "df.iloc[:,10] = df.iloc[:,11]\n",
    "df = df.rename(columns={df.columns[0]: 'Jahr', df.columns[1]:'Alter'})\n",
    "df.replace('95umehr', '95+', inplace = True)\n",
    "\n",
    "\n",
    "df['Jahr'] = df['Jahr'].astype('int32')\n",
    "non_nan_cols = [item for item in list(df.columns) if not(pd.isnull(item))]\n",
    "df = df[non_nan_cols]\n",
    "\n",
    "df.set_index(['Jahr', 'Alter'], inplace = True)\n",
    "df.drop(index = 'Insgesamt', level = 'Alter', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4a5ced6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Januar</th>\n",
       "      <th>Februar</th>\n",
       "      <th>März</th>\n",
       "      <th>April</th>\n",
       "      <th>Mai</th>\n",
       "      <th>Juni</th>\n",
       "      <th>Juli</th>\n",
       "      <th>August</th>\n",
       "      <th>September</th>\n",
       "      <th>Oktober</th>\n",
       "      <th>November</th>\n",
       "      <th>Dezember</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jahr</th>\n",
       "      <th>Alter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"16\" valign=\"top\">2023</th>\n",
       "      <th>0-15</th>\n",
       "      <td>289</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-30</th>\n",
       "      <td>319</td>\n",
       "      <td>297</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-35</th>\n",
       "      <td>245</td>\n",
       "      <td>188</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-40</th>\n",
       "      <td>323</td>\n",
       "      <td>329</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40-45</th>\n",
       "      <td>532</td>\n",
       "      <td>510</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-50</th>\n",
       "      <td>790</td>\n",
       "      <td>686</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50-55</th>\n",
       "      <td>1697</td>\n",
       "      <td>1460</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-60</th>\n",
       "      <td>3195</td>\n",
       "      <td>2818</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60-65</th>\n",
       "      <td>4944</td>\n",
       "      <td>4283</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65-70</th>\n",
       "      <td>6631</td>\n",
       "      <td>5730</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70-75</th>\n",
       "      <td>8700</td>\n",
       "      <td>7327</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75-80</th>\n",
       "      <td>10072</td>\n",
       "      <td>8715</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80-85</th>\n",
       "      <td>19066</td>\n",
       "      <td>15814</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85-90</th>\n",
       "      <td>20752</td>\n",
       "      <td>17183</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90-95</th>\n",
       "      <td>14330</td>\n",
       "      <td>11720</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95+</th>\n",
       "      <td>6583</td>\n",
       "      <td>5250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"16\" valign=\"top\">2022</th>\n",
       "      <th>0-15</th>\n",
       "      <td>271</td>\n",
       "      <td>272</td>\n",
       "      <td>280</td>\n",
       "      <td>264</td>\n",
       "      <td>279</td>\n",
       "      <td>329</td>\n",
       "      <td>313</td>\n",
       "      <td>306</td>\n",
       "      <td>303</td>\n",
       "      <td>287</td>\n",
       "      <td>302</td>\n",
       "      <td>374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-30</th>\n",
       "      <td>345</td>\n",
       "      <td>299</td>\n",
       "      <td>364</td>\n",
       "      <td>306</td>\n",
       "      <td>351</td>\n",
       "      <td>318</td>\n",
       "      <td>372</td>\n",
       "      <td>353</td>\n",
       "      <td>370</td>\n",
       "      <td>346</td>\n",
       "      <td>339</td>\n",
       "      <td>385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-35</th>\n",
       "      <td>238</td>\n",
       "      <td>193</td>\n",
       "      <td>235</td>\n",
       "      <td>251</td>\n",
       "      <td>216</td>\n",
       "      <td>231</td>\n",
       "      <td>276</td>\n",
       "      <td>248</td>\n",
       "      <td>213</td>\n",
       "      <td>225</td>\n",
       "      <td>202</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-40</th>\n",
       "      <td>361</td>\n",
       "      <td>355</td>\n",
       "      <td>351</td>\n",
       "      <td>359</td>\n",
       "      <td>357</td>\n",
       "      <td>385</td>\n",
       "      <td>392</td>\n",
       "      <td>365</td>\n",
       "      <td>374</td>\n",
       "      <td>350</td>\n",
       "      <td>374</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40-45</th>\n",
       "      <td>549</td>\n",
       "      <td>519</td>\n",
       "      <td>518</td>\n",
       "      <td>538</td>\n",
       "      <td>557</td>\n",
       "      <td>515</td>\n",
       "      <td>578</td>\n",
       "      <td>551</td>\n",
       "      <td>485</td>\n",
       "      <td>527</td>\n",
       "      <td>534</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45-50</th>\n",
       "      <td>806</td>\n",
       "      <td>721</td>\n",
       "      <td>831</td>\n",
       "      <td>784</td>\n",
       "      <td>766</td>\n",
       "      <td>730</td>\n",
       "      <td>748</td>\n",
       "      <td>720</td>\n",
       "      <td>742</td>\n",
       "      <td>782</td>\n",
       "      <td>771</td>\n",
       "      <td>882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50-55</th>\n",
       "      <td>1803</td>\n",
       "      <td>1541</td>\n",
       "      <td>1712</td>\n",
       "      <td>1633</td>\n",
       "      <td>1593</td>\n",
       "      <td>1556</td>\n",
       "      <td>1704</td>\n",
       "      <td>1658</td>\n",
       "      <td>1520</td>\n",
       "      <td>1686</td>\n",
       "      <td>1629</td>\n",
       "      <td>1791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-60</th>\n",
       "      <td>3210</td>\n",
       "      <td>2953</td>\n",
       "      <td>3102</td>\n",
       "      <td>3062</td>\n",
       "      <td>2961</td>\n",
       "      <td>2931</td>\n",
       "      <td>3068</td>\n",
       "      <td>2998</td>\n",
       "      <td>2886</td>\n",
       "      <td>3153</td>\n",
       "      <td>2986</td>\n",
       "      <td>3641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60-65</th>\n",
       "      <td>4988</td>\n",
       "      <td>4201</td>\n",
       "      <td>4668</td>\n",
       "      <td>4492</td>\n",
       "      <td>4470</td>\n",
       "      <td>4413</td>\n",
       "      <td>4621</td>\n",
       "      <td>4639</td>\n",
       "      <td>4264</td>\n",
       "      <td>4786</td>\n",
       "      <td>4511</td>\n",
       "      <td>5514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65-70</th>\n",
       "      <td>6182</td>\n",
       "      <td>5636</td>\n",
       "      <td>6291</td>\n",
       "      <td>5867</td>\n",
       "      <td>5808</td>\n",
       "      <td>5716</td>\n",
       "      <td>5985</td>\n",
       "      <td>6004</td>\n",
       "      <td>5741</td>\n",
       "      <td>6405</td>\n",
       "      <td>6106</td>\n",
       "      <td>7452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70-75</th>\n",
       "      <td>7850</td>\n",
       "      <td>7073</td>\n",
       "      <td>8079</td>\n",
       "      <td>7558</td>\n",
       "      <td>7349</td>\n",
       "      <td>7122</td>\n",
       "      <td>7740</td>\n",
       "      <td>7535</td>\n",
       "      <td>7240</td>\n",
       "      <td>8230</td>\n",
       "      <td>7858</td>\n",
       "      <td>9873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75-80</th>\n",
       "      <td>9767</td>\n",
       "      <td>8737</td>\n",
       "      <td>9858</td>\n",
       "      <td>9016</td>\n",
       "      <td>8840</td>\n",
       "      <td>8386</td>\n",
       "      <td>9316</td>\n",
       "      <td>9033</td>\n",
       "      <td>8513</td>\n",
       "      <td>9835</td>\n",
       "      <td>9422</td>\n",
       "      <td>11878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80-85</th>\n",
       "      <td>17719</td>\n",
       "      <td>16556</td>\n",
       "      <td>18626</td>\n",
       "      <td>16937</td>\n",
       "      <td>15902</td>\n",
       "      <td>15373</td>\n",
       "      <td>16725</td>\n",
       "      <td>16832</td>\n",
       "      <td>15717</td>\n",
       "      <td>18585</td>\n",
       "      <td>17213</td>\n",
       "      <td>22515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85-90</th>\n",
       "      <td>17429</td>\n",
       "      <td>16334</td>\n",
       "      <td>18751</td>\n",
       "      <td>17053</td>\n",
       "      <td>16019</td>\n",
       "      <td>15630</td>\n",
       "      <td>16954</td>\n",
       "      <td>17548</td>\n",
       "      <td>15980</td>\n",
       "      <td>19310</td>\n",
       "      <td>18189</td>\n",
       "      <td>24231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90-95</th>\n",
       "      <td>12397</td>\n",
       "      <td>11962</td>\n",
       "      <td>13958</td>\n",
       "      <td>12479</td>\n",
       "      <td>11342</td>\n",
       "      <td>10955</td>\n",
       "      <td>11974</td>\n",
       "      <td>12218</td>\n",
       "      <td>11395</td>\n",
       "      <td>13602</td>\n",
       "      <td>12644</td>\n",
       "      <td>16879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95+</th>\n",
       "      <td>5525</td>\n",
       "      <td>5457</td>\n",
       "      <td>6130</td>\n",
       "      <td>5623</td>\n",
       "      <td>5005</td>\n",
       "      <td>4878</td>\n",
       "      <td>5202</td>\n",
       "      <td>5499</td>\n",
       "      <td>5107</td>\n",
       "      <td>6128</td>\n",
       "      <td>5594</td>\n",
       "      <td>7692</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Januar Februar   März  April    Mai   Juni   Juli August September  \\\n",
       "Jahr Alter                                                                      \n",
       "2023 0-15     289     279    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     15-30    319     297    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     30-35    245     188    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     35-40    323     329    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     40-45    532     510    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     45-50    790     686    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     50-55   1697    1460    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     55-60   3195    2818    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     60-65   4944    4283    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     65-70   6631    5730    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     70-75   8700    7327    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     75-80  10072    8715    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     80-85  19066   15814    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     85-90  20752   17183    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     90-95  14330   11720    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     95+     6583    5250    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "2022 0-15     271     272    280    264    279    329    313    306       303   \n",
       "     15-30    345     299    364    306    351    318    372    353       370   \n",
       "     30-35    238     193    235    251    216    231    276    248       213   \n",
       "     35-40    361     355    351    359    357    385    392    365       374   \n",
       "     40-45    549     519    518    538    557    515    578    551       485   \n",
       "     45-50    806     721    831    784    766    730    748    720       742   \n",
       "     50-55   1803    1541   1712   1633   1593   1556   1704   1658      1520   \n",
       "     55-60   3210    2953   3102   3062   2961   2931   3068   2998      2886   \n",
       "     60-65   4988    4201   4668   4492   4470   4413   4621   4639      4264   \n",
       "     65-70   6182    5636   6291   5867   5808   5716   5985   6004      5741   \n",
       "     70-75   7850    7073   8079   7558   7349   7122   7740   7535      7240   \n",
       "     75-80   9767    8737   9858   9016   8840   8386   9316   9033      8513   \n",
       "     80-85  17719   16556  18626  16937  15902  15373  16725  16832     15717   \n",
       "     85-90  17429   16334  18751  17053  16019  15630  16954  17548     15980   \n",
       "     90-95  12397   11962  13958  12479  11342  10955  11974  12218     11395   \n",
       "     95+     5525    5457   6130   5623   5005   4878   5202   5499      5107   \n",
       "\n",
       "           Oktober November Dezember  \n",
       "Jahr Alter                            \n",
       "2023 0-15      NaN      NaN      NaN  \n",
       "     15-30     NaN      NaN      NaN  \n",
       "     30-35     NaN      NaN      NaN  \n",
       "     35-40     NaN      NaN      NaN  \n",
       "     40-45     NaN      NaN      NaN  \n",
       "     45-50     NaN      NaN      NaN  \n",
       "     50-55     NaN      NaN      NaN  \n",
       "     55-60     NaN      NaN      NaN  \n",
       "     60-65     NaN      NaN      NaN  \n",
       "     65-70     NaN      NaN      NaN  \n",
       "     70-75     NaN      NaN      NaN  \n",
       "     75-80     NaN      NaN      NaN  \n",
       "     80-85     NaN      NaN      NaN  \n",
       "     85-90     NaN      NaN      NaN  \n",
       "     90-95     NaN      NaN      NaN  \n",
       "     95+       NaN      NaN      NaN  \n",
       "2022 0-15      287      302      374  \n",
       "     15-30     346      339      385  \n",
       "     30-35     225      202      265  \n",
       "     35-40     350      374      366  \n",
       "     40-45     527      534      602  \n",
       "     45-50     782      771      882  \n",
       "     50-55    1686     1629     1791  \n",
       "     55-60    3153     2986     3641  \n",
       "     60-65    4786     4511     5514  \n",
       "     65-70    6405     6106     7452  \n",
       "     70-75    8230     7858     9873  \n",
       "     75-80    9835     9422    11878  \n",
       "     80-85   18585    17213    22515  \n",
       "     85-90   19310    18189    24231  \n",
       "     90-95   13602    12644    16879  \n",
       "     95+      6128     5594     7692  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "082892f1-40c5-49c2-9a26-c02a90cee5fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "def return_dataframe(page_number):\n",
    "    \n",
    "    url = 'https://www.destatis.de/DE/Themen/Gesellschaft-Umwelt/Bevoelkerung/Sterbefaelle-Lebenserwartung/Tabellen/sonderauswertung-sterbefaelle-pdf.pdf;jsessionid=3625BB3642C6C5041DA068AE79C45426.live741?__blob=publicationFile'\n",
    "    df = tabula.read_pdf(url, stream = True, pages = page_number)[0]\n",
    "\n",
    "    df.drop(columns = ['Lfd.', 'Insgesamt'], inplace = True)\n",
    "\n",
    "    df.replace('\\.', '', regex = True, inplace = True)\n",
    "\n",
    "    df.rename(columns = df.iloc[0], inplace = True)\n",
    "    df.replace(' ', '', regex = True, inplace = True)\n",
    "\n",
    "    df.drop(index = 0, inplace = True)\n",
    "    df.iloc[:,6] = df.iloc[:,7]\n",
    "    df.iloc[:,10] = df.iloc[:,11]\n",
    "    df = df.rename(columns={df.columns[0]: 'Jahr', df.columns[1]:'Alter'})\n",
    "    df.replace('95umehr', '95+', inplace = True)\n",
    "\n",
    "\n",
    "    df['Jahr'] = df['Jahr'].astype('int32')\n",
    "    non_nan_cols = [item for item in list(df.columns) if not(pd.isnull(item))]\n",
    "    df = df[non_nan_cols]\n",
    "\n",
    "    df.set_index(['Jahr', 'Alter'], inplace = True)\n",
    "    df.drop(index = 'Insgesamt', level = 'Alter', inplace = True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a5eb9fc4-3cf3-4f1a-a1d7-98b611dae3d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Got stderr: März 24, 2023 7:08:59 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "März 24, 2023 7:08:59 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "\n",
      "Got stderr: März 24, 2023 7:09:00 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "März 24, 2023 7:09:00 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "\n",
      "/tmp/ipykernel_16644/1523515556.py:5: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  large_df = large_df.append(df)\n",
      "Got stderr: März 24, 2023 7:09:01 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "März 24, 2023 7:09:02 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "\n",
      "/tmp/ipykernel_16644/1523515556.py:5: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  large_df = large_df.append(df)\n",
      "Got stderr: März 24, 2023 7:09:03 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "März 24, 2023 7:09:03 NACHM. org.apache.pdfbox.pdmodel.font.PDTrueTypeFont <init>\n",
      "WARNUNG: Using fallback font 'LiberationSans' for 'MetaNormalLF-Roman'\n",
      "\n",
      "/tmp/ipykernel_16644/1523515556.py:5: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "  large_df = large_df.append(df)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Januar</th>\n",
       "      <th>Februar</th>\n",
       "      <th>März</th>\n",
       "      <th>April</th>\n",
       "      <th>Mai</th>\n",
       "      <th>Juni</th>\n",
       "      <th>Juli</th>\n",
       "      <th>August</th>\n",
       "      <th>September</th>\n",
       "      <th>Oktober</th>\n",
       "      <th>November</th>\n",
       "      <th>Dezember</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jahr</th>\n",
       "      <th>Alter</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2023</th>\n",
       "      <th>0-15</th>\n",
       "      <td>289</td>\n",
       "      <td>279</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15-30</th>\n",
       "      <td>319</td>\n",
       "      <td>297</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-35</th>\n",
       "      <td>245</td>\n",
       "      <td>188</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-40</th>\n",
       "      <td>323</td>\n",
       "      <td>329</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40-45</th>\n",
       "      <td>532</td>\n",
       "      <td>510</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2016</th>\n",
       "      <th>75-80</th>\n",
       "      <td>12388</td>\n",
       "      <td>11644</td>\n",
       "      <td>12850</td>\n",
       "      <td>11600</td>\n",
       "      <td>11181</td>\n",
       "      <td>10740</td>\n",
       "      <td>10957</td>\n",
       "      <td>10844</td>\n",
       "      <td>10483</td>\n",
       "      <td>11566</td>\n",
       "      <td>11709</td>\n",
       "      <td>12805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80-85</th>\n",
       "      <td>13721</td>\n",
       "      <td>12988</td>\n",
       "      <td>14230</td>\n",
       "      <td>12632</td>\n",
       "      <td>12915</td>\n",
       "      <td>11729</td>\n",
       "      <td>12264</td>\n",
       "      <td>12238</td>\n",
       "      <td>11770</td>\n",
       "      <td>13147</td>\n",
       "      <td>13534</td>\n",
       "      <td>14704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85-90</th>\n",
       "      <td>15688</td>\n",
       "      <td>14556</td>\n",
       "      <td>15697</td>\n",
       "      <td>14250</td>\n",
       "      <td>13977</td>\n",
       "      <td>12588</td>\n",
       "      <td>13338</td>\n",
       "      <td>13145</td>\n",
       "      <td>12929</td>\n",
       "      <td>14189</td>\n",
       "      <td>14629</td>\n",
       "      <td>16123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90-95</th>\n",
       "      <td>11244</td>\n",
       "      <td>10304</td>\n",
       "      <td>11333</td>\n",
       "      <td>10022</td>\n",
       "      <td>9818</td>\n",
       "      <td>8960</td>\n",
       "      <td>9401</td>\n",
       "      <td>9243</td>\n",
       "      <td>9004</td>\n",
       "      <td>10221</td>\n",
       "      <td>10446</td>\n",
       "      <td>11830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95+</th>\n",
       "      <td>3839</td>\n",
       "      <td>3569</td>\n",
       "      <td>3954</td>\n",
       "      <td>3621</td>\n",
       "      <td>3588</td>\n",
       "      <td>3287</td>\n",
       "      <td>3417</td>\n",
       "      <td>3465</td>\n",
       "      <td>3345</td>\n",
       "      <td>3802</td>\n",
       "      <td>4015</td>\n",
       "      <td>4451</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Januar Februar   März  April    Mai   Juni   Juli August September  \\\n",
       "Jahr Alter                                                                      \n",
       "2023 0-15     289     279    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     15-30    319     297    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     30-35    245     188    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     35-40    323     329    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "     40-45    532     510    NaN    NaN    NaN    NaN    NaN    NaN       NaN   \n",
       "...           ...     ...    ...    ...    ...    ...    ...    ...       ...   \n",
       "2016 75-80  12388   11644  12850  11600  11181  10740  10957  10844     10483   \n",
       "     80-85  13721   12988  14230  12632  12915  11729  12264  12238     11770   \n",
       "     85-90  15688   14556  15697  14250  13977  12588  13338  13145     12929   \n",
       "     90-95  11244   10304  11333  10022   9818   8960   9401   9243      9004   \n",
       "     95+     3839    3569   3954   3621   3588   3287   3417   3465      3345   \n",
       "\n",
       "           Oktober November Dezember  \n",
       "Jahr Alter                            \n",
       "2023 0-15      NaN      NaN      NaN  \n",
       "     15-30     NaN      NaN      NaN  \n",
       "     30-35     NaN      NaN      NaN  \n",
       "     35-40     NaN      NaN      NaN  \n",
       "     40-45     NaN      NaN      NaN  \n",
       "...            ...      ...      ...  \n",
       "2016 75-80   11566    11709    12805  \n",
       "     80-85   13147    13534    14704  \n",
       "     85-90   14189    14629    16123  \n",
       "     90-95   10221    10446    11830  \n",
       "     95+      3802     4015     4451  \n",
       "\n",
       "[128 rows x 12 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "large_df = return_dataframe(77)\n",
    "\n",
    "for page in [78, 79, 80]:\n",
    "    df = return_dataframe(page)\n",
    "    large_df = large_df.append(df)\n",
    "    \n",
    "large_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3191610d-1cdb-407f-a2d3-7fabc63da9a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "large_df.to_csv('deaths_month_age.csv')"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}