<jupyter_start><jupyter_text>-------- This programming code is a raw version, an improved version will be uploaded soon  ---------<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt

import numpy as np
# Check which ISCO-codes are not in official document but are in the document of the Netherlands
def compare_df(df1,df2,compare_name):
    df_in = df1
    
    np1 = df1[compare_name].unique()

    np2 = df2[compare_name].unique()

    df_output = df_in.iloc[0:0]
    
    xyz = False
    number_missing = 0

    for i in np1:
        xyz = False
        for j in np2:
            if i == j:
                xyz = True
            
        if xyz == False:
            x = df_in[df_in[compare_name] == i]
            df_output = pd.concat([df_output,x])

    df_output = df_output.reset_index()
    df_output = df_output.drop(['index'],axis=1)
    return df_output
df_avg = pd.read_csv('avg_data.csv')
df_min = pd.read_csv('min_data.csv')
df_max = pd.read_csv('max_data.csv')
df_avg = df_avg.drop('Unnamed: 0', axis = 1)
df_min = df_min.drop('Unnamed: 0', axis = 1)
df_max = df_max.drop('Unnamed: 0', axis = 1)
df_avg = df_avg.rename(columns={"brc_beroep":'brc_code'})
df_min = df_min.rename(columns={"brc_beroep":'brc_code'})
df_max = df_max.rename(columns={"brc_beroep":'brc_code'})
df_avg = df_avg.drop('jobs_lost_low', axis = 1)
df_avg = df_avg.drop('jobs_lost_high', axis = 1)
df_avg = df_avg.rename(columns={"jobs_lost_avg":'jobs_lost_ET'})

df_min = df_min.drop('jobs_lost_avg', axis = 1)
df_min = df_min.drop('jobs_lost_high', axis = 1)
df_min = df_min.rename(columns={"jobs_lost_low":'jobs_lost_ET'})

df_max = df_max.drop('jobs_lost_low', axis = 1)
df_max = df_max.drop('jobs_lost_avg', axis = 1)
df_max = df_max.rename(columns={"jobs_lost_high":'jobs_lost_ET'})

df = df_avg
df.head()

df[df['brc_code'] == 711]
ham = df[df['jobs_lost_ET'] == 0]
ham.num_working.sum()
len(df)
len(df['brc_code'].unique())
df_occ = pd.read_excel('Keys_Occupation_data.xlsx')
df_occ['brc_code'] = df_occ['Title']
df_occ['brc_name'] = df_occ['Title']

#x = df_occ.iloc[0]['Title']
#x.split(' ', maxsplit = 1)

for i in range(0, len(df_occ)):
    x = df_occ.iloc[i]['Title']
    y = df_occ.iloc[i]['brc_code']

    a,b = x.split(" ", maxsplit = 1)
    
    df_occ['brc_code'].replace(y, value=a, inplace=True)


    
for i in range(0, len(df_occ)):
    x = df_occ.iloc[i]['brc_name']
    
    a,b = x.split(" ", maxsplit = 1)
    
    df_occ['brc_name'].replace(x, value =b, inplace=True)

df_occ = df_occ[['occ_code','brc_code', 'brc_name']]    

df_occ["brc_code"] = pd.to_numeric(df_occ["brc_code"])
df_occ.head()
compare_df(df_occ,df,'brc_code')
df_ed = pd.read_excel('Keys_Education_data.xlsx')
df_ed
df_pol = pd.read_csv('Policies_data.csv', sep = ';')
df_pol.head()
len(df_pol['occ_code'].unique())
df_pol.head()
df_pol[df_pol['occ_code'] == "A000163"].head()
df_pol[df_pol['occ_code'] == "A000238"].tail()
df_pol[df_pol['occ_code'] == "A000328"].tail()
df_occ[df_occ['brc_code'] == 111]
#df_pol[df_pol['occ_code'] == "A000230"]
expl1 = df_occ[['occ_code','brc_code']]

df1 = pd.merge(df_pol,expl1, on=['occ_code'],how ='inner')

