TRUST Principles for SARS-CoV-2 digital repositories: a case study of GISAID and the Covid-19 Data Portal

---

TRUST Principles

“As information and communication technology has become pervasive in our society, we are increasingly dependent on both digital data and repositories that provide access to and enable the use of such resources. Repositories must earn the trust of the communities they intend to serve and demonstrate that they are reliable and capable of appropriately managing the data they hold.” (Lin et al. 2020)

Transparency

• To be transparent about specific repository services and data holdings that are verifiable by publicly accessible evidence.

Responsibility

• To be responsible for ensuring the authenticity and integrity of data holdings and for the reliability and persistence of its service.

User Focus

• To ensure that the data management norms and expectations of target user communities are met.

Sustainability

• To sustain services and preserve data holdings for the long-term.

Technology

• To provide infrastructure and capabilities to support secure, persistent, and reliable services.

Data Cleaning and Transformation

#################################################################
##                           Library                           ##
#################################################################
pkgs = c("tidyverse",
         "ddplot",
         "countrycode",
         "showtext",
         "ggpubr")
installed_pkgs = pkgs %in% rownames(installed.packages())
if (any(installed_pkgs == FALSE)) {
  install.packages[!installed_pkgs]
}
invisible(lapply(pkgs, library, character.only = TRUE))
rm(installed_pkgs, pkgs)
font_add_google("Ubuntu", "ub")
##################################################################
##                       Helper Functions                       ## thanx for helpin
##################################################################
fetch_data = function(url,path) {
 url = url
 path = path
 download.file(url,path)
 read.csv(path)
}
dark_theme = function() {
  theme(
    # add border 1)
    panel.border = element_rect(
      colour = "slategrey",
      fill = NA,
      linetype = 2
    ),
    # color background 2)
    panel.background = element_rect(fill = "white"),
    # modify grid 3)
    panel.grid.major.x = element_line(
      colour = "#cf2e2e",
      linetype = 3,
      size = 0.5
    ),
    panel.grid.minor.x = element_blank(),
    panel.grid.minor.y = element_blank(),
    panel.grid.major = element_blank(),
    panel.grid.minor = element_blank(),
    # modify text, axis and colour 4) and 5)
    axis.text = element_text(
      colour = "white",
      face = "italic",
      family = "sans"
    ),
    axis.title = element_text(colour = "white", family = "sans"),
    axis.ticks = element_line(colour = "white"),
    plot.background = element_rect(fill = "#cf2e2e"),
    plot.title = element_text(family = "sans", hjust = .5, size = 16),
    plot.subtitle = element_text(family = "sans", hjust = .5, size = 12),
    legend.background = element_rect(fill = "#cf2e2e"),
    legend.text  = element_text(color = "white", family = "sans", size = 10),
    legend.key = element_rect(fill = "#cf2e2e"),
    # legend at the bottom 6)
    legend.position = "bottom"
  )
}
wss = function(k) {
  kmeans(b, k, nstart = 10 )$tot.withinss
}
options(scipen=999)
##########################################################################
##  Download latest John Hopkins global Covid-19 cases & vaccines data  ##
##########################################################################
##### CASES
jh_global_covid = fetch_data(url = "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv",
           path = "data/covid-jh.csv" ## fetch latest github data
           ) %>%
  select(-c(Lat, Long)) %>% # be gone lat long
  # select(-1) %>%
  rename(Country = 2) %>%
  mutate(Country = ifelse(Province.State == "French Guiana" & Country == "France", "French Guiana", Country)) %>%
  mutate(Country = ifelse(Province.State == "French Polynesia" & Country == "France", "French Polynesia", Country)) %>%
  mutate(Country = ifelse(Province.State == "Guadeloupe" & Country == "France", "Guadeloupe", Country)) %>%
  mutate(Country = ifelse(Province.State == "Martinique" & Country == "France", "Martinique", Country)) %>%
  mutate(Country = ifelse(Province.State == "Mayotte" & Country == "France", "Mayotte", Country)) %>%
  mutate(Country = ifelse(Province.State == "New Caledonia" & Country == "France", "New Caledonia", Country)) %>%
  mutate(Country = ifelse(Province.State == "Reunion" & Country == "France", "Reunion", Country)) %>%
  mutate(Country = ifelse(Province.State == "Saint Barthelemy" & Country == "France", "Saint Barthelemy", Country)) %>%
  mutate(Country = ifelse(Province.State == "Saint Pierre and Miquelon" & Country == "France", "Saint Pierre and Miquelon", Country)) %>%
  mutate(Country = ifelse(Province.State == "St Martin" & Country == "France", "St Martin", Country)) %>%
  mutate(Country = ifelse(Province.State == "Wallis and Futuna" & Country == "France", "Wallis and Futuna", Country)) %>%
  mutate(Country = ifelse(Province.State == "Curacao" & Country == "Netherlands", "Curacao", Country)) %>%
  mutate(Country = ifelse(Province.State == "Sint Maarten" & Country == "Netherlands", "    Sint Maarten", Country)) %>%
  mutate(Country = ifelse(Province.State == "Aruba" & Country == "Netherlands", "Aruba", Country)) %>%
  mutate(Country = ifelse(Province.State == "Bonaire, Sint Eustatius and Saba" & Country == "Netherlands", "Bonaire, Sint Eustatius and Saba", Country)) %>%
  mutate(Country = ifelse(Province.State == "Anguilla" & Country == "United Kingdom", "Anguilla", Country)) %>%
  mutate(Country = ifelse(Province.State == "Bermuda" & Country == "United Kingdom", "Bermuda", Country)) %>%
  mutate(Country = ifelse(Province.State == "British Virgin Islands" & Country == "United Kingdom", "British Virgin Islands", Country)) %>%
  mutate(Country = ifelse(Province.State == "Cayman Islands" & Country == "United Kingdom", "Cayman Islands", Country)) %>%
  mutate(Country = ifelse(Province.State == "Channel Islands" & Country == "United Kingdom", "Channel Islands", Country)) %>%
  mutate(Country = ifelse(Province.State == "Gibraltar" & Country == "United Kingdom", "Gibraltar", Country)) %>%
  mutate(Country = ifelse(Province.State == "Montserrat" & Country == "United Kingdom", "Montserrat", Country)) %>%
  mutate(Country = ifelse(Province.State == "Turks and Caicos Islands" & Country == "United Kingdom", "Turks and Caicos Islands", Country)) %>%
  select(-c(Province.State)) %>%
  pivot_longer(!Country, names_to = "Date", values_to = "cases") %>%
  mutate(Date = paste0(sub('.', '', Date))) %>%
  filter(Date == "2.22.22") %>%
  select(-c(Date)) %>%
  mutate(Country = case_when(
    Country == "US" ~ "USA",
    Country == "The Bahamas" ~ "Bahamas",
    Country == "Taiwan*" ~ "Taiwan",
    Country == "Korea, South" ~ "South Korea",
    Country == "Congo (Kinshasa)" ~ "Democratic Republic of the Congo",
    Country == "Congo (Brazzaville)" ~ "Republic of the Congo",
    Country == "Czechia" ~ "Czech Republic",
    Country == "Wallis and Futuna" ~ "Wallis and Futuna Islands",
    Country == "Bonaire, Sint Eustatius and Saba" ~ "Bonaire",
    TRUE ~ Country
  ))

