Tagging variables with keywords

metadata_database <- readRDS( file.path('../data-raw', 'eb_metadata_database_20200628.rds')) %>%
  mutate(archive_id = substr(filename, 1, 6))

eb_names <- rio::import("../data-raw/eb_waves_filenames.xlsx") %>%
  separate(x, c("archive_id", "x2", "title"), sep = ":") %>%
  separate(x2, c("wave", "dates"), sep = " \\(|\\)") %>%
  mutate(wave = gsub("Eurobarometer | ", "", wave))

eb_names %>%
  select(archive_id, wave, dates) %>%
  head() %>%
  kable %>%
  kable_styling(bootstrap_options =
                  c("striped", "hover", "condensed"),
                  fixed_thead = T,
                  font_size = 10 )

varlabs <- rio::import("../data-raw/eb_variable_groups_zacat.xlsx", sheet = 4) %>%
  mutate(wave_desc = ifelse(substr(var_label_zacat, 1, 13) == "Eurobarometer", var_label_zacat, NA),
         var_label_zacat = ifelse(substr(var_label_zacat, 1, 13)=="Eurobarometer", NA, var_label_zacat),
         # group names are writte in camel case and != toupper(group names)
         group = ifelse(var_label_zacat != toupper(var_label_zacat), var_label_zacat, NA),
         # exceptions from the rule identifying group names
         group = ifelse(substr(group, 1, 9) == "OWNERSHIP", NA, group), 
         group = ifelse(substr(group, 1, 19) == "POSITIVE EU RESULTS", NA, group),
         group = ifelse(substr(group, 1, 24) == "OPINION LEADERSHIP INDEX", NA, group),
         var_label_zacat = ifelse(!is.na(group), NA, var_label_zacat)) %>%
  fill(wave_desc, group) %>%
  drop_na(group, var_label_zacat) %>%
  mutate(wave = substr(wave_desc, 1, 18),
         wave = gsub("^Eurobarometer ", "", wave),
         var_label_zacat = gsub("^W[0-9]{1,2}[A-Z] |^W3A/W4A ", "", var_label_zacat)) %>%
  left_join(eb_names) %>%
  select(wave, archive_id, group, var_label_zacat, everything())

varlabs %>%
  select(archive_id, wave, group, var_label_zacat) %>%
  head() %>%
  kable %>%
  kable_styling(bootstrap_options =
                  c("striped", "hover", "condensed"),
                  fixed_thead = T,
                  font_size = 10 )

# vector of archive IDs
archive_ids <- varlabs %>% distinct(archive_id) %>% pull(archive_id)

length(archive_ids)
#> [1] 51

dict <- rio::import("../data-raw/var_lab_dict.xlsx", 
                    sheet = "dict") %>%
  gather(id, from, 2:6) %>%
  select(from, to) %>%
  drop_na(from)

head(dict) %>%
  kable %>%
  kable_styling(bootstrap_options =
                  c("striped", "hover", "condensed"),
                  fixed_thead = T,
                  font_size = 10 )

# this part now became very fast with stri_replace_all_regex

dat2 <- metadata_database %>%
  # filter only waves for which variable groups are available
  filter(archive_id %in% archive_ids,
         # exclude this strange variable
         var_label_orig != "filename") %>%
  # keep one row per variable
  distinct(filename, qb, var_name_orig, var_name_orig, var_label_orig, var_label_norm, archive_id) %>%
  # original variable labels in the data and in GESIS ZACAT are not exactly the same 
  # --> need some cleaning
  mutate(var_label_orig2 = gsub("^W[0-9]{1,2}", "", var_label_orig),
         var_label_orig2 = gsub("^W[0-9]{1,2}[A-Z]", "", var_label_orig),
         var_label_orig2 = gsub("^[0-9]{1-3} ", "", var_label_orig2),
         var_label_orig2 = gsub("^[A-Z][0-9]{1,2}[A-Z] ", "", var_label_orig2),
         var_label_orig2 = gsub("^[A-Z]{1,2}[0-9]{1,2} ", "", var_label_orig2),
         var_label_orig2 = gsub("^[A-Z]{1,2}[0-9]{1,2}[A-Z] ", "", var_label_orig2),
         var_label_orig2 = gsub("^[0-9]{1,2} ", "", var_label_orig2),
         var_label_orig2 = gsub("^[A-Z][0-9] [0-9]{1,2} ", "", var_label_orig2),
         var_label_orig2 = gsub("^W3A/W4A ", "", var_label_orig2)) %>%
  # join with table with variable groups
  left_join(varlabs, by = c("var_label_orig2" = "var_label_zacat", "archive_id")) %>%
  # remove variables in the following groups
  filter(!group %in% c("Archive and survey ID variables",
                       "Country group variables",
                       "Nationality (Q1)",
                       "Protocol variables (P)",
                       "Standard nation ID variables",
                       "Weight variables (W)",
                       "Nation ID variables",
                       "Protocol variables",
                       "Weight variables",
                       "Archive and ID variables",
                       "Household Weights"),
         # remove recoded variables
         !grepl("recoded|(rec)", var_label_norm)) %>%
  mutate(var_id = paste0(filename, " ", var_name_orig),
         # leftover question numbers at the beginning of value labels
         var_label_norm1 = gsub("^w[0-9]{1,2}", "", var_label_norm),
         var_label_norm1 = gsub("^[0-9]{1-3}_", "", var_label_norm1),
         var_label_norm1 = gsub("^[a-z][0-9]{2}[a-z]_", "", var_label_norm1),
         var_label_norm1 = gsub("^[a-z]{1,2}[0-9]{1,2}_", "", var_label_norm1),
         var_label_norm1 = gsub("^[a-z]{1,2}[0-9]{1,2}[a-z]_", "", var_label_norm1),
         var_label_norm1 = gsub("^[0-9]{1,2}_", "", var_label_norm1),
         var_label_norm1 = gsub("^[a-z]_", "", var_label_norm1),
         var_label_norm1 = gsub("^[a-z][0-9]_[0-9]{1,2}_", "", var_label_norm1)) %>%
  # apply the translation table
  mutate(var_label_norm1 = stri_replace_all_regex(var_label_norm1,
                                                  pattern = dict$from,
                                                  replacement = dict$to,
                                                  vectorize_all = FALSE)) %>%
  # replace underscore with space
  mutate(text = gsub("_", " ", var_label_norm1)) %>%
  ungroup() %>%
  # select the needed variables
  select(filename, qb, var_label_orig, var_label_norm, var_label_norm1, text, var_id, group)