Converting metadata for dbgap!

1 Introduction

Najib asked for an initial conversion of the data provided by Maria Adelaida into the various formats expected by dbgap. He sent me two sheets provided by her: “20210115_EXP_ESPECIAL_TMRC3.xlsx” and “CODEBOOK_EXP_ESPECIAL_TMRC3_20210115.xlsx”. The first provides the data and the second for formatting.

In addition, we will require some of the data in the shared sample sheet.

With that in mind, here are our inputs: 1. The metadata sheet of patients. 2. The meta metadata sheet, the ‘codebook’. 3. The tmrc3 sample sheet. I am likely to add a worksheet to it to contain the meta metadata.

Here are the expected outputs: 1. SubjectConsent_DS.txt: Tab separated, containing SUBJECT_ID, CONSENT, SEX, SUBJECT_SOURCE, and SUBJECT_SOURCE_ID In my current setup, this is only the patient ID, 1, sex (1,2,NA or whatever), TMRC3, and the patient ID repeated. 2. SSM_DS.txt: Tab separated, containing SUBJECT_ID and SAMPLE_ID. This is the cross reference between the sample sheet and patient IDs, so it will require some sort of merge between the tmrc3 sample sheet and the EXP_ESPECIAL worksheet. 3. SubjectPhenotypes_DS.txt: This is pretty much everything else from the EXP_ESPECIAL sheet with at least the first column needing to be renamed to ‘SUBJECT_ID’ and probably the birthday removed. 4. SampleAttributes_DS.txt: This is pretty much everything else from the sample sheet. A bunch of columns will need to be removed. 5. SubjectConsent_DD.xlsx: xlsx file, the meta-metadata for #1 above. I think we can just create this de-novo and leave it unmodified because it is quite small. 6. SSM_DD.xlsx: Ditto. This just explains the columns from #2 above and is therefore even smaller. 7. SubjectPhenotypes_DD.xlsx: This is the CODEBOOK from above and will likely need some changes, but I think they are pretty minor. 8. SampleAttributes_DD.xlsx: I want to add this to the tmrc3 sample sheet and use it to define which columns to pull from it.

2 Reading existing data

rundate <- format(Sys.Date(), format = "%Y%m%d")
phenotype_file <- "inputs/202401/20220721_EXP_ESPECIAL_TMRC3_V3.xlsx"
phenotype_meta <- "inputs/202401/CODEBOOK_EXP_ESPECIAL_TMRC3_VERSION_3_20220512.xlsx"
sample_file <- "inputs/202401/tmrc3_samples_pruned.xlsx"
sample_meta <- "templates/SampleAttributes_DD.xlsx"

subject_phenotypes <- openxlsx::read.xlsx(phenotype_file)
subject_meta <- openxlsx::read.xlsx(phenotype_meta)
sample_attributes <- openxlsx::read.xlsx(sample_file)
sample_meta <- openxlsx::read.xlsx(sample_meta)

created <- dir.create(glue("outputs/{rundate}"))

## Warning in dir.create(glue("outputs/{rundate}")): 'outputs/20240208' already exists

3 Sanitize column names slightly.

There are a few things which are required by dbGap which I can trivially change here.

## Sanitize the column names a little.
colnames(subject_phenotypes) <- toupper(colnames(subject_phenotypes))

subject_meta[["VARNAME"]] <- toupper(subject_meta[["VARNAME"]])

colnames(sample_attributes) <- toupper(colnames(sample_attributes))
colnames(sample_attributes) <- gsub(pattern = "\\.+",
                                    replacement = "_",
                                    x = colnames(sample_attributes))
colnames(sample_attributes) <- gsub(pattern = "\\)|\\(|\\[|\\]|,|-|/|%|'|´|\\s+",
                                    replacement = "",
                                    x = colnames(sample_attributes),
                                    perl = TRUE)