jh_global_covid = aggregate(. ~ Country, jh_global_covid,  FUN = sum)
# > head(jh_global_covid)
# Country  cases
# 1 Afghanistan 172716
# 2     Albania 270455
# 3     Algeria 264365
# 4     Andorra  37820
# 5      Angola  98671
# 6  Antarctica     11

#### VACCINES
jh_vaccine = fetch_data(url = "https://raw.githubusercontent.com/govex/COVID-19/master/data_tables/vaccine_data/global_data/vaccine_data_global.csv",
           path = "data/js-vac.csv") %>%
  select(Province_State,Country_Region, Doses_admin,People_partially_vaccinated,People_fully_vaccinated,Date) %>%
  select(-c(Date)) %>%
  rename(Country = Country_Region) %>%
  mutate(
    Country = ifelse(
      Province_State == "French Guiana" &
        Country == "France",
      "French Guiana",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "French Polynesia" &
        Country == "France",
      "French Polynesia",
      Country
    )
  ) %>%
  mutate(Country = ifelse(
    Province_State == "Guadeloupe" &
      Country == "France",
    "Guadeloupe",
    Country
  )) %>%
  mutate(Country = ifelse(
    Province_State == "Martinique" &
      Country == "France",
    "Martinique",
    Country
  )) %>%
  mutate(Country = ifelse(
    Province_State == "Mayotte" &
      Country == "France",
    "Mayotte",
    Country
  )) %>%
  mutate(
    Country = ifelse(
      Province_State == "New Caledonia" &
        Country == "France",
      "New Caledonia",
      Country
    )
  ) %>%
  mutate(Country = ifelse(
    Province_State == "Reunion" &
      Country == "France",
    "Reunion",
    Country
  )) %>%
  mutate(
    Country = ifelse(
      Province_State == "Saint Barthelemy" &
        Country == "France",
      "Saint Barthelemy",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Saint Pierre and Miquelon" &
        Country == "France",
      "Saint Pierre and Miquelon",
      Country
    )
  ) %>%
  mutate(Country = ifelse(
    Province_State == "St Martin" &
      Country == "France",
    "St Martin",
    Country
  )) %>%
  mutate(
    Country = ifelse(
      Province_State == "Wallis and Futuna" &
        Country == "France",
      "Wallis and Futuna",
      Country
    )
  ) %>%
  mutate(Country = ifelse(
    Province_State == "Curacao" &
      Country == "Netherlands",
    "Curacao",
    Country
  )) %>%
  mutate(
    Country = ifelse(
      Province_State == "Sint Maarten" &
        Country == "Netherlands",
      " Sint Maarten",
      Country
    )
  ) %>%
  mutate(Country = ifelse(
    Province_State == "Aruba" &
      Country == "Netherlands",
    "Aruba",
    Country
  )) %>%
  mutate(
    Country = ifelse(
      Province_State == "Bonaire, Sint Eustatius and Saba" &
        Country == "Netherlands",
      "Bonaire, Sint Eustatius and Saba",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Anguilla" &
        Country == "United Kingdom",
      "Anguilla",
      Country
    )
  ) %>%
  mutate(Country = ifelse(
    Province_State == "Bermuda" &
      Country == "United Kingdom",
    "Bermuda",
    Country
  )) %>%
  mutate(
    Country = ifelse(
      Province_State == "British Virgin Islands" &
        Country == "United Kingdom",
      "British Virgin Islands",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Cayman Islands" &
        Country == "United Kingdom",
      "Cayman Islands",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Channel Islands" &
        Country == "United Kingdom",
      "Channel Islands",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Gibraltar" &
        Country == "United Kingdom",
      "Gibraltar",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Montserrat" &
        Country == "United Kingdom",
      "Montserrat",
      Country
    )
  ) %>%
  mutate(
    Country = ifelse(
      Province_State == "Turks and Caicos Islands" &
        Country == "United Kingdom",
      "Turks and Caicos Islands",
      Country
    )
  ) %>%
  mutate(
    Country = case_when(
      Country == "US" ~ "USA",
      Country == "The Bahamas" ~ "Bahamas",
      Country == "Taiwan*" ~ "Taiwan",
      Country == "Korea, South" ~ "South Korea",
      Country == "Congo (Kinshasa)" ~ "Democratic Republic of the Congo",
      Country == "Congo (Brazzaville)" ~ "Republic of the Congo",
      Country == "Czechia" ~ "Czech Republic",
      Country == "Wallis and Futuna" ~ "Wallis and Futuna Islands",
      Country == "Bonaire, Sint Eustatius and Saba" ~ "Bonaire",
      TRUE ~ Country
    )
  ) %>%
  select(-c(Province_State))

