| 1234567891011121314151617181920212223242526272829303132333435363738394041424344 |
- import pandas as pd
- import numpy as np
- from pathlib import Path
- year = '2022'
- export_dir = f'/home/robert/Dropbox/Jugendchor 2000/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)
|
