list:\n",
" result = []\n",
" soup = BeautifulSoup(report, features=\"html.parser\")\n",
" for table in soup.find_all(\"table\", {\"class\": \"std_table\"}):\n",
" df = pd.read_html(StringIO(str(table)), flavor=\"bs4\")[0]\n",
" print(df.columns)\n",
" print(df.dtypes)\n",
" result.append(df)\n",
" return result\n",
"\n",
"\n",
"tables = parse_tables(sample_report)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Unnamed: 0_level_0 | \n",
" 2020 | \n",
" 2019 | \n",
" Veränderung | \n",
"
\n",
" \n",
" | \n",
" Unnamed: 0_level_1 | \n",
" T€ | \n",
" T€ | \n",
" T€ | \n",
" % | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" Jahresüberschuss/Jahresfehlbetrag | \n",
" 1.661 | \n",
" 1.599 | \n",
" 62 | \n",
" 39 | \n",
"
\n",
" \n",
" | 1 | \n",
" + Steuern vom Einkommen und vom Ertrag | \n",
" 796.000 | \n",
" 792.000 | \n",
" 4 | \n",
" 5 | \n",
"
\n",
" \n",
" | 2 | \n",
" = EBT | \n",
" 2.457 | \n",
" 2.391 | \n",
" 66 | \n",
" 28 | \n",
"
\n",
" \n",
" | 3 | \n",
" + Finanzergebnis | \n",
" -13.000 | \n",
" -99.000 | \n",
" 86 | \n",
" -869 | \n",
"
\n",
" \n",
" | 4 | \n",
" = EBIT | \n",
" 2.444 | \n",
" 2.292 | \n",
" 152 | \n",
" 66 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Unnamed: 0_level_0 2020 2019 Veränderung \n",
" Unnamed: 0_level_1 T€ T€ T€ %\n",
"0 Jahresüberschuss/Jahresfehlbetrag 1.661 1.599 62 39\n",
"1 + Steuern vom Einkommen und vom Ertrag 796.000 792.000 4 5\n",
"2 = EBT 2.457 2.391 66 28\n",
"3 + Finanzergebnis -13.000 -99.000 86 -869\n",
"4 = EBIT 2.444 2.292 152 66"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"current_table = tables[0]\n",
"current_table.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import re\n",
"\n",
"\n",
"def cleanse_string(value: str) -> str:\n",
" if value is not None and isinstance(value, str):\n",
" return re.sub(r\"(.+\\.).\", \"\", value)\n",
" return None"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\trist\\AppData\\Local\\Temp\\ipykernel_4708\\152097142.py:2: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n",
" current_table.iloc[index][0] = cleanse_string(row[0])\n",
"C:\\Users\\trist\\AppData\\Local\\Temp\\ipykernel_4708\\152097142.py:2: FutureWarning: Series.__setitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To set a value by position, use `ser.iloc[pos] = value`\n",
" current_table.iloc[index][0] = cleanse_string(row[0])\n"
]
},
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Unnamed: 0_level_0 | \n",
" 2020 | \n",
" 2019 | \n",
" Veränderung | \n",
"
\n",
" \n",
" | \n",
" Unnamed: 0_level_1 | \n",
" T€ | \n",
" T€ | \n",
" T€ | \n",
" % | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" Jahresüberschuss/Jahresfehlbetrag | \n",
" 1.661 | \n",
" 1.599 | \n",
" 62 | \n",
" 39 | \n",
"
\n",
" \n",
" | 1 | \n",
" + Steuern vom Einkommen und vom Ertrag | \n",
" 796.000 | \n",
" 792.000 | \n",
" 4 | \n",
" 5 | \n",
"
\n",
" \n",
" | 2 | \n",
" = EBT | \n",
" 2.457 | \n",
" 2.391 | \n",
" 66 | \n",
" 28 | \n",
"
\n",
" \n",
" | 3 | \n",
" + Finanzergebnis | \n",
" -13.000 | \n",
" -99.000 | \n",
" 86 | \n",
" -869 | \n",
"
\n",
" \n",
" | 4 | \n",
" = EBIT | \n",
" 2.444 | \n",
" 2.292 | \n",
" 152 | \n",
" 66 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Unnamed: 0_level_0 2020 2019 Veränderung \n",
" Unnamed: 0_level_1 T€ T€ T€ %\n",
"0 Jahresüberschuss/Jahresfehlbetrag 1.661 1.599 62 39\n",
"1 + Steuern vom Einkommen und vom Ertrag 796.000 792.000 4 5\n",
"2 = EBT 2.457 2.391 66 28\n",
"3 + Finanzergebnis -13.000 -99.000 86 -869\n",
"4 = EBIT 2.444 2.292 152 66"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"for index, row in current_table.iterrows():\n",
" current_table.iloc[index][0] = cleanse_string(row[0])\n",
"current_table.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"def parse_string_to_float(value) -> float:\n",
" try:\n",
" if value is None:\n",
" return None\n",
" # if isinstance(value, float):\n",
" # return value\n",
" return float(str(value).replace(\".\", \"\").replace(\",\", \".\"))\n",
" except Exception as e:\n",
" return None\n",
"\n",
"\n",
"def apply_factor(value, factor: float):\n",
" transformed_value = parse_string_to_float(value)\n",
" if transformed_value is None or isinstance(transformed_value, str):\n",
" return None\n",
" result = transformed_value * factor\n",
" # print(result)\n",
" return result"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Unnamed: 0_level_0 | \n",
" 2020 | \n",
" 2019 | \n",
" Veränderung | \n",
"
\n",
" \n",
" | \n",
" Unnamed: 0_level_1 | \n",
" T€ | \n",
" T€ | \n",
" T€ | \n",
" % | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" Jahresüberschuss/Jahresfehlbetrag | \n",
" 1661000.0 | \n",
" 1599000.0 | \n",
" 62000.0 | \n",
" 39 | \n",
"
\n",
" \n",
" | 1 | \n",
" + Steuern vom Einkommen und vom Ertrag | \n",
" 7960000.0 | \n",
" 7920000.0 | \n",
" 4000.0 | \n",
" 5 | \n",
"
\n",
" \n",
" | 2 | \n",
" = EBT | \n",
" 2457000.0 | \n",
" 2391000.0 | \n",
" 66000.0 | \n",
" 28 | \n",
"
\n",
" \n",
" | 3 | \n",
" + Finanzergebnis | \n",
" -130000.0 | \n",
" -990000.0 | \n",
" 86000.0 | \n",
" -869 | \n",
"
\n",
" \n",
" | 4 | \n",
" = EBIT | \n",
" 2444000.0 | \n",
" 2292000.0 | \n",
" 152000.0 | \n",
" 66 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Unnamed: 0_level_0 2020 2019 Veränderung \\\n",
" Unnamed: 0_level_1 T€ T€ T€ \n",
"0 Jahresüberschuss/Jahresfehlbetrag 1661000.0 1599000.0 62000.0 \n",
"1 + Steuern vom Einkommen und vom Ertrag 7960000.0 7920000.0 4000.0 \n",
"2 = EBT 2457000.0 2391000.0 66000.0 \n",
"3 + Finanzergebnis -130000.0 -990000.0 86000.0 \n",
"4 = EBIT 2444000.0 2292000.0 152000.0 \n",
"\n",
" \n",
" % \n",
"0 39 \n",
"1 5 \n",
"2 28 \n",
"3 -869 \n",
"4 66 "
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"converter = {\n",
" \"Mio€\": 1 * 10**6,\n",
" \"Mio\": 1 * 10**6,\n",
" \"T€\": 1 * 10**3,\n",
" \"TEUR\": 1 * 10**3,\n",
" \"EUR\": 1,\n",
" \"€\": 1,\n",
"}\n",
"\n",
"for column in current_table.columns[1:]:\n",
" if isinstance(column, tuple):\n",
" for c in column:\n",
" for x, factor in converter.items():\n",
" if x in c:\n",
" current_table[column] = current_table[column].apply(\n",
" lambda x: apply_factor(x, factor)\n",
" )\n",
" break\n",
" else:\n",
" for x, factor in converter.items():\n",
" parts = str(column).split(\" \")\n",
" for y in parts:\n",
" if re.match(x, y):\n",
" current_table[column] = current_table[column].apply(\n",
" lambda x: apply_factor(x, factor)\n",
" )\n",
" current_table.rename({column: parts[0]}, inplace=True, axis=1)\n",
" break\n",
"\n",
"current_table.dropna(axis=0, how=\"all\", inplace=True)\n",
"current_table.dropna(axis=1, how=\"all\", inplace=True)\n",
"current_table.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Unnamed: 0_level_0 Unnamed: 0_level_1 object\n",
"2020 T€ float64\n",
"2019 T€ float64\n",
"Veränderung T€ float64\n",
" % int64\n",
"dtype: object"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"current_table.dtypes"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Remove columns hosting non-numerics; excl. first column hosting keys\n",
"columns_to_prune = []\n",
"for column_index, column_type in enumerate(current_table.dtypes[1:]):\n",
" if column_type in [\"object\", \"str\"]:\n",
" columns_to_prune.append(column_index + 1)\n",
"\n",
"current_table = current_table.drop(\n",
" current_table.columns[columns_to_prune], axis=\"columns\"\n",
")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Unnamed: 0_level_0 | \n",
" 2020 | \n",
" 2019 | \n",
" Veränderung | \n",
"
\n",
" \n",
" | \n",
" Unnamed: 0_level_1 | \n",
" T€ | \n",
" T€ | \n",
" T€ | \n",
" % | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" Jahresüberschuss/Jahresfehlbetrag | \n",
" 1661000.0 | \n",
" 1599000.0 | \n",
" 62000.0 | \n",
" 39 | \n",
"
\n",
" \n",
" | 1 | \n",
" + Steuern vom Einkommen und vom Ertrag | \n",
" 7960000.0 | \n",
" 7920000.0 | \n",
" 4000.0 | \n",
" 5 | \n",
"
\n",
" \n",
" | 2 | \n",
" = EBT | \n",
" 2457000.0 | \n",
" 2391000.0 | \n",
" 66000.0 | \n",
" 28 | \n",
"
\n",
" \n",
" | 3 | \n",
" + Finanzergebnis | \n",
" -130000.0 | \n",
" -990000.0 | \n",
" 86000.0 | \n",
" -869 | \n",
"
\n",
" \n",
" | 4 | \n",
" = EBIT | \n",
" 2444000.0 | \n",
" 2292000.0 | \n",
" 152000.0 | \n",
" 66 | \n",
"
\n",
" \n",
" | 5 | \n",
" + Abschreibungsaufwand | \n",
" 2239000.0 | \n",
" 2273000.0 | \n",
" -34000.0 | \n",
" -15 | \n",
"
\n",
" \n",
" | 6 | \n",
" = EBITDA | \n",
" 4683000.0 | \n",
" 4565000.0 | \n",
" 118000.0 | \n",
" 26 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Unnamed: 0_level_0 2020 2019 Veränderung \\\n",
" Unnamed: 0_level_1 T€ T€ T€ \n",
"0 Jahresüberschuss/Jahresfehlbetrag 1661000.0 1599000.0 62000.0 \n",
"1 + Steuern vom Einkommen und vom Ertrag 7960000.0 7920000.0 4000.0 \n",
"2 = EBT 2457000.0 2391000.0 66000.0 \n",
"3 + Finanzergebnis -130000.0 -990000.0 86000.0 \n",
"4 = EBIT 2444000.0 2292000.0 152000.0 \n",
"5 + Abschreibungsaufwand 2239000.0 2273000.0 -34000.0 \n",
"6 = EBITDA 4683000.0 4565000.0 118000.0 \n",
"\n",
" \n",
" % \n",
"0 39 \n",
"1 5 \n",
"2 28 \n",
"3 -869 \n",
"4 66 \n",
"5 -15 \n",
"6 26 "
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Prune rows where first columns is None\n",
"import numpy as np\n",
"\n",
"current_table = current_table.replace(to_replace=\"None\", value=np.nan).dropna()\n",
"current_table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\trist\\AppData\\Local\\Temp\\ipykernel_4708\\971135872.py:5: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n",
" row[0] = re.sub(exp, \"\", row[0]).strip()\n",
"C:\\Users\\trist\\AppData\\Local\\Temp\\ipykernel_4708\\971135872.py:5: FutureWarning: Series.__setitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To set a value by position, use `ser.iloc[pos] = value`\n",
" row[0] = re.sub(exp, \"\", row[0]).strip()\n"
]
}
],
"source": [
"exps = [r\"^[0-9a-zA-Z]+[\\.\\)] \", r\"[\\+\\=\\-\\_]\"]\n",
"for _index, row in current_table.iterrows():\n",
" for exp in exps:\n",
" # print(row[0])\n",
" row[0] = re.sub(exp, \"\", row[0]).strip()\n",
" # print(row[0])"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\trist\\AppData\\Local\\Temp\\ipykernel_4708\\344636547.py:5: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n",
" name_cleansed = row[0]\n",
"C:\\Users\\trist\\AppData\\Local\\Temp\\ipykernel_4708\\344636547.py:9: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n",
" kpis[name_cleansed] = row[1]\n"
]
},
{
"data": {
"text/plain": [
"{'Jahresüberschuss/Jahresfehlbetrag': 1661000.0,\n",
" 'Steuern vom Einkommen und vom Ertrag': 7960000.0,\n",
" 'EBT': 2457000.0,\n",
" 'Finanzergebnis': -130000.0,\n",
" 'EBIT': 2444000.0,\n",
" 'Abschreibungsaufwand': 2239000.0,\n",
" 'EBITDA': 4683000.0}"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"kpis = {}\n",
"\n",
"exps = [r\"^[0-9a-zA-Z]+[\\.\\)] \", r\"[\\+\\=\\-\\_]\"]\n",
"for _index, row in current_table.iterrows():\n",
" name_cleansed = row[0]\n",
" for exp in exps:\n",
" # print(row[0])\n",
" name_cleansed = re.sub(exp, \"\", name_cleansed).strip()\n",
" kpis[name_cleansed] = row[1]\n",
"kpis"
]
}
],
"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.11.3"
},
"orig_nbformat": 4
},
"nbformat": 4,
"nbformat_minor": 2
}