df1.head()
df1[df1['brc_code'] == 1041].head()
print(df1['num_working'].sum())
len(df1['occ_code'].unique())
#Transition to one BRC code of formed database
df_ET = df_occ[['brc_code', 'brc_name']]
df_ET['num_working'] = 0
df_ET['jobs_lost_ET'] = 0
df_ET.head()
for i in range(0,len(df_ET)):
    #i = i - 1
    brc_code = df_occ.iloc[i]['brc_code']
    
    new_df = df[df['brc_code'] == brc_code]
    a = new_df['num_working'].sum()
    b = new_df['jobs_lost_ET'].sum()
    
    #x = df_ET.iloc[i]['num_working']
    #y = df_ET.iloc[i]['jobs_lost_ET']
    
    #df_ET['num_working'].replace(x, value=a, inplace=True)
    #df_ET['jobs_lost_ET'].replace(y, value=b, inplace=True)
    
    df_ET.loc[i,'num_working'] = a
    df_ET.loc[i,'jobs_lost_ET'] = b
    
    #i = i + 1
df_2 = df_ET
#df_2 = df_2[0:114]
df_2["brc_code"] = pd.to_numeric(df_2["brc_code"], downcast='integer')

pd.options.display.max_colwidth = 100

df_2
poi = df_2[df_2['brc_code'] == 111]
poi
kaas = df_2[df_2['jobs_lost_ET']== 0]
kaas.head()
kaas.num_working.sum()
expl7 = df_2[df_2['jobs_lost_ET']== 0]
expl7 = expl7[['brc_code']]
expl7 = expl7.reset_index(drop=True)
expl7
# In the origianl df_pol the overall categories are still incorporated therefor we lose this data. But we see
# that there are still 114 occupations in there 
expl2 = df_ed[['ed_code','education_level']]

df2 = pd.merge(df1,expl2,on=['ed_code'],how ='inner')

df2.head()
df3 = df2[['ID','brc_code','periods','education_level','num_working']]
df3.head()
# Percentage growht per brc_code
expl2003 = df3[df3['periods'] == ('2003JJ00')]

expl2018 = df3[df3['periods'] == ('2018JJ00')]
expl8 = expl7.copy()
expl8['jc_03'] = 0
expl8['jc_18'] = 0
expl8['job_decrease'] = 0
expl8['job_growth'] = 0
#expl8 = expl8.reset_index(drop=True)
expl8
for i in range(0,len(expl8)):
    brc_code = expl8.iloc[i]['brc_code']
    
    df1 = expl2003[expl2003['brc_code'] == brc_code]
    df2 = expl2018[expl2018['brc_code'] == brc_code]
    
    x = df1['num_working'].sum()
    y = df2['num_working'].sum()
    
    expl8.loc[i,'jc_03'] = x
    expl8.loc[i,'jc_18'] = y
    
    z = ((y-x)/15)
    
    v = z/x
    
    if (z>0): 
        expl8.loc[i,'job_growth'] = (x*v) * 12 # growth per year times 12 for the duration until 2030
    else:
        expl8.loc[i,'job_decrease'] = (-x*v) * 12


expl8.sum()
expl9 = expl8[['brc_code','job_decrease','job_growth']]
df12 = pd.merge(df_2,expl9,on=['brc_code'],how ='left')
df12 = df12.fillna(value = 0)
df12
df12['jobs_lost'] = df12['jobs_lost_ET'] + df12['job_decrease']
# Incorporate sectors
df_beroep = pd.read_excel("brc_sector.xlsx")
df_beroep = df_beroep.drop_duplicates()
df_beroep = df_beroep.reset_index()
df_beroep = df_beroep.drop(labels="index", axis = 1)
df_beroep = df_beroep.rename(columns={"brc_beroep":'brc_code'})
df_beroep.head()
df13 = pd.merge(df12,df_beroep, on=['brc_code'], how='inner')
df13 = df13[['brc_code','brc_sector','brc_name','num_working','job_growth','jobs_lost']]
df13.head()
df13.sum()
## Education level
expl22 = df3[df3['education_level']==0]
expl22.head()
expl22 = df3[df3['education_level']==0]

expl22['num_working'].sum()
expl22 = df3[df3['education_level']==1]

expl22['num_working'].sum()
expl22 = df3[df3['education_level']==2]

expl22['num_working'].sum()
expl22 = df3[df3['education_level']==3]

expl22['num_working'].sum()
# Check if there are brc code missing

compare_df(df, df3,'brc_code')
# reset all the education levels that are 0 to 1 since we assume education levels of 1 are reseached at least
df4 = df3
for i in range(0, len(df4)):
    x = df4.iloc[i]['education_level']
    
    if (x == 0):
        df4["education_level"].replace(x, value=1, inplace=True)
