import pandas as pd
import re

def remove_icode(df):
    df['code'] = df['icode'].replace({'i921.':'', 'i1388.':'','i1055.':'', 'i1222.':''}, regex=True)
    return df

def read_table():
    path = 'C:\\Users\\MarkvanKruistum\\Kurtosis\\Sharepoint - Vensters\\Vensters voor Gemeenten\\Data\\export\\questions\\calc_cols.xlsx'
    df = pd.read_excel(path)
    return df

def get_regex_groups(df):
    pattern_id = r'(?:i921|i1388|i1055|i1222)\.(\d+)'
    pattern_full = r'((?:i921|i1388|i1055|i1222)\.\d+)'
    rg_id = re.compile(pattern_id)
    rg_full = re.compile(pattern_full)
    df['groups_id'] = df['som'].apply(lambda x : rg_id.findall(x))
    df['groups_full'] = df['som'].apply(lambda x : rg_full.findall(x))
    return df

def create_groups_df(list):
    list_new = []
    for item in list:
        list_new.append("df['" + str(item) + "']")
    return list_new

def main():
    df = read_table()
    df = get_regex_groups(df)
    df['groups_id_df'] = df['groups_id'].apply(lambda x: create_groups_df(x))
    df['dict'] = df.apply(lambda x:dict(zip(x['groups_full'], x['groups_id_df'])), axis=1)

    new_dict = {}
    for d in df['dict'].values:
        new_dict.update(d)

    new_dict = {k: v for k, v in sorted(new_dict.items(), key=lambda item: item[1], reverse = True)}
    df['som_df'] = df['som'].replace(new_dict, regex=True)
    df = remove_icode(df)
    df = df[['icode', 'code', 'som', 'som_df']]
    df = df.drop_duplicates(subset='code', ignore_index=True)
    df.to_csv('df_calc_cols.csv', index=False, sep=';')

if __name__ == '__main__':
    main()