consolidator

1
import glob
2
import ntpath
3
import pandas as pd
4

5
def vm_handler(row:pd.Series)->pd.Series:
6
    try:
7
        row["Вид материала в SAP ERP"] = str(row["Вид материала в SAP ERP"])[0:4]
8
    except Exception as exp:
9
        row["Полное наименование материала"]=f"{exp}"
10
    finally:
11
        return row
12

13
if __name__=="__main__":
14
    try:
15
        pattern="D:\\work\\Укрупнение\\current\\data\\2024*\\*.xlsx"
16
        dump_dir="D:\\work\\Укрупнение\\current\\debug_data\\"
17
        exclude_pattern="нение_"
18
        neuro_columns=["ID в SAP ERP VMZ", "Вид материала в SAP ERP","Полное наименование материала"]
19
        files=[f for f in glob.glob(pattern) if exclude_pattern not in f] if exclude_pattern is not None else [f for f in glob.glob(pattern)]
20
        data={}
21
        list_data=[]
22
        for f in files:
23
            short_name = ntpath.basename(f)
24
            print(f"{f} -> {short_name}")
25
            xl = pd.ExcelFile(f)
26
            for sheet_name in xl.sheet_names:
27
                df=xl.parse(sheet_name=sheet_name)
28
                key = f"{dump_dir}[{short_name.replace('.xlsx','')}][{sheet_name}].xlsx"
29
                # print(f"\t{key} -> {df.empty}")
30
                if not df.empty:
31
                    if key not in data.keys():
32
                        # data[key]=df
33
                        df=df[neuro_columns] #.apply(vm_handler,axis=1)
34
                        list_data.append(df)
35
                    # else:
36
                    #     data[key]=pd.concat([data[key],df])
37
        # for key in data.keys():
38
        #     df:pd.DataFrame = data[key]
39
        #     df.to_excel(key,index=False)
40
        #     print(f"{key} : {df.shape} : done") 
41
        result = pd.concat(list_data)
42
        result = result.drop_duplicates()
43
        result.to_excel(f"{dump_dir}neuro_dump.xlsx",index=False)
44
        print(f"{result.shape} -> done")   
45
    except Exception as exp:
46
        print(f"error: {exp}")