import data_read_write as drw import pandas as pd import numpy as np import requests import json import traceback import os def send_to_teams(message): payload = { "text": message } headers = { "Content-Type": "application/json" } response = requests.post("https://kurtosisned.webhook.office.com/webhookb2/37df07de-7326-45ef-9c5f-71050ee19665@c1ec2c9f-59b3-4adb-a6ec-6f65890df339/IncomingWebhook/8feeeb6def464b52bd46c1c6ea435850/262b4721-3ae4-4942-aa46-b0a0da293625", data=json.dumps(payload), headers=headers) if response.status_code == 200: print("Bericht succesvol verstuurd naar Teams.") else: print("Er is een fout opgetreden bij het versturen van het bericht naar Teams.") print("Statuscode:", response.status_code) print("Response:", response.text) def calc_cols(df): df['96440'] = (df['96433']+df['15744'])/2 # if 35699 is empty, replace it with a zero (to prevent null values in columns that use 35699) df['15745'] = ((df['96433']/df['15734'])*1000) + df['35699'].combine_first(pd.Series(0, index=df.index, name='zeroes')) # 2020+ uses a different calculation than 2019 and below df.loc[df['Peiljaar'] == '1-1-2020', '15745'] = ((df['15744']/df['15734'])*1000) + df['35699'].combine_first(pd.Series(0, index=df.index, name='zeroes')) # 2017+ uses a different calculation than 2016 df['35702'] = ((df['15780']- df['15805'])/ df['15805']) # so 2016 has to be changed df.loc[df['Peiljaar'] == '1-1-2016', '35702'] = ((df['15744'] - df['15750'])/ df['15750']) df['96441'] = (df['15771']+df['15772'])/2 df['96488'] = ((df['96441']/df['15734'])*1000)+df['35699'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['35701'] = df['15772']/df['15744'] df['96444'] = (df['15779']+df['15780'])/2 df['15781'] = df['15780']/df['15772'] df['96445'] = df['15772']/df['15780'] df['15763'] = (df['15750'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15752'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15753'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15754'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15755'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15756'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15757'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15758'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15759'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15760'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15762'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['24293'].combine_first(pd.Series(0, index=df.index, name='zeroes')))-df['15751'].combine_first(pd.Series(0, index=df.index, name='zeroes'))-df['17196'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['15764'] = (df['15763']/df['15744']) df['15767'] = df['15752']+df['15753']+df['15754']+df['15755']+df['15756']+df['15758']+df['15759']+df['15762']+df['24293'] df.loc[df['Peiljaar'] == '1-1-2020', '15767'] = df['15752']+df['15753']+df['15754']+df['15755']+df['15756']+df['15758']+df['15759']+df['24293'] df['15768'] = (df['15767']/df['15744'])+(df['35700'].combine_first(pd.Series(0, index=df.index, name='zeroes'))/100) df['17239'] = df['15773']+df['15775'] df['15776'] = ((df['15775'])/df['15772'])*100 df['99861'] = (df['15775'])/(df['15773']+df['15775']+df['15777']) df['99860'] = ((df['15777'])/(df['15773']+df['15775']+df['15777']))*100 df['15778'] = ((df['15775'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15777'].combine_first(pd.Series(0, index=df.index, name='zeroes')))/(df['15773'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15775'].combine_first(pd.Series(0, index=df.index, name='zeroes'))+df['15777'].combine_first(pd.Series(0, index=df.index, name='zeroes')))) df['35806'] = df['35799']+df['35800']+df['35801']+df['35802']+df['35804']+df['35805'] df['15802'] = df['15800']/((df['15779']+df['15780'])/2) df['15803'] = df['15801']/((df['15779']+df['15780'])/2) df['24294'] = (df['15801']/df['15800'])*100 df['96448'] = df['15801']/df['15859'] df['15807'] = (df['15806']/df['15805'])*100 df['15810'] = (df['15809']/df['15780'])*100 df['15812'] = df['15812'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['15813'] = df['15813'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['15814'] = df['15814'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['15815'] = df['15815'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['15816'] = df['15816'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['17241'] = df['15812']+df['15813']+df['15814']+df['15815']+df['15816'] df['15817'] = (df['15812']+df['15813'])/df['15780'] df['15818'] = df['15816']/df['15780']*100 df['98137'] = df['15820']-df['15822'] # 2020- uses a different calculation than 2020 df['15829'] = (df['15820']/df['15779']) df['15830'] = (df['15821']/df['15779']) df['15831'] = (df['15822']/df['15779']) # so 2020 has to be changed df.loc[df['Peiljaar'] == '1-1-2020', '15829'] = (df['15820']/df['15780']) df.loc[df['Peiljaar'] == '1-1-2020', '15830'] = (df['15821']/df['15780']) df.loc[df['Peiljaar'] == '1-1-2020', '15831'] = (df['15822']/df['15780']) df['15832'] = (df['15823']/df['15779'])*100 df['15834'] = (df['15825']/df['15780'])*100 df['15835'] = (df['15826']/df['15780'])*100 df['15837'] = (df['15828']/df['15779'])*100 df['15848'] = df['15847']/((df['96433']+df['15744'])/2) df['15849'] = df['15847']/df['15734'] df['15861'] = df['15859']/df['15772'] df['15866'] = df['15864']/((df['96433']+df['15744'])/2) df['59153'] = df['15864']/df['15734'] df['15878'] = (df['15877']/df['24412'])*100 df['56065'] = ((df['24441']+(df['56056']+df['56057']+ df['56058']+df['56059']+df['56060']+df['56061']+df['56062']+df['56063']))/(df['15859']+(df['56056']+df['56057']+ df['56058']+df['56059']+df['56060']+df['56061']+df['56062']+df['56063'])))*100 df['15882'] = df['15880']/(df['15859']+df['15880']) df['15883'] = (df['15881']/(df['24441']+df['15881']))*100 df['15886'] = df['15885']/100 df.loc[df['Peiljaar'] == '1-1-2020', '15886'] = (df['15885']) df['36766'] = df['36762']+df['36763']+df['36764']+df['36765'] df['36769'] = (df['36762']*1+df['36763']*2+df['36764']*3+df['36765']*4)/100 df['15892'] = df['96461']+df['96460'] df['15893'] = df['15892']/((df['96433']+df['15744'])/2) df['24299'] = df['17228']/df['15734'] df['96467'] = df['96465']/df['15734'] df['35697'] = ((df['24441']+(df['39453']+df['39454']+ df['39455']+df['39456']+df['39457']+df['39458']+df['39459']+df['39460']+df['39461']))/(df['15859']+(df['39453']+df['39454']+ df['39455']+df['39456']+df['39457']+df['39458']+df['39459']+df['39460']+df['39461'])))*100 df['97948'] = ((df['15752']+df['15753']+df['15754']+df['15755']+df['15756']+df['15758']+df['15759'])* df['97941'])+((df['39453']+df['39454']+df['39455']+df['39456']+df['39457']+df['39458']+df['39460'])/ df['96440']) + df['15866'] df['96711'] = df['56071']/df['15744'] df['96712'] = df['56072']/df['15744'] df['96713'] = df['56076']/df['15744'] df['15912'] = df['15908']+df['15910'] df['15913'] = (df['15908'])/df['15744'] df.loc[df['Peiljaar'] == '1-1-2020', '15913'] = (df['15909'])/df['15744'] df.loc[df['Peiljaar'] == '1-1-2019', '15913'] = (df['15909'])/df['15744'] df['15914'] = (df['15908']/df['15912'])*100 df['15915'] = (df['15910']/df['15912'])*100 df['24317'] = df['24316']/df['15908'] df['15923'] = (df['15921']/df['15909'])*1000 df['15927'] = df['15925']/df['15909'] df['15924'] = df['15922']/df['15909'] df['15928'] = df['15926']/df['15909'] df['24301'] = df['24300']/df['15909'] df['24302'] = df['15924']+df['15928']+df['24301'] df['39349'] = (df['15922']/df['15921'])/1000 df['39350'] = df['15926']/df['15925'] df['15870'] = df['15869']/df['15744'] df['24296'] = df['24295']/df['15744'] df['15938'] = (df['15937']/df['15936']) df.loc[df['Peiljaar'] == '1-1-2020', '15938'] = (df['15938']) df['15941'] = (df['15940']/df['15936'])*100 df['15946'] = (df['15944']/df['15943'])*100 df['35797'] = (df['35796']/df['15943'])*100 df['17263'] = (df['24346']+df['24347']+df['24348']+df['24349']+df['24350']+df['24351']+df['24352']+df['24353']+df['24354']+df['24355']+df['24356']+df['24357']+df['24358']+df['24359']+df['24360']+df['24361']+df['24362']+df['24363']+df['24364']+df['24365']+df['24366']+df['24367']+df['24368']+df['24369']+df['24370']+df['24371']+df['24372'])/27 df['15965'] = df['15949']/((df['15779']+df['15780'])/2) df['96491'] = df['15750']-df['15751'] df['96492'] = df['15760']-df['17196'] df['96493'] = (df['96491']/df['15744']) df['96494'] = (df['96492']/df['15744']) df['96495'] = (df['15752']/df['15744']) df['96496'] = (df['15753']/df['15744']) df['96497'] = (df['15754']/df['15744']) df['96498'] = (df['15755']/df['15744']) df['96499'] = (df['15756']/df['15744']) df['96500'] = (df['15758']/df['15744']) df['96501'] = (df['24293']/df['15744']) df['96502'] = (df['15759']/df['15744']) df['96503'] = (df['15762']/df['15744']) df['104008'] = df['15813']+df['15814'] df['103115'] = df['103109']+df['103110']+df['103111']+df['103112']+df['103113']+df['103114'] df['104861'] = df['15864']-(df['103109']+df['103110']+df['103111']+df['103112']+df['103114']+df['103113']) # if 103108 is empty, replace it with a zero (to prevent null values in columns that use 103108) df['103116'] = df['15864']/(df['15780']+df['103108'].combine_first(pd.Series(0, index=df.index, name='zeroes'))) df['103117'] = df['15864']/(df['15859']+df['15880'])*100 df['103105'] = df['103101']+df['103102']+df['103103']+df['103104'] df['103106'] = df['103105']/df['15734'] df['104016'] = df['103911']/df['15734'] df['103913'] = df['103911']/((df['96433']+df['15744'])/2) df['104015'] = (df['103911']/(df['15880']+df['15859']))*100 df['104790'] = (df['35696']/df['35694'])*100 df['104789'] = (df['104788']/df['104787'])*100 df['104014'] = (df['15757']/df['15744'])*100 df['104007'] = df['15813']+df['15814'] df['104075'] = (df['104074']/df['15744'])*100 df['104010'] = df['103101']/df['15734'] df['104011'] = df['103102']/df['15734'] df['104012'] = df['103103']/df['15734'] df['104013'] = df['103104']/df['15734'] df['104020'] = df['15780']+df['103108'].combine_first(pd.Series(0, index=df.index, name='zeroes')) df['104021'] = df['103109']/df['104020'] df['104022'] = df['103110']/df['104020'] df['104023'] = df['103111']/df['104020'] df['104024'] = df['103112']/df['104020'] df['104025'] = df['103114']/df['104020'] df['104026'] = df['103113']/df['104020'] df['104028'] = df['103109']/df['15734'] df['104029'] = df['103110']/df['15734'] df['104030'] = df['103111']/df['15734'] df['104031'] = df['103112']/df['15734'] df['104032'] = df['103114']/df['15734'] df['104033'] = df['103113']/df['15734'] df['104036'] = (df['96449']/df['15772'])*100 df['104039'] = (df['96450']/df['15772'])*100 df['104040'] = (df['96451']/df['15772'])*100 df['104041'] = (df['96452']/df['15772'])*100 df['104042'] = (df['96453']/df['15772'])*100 df['104045'] = (df['96454']/df['15772'])*100 df['104049'] = (df['15825']/df['15780'])*100 df['104050'] = (df['15826']/df['15780'])*100 df['104051'] = ((df['15780']-df['15825']-df['15826'])/df['15780'])*100 df['104053'] = df['56056']+df['56057']+df['56058']+df['56059']+df['56060']+df['56061']+df['56062']+df['56063'] df['104058'] = (df['24441']/(df['15859']+df['104053']))*100 df['104059'] = (df['104053']/(df['15859']+df['104053']))*100 df['104054'] = df['39453']+df['39454']+df['39455']+df['39456']+df['39457']+df['39459']+df['39458']+df['39460']+df['39461'] df['104078'] = (df['15812']/df['17241'])*100 df['104079'] = (df['15813']/df['17241'])*100 df['104080'] = (df['15814']/df['17241'])*100 df['104081'] = (df['15815']/df['17241'])*100 df['104082'] = (df['15816']/df['17241'])*100 df['104084'] = (df['103108'].combine_first(pd.Series(0, index=df.index, name='zeroes'))/df['104020'])*100 df['104085'] = (df['15780']/df['104020'])*100 df['104086'] = df['15869']/df['104020'] df['104862'] = df['104861']/df['104020'] df['104863'] = df['104861']/df['15734'] df['157213'] = (df['157210']/df['15780'])*100 df['157524'] = ((df['15864']+df['15880']+df['15859']+df['24316']+df['15801'])-(df['103109']+df['103110'])) df['157339'] = df['157329']/df['96441'] df['157367'] = df['15829'] df['157369'] = df['15831'] df['157370'] = df['15776'] df['157371'] = df['15882'] df['157372'] = df['15795'] df['158226'] = df['96495']+df['96496']+df['96497']+df['96498']+df['96499']+df['96500']+df['96501']+df['96502']+df['96503'] df['157642'] = df['104861']/df['104020'] df['157643'] = df['104861']/df['15734'] df['157717'] = df['103095']+df['103096']+df['103097']+df['103098']+df['35952']+df['35950']+df['35951']+df['35953']+df['35955']+df['35956']+df['35957']+df['35954']+df['35958']+df['35962']+df['35964']+df['35965']+df['35966'] df['157713'] = df['103095']+df['103096']+df['103097']+df['103098']+df['35952']+df['35950']+df['35951']+df['35953']+df['35955']+df['35956']+df['35957']+df['35954']+df['35958'] df['157715'] = 13-df['157713'] df['157714'] = df['35962']+df['35964']+df['35965']+df['35966'] df['157716'] = 4-df['157714'] df['157676'] = (df['157534']+df['157535']+df['157537']+df['157536'])*25 df['157681'] = (df['157677']+df['157678']+df['157679']+df['157680'])*25 df['157686'] = (df['157682']+df['157683']+df['157684']+df['157685']+df['157687'])*20 df['157718'] = (((df['157700']+df['157701'])*50)+((df['157709']+df['157703'])/2))/2 df['160651'] = (df['96449']+df['96450']+df['96451']+df['96452']+df['96453']+df['96454']) df.loc[df['Peiljaar'] == '1-1-2020', '160651'] = (df['96450']+df['96451']+df['96452']+df['96453']+df['96454']) df['161594'] = (df['15861']*df['15767']) df['161592'] = df['39453']+df['39454']+df['39455']+df['39456']+df['39457']+df['39459']+df['39458']+df['39460'] df['161593'] = ((df['161594']+df['161592'])/(df['15859']+df['161592']))*100 df['160593'] = df['15949']/((df['15779']+df['15780'])/2) df['103124'] = df['103124']/100 df['163202'] = df['163202']/100 df['163204'] = df['163204']/100 df['163205'] = df['163205']/100 df['163206'] = df['163206']/100 df['163207'] = df['163207']/100 return df def main(): #maak connectie met de deb129704n2_prd-gemeenten-dashboard database config_path = 'config.ini' engine_vvb = drw.create_db_connection(config_path, 'gemeenten') # Oude jaren 2016-2020 ------------------------------------------------------------------------------------------------------------------ #haal de tabel vvb_uncalculated op met alle data van 2016-2020 df_uncalculated = drw.read_from_db('vvb_uncalculated', engine_vvb) #Maak alle berekende kolommen aam df_calculated = calc_cols(df_uncalculated) df_calculated = df_calculated.replace([np.inf, -np.inf], np.nan) #schrijf de data weg naar de tabel vvb_caculated_exc_21 drw.write_to_db(engine_vvb, 'vvb_calculated_exc_21', df_calculated, append=False) # Nieuwe jaren 2021 - 2022 ------------------------------------------------------------------------------------------------------------- # # Eenmalig appenden van nieuwe deelnames per jaar # deelnames_2022 = df_vvb_22[["ID Organisatie", "Naam organisatie", "Peiljaar"]].copy() # deelnames_2022["doet_mee"] = 1 # deelnames_2022['Peiljaar'] = pd.to_datetime(deelnames_2022['Peiljaar']) # deelnames_2022 = deelnames_2022.rename({'ID Organisatie': 'u_id', 'Naam organisatie':'naam_organisatie', 'Peiljaar':'jaar'}, axis=1) # deelnames_2022 # drw.write_to_db(engine_gemeenten, 'deelnames_per_jaar', deelnames_2022, append=True) df_vvb_21 = drw.read_from_db('data_pbi', engine_vvb) df_vvb_21 = df_vvb_21.replace(to_replace={r'^-$':np.nan, r'^nvt$':np.nan, r'^NB$':np.nan, r'10-20':15, r'€':np.nan }, regex=True) df_vvb_21['Peiljaar'] = '2021-01-01' df_vvb_21['Peiljaar'] = pd.to_datetime(df_vvb_21['Peiljaar']) df_vvb_21 = drw.remove_icode(df_vvb_21, 2021) engine_vvb_2023 = drw.create_db_connection(config_path, 'gemeenten_2023') df_vvb_22 = drw.read_from_db('data_pbi', engine_vvb_2023) df_vvb_22 = df_vvb_22.replace(to_replace={r'^-$':np.nan, r'^nvt$':np.nan, r'^NB$':np.nan, r'10-20':15, r'€':np.nan }, regex=True) lege_rij = pd.DataFrame({'u_id': [10000], 'Organisatienaam': ['Leeg']}) df_vvb_22 = pd.concat([df_vvb_22, lege_rij], ignore_index=True) df_vvb_22['Peiljaar'] = '2022-01-01' df_vvb_22['Peiljaar'] = pd.to_datetime(df_vvb_22['Peiljaar']) df_vvb_22.at[df_vvb_22[df_vvb_22['Organisatienaam'] == 'SamenTwente'].index[0], 'u_id'] = 99999 df_vvb_22 = drw.remove_icode(df_vvb_22, 2022) df_vvb_2122 = pd.concat([df_vvb_21, df_vvb_22]) # Convert numeric-like strings to actual numeric types for col in df_vvb_2122.columns: if df_vvb_2122[col].dtype == 'object': try: df_vvb_2122[col] = pd.to_numeric(df_vvb_2122[col].str.replace(',', '.'), errors='raise') except ValueError as e: print(f"Error converting column '{col}': {e}") df_vvb_2122['Organisatienaam'] = df_vvb_2122['Organisatienaam'].str.replace('Gemeente ', '') df_vvb_2122 = df_vvb_2122.rename({'u_id': 'ID Organisatie', 'Organisatienaam':'Naam organisatie'}, axis=1) columns_to_divide = [ '35701', '15882', '15764', '15768', '163202', '163204', '163205', '163206', '163207', '15886', '15795', '15778', '15829', '15831', '96448', '103124', '15938' ] df_vvb_2122[columns_to_divide] = df_vvb_2122[columns_to_divide]/100 df_vvb_2122[['103095', '103096', '103098']] = df_vvb_2122[['103095', '103096', '103098']].replace({'Volledig of grotendeels in eigen beheer': 0, 'Ongeveer evenveel zelf als uitbesteed': 0.5, 'Volledig of grotendeels uitbesteed': 1}) #Bereken de calculated kolomen opnieuw voor alle jaren na 2020 Uncalculated_colomns = ["ID Organisatie", "Naam organisatie", "Peiljaar", "17234", "15734", "23706", "163208", "23707", "23708", "35953", "103095", "35955", "35956", "35957", "35954", "35958", "103096", "103098", "35952", "35951", "15744", "15750", "15760", "15757", "15772", "15780", "15805", "15751", "17196", "15752", "15753", "15754", "15755", "15756", "15758", "15759", "24293", "163209", "15775", "15777", "103108", "96450", "96451", "96452", "96453", "96454", "15795", "15796", "15801", "15809", "15813", "15814", "15815", "15816", "15820", "15822", "15825", "15826", "163217", "163225", "163211", "15847", "15859", "24316", "15864", "103109", "103110", "103111", "103112", "103114", "103113", "103850", "163213", "163214", "163223", "163224", "15880", "103105", "103101", "103102", "103103", "103104", "15886", "163202", "163204", "163205", "163206", "163207", "15909", "15869", "15938", "103123", "103124", "163215", "163226", "163227", "56083", "56085", "56091", "161497", "96482", "96483", "96484", "96485", "96486","163230", "163231", "163232", "163233", "163234", "163235", "163236", "163237", "163238", "163239", "163240", "163241", "163242", "163243", "163244", "163245", "163246"] df = df_vvb_2122.loc[:, Uncalculated_colomns] def clean_and_convert_to_float(df, column_names): for col in column_names: if df[col].dtype == 'object': df[col] = df[col].str.replace(' ', '') df[col] = df[col].str.replace(',', '.') df[col] = df[col].str.replace('[^0-9.]', '', regex=True) df[col] = df[col].apply(lambda x: float(x) if x else float('nan')) column_names_to_clean = ['15801', '15859', '15880', '96485', '96486'] clean_and_convert_to_float(df, column_names_to_clean) #Lijst met berekende kollomen df['15745'] = ((df['15744'] / df['15734']) * 1000) df['35701'] = df['15772'] / df['15744'] df['35702'] = ((df['15780'] - df['15805']) / df['15805']) df['15767'] = df['15752'] + df['15753'] + df['15754'] + df['15755'] + df['15756'] + df['15758'] + df['15759'] + df['24293'] df['15763'] = ( df['15750'] + df['15752'] + df['15753'] + df['15754'] + df['15755'] + df['15756'] + df['15757'] + df['15758'] + df['15759'] + df['15760'] + df['24293']) - df['15751'] - df['17196'] df['15764'] = (df['15763'] / df['15744']) df['15768'] = (df['15767'] / df['15744']) df['163218'] = (df['163209'] / df['15744']) * 100 df['15776'] = ((df['15775']) / df['15772']) * 100 df['15778'] = (df['15775'] + df['15777']) / (df["15772"] + df['15777']) df['104020'] = df['15780'] + df['103108'] df['160651'] = df['96450'] + df['96451'] + df['96452'] + df['96453'] + df['96454'] df['96448'] = df['15801'] / df['15859'] df['163221'] = df['15809'] / df['15780'] * 100 df['17241'] = df['15813'] + df['15814'] + df['15815'] + df['15816'] df['15829'] = (df['15820'] / df['15780']) df['15831'] = (df['15822'] / df['15780']) df['157524'] = (df['15864'].fillna(0) + df['15880'].fillna(0) + df['15859'].fillna(0) + df['24316'].fillna(0) + df['15801'].fillna(0)) - (df['103109'].fillna(0) + df['103110'].fillna(0)) df['15849'] = df['15847'] / df['15734'] df['163212'] = df['15847'] / df['163211'] * 100 df['15861'] = df['15859'] / df['15772'] df['163229'] = df['24316'] / df['15847'] * 100 df['103115'] = df['103109'] + df['103110'] + df['103111'] + df['103112'] + df['103113'] + df['103114'] df['104861'] = df['15864']-(df['103109']+df['103110']+df['103111']+df['103112']+df['103114']+df['103113']) df['103116'] = df['15864']/(df['15780']+df['103108']) df['59153'] = df['15864']/df['15734'] df['163219'] = df['103850'] / df['15847'] * 100 df['163220'] = df['15864'] / df['15847'] * 100 df['163223'] =((df["15763"] - df["15758"]) * df["15861"]) + df["163213"] df['163224'] = df['163223'] / df['15847'] * 100 df['15882'] = df['15880']/(df['15859']+df['15880']) df['163222'] = df['103105'] / df['163211'] * 100 df['103106'] = df['103105'] / df['15734'] df['15913'] = (df['15909'])/df['15744'] df['15870'] = df['15869']/df['15744'] df['104086'] = df['15869']/df['104020'] nieuw_df_1522 = pd.concat([df_calculated, df]) #creer mutatie formatie nieuw_df_1522['last_year_15744'] = nieuw_df_1522.groupby('Naam organisatie')['15744'].shift() nieuw_df_1522['mutatie_formatie'] = (nieuw_df_1522['15744'] - nieuw_df_1522['last_year_15744']) / nieuw_df_1522['last_year_15744'] nieuw_df_1522 = nieuw_df_1522.drop('last_year_15744', axis=1) drw.write_to_db(engine_vvb, 'vvb_recalculated_inc_22', nieuw_df_1522, append=False) if __name__ == "__main__": try: main() bericht = f"Het verversen van de data Venster voor Gemeenten is succesvol (File: runner_vvb_new.py)" except Exception as ex: traceback_entries = traceback.extract_tb(ex.__traceback__) bericht = f"Het verversen van de data Venster voor Gemeenten is niet succesvol:\n\n" for entry in traceback_entries: filename = os.path.basename(entry.filename) bericht += f"File: {filename}, Line: {entry.lineno}, Function: {entry.name}\n\n" #, Line Contents: {entry.line} send_to_teams(bericht)