Python-Projekte/planner/csv_accounts.py

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


def actuals(period):
    base_dir = Path('.').absolute()
    df1 = pd.read_csv(base_dir.joinpath('planner/Planung/Belege_Planung_Ist_FC_AHR.csv'), sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df2 = pd.read_csv(base_dir.joinpath('planner/Planung/Belege_Planung_Ist_FC_AHA.csv'), sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df12 = pd.concat([df1, df2])
    df3 = pd.read_csv(base_dir.joinpath('planner/Planung/NW_GW_Stk_Planung_AHR.csv'), sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df4 = pd.read_csv(base_dir.joinpath('planner/Planung/NW_GW_Stk_Planung_AHA.csv'), sep=';', decimal=',',
                      dtype={0: str, 1: str, 2: str, 3: float})
    df34 = pd.concat([df3, df4])
    df = pd.merge(df12, df34, how='left', on=['Bookkeep_Period', 'Betrieb_Nr', 'Konto_Nr'])
    # 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['Jahr'] = df['Bookkeep_Period'].apply(lambda x: x[:4])
    current_year = period[:4]
    prev_year = str(int(current_year) - 1)
    next_year = str(int(current_year) + 1)
    month_no = int(period[4:])
    # df = df[df['Bookkeep_Period'] <= period]
    df['PY'] = np.where(df['Jahr'] == prev_year, df['Betrag'], 0)
    df['PYQ'] = np.where(df['Jahr'] == prev_year, df['Menge'], 0)
    df['CY'] = np.where(df['Jahr'] == current_year, df['Betrag'], 0)
    df['CYQ'] = np.where(df['Jahr'] == current_year, df['Menge'], 0)
    df['YTD'] = np.where(df['Bookkeep_Period'] <= period, df['CY'], 0)
    df['YTDQ'] = np.where(df['Bookkeep_Period'] <= period, df['CYQ'], 0)
    df['FC'] = df['YTD'] * 12 / month_no
    df['FCQ'] = df['YTDQ'] * 12 / month_no
    df.drop(columns=['Menge', 'Betrag'], inplace=True)

    # 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 (acct, dept), values in df.to_dict(orient='index').items():
        if acct not in res:
            res[acct] = {}
        res[acct][dept] = [round(v, 2) for v in values.values()]

    data = {'values': res}
    json.dump(data, open(base_dir.joinpath(f'planner/export/accounts_{next_year}.json'), 'w'), indent=2)


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

    df2 = pd.read_csv('planner/Planung/AHA_Global Planner_2018_PKW_MOT_ohne_Marketing.csv',
                      sep=';', decimal=',', encoding='latin-1', 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('planner/export/planning_2021.json', 'w'), indent=2)


def planning_new(filename):
    with open('planner/export/' + filename, 'r') as frh:
        structure = json.load(frh)
    year = str(int(filename[:4]) + 1)
    result = {}
    for s in structure:
        if len(s['accounts']) == 0:
            continue
        result[s['id']] = dict([(k, [v[10], v[5]]) for k, v in s['values2'].items()])

    with open(f"planner/export/planning_{year}.json", 'w') as fwh:
        json.dump({'values': result}, fwh, indent=2)


if __name__ == '__main__':
    # planning_prev()
    # actuals('202209')
    planning_new('2022_V2_20220407150009.json')