psrc_codebook_to_standard.Rmd

---
title: "psrc_codebook_to_standard"
author: "suzanne"
date: "2024-03-15"
output: html_document
---


The codebook is assumed to be in two parts:

variable_list
A dataset containing information about all variables existing in the hh, person, day, trip, and vehicle tables. The variables are as follows:

variable: Name of the variable
is_checkbox: The variable is a multiple response categorical variable question (multiple or mrcv variable)
hh: The variable exists in the hh table
person: The variable exists in the person table
day: The variable exists in the day table
trip: The variable exists in the trip table
vehicle: The variable exists in the vehicle table
location: The variable exists in the location table
data_type: Data type of the variable
description: A description of the variable
logic: Conditions where the variable should have a value
shared_name: the shared name of a multiple response categorical variable variable or the variable name for non-multiple response categorical variable variables
value_labels
A dataset containing the values for all variables found in variable_list The variables are as follows:

variable: Name of the variable
value: The numeric value of the variable
label: What the numeric value of the variable represents
label_value: value concatenated with the label (e.g., 11 85 or older)
val_order: order for each variable label to appear in

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(data.table)
library(stringr)
library(travelSurveyTools)
library(dplyr)
library(openxlsx)




```

## read in codebook as is
```{r}
cb_path = str_glue("J:/Projects/Surveys/HHTravel/Survey2023/Data/codebook/PSRC_Combined_Codebook_2023_groupings.xlsx")

output_path =str_glue("J:/Projects/Surveys/HHTravel/Survey2023/Data/codebook/PSRC_Combined_Codebook_2023_packagable.xlsx")

variable_list = readxl::read_xlsx(cb_path, sheet = 'variable_list_2023')
value_labels = readxl::read_xlsx(cb_path, sheet = 'value_labels_2023')


# read in variables and values
variable_list = readxl::read_xlsx(str_glue('J:/Projects/Surveys/HHTravel/Survey2023/Data/codebook/PSRC_Combined_Codebook_2023_groupings.xlsx'),
                                  sheet = 'variable_list_2023')
value_labels = readxl::read_xlsx(str_glue('J:/Projects/Surveys/HHTravel/Survey2023/Data/codebook/PSRC_Combined_Codebook_2023_groupings.xlsx'),
                                 sheet = 'value_labels_2023')
setDT(variable_list)
setDT(value_labels)
```

mush the codebook into the standard
first clean up variable list
```{r}
setnames(variable_list, c('psrc_variable', 'hh_23', 'per_23', 'veh_23', 'day_23', 'trip_23', 'location_final', 'description_2023', 'data_type_2023'),
         c('variable', 'hh', 'person', 'vehicle', 'day', 'trip', 'location', 'description', 'data_type'),
         skip_absent=TRUE)


variable_list[, shared_name := ifelse(
  grepl('--', description),
  sub('_[^_]*$', '', variable), variable)
]

variable_list[, is_checkbox := ifelse(grepl('--', description), 1, 0)]
variable_list[, logic := description]

variable_list<-variable_list%>%
  select(variable, is_checkbox, hh, person, day,trip, vehicle, location,data_type, description, logic, shared_name)



```

now clean up values
```{r}
setnames(value_labels, c('final_label', 'psrc_variable', 'psrc_value'), c('label', 'variable', 'value'),
         skip_absent=TRUE)

setnames(value_labels, c('final_label', 'psrc_variable', 'psrc_value'), c('label', 'variable', 'value'),
         skip_absent=TRUE)

value_labels[, val_order := seq_len(nrow(value_labels))]

value_labels<-value_labels%>%
select(variable, value, label, val_order, group_1_title, group_1_value, group_2_title, group_2_value, group_3_title, group_3_value)
```

```{r}
output_wb<-createWorkbook()
addWorksheet(output_wb, sheetName= 'variable_list')
addWorksheet(output_wb, sheetName= 'value_labels')

writeData(wb=output_wb, sheet= 'variable_list', x=variable_list)
writeData(wb=output_wb, sheet= 'value_labels', x=value_labels)

saveWorkbook(output_wb, output_path)
```