Covid Infection Dates

library(tidyverse)

## Warning: Paket 'tidyverse' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'ggplot2' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'tibble' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'tidyr' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'readr' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'purrr' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'dplyr' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'stringr' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'forcats' wurde unter R Version 4.4.1 erstellt

## Warning: Paket 'lubridate' wurde unter R Version 4.4.2 erstellt

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.1     ✔ tibble    3.2.1
## ✔ lubridate 1.9.4     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(readxl)

## Warning: Paket 'readxl' wurde unter R Version 4.4.2 erstellt

library(haven)

## Warning: Paket 'haven' wurde unter R Version 4.4.1 erstellt

library(writexl)

## Warning: Paket 'writexl' wurde unter R Version 4.4.2 erstellt

1.Duplicated infection dates in COVID Update data FU1

dt<- readxl::read_excel("C:/Users/mil7490mat/Desktop/Milica/Long Covid/F1/Data Preparation/Excel_Data/PV0808_T01591_NODUP.xlsx")
df_LC_FU1 <- dt %>%
  select(SIC , LC_FU1_CU_DATUM, LC_FU1_CU_F0009, LC_FU1_CU_F0010, LC_FU1_CU_F0011, LC_FU1_CU_F0079, LC_FU1_CU_F0147)

df_LC_FU1 <- df_LC_FU1 %>%
  rename(
    Datum = LC_FU1_CU_DATUM,
    Ja_Nein = LC_FU1_CU_F0009,
    Wie_viele = LC_FU1_CU_F0010,
    Erste_Datum = LC_FU1_CU_F0011, 
    Zweite_Datum = LC_FU1_CU_F0079, 
    Dritte_Datum = LC_FU1_CU_F0147)

df_LC_FU1 <- df_LC_FU1 %>%
  mutate_at(vars(contains("Datum")), as.Date)

## Ausfüll Datum in Erste, Zweite und Dritte zum NA
df_LC_FU1[, c("Erste_Datum", "Zweite_Datum", "Dritte_Datum")] <- lapply(
  df_LC_FU1[, c("Erste_Datum", "Zweite_Datum", "Dritte_Datum")],
  function(col) as.Date(ifelse(col == df_LC_FU1$Datum, NA, col), origin = "1970-01-01")
)
## Other possible duplicates
df_LC_FU1$duplicates <- apply(df_LC_FU1[, 2:ncol(df_LC_FU1)], 1, function(x) {
  anyDuplicated(x[!is.na(x)]) > 0
})

df_duplicates <- df_LC_FU1 %>%
  filter(duplicates == TRUE)

df_LC_FU1$Dritte_Datum[df_LC_FU1$SIC == "E3A812E3A9"] <- NA

## Clean dates from COVID Update FU1
df_LC_FU1 <- df_LC_FU1 %>%
  select(-duplicates)

2.Dates of the first Infection

