# Add Credit Suisse Table 3.1 to main data 

pathCreditSuisse <- "~/OneDrive - Nexus365/WEALTHPOL_RESEARCH/Data/Crossnational/Credit Suisse - Data/CreditSuisse_OCR.xlsx"


crdtCH <- pathCreditSuisse %>%
  excel_sheets() %>%
  set_names() %>%
  map(read_excel, path = pathCreditSuisse)


crdtCH <- crdtCH[c(31)]  # these are the relevant sheets

crdtCH <- lapply(crdtCH, function(x) x[-1,])  # remove first row from all dfs

# clean names of dfs
names(crdtCH) <- str_extract_all(names(crdtCH), "\\([^()]+\\)")
names(crdtCH) <- substring(names(crdtCH), 2, nchar(names(crdtCH))-1)

# rbind all elements of list into single df
crdtCH <- ldply(crdtCH, data.frame)

# clean up df.
names(crdtCH)[names(crdtCH) == ".id"] <- "year"
#crdtCH$year <- stri_sub(crdtCH$year,-4,-1)

crdtCH <- crdtCH[, c(2, 11)]  # restrict to key vars


crdtCH <- plyr::rename(crdtCH, c("Gini" = "WealthGini2017"))

crdtCH$ccode <- countrycode(crdtCH$Country, "country.name", "cown")  # Serbia not unambigiously matched; needs to be solved. 

crdtCH[,2] <- as.numeric(crdtCH[,2])

# turn variables into numeric

crdtCH <- crdtCH[!is.na(crdtCH$ccode),]  # for now, I exclude all non-matched.
setwd("~/OneDrive - Nexus365/WEALTHPOL_RESEARCH/Papers/longrunWealth/data")
library("readr")
write_csv( crdtCH, "credit_suisse_gini.csv")