Python-Projekte/planner/file_export.py

import pandas as pd
import numpy as np

base_dir = '/home/robert/projekte/python/planner/export/'

id_header = ['Ebene' + str(i) for i in range(1, 11)]
values2_header = ['VJ', 'AJ', 'FC', 'Plan_ori', 'Plan_Prozent', 'Stk', 'VAK', 'BE_Prozent', 'Plan_VJ', 'Plan_Stk_VJ', 'Plan',
                  'Jan', 'Feb', 'Mar', 'Apr', 'Mai', 'Jun', 'Jul', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez', 'Periode13']
season_header = ['Jan', 'Feb', 'Mar', 'Apr', 'Mai', 'Jun', 'Jul', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez']
header = ['text', 'costcenter', 'department'] + id_header + values2_header

source_header = ['department', 'text', 'costcenter', 'Ebene1', 'Plan']
export_header = ['Betrieb Nr', 'Zeile mit Bez', 'Bereich', 'Vstufe 1', 'Gesamt']    # 'Version', 'Konto', 'Jahr']


def expand(df, header, values_label):
    for i, key in enumerate(header):
        df[key] = df[values_label].str[i]
    return df


def apply_season(df):
    df['Saison'] = df['Ebene1'].str.contains('Umsatzerlöse|Materialaufwand|Verkaufsabh. Kosten')
    for i, key in enumerate(season_header):
        df[key] = np.where((df['Saison']) & (df[key + '_2'] != 8.3333), df['Plan'] * df[key + '_2'] / 100, df['Plan'] / 12)
    df['Dez'] = df['Plan'] - df[season_header].sum(axis=1) + df['Dez']
    return df


def data_cleansing(filename):
    df = pd.read_json(filename)
    df['values2'] = df['values2'].apply(lambda v: list(v.items()))
    df = df.explode('values2')
    df['department'], df['values2'] = zip(*df['values2'])

    df['id'] = df['id'].str.split(';')
    df = expand(df, id_header, 'id')
    df = expand(df, values2_header, 'values2')
    return df


def export_plan(version, target_year, amount_value):
    df = data_cleansing(f'{base_dir}/{version}.json')
    season = df[(df['level'] == 2) & (df['Ebene1'] == 'Umsatzerlöse')]
    df['Minus1'] = np.where(df['Ebene1'] != 'Umsatzerlöse', -1, 1)
    df['Plan'] = df[amount_value] * df['Minus1']
    plan = df[df['accounts'].apply(lambda a: len(a) > 0)]

    plan = pd.merge(plan, season, how='left', on=['Ebene2', 'department'], suffixes=('', '_2'))
    plan = apply_season(plan)
    plan = plan[source_header + season_header].rename(columns=dict(zip(source_header, export_header)))

    # Reisacher Spezialbedingungen
    plan['Zeile mit Bez'] = np.where(plan['Zeile mit Bez'].isin(['BMW aus Leasingrücklauf BFS', 'BMW aus Leasingrücklauf Alphabet']),
                                     '3040 - BMW aus Leasingrücklauf', plan['Zeile mit Bez'])
    plan['Zeile mit Bez'] = np.where(plan['Zeile mit Bez'].isin(['BMW an Wiederverkäufer BFS', 'BMW an Wiederverkäufer Alphabet']),
                                     '3120 - BMW an Wiederverkäufer', plan['Zeile mit Bez'])
    if amount_value == 'Stk':
        plan = plan[plan['Vstufe 1'] == 'Umsatzerlöse']
        plan['Vstufe 1'] = 'Verk. Stückzahlen'

    plan['Zeile'] = plan['Zeile mit Bez'].str.slice(stop=4)
    plan['Version'] = version
    plan['Konto'] = ''
    plan['Jahr'] = target_year

    plan.to_csv(open(f'{base_dir}/Planner_{target_year}_{version}_{amount_value}.csv', 'w', newline=''),
                sep=';', decimal=',', encoding='latin-1', index=False)


if __name__ == '__main__':
    export_plan('V3', '2021', 'Plan')
    export_plan('V3', '2021', 'Stk')