import pandas as pd
import numpy as np


base_dir = 'V:\\Kunden\\Luchtenberg\\1 Umstellung SKR51\\GCStruct_Modell\\Kontenrahmen\\'
source_csv = base_dir + 'Kontenrahmen_Export.csv'
mul_csv = base_dir + 'Kontenrahmen_mul.csv'
result_csv = base_dir + 'Kontenrahmen_Vorlage.csv'

df_source = pd.read_csv(source_csv, decimal=',', sep=';', encoding='latin-1', converters={i: str for i in range(0, 200)})
df_source['Konto_Klasse'] = df_source['Konto_Nr'].str.slice(0, 1)
df_source['Konto_Art'] = np.where(df_source['Konto_Klasse'].isin(['4', '5', '7', '8']), '2', '1')
df_mul = pd.read_csv(mul_csv, decimal=',', sep=';', encoding='latin-1', converters={i: str for i in range(0, 200)})
df_join = df_source.merge(df_mul, how='inner', on=['Konto_Klasse'], suffixes=[None, '_other'])
df_join['Konto_Nr'] = df_join['Konto_Nr'] + '_' + df_join['Suffix']
df_join.drop(['Konto_Klasse', 'Suffix'], axis=1, inplace=True)
df_source.drop(['Konto_Klasse'], axis=1, inplace=True)
df_join = df_join.append(df_source).sort_index()
df_join.to_csv(result_csv, decimal=',', sep=';', encoding='latin-1', index=None)