1 Introduction

Summary of Africa COVID-19 genomic data.

1.1 Table of Variants.

VOC - variants of concern, VUI - variant under investigation

1.1.1 South Africa

1.1.2 Africa

# Africa_all_data_15September
## import PHE/PHA variants file
variants <- read_delim("variants_Africa.tsv", "\t",escape_double = FALSE, trim_ws = TRUE)

my_order <- c("Alpha", "Beta", "Delta","A.23.1" ,"B.1.1.318", "C.1", "C.1.2","C.36.3", "Eta")

con <- dbConnect(RSQLite::SQLite(), "dashboardDB")
variant_summary <- 
dbFetch(dbSendQuery(con, 'SELECT 
                            * 
                            FROM 
(SELECT variant as name, count(variant) as "number of sequences", min(date) as "date of first sequence", max(date) as "date of last sequence", Cast ((JulianDay() - JulianDay(max(date))) As Integer) AS "No of days since last sampling" FROM tbl_metadata_Africa GROUP BY name) as a  
LEFT JOIN 
(SELECT variant as name, COUNT(variant) as "new sequences submitted in the last 30 days" FROM tbl_metadata_Africa  WHERE date_submitted >= month GROUP BY name) as b 
ON a.name = b.name LEFT JOIN 
(SELECT variant as name, COUNT(variant) as "new sequences sampled and submitted in the last 30 days" FROM tbl_metadata_Africa  WHERE date_submitted >= month AND date >= month GROUP BY name) as c on a.name = c.name'))
dbDisconnect(con)

variant_summary <- variant_summary[, -c(6,8)] 
variant_summary <- variant_summary %>% left_join(variants, by = "name" )

variant_summary[is.na(variant_summary$`new sequences submitted in the last 30 days`), "new sequences submitted in the last 30 days"] <- 0
variant_summary[is.na(variant_summary$`new sequences sampled and submitted in the last 30 days`), "new sequences sampled and submitted in the last 30 days"] <- 0

variant_summary <- variant_summary %>% select("name", "other_name", "variant_lineages","number of sequences","new sequences submitted in the last 30 days","new sequences sampled and submitted in the last 30 days", "date of first sequence","date of last sequence", "No of days since last sampling")
names(variant_summary) <- c("Variant (VOC,VOI,VUM)", "Other names by which this variant may be known", "Lineage/sub-lineages","Number of sequences","Sequences submitted in the last 30 days (data up to 2021-10-14)", "new sequences sampled and submitted in the last 30 days (data up to 2021-10-14)", "date of first sequence","date of last sequence", "No of days since last sampling")

variant_summary <- variant_summary %>%
  filter(`Variant (VOC,VOI,VUM)` %in% my_order) %>% 
    arrange(factor(`Variant (VOC,VOI,VUM)`, levels = my_order))  
 

variant_summary %>% 
      kbl() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = TRUE)
Variant (VOC,VOI,VUM) Other names by which this variant may be known Lineage/sub-lineages Number of sequences Sequences submitted in the last 30 days (data up to 2021-10-14) new sequences sampled and submitted in the last 30 days (data up to 2021-10-14) date of first sequence date of last sequence No of days since last sampling
Alpha VOC-202012/01 B.1.1.7 2564 399 1 2020-08-02 2021-09-30 19
Beta VOC-202012/02 B.1.351, B.1.351.1, B.1.351.2 10219 437 1 2020-05-27 2021-09-25 24
Delta VOC-21APR-02 B.1.617.2, AY.1, AY.3, AY.4, AY.5, AY.6, AY.7.1, AY.10, AY.11, AY.12, AY.13, AY.14, AY.15, AY.16, AY.17, AY.18, AY.19, AY.20, AY.21, AY.23, AY.24, AY.25 15089 5811 293 2020-09-10 2021-09-30 19
A.23.1 NA A.23.1 388 26 0 2020-10-21 2021-08-22 58
B.1.1.318 VUM-2021-06-04 B.1.1.318, AZ.1, AZ.2, AZ.5 672 110 0 2021-01-06 2021-08-11 69
C.1 NA C.1 384 0 0 2020-04-12 2021-06-28 113
C.1.2 VUM-2021-09-01 C.1.2 187 48 4 2021-05-11 2021-09-17 32
C.36.3 VUM-2021-06-16 C.36.3 104 2 0 2020-04-26 2021-06-16 125
Eta VUM-202102/03 B.1.525 991 197 3 2020-03-28 2021-09-17 32 
writexl::write_xlsx(variant_summary, "Africa_weekly_variant_summary.xlsx")

1.2 Beta in SADAC

# Africa_all_data_30September

con <- dbConnect(RSQLite::SQLite(), "dashboardDB")

beta_sadac_summary <- dbFetch(dbSendQuery(con, 'SELECT 
                            * 
                          FROM 
(SELECT country, region, variant, count(variant) as "number of sequences", min(date) as "date of first sequence", max(date) as "date of last sequence", Cast ((JulianDay("2021-10-14") - JulianDay(max(date))) As Integer) AS "No of days since last sampling" FROM tbl_metadata_Africa WHERE variant == "Beta"  GROUP BY variant, country) as a  
LEFT JOIN 
(SELECT country, COUNT(*) as "new sequences submitted in the last 30 days" FROM tbl_metadata_Africa  WHERE date_submitted >= month GROUP BY country) as b 
ON  a.country = b.country'))

dbDisconnect(con)

beta_sadac_summary <- beta_sadac_summary[, -8] 
#beta_sadac_summary <- beta_sadac_summary %>% left_join(variants, by = "name" )

beta_sadac_summary[is.na(beta_sadac_summary$`new sequences submitted in the last 30 days`), "new sequences submitted in the last 30 days"] <- 0

# cn_new_seq_dates <- paste0("New sequences submitted in the last 30 days (data up to ",Sys.Date(),")")
cn_new_seq_dates <- paste0("New sequences submitted in the last 30 days (data up to 2021-10-14)")
names(beta_sadac_summary) <- c("Country", "Region","Variant", "Number of sequences", "Date of first sequence", "Date of last sequence", "No of days since last sampling", cn_new_seq_dates)
  
 

beta_sadac_summary %>% 
      kbl() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), full_width = TRUE)
Country Region Variant Number of sequences Date of first sequence Date of last sequence No of days since last sampling New sequences submitted in the last 30 days (data up to 2021-10-14)
Angola South Beta 447 2020-08-25 2021-07-29 77 46
Benin West Beta 1 2021-05-23 2021-05-23 144 199
Botswana South Beta 378 2020-09-09 2021-08-10 65 150
Burundi East Beta 5 2021-05-27 2021-05-31 136 54
Cameroon Central Beta 9 2021-01-05 2021-03-01 227 3
Central African Republic Central Beta 1 2021-02-11 2021-02-11 245 0
Cote d’Ivoire West Beta 4 2021-03-06 2021-04-13 184 0
Democratic Republic of the Congo Central Beta 19 2021-01-22 2021-06-20 116 0
Djibouti East Beta 131 2021-03-15 2021-05-07 160 0
Equatorial Guinea Central Beta 49 2021-01-12 2021-08-27 48 16
Eswatini South Beta 33 2020-11-13 2021-07-09 97 90
Ethiopia East Beta 2 2021-05-25 2021-05-27 140 131
Gabon Central Beta 5 2021-02-01 2021-04-11 186 22
Ghana West Beta 21 2021-01-06 2021-05-07 160 162
Guinea-Bissau West Beta 1 2021-02-01 2021-02-01 255 0
Kenya East Beta 203 2020-12-15 2021-06-29 107 948
Lesotho South Beta 15 2020-12-16 2021-01-18 269 0
Liberia West Beta 6 2021-03-31 2021-05-20 147 33
Madagascar South Beta 201 2021-02-09 2021-04-26 171 221
Malawi South Beta 337 2020-11-25 2021-06-21 115 75
Mauritius South Beta 7 2020-12-06 2021-06-17 119 0
Morocco North Beta 1 2021-06-17 2021-06-17 119 88
Mozambique South Beta 358 2020-08-22 2021-04-22 175 0
Namibia South Beta 118 2020-10-15 2021-06-15 121 51
Nigeria West Beta 3 2021-02-05 2021-07-26 80 1308
Reunion East Beta 86 2021-07-13 2021-09-09 35 287
Rwanda East Beta 52 2020-06-04 2021-07-07 99 165
Seychelles East Beta 29 2021-02-05 2021-06-01 135 259
Somalia East Beta 4 2021-02-28 2021-02-28 228 0
South Africa South Beta 7116 2020-08-17 2021-09-25 19 2533
South Sudan East Beta 3 2021-03-31 2021-04-29 168 0
Sudan East Beta 13 2021-03-07 2021-03-29 199 6
Togo West Beta 5 2021-02-03 2021-04-01 196 0
Tunisia North Beta 4 2021-04-24 2021-05-28 139 0
Uganda East Beta 15 2021-02-13 2021-06-14 122 83
Union of the Comoros South Beta 6 2021-01-05 2021-01-10 277 0
Zambia South Beta 174 2020-12-01 2021-06-04 132 278
Zimbabwe South Beta 357 2020-05-27 2021-06-25 111 0 
writexl::write_xlsx(beta_sadac_summary, "africa_beta_sadac_summary.xlsx")