subject_colname_substitutions <- list(
    "CODIGO_PACIENTE" = "SUBJECT_ID",
    "EB_LC_SEXO" = "SEX",
    "TUBE_LABEL_ORIGIN" = "SUBJECT_ID",
    "SAMPLE_NAME" = "SAMPLE_ID",
    "TMRC_IDENTIFIER" = "TMRC_ID"
)
for (i in 1:length(subject_colname_substitutions)) {
  from <- names(subject_colname_substitutions)[i]
  to <- subject_colname_substitutions[[i]]
  colnames(subject_phenotypes) <- gsub(pattern = from, replacement = to,
                                       x = colnames(subject_phenotypes))
  colnames(sample_attributes) <- gsub(pattern = from, replacement = to,
                                      x = colnames(sample_attributes))
  subject_meta[["VARNAME"]] <- gsub(pattern = from, replacement = to,
                                    x = subject_meta[["VARNAME"]])
}

4 Sanity check

Now that I have made some changes to the metadata, let us attempt to make certain that they still match. This primarily refers to the column names of the subject_phenotype file and the VARNAME column from the subject metadata file.

misses <- subject_meta[["VARNAME"]] != colnames(subject_phenotypes)
summary(misses)

##    Mode   FALSE 
## logical      63

if (sum(misses) > 0) {
  stop("There is a mismatch between the metadata and column names.")
}

5 Filter out samples which were not sequenced.

We only want those samples for which we have extant TMRC IDs. Thus, filter the sample_attributes for the samples that have been given tmrc IDs for now.

useful_idx <- !is.na(sample_attributes[["TMRC_ID"]])
sample_attributes <- sample_attributes[useful_idx, ]

6 Create the SubjectConsent_DS.txt

Creating the SubjectConsent_DS.txt should be the easiest, just pull the ID and SEX columns and fill in the rest.

SubjectConsent_DS <- subject_phenotypes[, c("SUBJECT_ID", "SEX")]
SubjectConsent_DS[["CONSENT"]] <- 1
SubjectConsent_DS[["SUBJECT_SOURCE"]] <- "TMRC3"
SubjectConsent_DS[["SUBJECT_SOURCE_ID"]] <- SubjectConsent_DS[["SUBJECT_ID"]]
## And reorder it
column_order <- c("SUBJECT_ID", "CONSENT", "SEX", "SUBJECT_SOURCE", "SUBJECT_SOURCE_ID")
SubjectConsent_DS <- SubjectConsent_DS[, column_order]
readr::write_tsv(x = SubjectConsent_DS,
                 file = glue("outputs/{rundate}/{rundate}-SubjectConsent_DS.txt"))

7 Create the SubjectConsent_DD.xlsx

This file should be unchanged from the template, so we will read it in and immediately write it back out with a blank line in between in case we do in fact need to change something later.

SubjectConsent_DD <- openxlsx::read.xlsx("templates/SubjectConsent_DD.xlsx")


openxlsx::write.xlsx(x = SubjectConsent_DD,
                     file = glue("outputs/{rundate}/{rundate}-SubjectConsent_DD.xlsx"))

8 Create the SSM_DS.txt

The SSM_DS.txt will be more difficult, it requires a merge between sheets…

sample_attributes[["SUBJECT_ID"]] <- gsub(pattern = "^su", replacement = "SU",
                                          x = sample_attributes[["SUBJECT_ID"]])
SSM_DS <- merge(SubjectConsent_DS, sample_attributes, by = "SUBJECT_ID")
wanted_columns <- c("SUBJECT_ID", "SAMPLE_ID")
SSM_DS <- SSM_DS[, wanted_columns]
readr::write_tsv(x = SSM_DS, file = glue("outputs/{rundate}/{rundate}-SSM_DS.txt"))

9 Create the SSM_DD.xlsx

Once again, we will assume that copying this from the template will prove sufficient; but will perform explicit read/write steps in case we need to change anything.

SSM_DD <- openxlsx::read.xlsx("templates/SSM_DD.xlsx")


