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.
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.
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 saved 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.