Python-Projekte/planner/csv_accounts.py

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


def actuals(period):
    # df1 = pd.read_csv('Planung/Belege_Planung_Ist_FC.csv', sep=';', decimal=',',
    #                   dtype={0: str, 1: str, 2: str, 3: float})
    # df2 = pd.read_csv('Planung/Belege_Planung_Ist_FC_AHA.csv', sep=';', decimal=',',
    #                   dtype={0: str, 1: str, 2: str, 3: float})
    # df = pd.concat([df1, df2])
    df = pd.read_csv('Planung/Belege_Planung_Ist_FC_Dresen.csv', sep=';', decimal=',',
                     dtype={0: str, 1: str, 2: str, 3: str, 4: str, 5: float, 6: float})

    df = df[df['Bookkeep_Period'] <= period]
    df['Jahr'] = df['Bookkeep_Period'].apply(lambda x: x[:4])
    df['VJ'] = np.where(df['Jahr'] != period[:4], df['Betrag'], 0)
    df['AJ'] = np.where(df['Jahr'] == period[:4], df['Betrag'], 0)
    df['FC'] = df['AJ'] * 12 / int(period[4:])

    # df2 = pd.pivot_table(df, values='Betrag', index=['Konto Nr', 'Betrieb Nr'], columns=['Jahr'], aggfunc=np.sum, fill_value=0.0)
    df = df.groupby(['Konto_Nr', 'Betrieb_Nr']).sum()
    print(df.head())

    res = {}
    for (pkey, values) in df.to_dict(orient='index').items():
        account, department = pkey
        if account not in res:
            res[account] = {}
        res[account][department] = [round(values['VJ'], 2), round(values['AJ'], 2), round(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']]

    df['id'] = df['regex'].apply(lambda x: (list(filter(lambda y: match(x, y), structure_ids)) + [''])[0])
    df = df[df['id'] != '']

    res = {}
    for item in df.to_dict(orient='records'):
        if item['id'] not 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('202009')