createGADS: Creating a relational data baseIn the context of educational large-scale assessments (but also in other contexts) we frequently encounter data sets which have an hierarchical structure. In educational large-scale assessments these can, for example, be pupils nested in schools. Additional, hidden nested structures occur, if missing data are treated with multiple imputations or person parameters are estimated using plausible values. In these cases it is inefficient to store all the data in one rectangular data set. In other data science applications the use of relational data bases is a widespread measure to tackle this challenge.
eatGADS supports creating such relational data bases (based on the open source software SQLite3 and the R package eatDB) while maintaining its meta data and providing a very user friendly interface for data users who are unfamiliar with relational data bases. In doing so, it allows the handling of large data sets even on limited hardware settings. Furthermore, this approach allows the extraction of data from different hierarchy levels, which means that data has to be reshaped very rarely.
This vignette illustrates how a relational eatGADS data base can be created from a rectangular SPSS (.sav) data file. For illustrative purposes we use a small example data set from the campus files of the German PISA Plus assessment. The complete campus files and the original data set can be accessed here and here.
library(eatGADS)We can import an .sav (or an compressed .zsav) data set via the import_spss() function. Checks on variable names for SQLite3 compliance are performed automatically. Changes to the variable names are reported to the console.
sav_path <- system.file("extdata", "pisa.zsav", package = "eatGADS")
dat <- import_spss(sav_path)The next steps depend on the data structure: If the different hierarchy levels are saved in different source data sets (e.g. different .sav files) the next section can be skipped. However, sometimes data from different hierarchy levels is save in one data file. Then, splitting and reshaping becomes necessary.
In this case, we want to split the imported GADSdat object into its hierarchy levels (in our example: background data on level 1 and imputed competence data on level 2). This can be achieved by the splitGADS() function. We specify the hierarchical structure as a list. After this, we can extract separate GADSdat objects by name via the extractGADS() function. These objects can then be used for reshaping.
For reasons of simplicity, the example only contains two hierarchy levels. In practice, often more hierarchy levels are present. Splitting can be performed into as many hierarchy levels as desired. The reshaping has to be performed for each hierarchy level separately.
pvs <- grep("pv", namesGADS(dat), value = T)
splitted_gads <- splitGADS(dat, nameList = list(noImp = namesGADS(dat)[!namesGADS(dat) %in% pvs],
PVs = c("idstud", pvs)))
# new Structure
namesGADS(splitted_gads)
#> $noImp
#> [1] "idstud" "idschool" "idclass" "schtype" "sameteach"
#> [6] "g8g9" "ganztag" "classsize" "repeated" "gender"
#> [11] "age" "language" "migration" "hisced" "hisei"
#> [16] "homepos" "books" "pared" "computer_age" "internet_age"
#> [21] "int_use_a" "int_use_b" "truancy_a" "truancy_b" "truancy_c"
#> [26] "int_a" "int_b" "int_c" "int_d" "instmot_a"
#> [31] "instmot_b" "instmot_c" "instmot_d" "norms_a" "norms_b"
#> [36] "norms_c" "norms_d" "norms_e" "norms_f" "anxiety_a"
#> [41] "anxiety_b" "anxiety_c" "anxiety_d" "anxiety_e" "selfcon_a"
#> [46] "selfcon_b" "selfcon_c" "selfcon_d" "selfcon_e" "worketh_a"
#> [51] "worketh_b" "worketh_c" "worketh_d" "worketh_e" "worketh_f"
#> [56] "worketh_g" "worketh_h" "worketh_i" "intent_a" "intent_b"
#> [61] "intent_c" "intent_d" "intent_e" "behav_a" "behav_b"
#> [66] "behav_c" "behav_d" "behav_e" "behav_f" "behav_g"
#> [71] "behav_h" "teach_a" "teach_b" "teach_c" "teach_d"
#> [76] "teach_e" "cognact_a" "cognact_b" "cognact_c" "cognact_d"
#> [81] "cognact_e" "cognact_f" "cognact_g" "cognact_h" "cognact_i"
#> [86] "discpline_a" "discpline_b" "discpline_c" "discpline_d" "discpline_e"
#> [91] "relation_a" "relation_b" "relation_c" "relation_d" "relation_e"
#> [96] "belong_a" "belong_b" "belong_c" "belong_d" "belong_e"
#> [101] "belong_f" "belong_g" "belong_h" "belong_i" "attitud_a"
#> [106] "attitud_b" "attitud_c" "attitud_d" "attitud_e" "attitud_f"
#> [111] "attitud_g" "attitud_h" "grade_de" "grade_ma" "grade_bio"
#> [116] "grade_che" "grade_phy" "grade_sci"
#>
#> $PVs
#> [1] "idstud" "ma_pv1" "ma_pv2" "ma_pv3" "ma_pv4" "ma_pv5" "rea_pv1"
#> [8] "rea_pv2" "rea_pv3" "rea_pv4" "rea_pv5" "sci_pv1" "sci_pv2" "sci_pv3"
#> [15] "sci_pv4" "sci_pv5"
# Extract GADSdat objects
noImp_gads <- extractGADSdat(splitted_gads, "noImp")
pvs_gads <- extractGADSdat(splitted_gads, "PVs")For reshaping data we highly recommend the R package tidyr. Its performance might be less optimized than for example the data.table package, however it is very intuitive and user friendly. For our example data set we need to reshape the PVs from wide to long format and then separate the resulting column into two columns, containing the dimension and imputation number (imp) (Note: This results in a data set in which different dimensions for a single student are stored in separate rows, not columns). For this, we directly access the data in the GADSdat object via pvs_gads$dat. The reshaping is performed by tidyr::pivot_longer(). tidyr::separate() is used to separate our two additional identifier columns (dimension and imp). Finally, we clean the imp column and transform it to numeric.
# Extract raw data from pv gads
pvs_wide <- pvs_gads$dat
# Wide format
head(pvs_wide)
#> idstud ma_pv1 ma_pv2 ma_pv3 ma_pv4 ma_pv5 rea_pv1
#> 1 1 0.1537201 -0.0411933 0.5702895 0.01687233 0.3003968 0.4391437
#> 2 2 -0.3690980 -0.1201779 -0.2164011 -0.64099562 -0.3626861 -0.3471025
#> 3 3 1.7042239 2.2205527 1.7162633 2.78119427 2.6928097 0.8667544
#> 4 4 0.3490264 0.6069737 1.0037767 0.67002173 0.8012499 -0.7661811
#> 5 5 -0.6379547 -0.8142668 -0.6153099 -0.38015661 -0.1363339 0.1145925
#> 6 6 -1.5558856 -2.0435904 -0.7931236 -1.26866066 -1.1869012 -1.0732799
#> rea_pv2 rea_pv3 rea_pv4 rea_pv5 sci_pv1 sci_pv2
#> 1 0.01991714 1.42848870 -0.06243637 0.8371030 0.1317762 0.6783006
#> 2 0.09553654 0.49335276 0.10951613 0.6657507 -0.8650453 -0.3834589
#> 3 0.61768689 1.17497378 1.12938438 1.3001419 1.1035166 1.2730882
#> 4 0.80961068 0.09573558 -0.23817788 0.2853083 -0.3049963 0.2290473
#> 5 -0.08762244 0.06418227 0.57376133 -0.5326255 -0.8032184 -0.6878142
#> 6 -1.18496034 -0.67843740 -0.06669544 -0.5332718 -0.9191711 -1.6379850
#> sci_pv3 sci_pv4 sci_pv5
#> 1 1.46203909 0.61406429 0.4807234
#> 2 -0.54372393 -1.00303484 -0.8101605
#> 3 1.51685344 1.61485031 1.6091542
#> 4 0.18340247 -0.06804704 0.2677832
#> 5 -0.03322359 0.43998031 0.3998337
#> 6 -0.80060130 -0.43433496 -1.3110661
pvs_long1 <- tidyr::pivot_longer(pvs_wide, cols = all_of(pvs))
pvs_long2 <- tidyr::separate(pvs_long1, col = "name", sep = "_", into = c("dimension", "imp"))
pvs_long2$imp <- as.numeric(gsub("pv", "", pvs_long2$imp))
# Finale long format
head(as.data.frame(pvs_long2))
#> idstud dimension imp value
#> 1 1 ma 1 0.15372011
#> 2 1 ma 2 -0.04119330
#> 3 1 ma 3 0.57028949
#> 4 1 ma 4 0.01687233
#> 5 1 ma 5 0.30039680
#> 6 1 rea 1 0.43914365After reshaping we adapt the meta data in our initial GADSdat object via updateMeta(). This is necessary, as variables have been removed from the data set (e.g. "ma_pv1" etc.) and new variables have replaced them ("value", "dimension", "imp"). Now we have to add some variable labels, as most of the old variable labels got lost due to the reshaping. For an extensive tutorial see the vignette Handling Meta Data.
pvs_gads2 <- updateMeta(pvs_gads, newDat = as.data.frame(pvs_long2))
#> Removing the following rows from meta data: ma_pv1, ma_pv2, ma_pv3, ma_pv4, ma_pv5, rea_pv1, rea_pv2, rea_pv3, rea_pv4, rea_pv5, sci_pv1, sci_pv2, sci_pv3, sci_pv4, sci_pv5
#> Adding meta data for the following variables: dimension, imp, value
extractMeta(pvs_gads2)
#> varName varLabel format display_width labeled value valLabel
#> 1 idstud Student-ID F8.0 NA no NA <NA>
#> dimension dimension <NA> <NA> NA no NA <NA>
#> imp imp <NA> <NA> NA no NA <NA>
#> value value <NA> <NA> NA no NA <NA>
#> missings
#> 1 <NA>
#> dimension <NA>
#> imp <NA>
#> value <NA>
#
pvs_gads2 <- changeVarLabels(pvs_gads2, varName = c("dimension", "imp", "value"),
varLabel = c("Achievement dimension (math, reading, science)",
"Number of imputation of plausible values",
"Plausible Value"))
extractMeta(pvs_gads2)
#> varName varLabel format
#> 1 idstud Student-ID F8.0
#> dimension dimension Achievement dimension (math, reading, science) <NA>
#> imp imp Number of imputation of plausible values <NA>
#> value value Plausible Value <NA>
#> display_width labeled value valLabel missings
#> 1 NA no NA <NA> <NA>
#> dimension NA no NA <NA> <NA>
#> imp NA no NA <NA> <NA>
#> value NA no NA <NA> <NA>For the creation of a relational data base we recreate the initial hierarchical structure via mergeLabels() (which performs the reverse action as extractGADS()). Furthermore, we create two lists, a primary key list (pkList) and a foreign key list (fkList). Primary keys are the variables that uniquely identify each row within every hierarchy level. Foreign keys are the variables that allow merging between different hierarchy levels. In the list of foreign keys we also have to specify another hierarchy level, to which each hierarchy level connects. An exception is the lowest hierarchy levels, which serves as a basis.
By setting up the order and the foreign keys of the data base we specify how the data is merged together when we extract data from it. In contrast to conventional relational data bases, eatGADS data bases are less flexible: The package does not support modifying the data base after its creation or extracting data from it with different merges than specified when it was set up.
merged_gads <- mergeLabels(noImp = noImp_gads, PVs = pvs_gads2)
pkList <- list(noImp = "idstud",
PVs = c("idstud", "imp", "dimension"))
fkList <- list(noImp = list(References = NULL, Keys = NULL),
PVs = list(References = "noImp", Keys = "idstud"))Finally, we create the relational data base on disc via the createGADS() function.
temp_path <- paste0(tempfile(), ".db")
createGADS(merged_gads, pkList = pkList, fkList = fkList,
filePath = temp_path)
#> NULLFor a detailed tutorial on how to use a relational eatGADS data base see the vignette getGADS: Using a relational eatGADS data base.