df4[df4['education_level']==0]
df4[df4['education_level']==2].head()
# Check per occupation what the education level is
expl3 = df4[df4['brc_code'] == 111]

X1 = expl3[expl3['education_level']==1]
X2 = expl3[expl3['education_level']==2]
X3 = expl3[expl3['education_level']==3]
print (X3)
Y1 = X1['num_working'].sum()
Y2 = X2['num_working'].sum()
Y3 = X3['num_working'].sum()
print(Y1,Y2,Y3)

if (Y2 > Y3 ) & ( Y2 > Y1):
    print ('hi')
else:
    print('no')
# df  with      brc_code and education level
expl4 = df_occ['brc_code']
expl4 = pd.DataFrame(expl4)
expl5 = expl4[0:0]

#expl5['education_level_1'] = 1
#expl5['education_level_2'] = 1
#expl5['education_level_3'] = 1
expl5['percentage_ed_1'] = 1
expl5['percentage_ed_2'] = 1
expl5['percentage_ed_3'] = 1

for i in range(0, len(expl4)):
    x = expl4.iloc[i]['brc_code']
    
    df = df4[df4['brc_code'] == x]
    
    X1 = df[df['education_level']==1]
    X2 = df[df['education_level']==2]
    X3 = df[df['education_level']==3]
    
    Y1 = X1['num_working'].sum()
    Y2 = X2['num_working'].sum()
    Y3 = X3['num_working'].sum()
    
    
    print(Y1,Y2,Y3) 


for i in range(0, len(expl4)):
    x = expl4.iloc[i]['brc_code']
    
    df = df4[df4['brc_code'] == x]
    
    X1 = df[df['education_level']==1]
    X2 = df[df['education_level']==2]
    X3 = df[df['education_level']==3]
    
    Y1 = X1['num_working'].sum()
    Y2 = X2['num_working'].sum()
    Y3 = X3['num_working'].sum()
    
    YY = Y1 + Y2 + Y3
    
    Z1 = Y1/YY
    Z2 = Y2/YY
    Z3 = Y3/YY
    
    expl5 = expl5.append({'brc_code':x,'percentage_ed_1': Z1, 'percentage_ed_2': Z2, 'percentage_ed_3': Z3}, ignore_index=True)
    
    expl5["brc_code"] = pd.to_numeric(expl5["brc_code"], downcast='integer')
    #expl5 = expl5.to_numeric('brc_code',downcast='integer') 
    #pd.to_numeric(s, downcast='integer')
    #df.append({'Rank': new[0],'Probability': new[1],'soc_code': new[2],'Occupation': new[3]}, ignore_index=True)
    
    #if (Y3 > Y2 ) & ( Y3 > Y1):
        #expl4[''] df4["education_level"].replace(x, value=1, inplace=True)
expl5.head()
expl5.sum()/114
df4 = pd.merge(df13,expl5,on=['brc_code'], how = 'inner')
df4
df4['job_growth1'] = df4['job_growth'] * df4['percentage_ed_1']
df4['job_growth2'] = df4['job_growth'] * df4['percentage_ed_2']
df4['job_growth3'] = df4['job_growth'] * df4['percentage_ed_3']
df4['jobs_lost1'] = df4['jobs_lost'] * df4['percentage_ed_1']
df4['jobs_lost2'] = df4['jobs_lost'] * df4['percentage_ed_2']
df4['jobs_lost3'] = df4['jobs_lost'] * df4['percentage_ed_3']
df5 = df4[['brc_code','brc_sector','brc_name','num_working','job_growth1','job_growth2','job_growth3','jobs_lost1','jobs_lost2','jobs_lost3']]
df5.tail()
df_class = pd.read_excel("class_label.xlsx")
df_class
df6 = df_class
df6['num_working'] = 0
df6['job_growth1'] = 0
df6['job_growth2'] = 0
df6['job_growth3'] = 0
df6['jobs_lost1'] = 0
df6['jobs_lost2'] = 0 
df6['jobs_lost3'] = 0
df6
for i in range(0,len(df_class)):
    brc_sector = df_class.iloc[i]['brc_sector']
    
    df1 = df5[df5['brc_sector'] == brc_sector]
    
    x1 = df1['num_working'].sum()
    x2 = df1['job_growth1'].sum()
    x3 = df1['job_growth2'].sum()
    x4 = df1['job_growth3'].sum()
    x5 = df1['jobs_lost1'].sum()
    x6 = df1['jobs_lost2'].sum()
    x7 = df1['jobs_lost3'].sum()
    
    df6.loc[i,'num_working'] = x1
    df6.loc[i,'job_growth1'] = x2
    df6.loc[i,'job_growth2'] = x3
    df6.loc[i,'job_growth3'] = x4
    df6.loc[i,'jobs_lost1'] = x5
    df6.loc[i,'jobs_lost2'] = x6
    df6.loc[i,'jobs_lost3'] = x7
    
