# 2009 -------------------------------------------------------------------- # Region merging EU-SILC data with ISSP # Bring in EU_SILC data for urban/rural/region 2009 # library(Hmisc) library(tidyverse) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/EU_SILC") eu_silc_50_2009 <- read_csv("silc_averages_50.csv") uk_2010_data <- eu_silc_50_2009 %>% filter(DB010==2010 & DB020 =="UK") %>% mutate(uk_region = str_sub(DB040, 1, 3), uk_region = case_when(uk_region %in% c("UKC", "UKD", "UKE") ~"UKC", uk_region %in% c("UKF", "UKG") ~"UKF", uk_region %in% c("UKH", "UKJ", "UKK") ~"UKH", TRUE ~ uk_region)) %>% group_by(uk_region, DB100) %>% summarise_if(is.numeric, funs(weighted.mean(., weights=n_cases, na.rm=TRUE))) %>% ungroup() %>% mutate(DB020 = "UK", DB040 = uk_region) %>% select(-uk_region) eu_silc_50_2009 <- eu_silc_50_2009 %>% filter(DB010==2009 | (DB020=="CH" & DB010==2007) |(DB020=="DE" & DB010==2006)) %>% filter(!(DB010==2009 & DB020 %in% c("UK", "DE", "CH"))) eu_silc_50_2009 <- eu_silc_50_2009 %>% bind_rows(uk_2010_data) eu_silc_50_2009<- eu_silc_50_2009 %>% rename_all(funs(str_replace(., "^", "U_"))) eu_silc_50_2009<- eu_silc_50_2009 %>% rename(DB040 = U_DB040, DB100 = U_DB100) reg_eu_silc_50_2009 <- read_csv("silc_averages_r_50.csv") uk_2010_data_r <- reg_eu_silc_50_2009 %>% filter(DB010==2010 & DB020 =="UK") %>% mutate(uk_region = str_sub(DB040, 1, 3), uk_region = case_when(uk_region %in% c("UKC", "UKD", "UKE") ~"UKC", uk_region %in% c("UKF", "UKG") ~"UKF", uk_region %in% c("UKH", "UKJ", "UKK") ~"UKH", TRUE ~ uk_region)) %>% group_by(uk_region) %>% summarise_if(is.numeric, funs(weighted.mean(., weights=n_cases, na.rm=TRUE))) %>% ungroup() %>% mutate(DB020 = "UK", DB040 = uk_region) %>% select(-uk_region) reg_eu_silc_50_2009 <- reg_eu_silc_50_2009 %>% filter(DB010==2009 | (DB020=="CH" & DB010==2007) |(DB020=="DE" & DB010==2006)) %>% filter(!(DB010==2009 & DB020 %in% c("UK", "DE", "CH"))) reg_eu_silc_50_2009 <- reg_eu_silc_50_2009 %>% bind_rows(uk_2010_data_r) # ISSP 2009 region matching library(haven) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/ISSP") issp_2009 <- read_dta("ZA5400_v4-0-0.dta") issp_2009 <- issp_2009 %>% mutate(DB040 = case_when(AT_REG %in% c(7, 8, 9) ~ "AT1", AT_REG %in% c(5, 6) ~ "AT2", AT_REG %in% c(1, 2, 3, 4) ~ "AT3", BE_REG %in% c(6) ~ "BE1", BE_REG %in% c(1, 2, 3, 4, 5) ~ "BE2", CH_REG %in% c(1) ~ "CH01", CH_REG %in% c(2) ~ "CH02", CH_REG %in% c(3) ~ "CH03", CH_REG %in% c(4) ~ "CH04", CH_REG %in% c(5) ~ "CH05", CH_REG %in% c(6) ~ "CH06", CH_REG %in% c(7) ~ "CH07", DE_REG %in% c(8) ~ "DE1", DE_REG %in% c(9) ~ "DE2", DE_REG %in% c(5) ~ "DEA", DE_REG %in% c(17, 13, 12, 11, 14, 15, 16) ~ "DENE", DE_REG %in% c(1, 3, 2, 4) ~ "DENW", DE_REG %in% c(6, 10, 7) ~ "DECE", DK_REG %in% c(1, 2, 3, 4, 5, 6, 7) ~"DK0", ES_REG %in% c(11) ~"ES11", ES_REG %in% c(3) ~"ES12", ES_REG %in% c(7) ~"ES13", ES_REG %in% c(16) ~"ES21", ES_REG %in% c(15) ~"ES22", ES_REG %in% c(12) ~"ES23", ES_REG %in% c(2) ~"ES24", ES_REG %in% c(13) ~"ES30", ES_REG %in% c(8) ~"ES41", ES_REG %in% c(9) ~"ES42", ES_REG %in% c(10) ~"ES43", ES_REG %in% c(5) ~"ES51", ES_REG %in% c(17) ~"ES52", ES_REG %in% c(4) ~"ES53", ES_REG %in% c(1) ~"ES61", ES_REG %in% c(14) ~"ES62", ES_REG %in% c(6) ~"ES70", FI_REG %in% c( 4, 6, 14, 15, 13) ~"FI19", FI_REG %in% c( 1, 20) ~"FI1B", FI_REG %in% c( 2, 5, 7, 8, 9) ~"FI1C", FI_REG %in% c( 10, 11, 12, 18, 16, 17, 19) ~"FI1D", FR_REG %in% c(75, 77, 78, 91, 92, 93, 94, 95) ~"FR10", FR_REG %in% c(8, 10, 51, 52) ~"FR21", FR_REG %in% c(2, 51, 80) ~"FR22", FR_REG %in% c(27, 76) ~"FR23", FR_REG %in% c(18, 28, 36, 37, 41, 45) ~"FR24", FR_REG %in% c(14, 50, 61) ~"FR25", FR_REG %in% c(21, 58, 71, 89) ~"FR26", FR_REG %in% c(59, 62) ~"FR30", FR_REG %in% c(54, 55, 57, 88) ~"FR41", FR_REG %in% c(67, 68) ~"FR42", FR_REG %in% c(25, 39, 70, 90) ~"FR43", FR_REG %in% c(44, 49, 53, 72, 85) ~"FR51", FR_REG %in% c(22, 29, 35, 56) ~"FR52", FR_REG %in% c(16, 17, 79, 86) ~"FR53", FR_REG %in% c(24, 33, 40, 47, 64) ~"FR61", FR_REG %in% c(9, 12, 31, 32, 46, 65, 81, 82) ~"FR62", FR_REG %in% c(19, 23, 87) ~"FR63", FR_REG %in% c(1, 7, 26, 38, 42, 69, 73, 74) ~"FR71", FR_REG %in% c(3, 15, 43, 63) ~"FR72", FR_REG %in% c(11, 30, 34, 48, 66) ~"FR81", FR_REG %in% c(4, 5, 6, 13, 83, 84) ~"FR82", GB_REG %in% c(1) ~ "UKM", GB_REG %in% c(2) ~ "UKC", GB_REG %in% c(3) ~ "UKF", GB_REG %in% c(4) ~ "UKL", GB_REG %in% c(5) ~ "UKH", GB_REG %in% c(6) ~ "UKI", NO_REG %in% c(1, 2, 3, 4, 5, 6) ~ "NO0", PT_REG %in% c(1, 2, 3, 4, 5) ~ "PT0", SE_REG %in% c(3, 4) ~ "SE1", SE_REG %in% c(5, 6, 7, 8, 9) ~ "SE2", SE_REG %in% c(1, 2) ~ "SE3"), DB100 = case_when(URBRURAL ==1 ~ 1, URBRURAL %in%c(2, 3) ~2, URBRURAL %in%c(4, 5) ~3), redist = case_when(V33 %in% c(1, 2, 3, 4, 5) ~5-V33), wealthy_fam = case_when(V6 %in% c(1, 2, 3, 4, 5) ~5-V6), hh_inc_rel_mean = case_when (!is.na(AT_INC) & AT_INC<999990 ~ AT_INC/weighted.mean(AT_INC[AT_INC<999990], WEIGHT[AT_INC<999990], na.rm=T), !is.na(BE_INC) & BE_INC<999990 ~ BE_INC/weighted.mean(BE_INC[BE_INC<999990], WEIGHT[BE_INC<999990], na.rm=T), !is.na(CH_INC) & CH_INC<999990 ~ CH_INC/weighted.mean(CH_INC[CH_INC<999990], WEIGHT[CH_INC<999990], na.rm=T), !is.na(DE_INC) & DE_INC<999990 ~ DE_INC/weighted.mean(DE_INC[DE_INC<999990], WEIGHT[DE_INC<999990], na.rm=T), !is.na(DK_INC) & DK_INC<999990 ~ DK_INC/weighted.mean(DK_INC[DK_INC<999990], WEIGHT[DK_INC<999990], na.rm=T), !is.na(ES_INC) & ES_INC<999990 ~ ES_INC/weighted.mean(ES_INC[ES_INC<999990], WEIGHT[ES_INC<999990], na.rm=T), !is.na(FI_INC) & FI_INC<999990 ~ FI_INC/weighted.mean(FI_INC[FI_INC<999990], WEIGHT[FI_INC<999990], na.rm=T), !is.na(FR_INC) & FR_INC<999990 ~ FR_INC/weighted.mean(FR_INC[FR_INC<999990], WEIGHT[FR_INC<999990], na.rm=T), !is.na(GB_INC) & GB_INC<999990 ~ GB_INC/weighted.mean(GB_INC[GB_INC<999990], WEIGHT[GB_INC<999990], na.rm=T), !is.na(NO_INC) & NO_INC<999990 ~ NO_INC/weighted.mean(NO_INC[NO_INC<999990], WEIGHT[NO_INC<999990], na.rm=T), !is.na(PT_INC) & PT_INC<999990 ~ PT_INC/weighted.mean(PT_INC[PT_INC<999990], WEIGHT[PT_INC<999990], na.rm=T), !is.na(SE_INC) & SE_INC<999990 ~ SE_INC/weighted.mean(SE_INC[SE_INC<999990], WEIGHT[SE_INC<999990], na.rm=T)), log_inc_rel_mean = case_when(hh_inc_rel_mean>0 ~ log(hh_inc_rel_mean)), left_right = case_when(PARTY_LR %in% c(1, 2, 3, 4, 5) ~ PARTY_LR), left_right_2 = case_when(PARTY_LR %in% c(1, 2, 3, 4, 5) ~ PARTY_LR, PARTY_LR %in% c(6, 7) ~ 3), household_size = case_when(HOMPOP <= 10 ~ HOMPOP, HOMPOP > 10 ~ 10), employed = case_when(WRKST <= 3 ~ 1, WRKST > 3 ~ 0), urbrural = case_when(URBRURAL %in% c(1, 2, 3, 4, 5) ~URBRURAL), age = case_when(AGE<99 &AGE>0 ~ AGE), retired = case_when(WRKST == 7 ~ 1, WRKST > 0 & WRKST !=7 ~ 0), relgiosity = 8-ATTEND, homeowner = case_when(V67==2 ~ 0, V67 ==1 ~ 1, V67 >2 & V67<99998 ~1), house_equity = case_when(homeowner==1 ~ V67 %% 100), just_debts = case_when(V67 == 1 ~ 1, V67 == 2 ~ 0, V67 >2 & V67<99998 ~0), renter = case_when( V67==2 ~ 1, V67 ==1 ~ 0, V67 >2 & V67<99998 ~0), some_equity = case_when( V67==2 ~ 0, V67 ==1 ~ 0, V67 >2 & V67<99998 ~1), ) %>% filter(!is.na(DB040)) %>% left_join(reg_eu_silc_50_2009, by = "DB040") %>% left_join(eu_silc_50_2009, by = c("DB040", "DB100")) %>% mutate (DB020 = if_else(is.na(DB020),"PT", DB020)) write_dta(issp_2009, "issp_2009_eusilc.dta") # 2016 -------------------------------------------------------------------- # 2016 Matching # Bring in EU_SILC data for urban/rural/region 20016# library(Hmisc) library(tidyverse) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/EU_SILC") eu_silc_50_2016 <- read_csv("silc_averages_50.csv") uk_2016_data <- eu_silc_50_2016 %>% filter(DB010==2016 & DB020 =="UK") %>% mutate(uk_region = str_sub(DB040, 1, 3), uk_region = case_when(uk_region %in% c("UKC", "UKD", "UKE") ~"UKC", uk_region %in% c("UKF", "UKG") ~"UKF", uk_region %in% c("UKH", "UKJ", "UKK") ~"UKH", TRUE ~ uk_region)) %>% group_by(uk_region, DB100) %>% summarise_if(is.numeric, funs(weighted.mean(., weights=n_cases, na.rm=TRUE))) %>% ungroup() %>% mutate(DB020 = "UK", DB040 = uk_region) %>% select(-uk_region) eu_silc_50_2016 <- eu_silc_50_2016 %>% filter(DB010==2016) %>% filter(!(DB010==2016 & DB020 %in% c("UK"))) eu_silc_50_2016 <- eu_silc_50_2016 %>% bind_rows(uk_2016_data) eu_silc_50_2016<- eu_silc_50_2016 %>% rename_all(funs(str_replace(., "^", "U_"))) eu_silc_50_2016<- eu_silc_50_2016 %>% rename(DB040 = U_DB040, DB100 = U_DB100) eu_silc_50_2016<- eu_silc_50_2016 %>% mutate(DB040 = case_when(U_DB020=="DE" ~ "DE0", U_DB020=="NL" ~ "NL0", U_DB020=="PT" ~ "PT0", TRUE ~ DB040)) reg_eu_silc_50_2016 <- read_csv("silc_averages_r_50.csv") uk_2016_data_r <- reg_eu_silc_50_2016 %>% filter(DB010==2016 & DB020 =="UK") %>% mutate(uk_region = str_sub(DB040, 1, 3), uk_region = case_when(uk_region %in% c("UKC", "UKD", "UKE") ~"UKC", uk_region %in% c("UKF", "UKG") ~"UKF", uk_region %in% c("UKH", "UKJ", "UKK") ~"UKH", TRUE ~ uk_region)) %>% group_by(uk_region) %>% summarise_if(is.numeric, funs(weighted.mean(., weights=n_cases, na.rm=TRUE))) %>% ungroup() %>% mutate(DB020 = "UK", DB040 = uk_region) %>% select(-uk_region) reg_eu_silc_50_2016 <- reg_eu_silc_50_2016 %>% filter(DB010==2016) %>% filter(!(DB010==2016 & DB020 %in% c("UK"))) reg_eu_silc_50_2016 <- reg_eu_silc_50_2016 %>% bind_rows(uk_2016_data_r) reg_eu_silc_50_2016 <- reg_eu_silc_50_2016 %>% mutate(DB040 = case_when(DB020=="DE" ~ "DE0", DB020=="NL" ~ "NL0", DB020=="PT" ~ "PT0", TRUE ~ DB040)) # ISSP 2016 region matching library(haven) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/ISSP/ZA6900_v2-0-0") issp_2016 <- read_dta("ZA6900_v2-0-0.dta") issp_2016 <- issp_2016 %>% mutate(DB040 = case_when(BE_REG %in% c(10) ~ "BE1", BE_REG %in% c(21, 22, 23, 24, 25) ~ "BE2", BE_REG %in% c(31, 32, 33, 34, 35) ~ "BE3", CH_REG %in% c(1, 2, 3, 4, 5, 6, 7) ~ "CH0", DE_REG %in% 1:17 ~ "DE0", DK_REG %in% c(1, 2, 3, 4, 5, 6, 7) ~"DK0", ES_REG %in% c(12) ~"ES11", ES_REG %in% c(3) ~"ES12", ES_REG %in% c(6) ~"ES13", ES_REG %in% c(16) ~"ES21", ES_REG %in% c(15) ~"ES22", ES_REG %in% c(17) ~"ES23", ES_REG %in% c(2) ~"ES24", ES_REG %in% c(13) ~"ES30", ES_REG %in% c(8) ~"ES41", ES_REG %in% c(7) ~"ES42", ES_REG %in% c(11) ~"ES43", ES_REG %in% c(9) ~"ES51", ES_REG %in% c(10) ~"ES52", ES_REG %in% c(4) ~"ES53", ES_REG %in% c(1) ~"ES61", ES_REG %in% c(14) ~"ES62", ES_REG %in% c(5) ~"ES70", FI_REG %in% c( 4, 6, 14, 15, 13) ~"FI19", FI_REG %in% c( 1, 20) ~"FI1B", FI_REG %in% c( 2, 5, 7, 8, 9) ~"FI1C", FI_REG %in% c( 10, 11, 12, 18, 16, 17, 19) ~"FI1D", FR_REG %in% c(75, 77, 78, 91, 92, 93, 94, 95) ~"FR10", FR_REG %in% c(8, 10, 51, 52) ~"FR21", FR_REG %in% c(2, 51, 80) ~"FR22", FR_REG %in% c(27, 76) ~"FR23", FR_REG %in% c(18, 28, 36, 37, 41, 45) ~"FR24", FR_REG %in% c(14, 50, 61) ~"FR25", FR_REG %in% c(21, 58, 71, 89) ~"FR26", FR_REG %in% c(59, 62) ~"FR30", FR_REG %in% c(54, 55, 57, 88) ~"FR41", FR_REG %in% c(67, 68) ~"FR42", FR_REG %in% c(25, 39, 70, 90) ~"FR43", FR_REG %in% c(44, 49, 53, 72, 85) ~"FR51", FR_REG %in% c(22, 29, 35, 56) ~"FR52", FR_REG %in% c(16, 17, 79, 86) ~"FR53", FR_REG %in% c(24, 33, 40, 47, 64) ~"FR61", FR_REG %in% c(9, 12, 31, 32, 46, 65, 81, 82) ~"FR62", FR_REG %in% c(19, 23, 87) ~"FR63", FR_REG %in% c(1, 7, 26, 38, 42, 69, 73, 74) ~"FR71", FR_REG %in% c(3, 15, 43, 63) ~"FR72", FR_REG %in% c(11, 30, 34, 48, 66) ~"FR81", FR_REG %in% c(4, 5, 6, 13, 83, 84) ~"FR82", GB_REG %in% c(1) ~ "UKM", GB_REG %in% c(2) ~ "UKC", GB_REG %in% c(3) ~ "UKF", GB_REG %in% c(4) ~ "UKL", GB_REG %in% c(5) ~ "UKH", GB_REG %in% c(6) ~ "UKI", NO_REG %in% c(1, 2, 3, 4, 5, 6) ~ "NO0", SE_REG %in% c(1, 2) ~ "SE11", SE_REG %in% c(3, 4, 5, 6, 7, 8, 9, 10, 45, 46, 47, 48, 49, 50, 51) ~ "SE12", SE_REG %in% 11:21 ~ "SE21", SE_REG %in% 22:31 ~ "SE22", SE_REG %in% 32:40 ~ "SE23", SE_REG %in% c(41, 42, 43, 44, 52, 53, 54, 55, 56, 57, 58) ~ "SE31", SE_REG %in% c(59, 60, 61, 62, 63) ~ "SE32", SE_REG %in% 64:70 ~ "SE33"), DB100 = case_when(URBRURAL ==1 ~ 1, URBRURAL %in%c(2, 3) ~2, URBRURAL %in%c(4, 5) ~3), redist = case_when(v27 %in% c(1, 2, 3, 4) ~4-v27), decent_housing = case_when(v29 %in% c(1, 2, 3, 4) ~4-v29), cut_spending = case_when(v7 %in% c(1, 2, 3, 4, 5) ~5-v7), hh_inc_rel_mean = case_when (!is.na(BE_INC) & BE_INC<999990 ~ BE_INC/weighted.mean(BE_INC[BE_INC<999990], WEIGHT[BE_INC<999990], na.rm=T), !is.na(CH_INC) & CH_INC<999990 ~ CH_INC/weighted.mean(CH_INC[CH_INC<999990], WEIGHT[CH_INC<999990], na.rm=T), !is.na(DE_INC) & DE_INC<999990 ~ DE_INC/weighted.mean(DE_INC[DE_INC<999990], WEIGHT[DE_INC<999990], na.rm=T), !is.na(DK_INC) & DK_INC<999990 ~ DK_INC/weighted.mean(DK_INC[DK_INC<999990], WEIGHT[DK_INC<999990], na.rm=T), !is.na(ES_INC) & ES_INC<999990 ~ ES_INC/weighted.mean(ES_INC[ES_INC<999990], WEIGHT[ES_INC<999990], na.rm=T), !is.na(FI_INC) & FI_INC<999990 ~ FI_INC/weighted.mean(FI_INC[FI_INC<999990], WEIGHT[FI_INC<999990], na.rm=T), !is.na(FR_INC) & FR_INC<999990 ~ FR_INC/weighted.mean(FR_INC[FR_INC<999990], WEIGHT[FR_INC<999990], na.rm=T), !is.na(GB_INC) & GB_INC<999990 ~ GB_INC/weighted.mean(GB_INC[GB_INC<999990], WEIGHT[GB_INC<999990], na.rm=T), !is.na(NO_INC) & NO_INC<999990 ~ NO_INC/weighted.mean(NO_INC[NO_INC<999990], WEIGHT[NO_INC<999990], na.rm=T), !is.na(SE_INC) & SE_INC<999990 ~ SE_INC/weighted.mean(SE_INC[SE_INC<999990], WEIGHT[SE_INC<999990], na.rm=T)), log_inc_rel_mean = log(hh_inc_rel_mean), left_right = case_when(PARTY_LR %in% 1:5 ~ PARTY_LR), household_size = case_when(HOMPOP <= 10 & HOMPOP>1 ~ HOMPOP, HOMPOP > 10 & HOMPOP <99 ~ 10), employed = case_when(MAINSTAT ==1 ~ 1, MAINSTAT > 1 & MAINSTAT<99 ~ 0), retired = case_when(MAINSTAT == 6 ~ 1, MAINSTAT > 0 & MAINSTAT !=6 & MAINSTAT<99 ~ 0), religiosity = case_when(ATTEND %in% 1:8 ~ 8-ATTEND), age = case_when(AGE<999 &AGE>0 ~ AGE), sex = case_when(SEX<9 ~SEX-1) ) %>% filter(!is.na(DB040)) %>% left_join(reg_eu_silc_50_2016, by = "DB040") %>% left_join(eu_silc_50_2016, by = c("DB040", "DB100")) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/ISSP") write_dta(issp_2016, "issp_2016_eusilc.dta") # 2006 EU SILC ------------------------------------------------------------ # 2006 Matching # Bring in EU_SILC data for urban/rural/region 20016# library(Hmisc) library(tidyverse) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/EU_SILC") eu_silc_50_2006 <- read_csv("silc_averages_50.csv") eu_silc_50_2006 <- eu_silc_50_2006 %>% filter(DB010==2006 | (DB020=="CH" & DB010==2007) | (DB020=="FR" & DB010==2005) ) %>% filter(!(DB010==2006 & DB020 %in% c( "CH"))) eu_silc_50_2006<- eu_silc_50_2006 %>% rename_all(funs(str_replace(., "^", "U_"))) eu_silc_50_2006<- eu_silc_50_2006 %>% rename(DB040 = U_DB040, DB100 = U_DB100) %>% mutate(DB040 = case_when(U_DB020=="UK" ~ "UK0", U_DB020=="NL" ~ "NL0", U_DB020=="PT" ~ "PT0", TRUE ~ DB040)) reg_eu_silc_50_2006 <- read_csv("silc_averages_r_50.csv") reg_eu_silc_50_2006 <- reg_eu_silc_50_2006 %>% filter(DB010==2006 | (DB020=="CH" & DB010==2007) | (DB020=="FR" & DB010==2005)) %>% filter(!(DB010==2006 & DB020 %in% c( "CH"))) %>% mutate(DB040 = case_when(DB020=="UK" ~ "UK0", DB020=="NL" ~ "NL0", DB020=="PT" ~ "PT0", TRUE ~ DB040)) # ISSP 2006 --------------------------------------------------------------- library(haven) setwd("~/Dropbox/WEALTHPOL_Research/Papers/JESP/Data/ISSP") issp_2006 <- read_dta("ZA4700.dta") issp_2006 <- issp_2006 %>% mutate(DB040 = case_when(CH_REG %in% c(1) ~ "CH01", CH_REG %in% c(2) ~ "CH02", CH_REG %in% c(3) ~ "CH03", CH_REG %in% c(4) ~ "CH04", CH_REG %in% c(5) ~ "CH05", CH_REG %in% c(6) ~ "CH06", CH_REG %in% c(7) ~ "CH07", DE_REG %in% c(8) ~ "DE1", DE_REG %in% c(9) ~ "DE2", DE_REG %in% c(5) ~ "DEA", DE_REG %in% c(17, 13, 12, 11, 14, 15, 16) ~ "DENE", DE_REG %in% c(1, 3, 2, 4) ~ "DENW", DE_REG %in% c(6, 10, 7) ~ "DECE", DK_REG %in% c(1, 2, 3, 4, 5, 6, 7) ~"DK0", ES_REG %in% c(12) ~"ES11", ES_REG %in% c(3) ~"ES12", ES_REG %in% c(6) ~"ES13", ES_REG %in% c(16) ~"ES21", ES_REG %in% c(15) ~"ES22", ES_REG %in% c(17) ~"ES23", ES_REG %in% c(2) ~"ES24", ES_REG %in% c(13) ~"ES30", ES_REG %in% c(8) ~"ES41", ES_REG %in% c(7) ~"ES42", ES_REG %in% c(11) ~"ES43", ES_REG %in% c(9) ~"ES51", ES_REG %in% c(10) ~"ES52", ES_REG %in% c(4) ~"ES53", ES_REG %in% c(1) ~"ES61", ES_REG %in% c(14) ~"ES62", ES_REG %in% c(5) ~"ES70", FI_REG %in% c( 4, 6, 14, 15, 13) ~"FI19", FI_REG %in% c(1, 2, 5, 7, 8, 9, 20) ~"FI18", FI_REG %in% c( 10, 11, 12, 18) ~"FI13", FI_REG %in% c( 16, 17, 19) ~"FI1A", FR_REG %in% c(75, 77, 78, 91, 92, 93, 94, 95) ~"FR10", FR_REG %in% c(8, 10, 51, 52) ~"FR21", FR_REG %in% c(2, 51, 80) ~"FR22", FR_REG %in% c(27, 76) ~"FR23", FR_REG %in% c(18, 28, 36, 37, 41, 45) ~"FR24", FR_REG %in% c(14, 50, 61) ~"FR25", FR_REG %in% c(21, 58, 71, 89) ~"FR26", FR_REG %in% c(59, 62) ~"FR30", FR_REG %in% c(54, 55, 57, 88) ~"FR41", FR_REG %in% c(67, 68) ~"FR42", FR_REG %in% c(25, 39, 70, 90) ~"FR43", FR_REG %in% c(44, 49, 53, 72, 85) ~"FR51", FR_REG %in% c(22, 29, 35, 56) ~"FR52", FR_REG %in% c(16, 17, 79, 86) ~"FR53", FR_REG %in% c(24, 33, 40, 47, 64) ~"FR61", FR_REG %in% c(9, 12, 31, 32, 46, 65, 81, 82) ~"FR62", FR_REG %in% c(19, 23, 87) ~"FR63", FR_REG %in% c(1, 7, 26, 38, 42, 69, 73, 74) ~"FR71", FR_REG %in% c(3, 15, 43, 63) ~"FR72", FR_REG %in% c(11, 30, 34, 48, 66) ~"FR81", FR_REG %in% c(4, 5, 6, 13, 83, 84) ~"FR82", GB_REG %in% c(1, 2, 3, 4, 5, 6) ~ "UK0", NO_REG %in% c(1, 2, 3, 4, 5, 6) ~ "NO0", SE_REG %in% c(1, 2) ~ "SE11", SE_REG %in% c(3, 4, 5, 6, 7, 8, 9, 10, 45, 46, 47, 48, 49, 50, 51) ~ "SE12", SE_REG %in% 11:21 ~ "SE21", SE_REG %in% 22:31 ~ "SE22", SE_REG %in% 32:40 ~ "SE23", SE_REG %in% c(41, 42, 43, 44, 52, 53, 54, 55, 56, 57, 58) ~ "SE31", SE_REG %in% c(59, 60, 61, 62, 63) ~ "SE32", SE_REG %in% 64:70 ~ "SE33"), DB100 = case_when(urbrural ==1 ~ 1, urbrural %in%c(2, 3) ~2, urbrural %in%c(4, 5) ~3), redist = case_when(V31 %in% c(1, 2, 3, 4) ~4-V31), decent_housing = case_when(V33 %in% c(1, 2, 3, 4) ~4-V33), cut_spending = case_when(V11 %in% c(1, 2, 3, 4, 5) ~5-V11), hh_inc_rel_mean = case_when (!is.na(CH_INC) & CH_INC<999990 ~ CH_INC/weighted.mean(CH_INC[CH_INC<999990], weight[CH_INC<999990], na.rm=T), !is.na(DE_INC) & DE_INC<999990 ~ DE_INC/weighted.mean(DE_INC[DE_INC<999990], weight[DE_INC<999990], na.rm=T), !is.na(DK_INC) & DK_INC<999990 ~ DK_INC/weighted.mean(DK_INC[DK_INC<999990], weight[DK_INC<999990], na.rm=T), !is.na(ES_INC) & ES_INC<999990 ~ ES_INC/weighted.mean(ES_INC[ES_INC<999990], weight[ES_INC<999990], na.rm=T), !is.na(FI_INC) & FI_INC<999990 ~ FI_INC/weighted.mean(FI_INC[FI_INC<999990], weight[FI_INC<999990], na.rm=T), !is.na(FR_INC) & FR_INC<999990 ~ FR_INC/weighted.mean(FR_INC[FR_INC<999990], weight[FR_INC<999990], na.rm=T), !is.na(GB_INC) & GB_INC<999990 ~ GB_INC/weighted.mean(GB_INC[GB_INC<999990], weight[GB_INC<999990], na.rm=T), !is.na(NO_INC) & NO_INC<999990 ~ NO_INC/weighted.mean(NO_INC[NO_INC<999990], weight[NO_INC<999990], na.rm=T), !is.na(SE_INC) & SE_INC<999990 ~ SE_INC/weighted.mean(SE_INC[SE_INC<999990], weight[SE_INC<999990], na.rm=T)), log_inc_rel_mean = case_when(hh_inc_rel_mean>0 ~ log(hh_inc_rel_mean)), left_right = case_when(PARTY_LR %in% 1:5 ~ PARTY_LR), household_size = case_when(hompop <= 10 & hompop>1 ~ hompop, hompop > 10 & hompop <99 ~ 10), employed = case_when(wrkst <= 3 ~ 1, wrkst > 3 ~ 0), retired = case_when(wrkst == 7 ~ 1, wrkst > 0 & wrkst !=7 ~ 0), religiosity = case_when(attend %in% 1:8 ~ 8-attend), age = case_when(age<999 &age>0 ~ age), sex = case_when(sex<9 ~sex-1) ) %>% filter(!is.na(DB040)) %>% left_join(reg_eu_silc_50_2006, by = "DB040") %>% left_join(eu_silc_50_2006, by = c("DB040", "DB100")) write_dta(issp_2006, "issp_2006_eusilc.dta")