link to the data: [https://mia.nl.tab.digital/s/P5igrdQBzcTXb5j]

This data for the overwrite has no script as the decision were made for each case separately by Frau Zeynalova considering all available data from LC and LIFE-Adult based on the agreement made on the Long COVID Runde and Long COVID Auswertung Runde

spss_data <- read_sav("C:/Users/mil7490mat/Desktop/Milica/Long Covid/Daten Kontrolle_LongCovid/Erste_COVID_Infektion_Datum.sav")

all_data_tab <- inner_join(df_LC_FU1, spss_data, by = "SIC")

## Merge the tables by SIC
all_data_tab$Erste_Datum <- as.Date(all_data_tab$Erste_Datum, format = "%Y-%m-%d")
all_data_tab$result <- ifelse(
  is.na(all_data_tab$Erste_Datum) | all_data_tab$Erste_Datum == 0 |
    is.na(all_data_tab$Datum_COVID) | all_data_tab$Datum_COVID == 0,
  FALSE, 
  all_data_tab$Erste_Datum == all_data_tab$Datum_COVID  
)
## Comparison of the first COVID infection date from FU1 and proofed dates
aresame <- all(all_data_tab$result, na.rm = TRUE)
if (aresame) {
  print(TRUE)
} else {
  print(FALSE)
}

## [1] FALSE

inf_dates_false <- all_data_tab %>%
  filter(result == FALSE)

## Overwrite date of the first covid from FU1 with the data from proofed table
df_LC_FU1$Erste_Datum[df_LC_FU1$SIC %in% inf_dates_false$SIC] <- 
  ifelse(
    is.na(inf_dates_false$Datum_COVID[match(df_LC_FU1$SIC[df_LC_FU1$SIC %in% inf_dates_false$SIC], inf_dates_false$SIC)]) |
      inf_dates_false$Datum_COVID[match(df_LC_FU1$SIC[df_LC_FU1$SIC %in% inf_dates_false$SIC], inf_dates_false$SIC)] == 0,
    NA,
    inf_dates_false$Datum_COVID[match(df_LC_FU1$SIC[df_LC_FU1$SIC %in% inf_dates_false$SIC], inf_dates_false$SIC)]
  )
## Save of data as a new table frame
Infektiondaten <- df_LC_FU1 %>%
  select(-Datum)

3.Comparation COvid Update FU1 data to Screening TO1575 data

tab_3 <- readxl::read_excel("C:/Users/mil7490mat/Desktop/Milica/Long Covid/DATEN/Pre LC Basis/PV0808_T01575_NODUP.xlsx")

tab_3_ss <- tab_3 %>%
  select(PSEUDONYM , LC2_SB_F0012, LC2_SB_F0013, LC2_SB_F0014, LC2_SB_F0015, LC2_SB_F0016, 
         LC2_SB_F0030, LC2_SB_F0031, LC2_SB_F0032, LC2_SB_F0046, LC2_SB_F0047, LC2_SB_F0048)

## forming the month-year date format from the corresponding columns
tab_3_ss$first_inf <- ifelse(
  is.na(tab_3_ss$LC2_SB_F0015) | is.na(tab_3_ss$LC2_SB_F0016),
  NA,                                  
  paste(tab_3_ss$LC2_SB_F0016, sprintf("%02d", tab_3_ss$LC2_SB_F0015), sep = "-")
)

tab_3_ss$sec_inf <-  ifelse(
  is.na(tab_3_ss$LC2_SB_F0032) | is.na(tab_3_ss$LC2_SB_F0031), 
  NA,                            
  paste(tab_3_ss$LC2_SB_F0032, sprintf("%02d", tab_3_ss$LC2_SB_F0031), sep = "-")
)

tab_3_ss$thrid_inf <-  ifelse(
  is.na(tab_3_ss$LC2_SB_F0048) | is.na(tab_3_ss$LC2_SB_F0047),
  NA,                            
  paste(tab_3_ss$LC2_SB_F0048, sprintf("%02d", tab_3_ss$LC2_SB_F0047), sep = "-")
)

## rename of the ID column
colnames(tab_3_ss)[1] <- "SIC"

## data merge
joined_data <- inner_join(Infektiondaten, tab_3_ss, by = "SIC")

joined_data$LC_first_inf <- format(as.Date(joined_data$Erste_Datum), "%Y-%m")

## 1st Inf dates
joined_data$result1 <- (joined_data$LC_first_inf == joined_data$first_inf)
aresame1 <- all(joined_data$result1)
if (aresame1) {
  print(TRUE)
} else {
  print(FALSE)
}

## [1] FALSE

first_inf_dates_false <- joined_data %>%
  filter(result1 == FALSE)

## 2nd Inf
joined_data$LC_sec_inf <- format(as.Date(joined_data$Zweite_Datum), "%Y-%m")
joined_data$result2 <- (joined_data$LC_sec_inf == joined_data$sec_inf)
aresame2 <- all(joined_data$result2)
if (aresame2) {
  print(TRUE)
} else {
  print(FALSE)
}

## [1] FALSE

sec_inf_dates_false <- joined_data %>%
  filter(result2 == FALSE)

## 3rd Inf
joined_data$LC_third_inf <- format(as.Date(joined_data$Dritte_Datum), "%Y-%m")
joined_data$result3 <- (joined_data$LC_third_inf == joined_data$thrid_inf)
aresame3 <- all(joined_data$result3)
if (aresame3) {
  print(TRUE)
} else {
  print(FALSE)
}

## [1] FALSE

third_inf_dates_false <- joined_data %>%
  filter(result3 == FALSE)

4.Overwirte second and third COVID infection dates with the dates from Screening TO1575

when the comparison result indicated FALSE, the dates that participants provided earlier in T01575 are taken

## Second infection
tab_3_ss$sec_inf_dmy <- ifelse(
  is.na(tab_3_ss$LC2_SB_F0030) | is.na(tab_3_ss$LC2_SB_F0031) | is.na(tab_3_ss$LC2_SB_F0032),
  NA,                            
  paste(sprintf("%02d", ifelse(is.na(tab_3_ss$LC2_SB_F0030) | tab_3_ss$LC2_SB_F0030 == 0, 1, tab_3_ss$LC2_SB_F0030)), 
        sprintf("%02d", tab_3_ss$LC2_SB_F0031), 
        tab_3_ss$LC2_SB_F0032, 
        sep = "-")
)
### filter the SIC values from the sec_inf_dates_false table
sic_values_sec <- c("7C23077C24", "ECD524EF12", "8591C5A31C", "205FD9B562", 
                    "E4A885613C", "7555243C3E", "B172C43BB3", "AF94FF83F9", 
                    "65EC94336C", "13678F54F4", "13139A1430", "C4603F50E2", 
                    "5B4216C7C6", "255F9B0929", "BA67E4C266", "DE11DEBA9A")

sec_inf_dates_correct <- tab_3_ss[tab_3_ss$SIC %in% sic_values_sec, ]

sec_inf_dates_correct$sec_inf_dmy <- as.POSIXct(sec_inf_dates_correct$sec_inf_dmy, 
                                                format = "%d-%m-%Y", 
                                                tz = "UTC")
### overwrite the dates of the second infection
Infektiondaten$Zweite_Datum[Infektiondaten$SIC %in% sec_inf_dates_correct$SIC] <- sec_inf_dates_correct$sec_inf_dmy[match(Infektiondaten$SIC[Infektiondaten$SIC %in% sec_inf_dates_correct$SIC], sec_inf_dates_correct$SIC)]

## Third Infection Dates
tab_3_ss$third_inf_dmy <- ifelse(
  is.na(tab_3_ss$LC2_SB_F0046) | is.na(tab_3_ss$LC2_SB_F0047) | is.na(tab_3_ss$LC2_SB_F0048),
  NA,                            
  paste(sprintf("%02d", ifelse(is.na(tab_3_ss$LC2_SB_F0046) | tab_3_ss$LC2_SB_F0046 == 0, 1, tab_3_ss$LC2_SB_F0046)), 
        sprintf("%02d", tab_3_ss$LC2_SB_F0047), 
        tab_3_ss$LC2_SB_F0048, 
        sep = "-")
)
### filter the SIC values from the third_inf_dates_false table
sic_values_third_Inf <- c("0EF4903E77", "65EC94336C", "C4603F50E2")

third_inf_dates_correct <- tab_3_ss[tab_3_ss$SIC %in% sic_values_third_Inf, ]
third_inf_dates_correct$third_inf_dmy <- as.POSIXct(third_inf_dates_correct$third_inf_dmy, 
                                                    format = "%d-%m-%Y", 
                                                    tz = "UTC")
### overwrite the dates of the third infection
Infektiondaten$Dritte_Datum[Infektiondaten$SIC %in% third_inf_dates_correct$SIC] <- third_inf_dates_correct$third_inf_dmy[match(Infektiondaten$SIC[Infektiondaten$SIC %in% third_inf_dates_correct$SIC], third_inf_dates_correct$SIC)]

5.Check of the time consistancy within the first, second and third COVID infection dates

## 1st infection to 2nd infection
Infektiondaten$res1 <- (Infektiondaten$Erste_Datum < Infektiondaten$Zweite_Datum)
first_before <- all(Infektiondaten$res1, na.rm = TRUE)
if (first_before) {
  (TRUE)
} else {
  (FALSE)
}

## [1] FALSE

View(Infektiondaten)
sic_to_modify_res1 <- c("00013C9FC9", "6551E6A73E", "A7B97C99C6", "D6996FA630", "E8425754C3")

## remove of FALSE values (dates of the second infection which are greater of the first infection date)
Infektiondaten <- Infektiondaten %>%
  mutate(Zweite_Datum = case_when(
    SIC %in% sic_to_modify_res1 ~ as.Date(NA),
    TRUE ~ Zweite_Datum
  ))

## 1st Infection to 3rd Infection
Infektiondaten$res2 <- (Infektiondaten$Erste_Datum < Infektiondaten$Dritte_Datum)
first_before3 <- all(Infektiondaten$res2, na.rm = TRUE)
if (first_before3) {
  (TRUE)
} else {
  (FALSE)
}

## [1] TRUE

## 2st Infection to 3rd Infection
Infektiondaten$res3 <- (Infektiondaten$Zweite_Datum < Infektiondaten$Dritte_Datum)
sec_before3 <- all(Infektiondaten$res3, na.rm = TRUE)
if (sec_before3) {
  (TRUE)
} else {
  (FALSE)
}

## [1] FALSE

## remove of FALSE values
sic_to_modify_res3 <- c("2B799FDEBD")
Infektiondaten <- Infektiondaten %>%
  mutate(Dritte_Datum = case_when(
    SIC %in% sic_to_modify_res3 ~ as.Date(NA),
    TRUE ~ Dritte_Datum
  ))

## remove of the columns with the comparation results
Infektiondaten <- Infektiondaten %>%
  select(-res1, -res2, -res3)

6.Save the data

write_xlsx(Infektiondaten, "H:/LongCOVID/Infektiondaten.xlsx")
save(Infektiondaten, file ="Infektiondaten.RData")

The link to the final data is [https://mia.nl.tab.digital/s/HRxcpqDPZDLecwq]