df6.sum()
df7 = df6
df7
expl10 = df6
expl10['job_growth'] = expl10['job_growth1'] + expl10['job_growth2'] + expl10['job_growth3']
expl10['jobs_lost'] = expl10['jobs_lost1'] + expl10['jobs_lost2'] + expl10['jobs_lost3']
expl10 = expl10[[ 'brc_sector','jobs_lost','job_growth']]
expl10
expl10.plot.bar(x ='brc_sector')
plt.show()

#incorporated are job growth and extra job loss through historical information
# Replace workers with workers from the same sector
df7 = df7.round(decimals = 3)
df7 = df7[['brc_sector','job_growth1','job_growth2','job_growth3','jobs_lost1','jobs_lost2','jobs_lost3']]
df7
df7.sum()

for i in range(0,len(df7)):
    
    x1 = df7.iloc[i]['jobs_lost3']
    x2 = df7.iloc[i]['job_growth3']
    
    if (x1 < x2):
        df7.loc[i,'jobs_lost3'] = 0
        df7.loc[i,'job_growth3'] = (x2 - x1)
    else:
        df7.loc[i,'jobs_lost3'] = (x1 - x2)
        df7.loc[i,'job_growth3'] = 0
        
    x3 = df7.iloc[i]['jobs_lost2']
    x4 = df7.iloc[i]['job_growth2']
    
    if (x3 < x4):
        df7.loc[i,'jobs_lost2'] = 0
        df7.loc[i,'job_growth2'] = (x4 - x3)
    else:
        df7.loc[i,'jobs_lost2'] = (x3 - x4)
        df7.loc[i,'job_growth2'] = 0
        
    x5 = df7.iloc[i]['jobs_lost1']
    x6 = df7.iloc[i]['job_growth1']
    
    if (x5 < x6):
        df7.loc[i,'jobs_lost1'] = 0
        df7.loc[i,'job_growth1'] = (x6 - x5)
    else:
        df7.loc[i,'jobs_lost1'] = (x5 - x6)
        df7.loc[i,'job_growth1'] = 0

df7
for i in range(0,len(df7)):
    
    x1 = df7.iloc[i]['jobs_lost3']
    x2 = df7.iloc[i]['job_growth3']
    
    x3 = df7.iloc[i]['jobs_lost2']
    x4 = df7.iloc[i]['job_growth2']
    
    x5 = df7.iloc[i]['jobs_lost1']
    x6 = df7.iloc[i]['job_growth1']
    
      
    if (x3 < x2):
        df7.loc[i,'jobs_lost2'] = 0
        df7.loc[i,'job_growth3'] = (x2 - x3)
    else:
        df7.loc[i,'jobs_lost2'] = (x3 - x2)
        df7.loc[i,'job_growth3'] = 0
        

    
    if (x5 < x4):
        df7.loc[i,'jobs_lost1'] = 0
        df7.loc[i,'job_growth2'] = (x4 - x5)
    else:
        df7.loc[i,'jobs_lost1'] = (x5 - x4)
        df7.loc[i,'job_growth2'] = 0
        
    
 
df7
df7.sum()
df8 = df7[['brc_sector','jobs_lost1','jobs_lost2', 'jobs_lost3']]
df8

# What is the distribution of workers that is available
df8.plot.bar(x ='brc_sector')
plt.show()
z1 = int(df8['jobs_lost1'].sum()*1000)
z2 = int(df8['jobs_lost2'].sum()*1000)
z3 = int(df8['jobs_lost3'].sum()*1000)

print('Number of people lost current job educational level 1')
print(z1)
print('Number of people lost current job educational level 2')
print(z2)
print('Number of people lost current job educational level 3')
print(z3)

print('Total number of current jobs lost')
print(z1+z2+z3)<jupyter_output>Number of people lost current job educational level 1
992005
Number of people lost current job educational level 2
1325076
Number of people lost current job educational level 3
408372
Total number of current jobs lost
2725453
StarcoderdataJupyterStructuredCleanDedup