Python-Projekte/csv_accounts.py

import pandas as pd
import numpy as np
from re import match
import json


def actuals(period):
    df = pd.read_csv('Planung/Belege_Planung_Ist_FC.csv', sep=';', decimal=',',
                     dtype={0: str, 1: str, 2: str, 3: float})
    df = df[df['Bookkeep_Period'] <= period]
    df['Jahr'] = df['Bookkeep_Period'].apply(lambda x: 'AJ' if x[:4] == '2020' else 'VJ')
    df['primary_key'] = df['Betrieb Nr'] + '_' + df['Konto Nr']

    df = pd.pivot_table(df, values='Betrag', index=['primary_key'], columns=['Jahr'], aggfunc=np.sum, fill_value=0.0)
    df['FC'] = df['AJ'] * 12 / int(period[4:])

    res = {}
    for (pkey, values) in df.to_dict(orient='index').items():
        split = pkey.split('_')
        if not split[1] in res:
            res[split[1]] = {}
        res[split[1]][split[0]] = [values['VJ'], values['AJ'], values['FC'], 0.0, 0.0, 0.0]

    data = {'values': res}
    json.dump(data, open('Planung/export/accounts.json', 'w'), indent=2)


def planning_prev():
    df1 = pd.read_csv('Planung/Global Planner_2018_ohne_Marketing.csv',
                      sep=';', decimal=',', encoding='ansi', dtype={'Betrieb Nr': str, 'Bereich': str})
    df1 = df1[['Jahr', 'Betrieb Nr', 'Vstufe 1', 'Bereich', 'Zeile mit Bez', 'Version', 'Menge', 'Wert']]

    df2 = pd.read_csv('Planung/AHA_Global Planner_2018_PKW_MOT_ohne_Marketing.csv',
                      sep=';', decimal=',', encoding='ansi', dtype={'Betrieb Nr': str, 'Bereich': str})
    df2 = df2[['Jahr', 'Betrieb Nr', 'Vstufe 1', 'Bereich', 'Zeile mit Bez', 'Version', 'Menge', 'Wert']]
    df = pd.concat([df1, df2])

    df['Bereich'] = df['Bereich'].fillna('NA').replace('VW (inkl. GF)', '?')
    df['Zeile'] = df['Zeile mit Bez'].apply(lambda x: x[:4])
    df['Konto'] = ''
    df['regex'] = df['Vstufe 1'] + ";" + df['Bereich'] + ";.*" + df['Zeile'] + ' - [^;]*;;'
    df = df[df['Wert'] != 0]

    gcstruct = json.load(open('GCStruct_Reisacher_Planung/gcstruct_reisacher.json', 'r'))
    structure_ids = [s['id'] for s in gcstruct['flat']['Struktur_FB']]
    # print(structure_ids)
    df['id'] = df['regex'].apply(lambda x: (list(filter(lambda y: match(x, y), structure_ids)) + [''])[0])
    df = df[df['id'] != '']
    # df['pkey'] = df['id'] + "_" + df['Betrieb Nr']
    # df = df.set_index('pkey')
    res = {}
    for item in df.to_dict(orient='records'):
        if not item['id'] in res:
            res[item['id']] = {}
        res[item['id']][item['Betrieb Nr']] = [item['Wert'], item['Menge']]
    data = {'values': res}
    json.dump(data, open('Planung/export/planning.json', 'w'), indent=2)


if __name__ == '__main__':
    planning_prev()
    actuals('202010')