insightsengineering / insightsengineering/teal.transform
Allow to customize merge keys
- Dominant language
- R
- Stars
- 2
- Forks
- 3
- PR merge metrics
- No merged PRs in 30d
Description
# Problem description
[Design of data_extract/data_merge](https://nest.pages.roche.com/docs/agile-R/teal_module_development/data_extract/) is as follows. Consider columns taken from datasets, filtered separately and merged all together.

Above can be express in the following code
```r
library(scda)
library(dplyr)
ADSL <- synthetic_cdisc_data("latest")$adsl
ADTTE <- synthetic_cdisc_data("latest")$adtte
ANL1 <- ADTTE %>% filter(PARAMCD == "CRSD") %>% select(STUDYID, USUBJID, AVAL)
ANL2 <- ADSL %>% select(STUDYID, USUBJID, AGE)
ANL <- left_join(ANL1, ANL2, by = c("STUDYID", "USUBJID"))
# STUDYID USUBJID AVAL AGE
#
# 1 AB12345 AB12345-BRA-1-id-105 1274. 38
# 2 AB12345 AB12345-BRA-1-id-134 545. 47
# 3 AB12345 AB12345-BRA-1-id-141 77.2 35
# 4 AB12345 AB12345-BRA-1-id-236 336. 32
# 5 AB12345 AB12345-BRA-1-id-265 270. 25
```
Merge module supports multiple scenarios and keys management is the "key" here. Consider following scenario where we have a two selectors to compare specific AVAL level from the same dataset across different factor values - for example AVAL increase between two visits
```r
ADLB <- synthetic_cdisc_data("latest")$adlb
keys <- c("STUDYID", "USUBJID", "PARAMCD", "AVISITN")
ANL1 <- ADLB %>%
filter(PARAMCD == "CRP" & AVISITN == "-1") %>%
select(keys, -PARAMCD, -AVISITN, AVAL1 = AVAL)
ANL2 <- ADLB %>%
filter(PARAMCD == "CRP" & AVISITN == "0") %>%
select(keys, -PARAMCD, -AVISITN, AVAL2 = AVAL)
ANL <- left_join(ANL1, AN2, by = c("STUDYID", "PARAMCD"))
```
In above example, PARAMCD and AVISITN in both selectors have just one value which means that it's no longer the key for these ANLs as they were filtered-out (does not distinguish the rows). The reason to drop these keys is that it wouldn't be possible if `by = c("USUBJID", "STUDYID", "PARAMCD", "AVISITN")` because they have a different values and it's not possible to merge them together.
For `scda` data we have no problem with this as we have a static data and keys are relevant for our test data. Problems starts when SMEs create their own apps which are using real data. The keys used to be different than [default teal keys](https://github.com/insightsengineering/teal/blob/main/inst/cdisc_datasets/cdisc_datasets.yaml), but the keys follow GDSR requirements. For example ADLB keys are `STUDYID USUBJID PARAMCD BASETYPE AVISITN ATPTN DTYPE ADTM LBSEQ ASPID` vs teal `STUDYID USUBJID PARAMCD AVISIT`. SMEs [require](insightsengineering/teal.data#38) the change in the keys to fit GDSR standards which complicates the design little bit.
If we go back to the last example which is using `ADLB`. Extending set of keys will result in joining by keys which have not been filtered-out. This means that ANLs will be joined by `"STUDYID", "USUBJID", "ATPTN", "DTYPE", "ADTM", "LBSEQ", "ASPID"` if we put only `PARAMCD` and `AVISITN` as a `filter_spec`.
```r
ADLB <- synthetic_cdisc_data("latest")$adlb
keys <- c("STUDYID", "USUBJID", "PARAMCD", "AVISITN", "ATPTN", "DTYPE", "ADTM", "LBSEQ", "ASPID")
ANL1 <- ADLB %>%
filter(PARAMCD == "CRP" & AVISITN == "-1") %>%
select(keys, -PARAMCD, -AVISITN, AVAL1 = AVAL)
ANL2 <- ADLB %>%
filter(PARAMCD == "CRP" & AVISITN == "0") %>%
select(keys, -PARAMCD, -AVISITN, AVAL2 = AVAL)
ANL <- inner_join(ANL1, ANL2, by = c("STUDYID", "USUBJID", "ATPTN", "DTYPE", "ADTM", "LBSEQ", "ASPID"))
```
Changing the keys only will result in:
1. Current apps which are created with `PARAMCD` and/or `AVISIT` `filter_spec` might fail because output ANL will be merged on a columns which have a different values across the subjects. One can run above code and see that the result of inner_join is empty data.frame. Also our problem is that GDSR keys are not the same what we think they are. Some of the keys in the datasets does not make any row unique adding them - which means that for us they are just values. For example `AVISITN` is a number of a patient visit while `ADTM` can be a data of the visit. This means that adding `ADTM` to the keys does not make any row unique, because `STUDYID`, `USUBJID`, `PARAMCD` and `AVISITN` make them unique already.
```r
ADLB %>%
group_by(STUDYID, USUBJID, PARAMCD, AVISIT) %>%
summarize(n = n()) %>%
.$n
```
Above is not the case of our sample datasets only, but might occur also in real data [see the issue](https://github.roche.com/NEST/teal/issues/1001#issuecomment-77768).
2. In case of merging selectors from the same dataset we need to add filter_spec for all of them with `drop_keys = TRUE` to avoid cartesian join.
Because keys/data_merge have been problematic since introduction, we need to simplify the way to handle them before reaching wider users base.
# Proposition
Change the keys in `teal` according to GDSR and implement merge module to visualize the join keys.
See a [good example]](https://help.tableau.com/current/pro/desktop/en-us/joining_tables.htm)

Otherwise if we want to keep tea.devel as is we need to fix the documentation to visualize multiple scenarios like:
- multiple single datasets
- reshaping
- mixing datasets
- drop_keys = TRUE consequences
Current documentation in my opinion is not sufficient.
Contributor guide
Assessment
This issue has not been assessed yet.