| 1234567891011121314151617181920212223242526272829303132333435363738394041424344 |
- import pandas as pd
- import numpy as np
- 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
- 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['Plan'] * df[key + '_2'] / 100, df['Plan'] * 8.3333 / 100)
- 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
- df = data_cleansing('Planung/V1.json')
- season = df[(df['level'] == 2) & (df['Ebene1'] == 'Umsatzerlöse')]
- 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[header]
- plan.to_csv(open('Planung/V1.csv', 'w', newline=''), sep=';', decimal=',', encoding='ansi', index=False)
|
