Creating and assessing trend data

(last update: 2022-01-26 20:32:20)

1. Data management - example of C19CM

1. Import summary results (aka purple tab)

datapath<-"~/Dropbox/0 iSquared/iSquared_WHO/ACTA/3.AnalysisTrends/dtaExamples/"
dir(datapath)

#Zambia
temp1 <- read_excel(paste0(datapath, "Zambia_WHO_COVID19HospitalReadiness_Chartbook_20082021.xlsx"), 
                          sheet = "Indicator estimate data")

temp2 <- read.csv(paste0(datapath, "summary_COVID19HospitalReadiness_Zambia_R2.csv"))

#Kenya
temp3 <- read_excel(paste0(datapath, "KEN_Hospital_Chartbook_R1.xlsx"), 
                          sheet = "Indicator estimate data")

temp4 <- read_excel(paste0(datapath, "KEN_Hospital_Chartbook_R2_26_05_2021_JK_(Jamlick).xlsx"), 
                          sheet = "Indicator estimate data")

temp5 <- read_excel(paste0(datapath, "KEN_Hospital_Chartbook _14_12.xlsx"), 
                          sheet = "Indicator estimate data")

dflist <- list(temp1, temp2) #Zambia
#dflist <- list(temp3, temp4, temp5) #KENYA

for(df in dflist) {
    print(dim(df))
    print(table(df$group))
    print(df%>%select(starts_with("update"))%>%colnames())
}

2. Append results across rounds

• Keep variables that exist in all rounds
  
• Keep sub-national estimates that likely have a sufficient denominator
  
• Keep sub-national estimates that exist across all rounds

###### Append
dtawide<-bind_rows(temp1, temp2) #Zambia
#dtawide<-bind_rows(temp3, temp4, temp5) #KENYA

dim(dtawide)

###### Keep variables that exist in all rounds 
temp<-dtawide%>%filter(group=="All")

      nrounds<-nrow(temp)
      tempA<-as.data.frame(colnames(temp))
      tempB<-as.data.frame(sapply(temp, function(x) sum(is.na(x))))
      tempsum<-cbind(tempA, tempB)%>%
          rename(
              var = "colnames(temp)", 
              missing = "sapply(temp, function(x) sum(is.na(x)))")%>%
          filter(missing==0)
      varlist<-tempsum$var

dtawide<-dtawide%>%select(varlist)
dim(dtawide) 

###### Keep sub-national estimates that likely have a sufficient denominator 
dtawide<-dtawide%>%
    filter(group=="All" | group=="Level" | group=="Location" | group=="Sector")   
dim(dtawide)

###### Keep sub-national estimates that exist across all rounds  
dtawide<-dtawide%>%
    group_by(grouplabel)%>%
    mutate(temp=n())%>%ungroup()%>%
    filter(temp==length(unique(dtawide$round)))%>%select(-temp)
dim(dtawide) 

3. Reshape to “long”

###### Reshape long 
# In every summary data, the first six columns are same: but double check if you get errors
colnames(dtawide[1:6])

indicatorlist<-colnames(dtawide[7:ncol(dtawide)])                       

dtawide<-as.data.frame(dtawide)

dtalong<-reshape(dtawide, 
        direction='long', 
        varying=colnames(dtawide[7:ncol(dtawide)]), 
        timevar="indicator",
        times=indicatorlist,
        v.names=c("estimate"),
        idvar=colnames(dtawide[1:6])  
        )

dim(dtalong)
colnames(dtalong)

4. Further management

dtalong<-dtalong%>%
    mutate(
        country=toupper(country), #convert to all CAP
        id_domain = paste(country, grouplabel),
        id_domain_indicator = paste(country, grouplabel, indicator),
        #time=as.Date((paste0(as.character(year),"-",as.character(month),"-15")))
        time=as.Date(as.yearmon((paste0(as.character(year),"-",as.character(month))),
                        "%Y-%m"))
        )

str(dtalong)

5. Just for chartbook, reshape and export to chartbook/csv

###### Reshape wide 
# In every summary data, the first six columns are same: but double check if you get errors
#colnames(dtawide[1:6])
#indicatorlist<-colnames(dtawide[7:ncol(dtawide)])                       
#dtawide<-as.data.frame(dtawide)

dtachartbook<-reshape(dtalong, 
        direction='wide', 
        varying=colnames(dtawide[7:ncol(dtawide)]), 
        timevar="indicator",
        times=indicatorlist,
        v.names=c("estimate"),
        idvar=colnames(dtawide[1:6])  
        )

dim(dtachartbook)
colnames(dtachartbook)

2. Assess trend data

2.1 Number of sentinel sites

country	round	grouplabel	obs	obs_ipt	obs_icu	obs_vac	obs_spcm	obs_pcr	obs_rdt	obs_onsite	obs_offsite
ZAMBIA	1	All	53	51	51	41	49	16	45	46	2
Zambia	2	All	55	54	54	50	55	18	52	53	2

2.2 Summary scores

2.3 Percent of facilities with all vs. half of tracer items

2.4 Detailed results

PPE: all

IPC items

Safety strategies

Guidelines

Training

Medicines

O2

Oxygen distribution