Python-Projekte/sandbox/starmoney.py

import pandas as pd
import numpy as np
from pathlib import Path

year = "2023"
export_dir = f"/home/robert/Nextcloud/Sängerlust Hausen/Kasse/{year}/Export"
kst = {"3115532": "0", "3123981": "1", "3124005": "2", "3123999": "3", "3124039": "4", "3123973": "5", "3124013": "6"}
from_label = [
    "Buchungstag",
    "Internet",
    "Verwendungszweckzeile 1",
    "Kommentar",
    "KST",
    "Verwendungszweckzeile 3",
    "Verwendungszweckzeile 4",
    "Verwendungszweckzeile 5",
    "row_num",
    "Einnahmen",
    "Ausgaben",
]
to_label = [
    "Datum",
    "frei",
    "Vorgang",
    "Konto",
    "KST",
    "Nr.",
    "Einnahmen_Kasse",
    "Ausgaben_Kasse",
    "Nr.",
    "Einnahmen",
    "Ausgaben",
]


def import_csv(filename):
    kto = filename.name.split("_")[0]
    print(kto + ": " + kst[kto])
    df: pd.DataFrame = pd.read_csv(
        filename, sep=";", decimal=",", encoding="utf-8", index_col=["IBAN", "Laufende Nummer"]
    ).reset_index()
    df["Einnahmen"] = np.where(df["Betrag"] > 0, df["Betrag"], 0)
    df["Ausgaben"] = np.where(df["Betrag"] < 0, 0 - df["Betrag"], 0)
    df["KST"] = kst[kto]
    first_saldo = df.loc[0]["Saldo"] - df.loc[0]["Betrag"]
    last_saldo = df.loc[len(df) - 1]["Saldo"]
    df = df[df["Betrag"] != 0]
    df["row_num"] = np.arange(1, len(df) + 1)
    df = df[from_label]
    print(df.shape)
    first_row = [f"01.01.{year}", "", "Anfangsbestand - SLS " + kto, "AB", kst[kto], "", "", "", 0, first_saldo, 0]
    last_row = [f"31.12.{year}", "", "Endbestand - SLS " + kto, "EB", kst[kto], "", "", "", len(df) + 1, 0, last_saldo]
    df = pd.concat([pd.DataFrame([first_row], columns=from_label), df, pd.DataFrame([last_row], columns=from_label)])
    print(df.shape)
    return df


df = [import_csv(f) for f in Path(export_dir).glob("*.csv")]
df_union: pd.DataFrame = pd.concat(df)
print(df_union.shape)


df_union = df_union.rename(columns=dict(zip(from_label, to_label)))
df_union.to_csv(export_dir + "/export.csv.txt", sep=";", decimal=",", encoding="latin-1", index=False)