openxlsx::write.xlsx(x = SSM_DD, file = glue("outputs/{rundate}/{rundate}-SSM_DD.xlsx"))

10 Create the SubjectPhenotypes_DD

Normally, I would move the english-translated columns to the defaults; but we do not have a translated version of this at this time.

## I do not think I need to do anything else to the metadata file at this time.
SubjectPhenotypes_DD <- subject_meta

10.1 Juggle the columns

I cannot run the following block at this time because the current file has not been translated.

## We will need to more VARNAME_ENG to VARNAME
SubjectPhenotypes_DD[["VARNAME"]] <- SubjectPhenotypes_DD[["VARNAME_ENG"]]
SubjectPhenotypes_DD[["VARNAME_ENG"]] <- NULL
## Ditto for VARDESC and TYPE
SubjectPhenotypes_DD[["VARDESC"]] <- SubjectPhenotypes_DD[["VARDESC_ENG"]]
SubjectPhenotypes_DD[["VARDESC_ENG"]] <- NULL
SubjectPhenotypes_DD[["TYPE"]] <- SubjectPhenotypes_DD[["TYPE_ENG"]]
SubjectPhenotypes_DD[["TYPE_ENG"]] <- NULL
## Having done that, we need to set the column names of the SubjectPhenotypes_DS to the new
## VARNAME column.

11 Write out the SubjectPhenotypes_DD

starting_ds_colnames <- SubjectPhenotypes_DD[["VARNAME"]]
openxlsx::write.xlsx(x = SubjectPhenotypes_DD,
                     file = glue("outputs/{rundate}/{rundate}-SubjectPhenotypes_DD.xlsx"))

12 Create the SubjectPhenotypes_DS

The SubjectPhenotypes_DS is just the original subject_phenotypes with some columns blacklisted.

subject_phenotypes_blacklist <- c("EB_LC_FECHA_NACIMIENTO", "SEX")
SubjectPhenotypes_DS <- subject_phenotypes
colnames(SubjectPhenotypes_DS) <- starting_ds_colnames
for (i in subject_phenotypes_blacklist) {
  SubjectPhenotypes_DS[[i]] <- NULL
  meta_keepers <- subject_meta[["VARNAME"]] != i
  subject_meta <- subject_meta[meta_keepers, ]
}
readr::write_tsv(x = SubjectPhenotypes_DS,
                 file = glue("outputs/{rundate}/{rundate}-SubjectPhenotypes_DS.txt"))

13 Create the SampleAttributes_DS

The SampleAttributes_DS will require some more work: 1. Read in the SampleAttributes_DD and keep only the columns defined in it. 2. Recast the data to ensure they are pure text.

Currently that is all we are doing, so it doesn’t really require very much.

I do need to change the column ‘FINAL_OUTCOME’ to ‘CLINICAL_OUTCOME’

colnames(sample_attributes) <- gsub(x = colnames(sample_attributes), pattern = "FINAL_OUTCOME",
                                    replacement = "CLINICAL_OUTCOME")
SampleAttributes_DS <- sample_attributes
kept_columns <- sample_meta[["VARNAME"]]

kept_columns[! kept_columns %in% colnames(SampleAttributes_DS)]

## character(0)

SampleAttributes_DS <- SampleAttributes_DS[, kept_columns]
for (i in 1:ncol(SampleAttributes_DS)) {
  SampleAttributes_DS[[i]] <- as.character(SampleAttributes_DS[[i]])
}
readr::write_tsv(x = SampleAttributes_DS,
                 file = glue("outputs/{rundate}/{rundate}-SampleAttributes_DS.txt"))

14 Write the SampleAttributes_DD

Once again, the DD file is expected to be identical to our template.

SampleAttributes_DD <- sample_meta
openxlsx::write.xlsx(x = SampleAttributes_DD,
                     file = glue("outputs/{rundate}/{rundate}-SampleAttributes_DD.xlsx"))