jh_vaccine = aggregate(. ~ Country, jh_vaccine, FUN = sum)
# > head(jh_vaccine)
# Country Doses_admin People_partially_vaccinated People_fully_vaccinated
# 1         Afghanistan     5535254                     4907058                 4231984
# 2             Albania     2707658                     1269746                 1196277
# 3             Algeria    13461201                     7456361                 6076272
# 4             Andorra      142420                       57797                   53250
# 5              Angola    15505389                    10591264                 5448403
# 6 Antigua and Barbuda      124726                       63492                   60963
##################################################################
##                  GISAID Global Monthly Data                  ##
##################################################################
gisaid = readxl::read_xlsx("../../../Downloads/gisaid_monthly_submissions_global_2022-02-21.xlsx") %>%
  rename(Country = ...1) %>%
  mutate(Country = case_when(
    Country == "US" ~ "USA",
    Country == "The Bahamas" ~ "Bahamas",
    TRUE ~ Country
  ))  %>%
  pivot_longer(!Country, names_to = "Date", values_to = "Count") %>%
  filter(Date != "country_total") %>%
  mutate(Date = paste0(substr(Date, 4, 8), "/", substr(Date, 1, 2))) %>%
  filter(Country != "monthly total:")

gisaid$GISAID.total.Submissions = ave(gisaid$Count, gisaid$Country, FUN = cumsum)

latest_gisaid = gisaid %>% filter(Date == "2022/02") %>% arrange(desc(GISAID.total.Submissions))


#################################################################
##                          Join Data                          ##
#################################################################
main_df = read.csv("../../../Downloads/summary-data-countries (1).csv") %>%
  rename(Country =  ï..Country) %>%
  mutate(
    Country = case_when(
      Country == "State of Palestine" ~ "Palestine",
      Country == "Viet Nam" ~ "Vietnam",
      TRUE ~ Country
    )
  ) %>%
  right_join(latest_gisaid)  %>%
  select(-c(Date, Raw.reads.submitted, Count)) %>%
  rename(C19DP.total.Submissions = Sequences.submitted) %>%
  left_join(jh_global_covid) %>%
  left_join(jh_vaccine) %>%
  na.omit(cases) %>%
  mutate("Genomes per confirmed cases (GISAID)" = GISAID.total.Submissions / cases) %>%
  mutate("Genomes per confirmed cases (C19DP)" =  C19DP.total.Submissions / cases) %>%
  mutate("Genomes per confirmed full vaccine (GISAID)" = GISAID.total.Submissions / cases) %>%
  mutate("Genomes per confirmed full vaccine (C19DP)" =  C19DP.total.Submissions / cases)

main_df$continent = countrycode(sourcevar = main_df[, "Country"],
                                origin = "country.name",
                                destination = "continent")

Reproducing: A race against time (Romano and Melo 2021)

GISAID

Bar Chart Race (Countries with greater than 5000 sequence submissions)

Covid-19 Data Portal comparison