import pandas as pd import numpy as np import xml.etree.ElementTree as ET import json import csv import re import chevron # from shutil import copyfile from bs4 import BeautifulSoup from functools import reduce from pathlib import Path def get_flat(node): result = [ { "id": node["id"], "text": node["text"], "children": [x["id"] for x in node["children"]], "children2": [], "parents": node["parents"], "accounts": node["accounts"], "costcenter": "", "level": node["level"], "drilldown": node["level"] < 2, # (node['level'] != 2 and len(node['accounts']) == 0), "form": node["form"], "accountlevel": False, "absolute": True, "seasonal": True, "status": "0", "values": [], "values2": {}, } ] for child in node["children"]: result += get_flat(child) return result def get_parents_list(p_list): id = ";".join(p_list) + ";" * (10 - len(p_list)) if len(p_list) > 0: return [id] + get_parents_list(p_list[:-1]) return [";" * 9] def structure_from_tree(node): result = [] result.append(node["id"]) for child in node["children"]: result.extend(structure_from_tree(child)) return result def xml_from_tree(xml_node, tree_node): for child in tree_node["children"]: element = ET.SubElement(xml_node, "Ebene") element.set("Name", child["text"]) xml_from_tree(element, child) def split_it(text, index): try: return re.findall(r"([^;]+) - ([^;]*);;", text)[0][index] except Exception: return "" def last_layer(text): try: return re.findall(r"([^;]+);;", text)[0] except Exception: return "" def get_default_cols(i): return ["Ebene" + str(i) for i in range(i * 10 + 1, (i + 1) * 10 + 1)] def get_structure_exports(s): result = { "files": {}, "format": { "KontoFormat": "{0} - {1}", "HerstellerkontoFormat": "{{Herstellerkonto_Nr}}", "HerstellerBezeichnungFormat": "{{Herstellerkonto_Bez}}", "NeueHerstellerkontenAnlegen": False, }, } export_files = [ "ExportStk", "ExportStrukturenStk", "ExportAdjazenz", "ExportUebersetzung", "ExportUebersetzungStk", "ExportHerstellerKontenrahmen", ] export_format = [ "KontoFormat", "HerstellerkontoFormat", "HerstellerBezeichnungFormat", "NeueHerstellerkontenAnlegen", ] for e in export_files: if s.find(e) is not None and s.find(e).text is not None and s.find(e).text[-4:] == ".csv": result["files"][e] = s.find(e).text for e in export_format: if s.find(e) is not None and s.find(e).text != "": result["format"][e] = s.find(e).text result["format"]["NeueHerstellerkontenAnlegen"] = result["format"]["NeueHerstellerkontenAnlegen"] == "true" return result class GCStruct: config = { "path": "c:/projekte/python/gcstruct", "path2": "c:/projekte/python/gcstruct", "file": "c:/projekte/python/gcstruct/config/config.xml", "output": "gcstruct.json", "default": [], "special": {}, "special2": { "Planner": ["Kostenstelle", "Ebene1", "Ebene2"], "Test": ["Ebene1", "Ebene2"], }, "columns": [ "Konto_Nr", "Konto_Bezeichnung", "Konto_Art", "Konto_KST", "Konto_STK", "Konto_1", "Konto_2", "Konto_3", "Konto_4", "Konto_5", ], "struct": {}, "export": {}, } json_result = { "accounts": {}, "tree": {}, "flat": {}, "struct_export": {}, "skr51_vars": {}, } structure_ids = [] translate = { "Konto_Nr": "SKR51", "Kostenstelle": "KST", "Absatzkanal": "ABS", "Kostenträger": "KTR", "Marke": "MAR", "Standort": "STA", "Marke_HBV": "MAR", "Standort_HBV": "BMC", } def __init__(self, struct_dir, export_dir=None): self.config["path"] = struct_dir self.config["path2"] = struct_dir + "/export" if export_dir is None else export_dir self.config["file"] = f"{self.config['path']}/config/gcstruct.xml" if not Path(self.config["file"]).exists(): self.config["file"] = f"{self.config['path']}/config/config.xml" cfg = ET.parse(self.config["file"]) self.config["default"] = [ s.find("Name").text for s in cfg.getroot().find("Strukturdefinitionen").findall("Struktur") ] self.config["export"] = dict( [ (s.find("Name").text, get_structure_exports(s)) for s in cfg.getroot().find("Strukturdefinitionen").findall("Struktur") ] ) struct = dict([(x, get_default_cols(i)) for (i, x) in enumerate(self.config["default"])]) struct.update(self.config["special"]) self.config["struct"] = struct # print(self.config['struct']) def export_header(self, filetype): return { "ExportStk": [], "ExportStrukturenStk": [], "ExportAdjazenz": [], "ExportUebersetzung": [ "Konto_Nr_Hersteller", "Konto_Nr_Split", "Konto_Nr_Haendler", "Info", ], "ExportUebersetzungStk": [ "Konto_Nr_Hersteller", "Konto_Nr_Split", "Konto_Nr_Haendler", "Info", ], "ExportHerstellerKontenrahmen": [ "Konto_Nr", "Konto_Bezeichnung", "Case", "Info", ], }[filetype] def accounts_from_csv(self, struct): max_rows = (len(self.config["default"]) + 1) * 10 with open( f"{self.config['path']}/Kontenrahmen/Kontenrahmen.csv", "r", encoding="latin-1", ) as f: csv_reader = csv.reader(f, delimiter=";") imported_csv = [row[:max_rows] for row in csv_reader] df = pd.DataFrame.from_records(np.array(imported_csv[1:], dtype="object"), columns=imported_csv[0]).fillna( value="" ) df = df.rename(columns={"Kostenstelle": "Konto_KST", "STK": "Konto_STK"}) for i, (s, cols) in enumerate(struct.items()): df[s] = reduce(lambda x, y: x + ";" + df[y], cols, "") df[s] = df[s].apply(lambda x: x[1:]) df["LetzteEbene" + str(i + 1)] = df[s].apply(lambda x: last_layer(x)) df["LetzteEbene" + str(i + 1) + "_Nr"] = df[s].apply(lambda x: split_it(x, 0)) df["LetzteEbene" + str(i + 1) + "_Bez"] = df[s].apply(lambda x: split_it(x, 1)) df["Herstellerkonto_Nr"] = df["LetzteEbene1_Nr"] df["Herstellerkonto_Bez"] = df["LetzteEbene1_Bez"] return df def tree_from_xml(self, struct, df): result = {} for s, cols in struct.items(): try: tree = ET.parse(f"{self.config['path']}/Xml/{s}.xml") result[s] = self.get_tree_root(tree.getroot(), s) except FileNotFoundError: print("XML-Datei fehlt") used_entries = [x.split(";")[1:] for x in set(df[s].to_numpy())] print(used_entries) root = ET.Element("Ebene") root.set("Name", s) result[s] = self.get_tree_root(root, s) # self.json_result["tree"][s] = get_tree_from_accounts(cols, []) return result def get_structure_and_tree(self): df = self.accounts_from_csv(self.config["struct"]) self.json_result["accounts"] = df.to_dict("records") self.structure_ids = df.melt( id_vars=["Konto_Nr"], value_vars=self.config["struct"].keys(), var_name="Struktur", value_name="id", ).groupby(by=["Struktur", "id"]) self.json_result["tree"] = self.tree_from_xml(self.config["struct"], df) for s, cols in self.config["struct"].items(): self.json_result["flat"][s] = get_flat(self.json_result["tree"][s]) for s, entries in self.json_result["flat"].items(): cols = self.config["struct"][s] df_temp = pd.DataFrame([x["id"].split(";") for x in entries], columns=cols) self.json_result["struct_export"][s] = df_temp.to_dict(orient="records") # {'accounts': {}, 'tree': {}, 'flat': {}, 'struct_export': {}, 'skr51_vars': {}} return self.json_result def export_structure_and_tree(self): json.dump( self.json_result, open(f"{self.config['path2']}/{self.config['output']}", "w"), indent=2, ) def get_accounts(self, structure, id): return [x["Konto_Nr"] for x in self.json_result["accounts"] if x[structure] == id] # return [] # res = self.structure_ids.groups.get((structure, id)) # if res is None: # return [] # return res.values def export(self): for s in self.config["export"].keys(): for filetype, filename in self.config["export"][s]["files"].items(): with open(self.config["path2"] + "/" + filename, "w") as fwh: fwh.write("Konto_Nr_Hersteller;Konto_Nr_Split;Konto_Nr_Haendler;Info\n") # 'Hersteller'Konto_Nr;Konto_Bezeichnung;Case;Info' for a in self.json_result["accounts"]: if a["Herstellerkonto_Nr"] != "": account = chevron.render( self.config["export"]["SKR51"]["format"]["HerstellerkontoFormat"], a, ) fwh.write( account + ";" + account + ";" + a["Konto_Nr"] + ";" + "\n" ) # a['Herstellerkonto_Bez'] def get_tree(self, node, parents, structure): result = [] for child in node: p = get_parents_list(parents) parents.append(child.attrib["Name"]) id = ";".join(parents) + ";" * (10 - len(parents)) result.append( { "id": id, "text": child.attrib["Name"], "children": self.get_tree(child, parents, structure), "parents": p, "accounts": self.get_accounts(structure, id), "level": len(parents), "form": child.attrib.get("Split", ""), } ) parents.pop() return result def get_tree_root(self, node, structure): id = ";" * 9 return { "id": id, "text": node.attrib["Name"], "children": self.get_tree(node, [], structure), "parents": [], "accounts": [], "level": 0, "form": "", } def post_structure_and_tree(self): json_post = json.load(open(f"{self.config['path']}/{self.config['output']}", "r")) # Kontenrahmen.csv ebenen = ["Ebene" + str(i) for i in range(1, len(self.config["default"]) * 10 + 1)] header = ";".join(self.config["columns"] + ebenen) cols = self.config["columns"] + self.config["default"] with open( self.config["path"] + "/Kontenrahmen/Kontenrahmen_out.csv", "w", encoding="latin-1", ) as f: f.write(header + "\n") for row in json_post["Kontenrahmen"]: f.write(";".join([row[e] for e in cols]) + "\n") # print(header) # xml und evtl. Struktur.csv for i, s in enumerate(self.config["default"]): with open( f"{self.config['path']}/Strukturen/Kontenrahmen.csv/{s}_out.csv", "w", encoding="latin-1", ) as f: f.write(";".join(["Ebene" + str(i * 10 + j) for j in range(1, 11)]) + "\n") rows = structure_from_tree({"id": ";" * 9, "children": json_post[s]}) f.write("\n".join(rows)) # with open(self.config['path'] + "/Strukturen/Kontenrahmen.csv/" + structure + "_2.csv", "w", encoding="latin-1") as f: root = ET.Element("Ebene") root.set("Name", s) xml_from_tree(root, {"id": ";" * 9, "children": json_post[s]}) with open(f"{self.config['path']}/Xml/{s}_out.xml", "w", encoding="utf-8") as f: f.write(BeautifulSoup(ET.tostring(root), "xml").prettify()) def skr51_translate(self, accounts_combined_files): df = self.accounts_from_csv(self.config["struct"]) df_translate = {} for i, (t_from, t_to) in enumerate(self.translate.items()): last = "LetzteEbene" + str(i + 1) from_label = [ "Konto_Nr", "Konto_Bezeichnung", last, last + "_Nr", last + "_Bez", "Ebene" + str(i * 10 + 1), "Ebene" + str(i * 10 + 2), ] to_label = [ t_to, t_to + "_Bez_ori", t_to + "_Ebene", t_to + "_Nr", t_to + "_Bez", "Ebene1", "Ebene2", ] df_translate[t_from] = df[df[last + "_Nr"] != ""][from_label].rename( columns=dict(zip(from_label, to_label)) ) # print(df_translate[t_to].head()) df2 = [] for ac_file in accounts_combined_files: df2.append( pd.read_csv( ac_file, decimal=",", sep=";", encoding="latin-1", converters={i: str for i in range(0, 200)}, ) ) df_source = pd.concat(df2) df3 = df_source.copy() df3["Konto_Nr"] = df3["Konto_Nr"] + "_STK" df_source = pd.concat([df_source, df3]) for t_from, t_to in self.translate.items(): if t_to == "SKR51": df_source["SKR51"] = df_source["Konto_Nr"] elif t_from in ["Marke_HBV"]: df_source["Marke_HBV"] = df_source["Marke"] elif t_from in ["Standort_HBV"]: df_source["Standort_HBV"] = df_source["Standort"] + "_" + df_source["Marke"] df_source["BMC"] = "BMC_" + df_source["Standort_HBV"] elif t_to == "KTR": df_source["KTR"] = np.where( df_source["Kostenträger_Quelle"] == "TZ", "KTR_TZ_" + df_source["Kostenträger"], "KTR_00", ) df_source["KTR"] = np.where( df_source["Kostenträger_Quelle"].isin(["NW", "SC"]), "KTR_" + df_source["Kostenträger_Quelle"] + "_" + df_source["Marke"] + "_" + df_source["Kostenträger"], df_source["KTR"], ) else: df_source[t_to] = t_to + "_" + df_source[t_from] df_source = df_source.merge(df_translate[t_from], how="left", on=[t_to], suffixes=(None, "_" + t_to)) df_source[t_to + "_Nr"] = np.where( df_source[t_to + "_Nr"].isna(), df_source[t_from], df_source[t_to + "_Nr"], ) df_source["Konto_Nr_SKR51"] = ( df_source["MAR_Nr"] + "-" + df_source["STA_Nr"] + "-" + df_source["SKR51_Nr"] + "-" + df_source["KST_Nr"] + "-" + df_source["ABS_Nr"] + "-" + df_source["KTR_Nr"] ) df_source["Konto_Nr_Händler"] = ( df_source["Marke"] + "-" + df_source["Standort"] + "-" + df_source["Konto_Nr"] + "-" + df_source["Kostenstelle"] + "-" + df_source["Absatzkanal"] + "-" + df_source["Kostenträger"] ) df_source["Konto_Nr_Händler_mit_KST"] = df_source["Konto_Nr"] + "_" + df_source["Kostenstelle"] # df_source.to_csv(f"{self.config['path2']}/SKR51_Uebersetzung.csv", sep=';', encoding='latin-1', index=False) df_source["MAR_Nr_MAR"] = np.where(df_source["MAR_Nr_MAR"].isna(), "0000", df_source["MAR_Nr_MAR"]) from_label = [ "MAR_Nr", "STA_Nr", "SKR51_Nr", "KST_Nr", "ABS_Nr", "KTR_Nr", "KTR_Ebene", "Konto_Nr_Händler", "Konto_Nr_SKR51", "MAR_Nr_MAR", "BMC_Nr", "Konto_Nr", "Konto_Nr_Händler_mit_KST", "SKR51_Bez_ori", ] to_label = [ "Marke", "Standort", "Konto_Nr", "Kostenstelle", "Absatzkanal", "Kostenträger", "Kostenträger_Ebene", "Konto_Nr_Händler", "Konto_Nr_SKR51", "Marke_HBV", "Standort_HBV", "Konto_Nr_Händler_kurz", "Konto_Nr_Händler_mit_KST", "Konto_Bezeichnung_Händler", ] df_combined = df_source[from_label].rename(columns=dict(zip(from_label, to_label))) df_combined.to_csv( f"{self.config['path2']}/Kontenrahmen_uebersetzt.csv", sep=";", encoding="latin-1", index=False, ) def skr51_translate2(self, accounts_combined_file): df = self.accounts_from_csv(self.config["struct"]) df_list = [] for i, s in enumerate(self.config["struct"].keys()): from_label = [ "Konto_Nr", "Ebene" + str(i * 10 + 1), "Ebene" + str(i * 10 + 2), "Ebene" + str(i * 10 + 3), ] to_label = ["Konto_Nr", "key", "value", "value2"] df_temp = df[from_label].rename(columns=dict(zip(from_label, to_label))) df_temp["key"] = "{" + s + "/" + df_temp["key"] + "}" df_list.append(df_temp[df_temp["value"] != ""]) df_translate = pd.concat(df_list) # df_translate.to_csv(f"{self.config['path2']}/SKR51_Variablen.csv", sep=';', encoding='latin-1', index=False) df_source = pd.read_csv( accounts_combined_file, decimal=",", sep=";", encoding="latin-1", converters={i: str for i in range(0, 200)}, ) df_source = df_source[df_source["Konto_Nr"].str.contains("_STK") == False] df_source["Konto_Nr_Gesamt"] = df_source["Konto_Nr"] df_source["Konto_Nr"] = np.where( df_source["Konto_Nr"].str.contains(r"^[4578]"), df_source["Konto_Nr"] + "_" + df_source["Kostenstelle"].str.slice(stop=1), df_source["Konto_Nr"], ) df_source["Konto_Nr"] = np.where( df_source["Konto_Nr"].str.contains(r"^5\d+_4"), df_source["Konto_Nr"] + df_source["Kostenstelle"].str.slice(start=1, stop=2), df_source["Konto_Nr"], ) df_source = df_source.merge(df, how="left", on=["Konto_Nr"]) # rows = df_source.shape[0] df_source["value"] = "" cols = get_default_cols(0) for t_from, t_to in self.translate.items(): if t_from in ["Marke_HBV", "Standort_HBV"]: continue if t_from == "Konto_Nr": df_source[t_to] = df_source[t_from] else: df_source[t_to] = t_to + "_" + df_source[t_from] for e in cols: df_source = df_source.merge( df_translate, how="left", left_on=[t_to, e], right_on=["Konto_Nr", "key"], suffixes=(None, "_" + t_to + "_" + e), ) df_source[e] = np.where( df_source["value_" + t_to + "_" + e].notna(), df_source["value_" + t_to + "_" + e], df_source[e], ) # if df_source.shape[0] > rows: # print(t_to + '_' + e + ': ' + str(df_source.shape[0])) # df_source.to_csv(f"{self.config['path2']}/SKR51_Variablen2.csv", sep=';', encoding='latin-1', index=False) # df_source[t_to + '_Nr'] = np.where(df_source[t_to + '_Nr'].isna(), df_source[t_from], df_source[t_to + '_Nr']) for e in cols: df_source[e] = np.where( df_source[e].str.startswith("{"), df_source[e].str.extract(r"\/(.*)}", expand=False) + " falsch", df_source[e], ) # df_source[e].str.extract(r'/(.*)}') + df_source[e] = np.where(df_source[e] == "[KTR]", df_source["Kostenträger_Ebene"], df_source[e]) # df_all[df_all['Ebene1'] == ] # print(df_source.head()) df_source["Konto_neu"] = ( df_source["Marke"] + "-" + df_source["Standort"] + "-" + df_source["Konto_Nr"] + "-" + df_source["Kostenstelle"] + "-" + df_source["Absatzkanal"] + "-" + df_source["Kostenträger"] + " - " + df_source["Konto_Bezeichnung"] ) df_source["Ebene1_empty"] = df_source["Ebene1"].isna() # , df_source['Ebene1'].map(lambda x: x == '')) df_source["Konto_neu"] = np.where(df_source["Ebene1_empty"], "keine Zuordnung", df_source["Konto_neu"]) df_source["Ebene1"] = np.where(df_source["Ebene1_empty"], "keine Zuordnung", df_source["Ebene1"]) df_source["Konto_Gruppe"] = df_source["Konto_Nr"] + " - " + df_source["Konto_Bezeichnung"] df_source["Konto_Gruppe"] = np.where(df_source["Ebene1_empty"], "keine Zuordnung", df_source["Konto_Gruppe"]) df_source["Konto_Gesamt"] = df_source["Konto_Nr_Gesamt"] + " - " + df_source["Konto_Bezeichnung"] df_source["Konto_Händler"] = df_source["Konto_Nr_Händler_kurz"] + " - " + df_source["Konto_Bezeichnung_Händler"] df_source["Konto_mit_KST_Händler"] = ( df_source["Konto_Nr_Händler_mit_KST"] + " - " + df_source["Konto_Bezeichnung_Händler"] ) df_amount = df_source[df_source["Ebene1"] == "Umsatzerlöse"].reset_index() df_amount["Ebene1"] = "verkaufte Stückzahlen" df_amount["Ebene72"] = "verkaufte Stückzahlen" df_amount["Konto_neu"] = "STK " + df_amount["Konto_neu"] df_amount["Konto_Nr_Händler"] = df_amount["Konto_Nr_Händler"] + "_STK" df_amount["Konto_Gruppe"] = "STK " + df_amount["Konto_Gruppe"] df_amount["Konto_Gesamt"] = "STK " + df_amount["Konto_Gesamt"] df_amount["Konto_Händler"] = "STK " + df_amount["Konto_Händler"] df_amount["Konto_mit_KST_Händler"] = "STK " + df_amount["Konto_mit_KST_Händler"] df_source = pd.concat([df_source, df_amount]) if "Ebene81" in df_source.columns: df_source["Ebene91"] = df_source["Ebene81"] df_source["Ebene92"] = df_source["Ebene82"] df_source["Ebene93"] = df_source["Ebene83"] df_source["Ebene94"] = df_source["Ebene84"] df_source["Ebene95"] = df_source["Ebene85"] df_source["Ebene96"] = df_source["Ebene86"] df_source["Ebene97"] = df_source["Ebene87"] df_source["Ebene98"] = df_source["Ebene88"] df_source["Ebene99"] = df_source["Ebene89"] df_source["Ebene100"] = df_source["Ebene90"] else: df_source["Ebene91"] = "" df_source["Ebene92"] = "" df_source["Ebene93"] = "" df_source["Ebene94"] = "" df_source["Ebene95"] = "" df_source["Ebene96"] = "" df_source["Ebene97"] = "" df_source["Ebene98"] = "" df_source["Ebene99"] = "" df_source["Ebene100"] = "" df_source["GuV"] = df_source["Ebene71"] == "GuV" df_source["Ebene81"] = np.where(df_source["GuV"], df_source["Ebene72"], "Bilanz") df_source["Ebene82"] = np.where(df_source["GuV"], df_source["Ebene73"], "") df_source["Ebene83"] = np.where(df_source["GuV"], df_source["Ebene74"], "") df_source["Ebene84"] = np.where(df_source["GuV"], df_source["Ebene75"], "") df_source["Ebene85"] = np.where(df_source["GuV"], df_source["Ebene76"], "") df_source["Ebene86"] = np.where(df_source["GuV"], df_source["Ebene77"], "") df_source["Ebene87"] = np.where(df_source["GuV"], df_source["Ebene78"], "") df_source["Ebene88"] = np.where(df_source["GuV"], df_source["Ebene79"], "") df_source["Ebene89"] = np.where(df_source["GuV"], df_source["Ebene80"], "") df_source["Ebene90"] = "" df_source["Ebene71"] = np.where(df_source["GuV"], "GuV", df_source["Ebene72"]) df_source["Ebene72"] = np.where(df_source["GuV"], "", df_source["Ebene73"]) df_source["Ebene73"] = np.where(df_source["GuV"], "", df_source["Ebene74"]) df_source["Ebene74"] = np.where(df_source["GuV"], "", df_source["Ebene75"]) df_source["Ebene75"] = np.where(df_source["GuV"], "", df_source["Ebene76"]) df_source["Ebene76"] = np.where(df_source["GuV"], "", df_source["Ebene77"]) df_source["Ebene77"] = np.where(df_source["GuV"], "", df_source["Ebene78"]) df_source["Ebene78"] = np.where(df_source["GuV"], "", df_source["Ebene79"]) df_source["Ebene79"] = np.where(df_source["GuV"], "", df_source["Ebene80"]) df_source["Ebene80"] = "" df_source["Susa"] = df_source["Konto_Gruppe"].str.slice(stop=1) df_source["Konto_KST"] = "" df_source["GuV_Bilanz"] = df_source["Konto_Art"] from_label = ["Konto_neu", "Konto_Nr_Händler", "Konto_Nr_Händler_kurz"] to_label = ["Konto", "Acct_Nr", "Konto_Nr_Händler"] df_source = df_source.rename(columns=dict(zip(from_label, to_label))) df_source = df_source[ [ "Konto", "Acct_Nr", "Konto_Bezeichnung", "GuV_Bilanz", "Konto_KST", "Konto_STK", "Konto_1", "Konto_2", "Konto_3", "Konto_4", "Konto_5", ] + get_default_cols(0) + get_default_cols(7) + get_default_cols(8) + get_default_cols(9) + [ "Konto_Gruppe", "Konto_Nr_Gesamt", "Konto_Gesamt", "Susa", "Konto_Nr_Händler", "Konto_Händler", "Konto_mit_KST_Händler", ] ] df_source.to_csv( f"{self.config['path2']}/SKR51_Uebersetzung.csv", sep=";", encoding="latin-1", index=False, ) def skr51_vars(self): self.get_structure_and_tree() cols = get_default_cols(0) df_temp = pd.read_csv( f"{self.config['path']}/Export/Kostentraeger.csv", decimal=",", sep=";", encoding="latin-1", converters={i: str for i in range(0, 200)}, ) df_temp["value"] = df_temp["Ebene33"] df_temp["key"] = "[KTR]" df_temp = df_temp[df_temp["value"].str.contains(" - ")] df_list = [df_temp[["key", "value"]]] for s, entries in self.json_result["flat"].items(): df = pd.DataFrame([x["id"].split(";") for x in entries], columns=cols) df["key"] = df[cols[0]].apply(lambda x: "{" + s + "/" + x + "}") df["value"] = df[cols[1]] df_list.append(df[["key", "value"]]) df = pd.concat(df_list) df_vars = df[df["value"] != ""] # df_vars.to_csv(f"{self.config['path2']}/SKR51_Variablen2.csv", sep=';', encoding='latin-1', index=False) df_main = pd.DataFrame( [x["id"].split(";") for x in self.json_result["flat"]["SKR51"]], columns=cols, ) df_main["value"] = "" for c in cols: df_main = df_main.merge(df_vars, how="left", left_on=c, right_on="key", suffixes=(None, "_" + c)) df_main[c] = np.where(df_main["value_" + c].isna(), df_main[c], df_main["value_" + c]) df_amount = df_main[df_main["Ebene1"] == "Umsatzerlöse"].reset_index() df_amount["Ebene1"] = "verkaufte Stückzahlen" df_main = pd.concat([df_main, df_amount]) # from_label = cols to_label = cols # get_default_cols(9) # df_main = df_main.rename(columns=dict(zip(from_label, to_label))) df_main[to_label].to_csv( f"{self.config['path2']}/SKR51_Struktur.csv", sep=";", encoding="latin-1", index_label="Sortierung", ) def dresen(): struct = GCStruct("c:/projekte/GCHRStruct_Hyundai_Export") struct.get_structure_and_tree() struct.export_structure_and_tree() struct.export() def reisacher(): base_dir = "X:/Robert/Planung Reisacher/GCStruct_neue_Struktur_Planung" if not Path(base_dir).exists(): base_dir = "/media/fileserver1/austausch/Robert/Planung Reisacher/GCStruct_neue_Struktur_Planung" struct = GCStruct(base_dir) struct.get_structure_and_tree() struct.export_structure_and_tree() # json.dump(res['flat'], open(f"{self.config['path2']}/{self.config['output']}", 'w'), indent=2) if __name__ == "__main__": # struct = GCStruct('c:/projekte/gcstruct_dresen') # struct = GCStruct('c:/projekte/python/gcstruct') # struct = GCStruct('c:/projekte/python/gcstruct_reisacher_planung') reisacher() # dresen()