This provides steps and code for data cleaning, management, and analysis of COVID-19 hospital case management capacity assessment survey. See here for the questionnare (link forthcoming). This code:
1. Imports and cleans COVID-19 hospital case management capacity (aka “hospital products”) dataset from Lime Survey (i.e., green and blue tabs in the chartbook),
2. Creates field check tables for data quality monitoring, and
3. Creates indicator estimate data for dashboards and chartbook (purple tab in the chartbook).
TWO parts must be updated per country-specific adaptation.
1. Directories and local macro in A. SETTING per survey implementation information
2. Local macro in E.1. Country speciic code local per section 1
#working directory where this markdown file and subfolders are located.
setwd("C:/Users/YoonJoung Choi/Dropbox/0 iSquared/iSquared_WHO/ACTA/3.AnalysisPlan/")
#chartbookdir<-("C:/Users/YoonJoung Choi/World Health Organization/BANICA, Sorin - HSA unit/2 Global goods & tools/2 HFAs/1 HFAs for COVID-19/4. Implementation support materials/4. Analysis and dashboards/")
chartbookdir<-("C:/Users/YoonJoung Choi/Dropbox/0 iSquared/iSquared_WHO/ACTA/3.AnalysisPlan/")
limesurveydir<-("C:/Users/YoonJoung Choi/Dropbox/0 iSquared/iSquared_WHO/ACTA/3.AnalysisPlan/ExportedCSV_FromLimeSurvey/")
# Define local macro for the survey
country<-"COUNTRY NAME" #country name
round<-1 #round
year<-2020 #year of the mid point in data collection
month<-12 #month of the mid point in data collection
# local macro for analysis: no change needed dtaraw<-read.csv(paste0(limesurveydir,"LimeSurvey_COVID19HospitalReadiness_GreecePilot_R1.csv"))
obsraw<-nrow(dtaraw)
cols<-ncol(dtaraw)As of 2020-12-15 11:29:00, the downloaded raw data has 8 observations and 142 variables.
Then, export the raw data into the chartbook (green tab: “Facility-level raw data”) - as is.
Assess duplicate rows based on facility code: Q101. Among the duplicate rows, keep the latest row, based on the submission date/time.
# Identify duplicates
dta<-dtaraw %>%
mutate(
submitdate_string=as.character(submitdate),
submitdate=as.POSIXct(as.character(submitdate),format= "%m/%d/%Y %H:%M")
)%>%
group_by(Q101)%>%
mutate(
dupe = n()>1,
latest = submitdate ==max(submitdate, na.rm = TRUE))%>%
ungroup()
# Assess duplicates
table(dta$dupe, dta$latest) #cross tab
dtaduplicate<-dta%>%filter(dupe==1)
obsduplicate<-dtaduplicate%>%nrow() #number of duplicate rows
obsduplicateunique<-length(unique(dtaduplicate$Q101)) #number of unique facilities among duplicate rows
#keep only unique/latest rows per facility
dta<-dta%>%
filter(latest==1)%>%
select(-dupe, -latest)
obs<-nrow(dta)
obsunique<-length(unique(dta$Q101))A total of 2 duplicate rows from 1 unique facilities were identified based on Q101. Now keeping only one unique row per facility, the dataset has 7 observations.
names(dta)<-tolower(names(dta))#Assess odd names to change
colnames(dta)
dtanew<-dta%>%
#/*replace .other with other*/
rename_all(.funs = funs(sub(".other.", "other", .)))%>%
#/*replace .sq with _*/
rename_all(.funs = funs(sub(".sq", "_sq", .)))%>%
#/*replace _sq with _*/
rename_all(.funs = funs(sub("_sq", "_", .)))%>%
#/*replace sq with _*/
rename_all(.funs = funs(sub("sq", "_", .)))%>%
#/*replace y with nothing*/
rename_all(.funs = funs(sub("y", "", .)))%>%
#/*replace any remaining . with nothing - BUT THIS CODE NOT WORKING??/!!*/
#rename_at(.vars = vars(ends_with(".")), .funs = funs(sub(".", "", .)))%>%
rename_all(.funs = funs(sub("001.", "001", .)))%>%
rename_all(.funs = funs(sub("002.", "002", .)))%>%
rename_all(.funs = funs(sub("003.", "003", .)))%>%
rename_all(.funs = funs(sub("004.", "004", .)))%>%
rename_all(.funs = funs(sub("005.", "005", .)))%>%
rename_all(.funs = funs(sub("006.", "006", .)))%>%
rename_all(.funs = funs(sub("007.", "007", .)))%>%
rename_all(.funs = funs(sub("008.", "008", .)))%>%
rename_all(.funs = funs(sub("009.", "009", .)))%>%
rename_all(.funs = funs(sub("010.", "010", .)))%>%
rename_all(.funs = funs(sub("011.", "011", .)))%>%
rename_all(.funs = funs(sub("012.", "012", .)))%>%
rename(
q604=q604n,
q605=q605n
)
# Assess new names
colnames(dtanew)
# take care of still strange col names (why do they exist?)
str(dtanew$q701_003_003.)
dtanew<-dtanew%>%
rename_all(.funs = funs(sub("002_002.", "002_002", .)))%>%
rename_all(.funs = funs(sub("003_003.", "003_003", .)))
colnames(dtanew)
dta<-dtanewCheck variables section by scion, drop prefix “A” in categorical/character variables, and convert to numeric.
#change all factor to numeric
dta<-dta%>%
mutate_if(is.factor, as.character)
#####* Section 1
varlist<-dta%>%select(q104, q105, q106, q115, q116, q117, starts_with("q118"))%>%colnames()
str(dta[varlist])
dta<-dta%>%
mutate_at(vars(varlist), list(~ sub("^A", "\\1", .)))%>%
mutate_at(vars(varlist), list(~ sub("-oth-", "88", .)))%>%
mutate_at(vars(varlist), funs(as.numeric))
str(dta[varlist]) #check variables ready for further processing
#####* Section 2
varlist<-dta%>%select(starts_with("q2"))%>%colnames()
str(dta[varlist])
#####* Section 3
varlist<-dta%>%select(starts_with("q3"))%>%colnames()
str(dta[varlist])
#####* Section 4
varlist<-dta%>%select(starts_with("q4"))%>%colnames()
str(dta[varlist])
dta<-dta%>%
mutate_at(vars(varlist), list(~ sub("^A", "\\1", .)))%>%
mutate_at(vars(varlist), funs(as.numeric))
str(dta[varlist])
#####* Section 5
varlist<-dta%>%select(starts_with("q502"), starts_with("q503"))%>%colnames()
str(dta[varlist])
dta<-dta%>%
mutate_at(vars(varlist), list(~ sub("^A", "\\1", .)))%>%
mutate_at(vars(varlist), funs(as.numeric))
str(dta[varlist])
#####* Section 6
varlist<-dta%>%select(starts_with("q602"), q605, q608)%>%colnames()
str(dta[varlist])
dta<-dta%>%
mutate_at(vars(varlist), list(~ sub("^A", "\\1", .)))%>%
mutate_at(vars(varlist), funs(as.numeric))
str(dta[varlist])
#####* Section 7
varlist<-dta%>%select(starts_with("q7"))%>%colnames()
str(dta[varlist])
#####* Section 8
varlist<-dta%>%select(starts_with("q8"))%>%colnames()
str(dta[varlist])
varlist<-dta%>%select(starts_with("q808"), starts_with("q811"), starts_with("q813"))%>%colnames()
str(dta[varlist])
dta<-dta%>%
mutate_at(vars(varlist), list(~ sub("^A", "\\1", .)))%>%
mutate_at(vars(varlist), funs(as.numeric))
str(dta[varlist])
#####* Section 9
varlist<-dta%>%select(starts_with("q9"))%>%colnames()
str(dta[varlist])
varlist<-dta%>%select(q901, q904)%>%colnames()
str(dta[varlist])
dta<-dta%>%
mutate_at(vars(varlist), list(~ sub("^A", "\\1", .)))%>%
mutate_at(vars(varlist), funs(as.numeric))
str(dta[varlist]) str(dta$q118_001)
varlist<-dta%>%
select(starts_with("q118"), starts_with("q609"))%>%
colnames()
dta[varlist][dta[varlist] == 2 ] <- 0 #no
str(dta$q118_001)
str(dta$q602_001)
varlist<-dta%>%
select(starts_with("q401_"), starts_with("q402_"), starts_with("q404_"),
starts_with("q503_"), starts_with("q602_"))%>%
colnames()
dta[varlist][dta[varlist] == 2 ] <- 0 #no
dta[varlist][dta[varlist] == 3 ] <- NA #not applicable
str(dta$q602_001)The following is value labels.
#delimit;
#lab define q104
1"1. urban"
2"2. rural";
#lab values q104 q104;
#lab define q105
1"1.Primary care centre/clinic"
2"2.First referral hospital (district hospital)"
3"3.Other general hospital with specialties or single-specialty hospital"
4"4.Long-term care facility"
88"5.Other" ;
#lab values q105 q105;
#lab define q106
1"1.Government"
2"2.Private for profit"
3"3.Private not for profit"
4"4.Other";
#lab values q106 q106;
#lab define ppe
1"1.Currently available for all health workers"
2"2.Currently available only for some health workers"
3"3.Currently unavailable for any health workers"
4"4.Not applicable - never procured or provided" ;
foreach var of varlist q502* {;
#lab values `var' ppe;
};
#lab define q605
1"1.<24 hrs"
2"2.24-47 hrs (1-2 days)"
3"3.48-72 hrs (2-3 days)"
4"4.>=72 hrs ( days or longer)" ;
#lab values q605 q605;
#lab define availfunc
1"1.Yes, functional"
2"2.Yes, but not functional"
3"3.No";
foreach var of varlist
q608 q804 q805 {;
#lab values `var' availfunc ;
};
#lab define icepack
1"1.Yes, a set of ice packs for all cold boxes"
2"1.Yes, a set of ice packs only for some cold boxes"
3"3.No";
foreach var of varlist q808 q811 {;
#lab values `var' icepack ;
};
#lab define icepackfreeze
1"1.All"
2"2.Only some"
3"3.None-no ice packs"
4"4.None-no functional freezer" ;
#lab values q813 icepackfreeze ;
#lab define yesno 1"1. yes" 0"0. no";
foreach var of varlist
q115 q118*
q2*
q501
q601 q603 q604
q801 q802 q806 q809
{;
labe values `var' yesno;
};
#lab define yesnona 1"1. yes" 0"0. no";
foreach var of varlist
q401_* q402_* q404_* q503_* q602_*
{;
labe values `var' yesnona;
};
#delimit cr
As of 2020-12-15, the following are “field check tables.” In Stata program, xls file is created. In R, the results are directly presented in this markdown file.
dtacheck<-dta%>%
mutate(
updatedate= as.character(as.Date(Sys.time( ), format='%d%b%Y')),
date=as.POSIXct(as.character(submitdate_string),format= "%m/%d/%Y"),
#xresult=q904==1, recover this line with real data
xresult=1, #delete this line with real data
xresult= ifelse(q101==1222, 0, xresult), #delete this line with real data
responserate= xresult==1, #label define responselist 0 "Not complete" 1 "Complete"
starttime=as.POSIXct(as.character(startdate),format= "%m/%d/%Y %H:%M"),
endtime=as.POSIXct(as.character(datestamp),format= "%m/%d/%Y %H:%M"),
time= endtime - starttime,
time_all = round(mean(time, na.rm=TRUE), 1))%>%
group_by(xresult)%>%
mutate(
time_ = round(mean(time, na.rm=TRUE), 1),
time_complete = time_,
time_complete = ifelse(xresult==0, NA, time_complete),
time_incomplete = time_,
time_incomplete = ifelse(xresult==1, NA, time_incomplete) )%>%
ungroup()Assess interview characteristics among all interviews.
# Date of field check table update and the total number of interviews
print(date)[1] “2020-12-15”
# Date of interviews (submission date, final)
table(dtacheck$date)2020-09-21 2020-09-25 2020-09-28 2020-10-01 2020-10-12 2020-10-14 12 12 12 12 24 12
# Interview response rate (%)
print(paste(as.character(round(mean(dtacheck$responserate, na.rm=TRUE)*100, 1)), "%"))[1] “85.7 %”
# Number of interviews by facility type
table(dtacheck$q104)1 2 60 24
#Average interview length (minutes), among all interviews
print(paste(as.character(round(mean(dtacheck$time_all, na.rm=TRUE))), "minutes")) [1] “6799 minutes”
#Average interview length (minutes), among completed interviews
print(paste(as.character(round(mean(dtacheck$time_complete, na.rm=TRUE))), "minutes"))[1] “7930 minutes”
#Average interview length (minutes), among partly completed interviews
print(paste(as.character(round(mean(dtacheck$time_incomplete, na.rm=TRUE))), "minutes"))[1] “14 minutes”
#####/*the following calcualtes % missing in select questions among completed interviews*/
#1. Missing number of beds when facility provides inpatient services
dtacheck<-dtacheck%>%
mutate(
missing1 = 0,
missing1 = ifelse(q115==1 & is.na(q116)==1, 1,
ifelse(q115==1 & is.na(q116)==1, 1,
ifelse(is.na(q115)==1, 1,
ifelse(q115==0, NA, missing1))))
)
#2. Missing response to medicines questins
varlist<-dtacheck%>%select(starts_with("q401_"))%>%colnames()
str(dtacheck[varlist])
dtacheck[varlist] <- lapply(dtacheck[varlist],
function(x){ifelse(is.na(x)==0,0,
ifelse(is.na(x)==1,1,x))})
dtacheck<-dtacheck%>%
mutate(
missingmed_num = rowSums(dtacheck[varlist] ),
missingmed = missingmed_num,
missingmed = ifelse(missingmed_num>=1, 1, missingmed) )
#3. Missing response to PPE
varlist<-dtacheck%>%select(starts_with("q502_"))%>%colnames()
str(dtacheck[varlist])
dtacheck[varlist] <- lapply(dtacheck[varlist],
function(x){ifelse(is.na(x)==0,0,
ifelse(is.na(x)==1,1,x))})
dtacheck<-dtacheck%>%
mutate(
missingppe_num = rowSums(dtacheck[varlist] ),
missingppe = missingppe_num,
missingppe = ifelse(missingppe_num>=1, 1, missingppe) )
#4. Missing PCR capacity
dtacheck<-dtacheck%>%
mutate(
missingpcr = 0,
missingpcr = ifelse(q603==1 & is.na(q606)==1, 1,
ifelse(q603==1 & is.na(q607)==1, 1,
ifelse(is.na(q603)==1, 1,
ifelse(q603==0, NA, missingpcr))))
)
#5. Missing response to equipment
varlist<-dtacheck%>%select(starts_with("q701_"))%>%colnames()
str(dtacheck[varlist])
dtacheck[varlist] <- lapply(dtacheck[varlist],
function(x){ifelse(is.na(x)==0,0,
ifelse(is.na(x)==1,1,x))})
dtacheck<-dtacheck%>%
mutate(
missingeqp_num = rowSums(dtacheck[varlist] ),
missingeqp = missingeqp_num,
missingeqp = ifelse(missingeqp_num>=1, 1, missingeqp) )Assess level of missing responses among COMPLETED interviews.
dtacheck<-dtacheck%>%
filter(xresult==1)
#1. Missing number of beds when facility provides inpatient services
#print("1. Percent of interviews with missing responses for inpatient or ICU beds")
print(paste(as.character(round(mean(dtacheck$missing1, na.rm=TRUE)*100, 1)), "%"))[1] “20 %”
#2. Missing response to medicines questins
#print("2. Percent of interviews with one or missing responses in medicines questions")
print(paste(as.character(round(mean(dtacheck$missingmed, na.rm=TRUE)*100, 1)), "%")) [1] “83.3 %”
#print("2.1. Of those, average number of missing responses")
temp<-dtacheck%>%filter(missingmed==1)
round(mean(temp$missingmed_num, na.rm=TRUE), 1)[1] 4
#3. Missing response to PPE (%)
#print("3. Percent of interviews with one or missing responses in PPE questions")
print(paste(as.character(round(mean(dtacheck$missingppe, na.rm=TRUE)*100, 1)), "%")) [1] “16.7 %”
#print("3.1. Of those, average number of missing responses")
temp<-dtacheck%>%filter(missingppe==1)
round(mean(temp$missingppe_num, na.rm=TRUE), 1)[1] 6
#4. Missing PCR capacity
#print("4. Percent of interviews with missing responses regarding PCR questions")
print(paste(as.character(round(mean(dtacheck$missingpcr, na.rm=TRUE)*100, 1)), "%"))[1] “50 %”
#5. Missing response to equipment
#print("5. Percent of interviews with one or missing responses in equipment questions")
print(paste(as.character(round(mean(dtacheck$missingeqp, na.rm=TRUE)*100, 1)), "%"))[1] “66.7 %”
#print("5.1. Of those, average number of missing responses")
temp<-dtacheck%>%filter(missingeqp==1)
round(mean(temp$missingeqp_num, na.rm=TRUE), 1)[1] 8
Update code here based on the questionnaire in the country
urbanmin<-1
urbanmax<-1
minlow <-1 #/*lowest code for lower-level facilities in Q105*/
maxlow <-1 #/*highest code for lower-level facilities in Q105*/
minhigh <-2 #/*lowest code for hospital/high-level facilities in Q105*/
maxhigh <-88 #/*highest code for hospital/high-level facilities in Q105*/
districthospital <-2 #/*district hospital or equivalent */
pubmin<-1
pubmax<-1
maxdrug <-12 #/*total medicines asked in q401*/Created analysis variables section by section.
* give prefix z for background characteristics, which can be used as analysis strata
* give prefix x for binary variables, which will be used to calculate percentage
* give prefix y for integer/continuous variables, which will be used to calculate total number
#####* Section 1
dta<-dta%>%
mutate(
country = country,
round = round,
facilitycode = q101,
month = month,
year = year,
zurban = q104>=urbanmin & q104<=urbanmax,
zlevel =q105,
zlevel_hospital =q105>=minhigh & q105<=maxhigh,
zlevel_disthospital =q105==districthospital,
zlevel_low =q105>=minlow & q105<=maxlow,
zpub =q106>=pubmin & q106<=pubmax
)%>%
mutate_if(is.logical, as.numeric)%>%
mutate_at(vars(starts_with("z")),
~replace(., is.na(.)==TRUE, 0))
#lab define zurban 0"Rural" 1"Urban"
#lab define zlevel_hospital 0"Non-hospital" 1"Hospital"
#lab define zpub 0"Non-public" 1"Public"
#lab values zurban zurban
#lab values zlevel_hospital zlevel_hospital
#lab values zpub zpub
#lab var id "ID generated from Lime Survey"
#lab var facilitycode "facility ID from sample list" /*this will be used to merge with sampling weight, if relevant*/#####* Section 2
dta<-dta%>%
mutate(
ximst = q201==1,
ximst_fun = q202==1
)%>%
mutate_if(is.logical, as.numeric)#####* Section 3: bed caoacity
dta<-dta%>%
mutate(
xipt= q115==1,
xicu= q117>=1 & is.na(q117)==FALSE ,
#lab var xipt "facilities providing IPT services"
#lab var xicu "facilities providing ICU services"
ybed = q116,
ybed_icu = q117,
ybed_icu=ifelse(xipt==1 & xicu==0 , 0, ybed_icu),
#/*assume 0 ICU beds if IPT provided but no ICU beds reported*/
ybed_cap_covid = q301,
ybed_cap_covid_severe = q302,
ybed_cap_covid_critical = q303,
ybed_covid_night = (q304 + q305)/2,
ybed_cap_respiso = q306 ,
ybed_convert_respiso = q307,
ybed_convert_icu = q308,
xocc_lastnight = q309,
xocc_lastmonth = q310
)%>%
mutate_if(is.logical, as.numeric)#####* Section 4: Therapeutics
varlist<-dta%>%select(starts_with("q401_"))%>%colnames()
dta<-dta%>%
mutate(
max=maxdrug,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xdrug_score =100*(temp/max),
xdrug_100 =xdrug_score>=100,
xdrug_50 =xdrug_score>=50
)
varlist<-dta%>%select(starts_with("q402_"))%>%colnames()
dta<-dta%>%
mutate(
max=3,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xsupp_score =100*(temp/max),
xsupp_100 =xsupp_score>=100,
xsupp_50 =xsupp_score>=50
)
varlist<-dta%>%select(starts_with("q404_"))%>%colnames()
dta<-dta%>%
mutate(
max=5,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xsolidarity_score =100*(temp/max),
xsolidarity_100 =xsolidarity_score>=100,
xsolidarity_50 =xsolidarity_score>=50
)
dta<-dta%>%
rename_all(.funs = funs(sub("q401_", "xdrug__", .)))%>%
rename_all(.funs = funs(sub("q402_", "xsupply__", .)))%>%
mutate_at(vars(starts_with("xdrug__"), starts_with("xsupply__")),
~replace(., is.na(.)==TRUE, 0))#####* Section 5: IPC
dta<-dta%>%
mutate(
xppe= q501 ,
xppe_allsome__001 = q502_001==1 | q502_001==2,
xppe_allsome__002 = q502_002==1 | q502_002==2,
xppe_allsome__003 = q502_003==1 | q502_003==2,
xppe_allsome__004 = q502_004==1 | q502_004==2,
xppe_allsome__005 = q502_005==1 | q502_005==2,
xppe_allsome__006 = q502_006==1 | q502_006==2,
xppe_all__001 = q502_001==1,
xppe_all__002 = q502_002==1,
xppe_all__003 = q502_003==1,
xppe_all__004 = q502_004==1,
xppe_all__005 = q502_005==1,
xppe_all__006 = q502_006==1,
xipcitem__001 = q503_001,
xipcitem__002 = q503_002,
xipcitem__003 = q503_003,
xipcitem__004 = q503_004
)%>%
mutate_if(is.logical, as.numeric)%>%
mutate_at(vars(starts_with("xppe_"), starts_with("xipcitem_")),
~replace(., is.na(.)==TRUE, 0))
varlist<-dta%>%select(starts_with("xppe_allsome__"))%>%colnames()
dta<-dta%>%
mutate(
max=6,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xppe_allsome_score =100*(temp/max),
xppe_allsome_100 =xppe_allsome_score>=100,
xppe_allsome_50 =xppe_allsome_score>=50
)
varlist<-dta%>%select(starts_with("xppe_all__"))%>%colnames()
dta<-dta%>%
mutate(
max=6,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xppe_all_score =100*(temp/max),
xppe_all_100 =xppe_all_score>=100,
xppe_all_50 =xppe_all_score>=50
)
varlist<-dta%>%select(starts_with("xipcitem__"))%>%colnames()
dta<-dta%>%
mutate(
max=4,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xipcitem_score =100*(temp/max),
xipcitem_100 =xipcitem_score>=100,
xipcitem_50 =xipcitem_score>=50
)#####* Section 6 : LAB
dta<-dta%>%
mutate(
xspcm =q601==1,
xspcmitem__001 = q602_001==1,
xspcmitem__002 = q602_002==1,
xspcmitem__003 = q602_003==1,
xtest =q603!=1,
xtesttransport =q604==1,
xtesttime_24 =q605==1,
xtesttime_48 =q605<=2,
xtesttime_72 =q605<=3,
xpcr = q603==1,
xpcr_capacity = q606/q607,
xpcr_equip = q608==1
)%>%
mutate_if(is.logical, as.numeric)%>%
mutate_at(vars(starts_with("xspcm"), starts_with("xtest"), starts_with("xpcr")),
~replace(., is.na(.)==TRUE, 0))%>%
mutate_at(vars(xtesttransport, starts_with("xtesttime")),
funs(ifelse(xtest!=1, NA, .)))%>%
mutate_at(vars(starts_with("xpcr_")),
funs(ifelse(xpcr!=1, NA, .)))
varlist<-dta%>%select(starts_with("xspcmitem__"))%>%colnames()
dta<-dta%>%
mutate(
max=3,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xspcmitem_score =100*(temp/max),
xspcmitem_100 =xspcmitem_score>=100,
xspcmitem_50 =xspcmitem_score>=50
)
varlist<-dta%>%select(xspcmitem_100, xtesttime_48, xpcr_equip)%>%colnames()
dta<-dta%>%
mutate(
max=2,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xdiagcovid_score = 100*(temp/max),
xdiagcovid_100 = xdiagcovid_score >=100,
xdiagcovid_50 = xdiagcovid_score >=50
)#####* Section 7: Equipment
dta<-dta%>%
mutate(
yequip_ventilator = q701_003_003,
yequip_noninvventilator = q701_004_003,
yequip_o2concentrator = q701_005_003,
xequip_anyfunction__001 = q701_001_002>=1,
xequip_anyfunction__002 = q701_002_002>=1,
xequip_anyfunction__003 = q701_003_002>=1,
xequip_anyfunction__004 = q701_004_002>=1,
xequip_anyfunction__005 = q701_005_002>=1,
xequip_allfunction__001 = q701_001_002>=1 & q701_001_003==0,
xequip_allfunction__002 = q701_002_002>=1 & q701_002_003==0,
xequip_allfunction__003 = q701_003_002>=1 & q701_003_003==0,
xequip_allfunction__004 = q701_004_002>=1 & q701_004_003==0,
xequip_allfunction__005 = q701_005_002>=1 & q701_005_003==0,
xequip_anymalfunction__001 = q701_001_003>=1 & is.na(q701_001_003)==FALSE,
xequip_anymalfunction__002 = q701_002_003>=1 & is.na(q701_002_003)==FALSE,
xequip_anymalfunction__003 = q701_003_003>=1 & is.na(q701_003_003)==FALSE,
xequip_anymalfunction__004 = q701_004_003>=1 & is.na(q701_004_003)==FALSE,
xequip_anymalfunction__005 = q701_005_003>=1 & is.na(q701_005_003)==FALSE,
xequip_malfunction_reason__001 = q704_001==1 | q706_001==1 | q708_001==1,
xequip_malfunction_reason__002 = q704_002==1 | q706_002==1 | q708_002==1,
xequip_malfunction_reason__003 = q704_003==1 | q706_003==1 | q708_003==1,
xequip_malfunction_reason__004 = q704_004==1 | q706_004==1 | q708_004==1
)%>%
mutate_if(is.logical, as.numeric)%>%
mutate_at(vars(starts_with("xequip_")),
~replace(., is.na(.)==TRUE, 0))
varlist<-dta%>%select(starts_with("xequip_anyfunction__"))%>%colnames()
dta<-dta%>%
mutate(
max=5,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xequip_anyfunction_score =100*(temp/max),
xequip_anyfunction_100 =xequip_anyfunction_score>=100,
xequip_anyfunction_50 =xequip_anyfunction_score>=50
)
varlist<-dta%>%select(starts_with("xequip_allfunction__"))%>%colnames()
dta<-dta%>%
mutate(
max=5,
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xequip_allfunction_score =100*(temp/max),
xequip_allfunction_100 =xequip_allfunction_score>=100,
xequip_allfunction_50 =xequip_allfunction_score>=50
)
varlist<-dta%>%select(starts_with("xequip_anymalfunction__"))%>%colnames()
dta<-dta%>%
mutate(
temp=rowSums(dta[ , varlist], na.rm=TRUE),
xequip_anymalfunction=temp>=1
)
dta<-dta%>%
mutate_at(vars(starts_with("xequip_malfunction_reason_")),
funs(ifelse(xequip_anymalfunction!=1, NA, .)))%>%
mutate(
xoxygen_portable = q709==1,
xoxygen_plant = q710==1,
xoxygen_piped = q711==1)%>%
mutate_at(vars(starts_with("xoxygen_")),
funs(as.numeric(.) ))%>%
mutate_at(vars(starts_with("xoxygen_")),
~replace(., is.na(.)==TRUE, 0))%>%
mutate(
temp=rowSums(.[grep("xoxygen_", names(.))], na.rm = TRUE),
xoxy = temp==3
)#####* Section 8: vaccine
dta<-dta%>%
mutate(
xvac= q801==1 | q802==1,
xvac_av_fridge = q804==1 | q804==2,
xvac_avfun_fridge = q804==1 ,
xvac_avfun_fridgetemp = q804==1 & q805==1,
xvac_av_coldbox = q806==1,
xvac_avfun_coldbox_all = q806==1 & (q807>=1 & is.na(q807)==FALSE) & q808==1,
xvac_avfun_coldbox_all_full = q806==1 & (q807>=1 & is.na(q807)==FALSE) & q808==1 & q813==1,
yvac_avfun_coldbox_all =NA,
yvac_avfun_coldbox_all = ifelse(xvac_avfun_coldbox_all==1, q807,
yvac_avfun_coldbox_all),
yvac_avfun_coldbox_all_full =NA,
yvac_avfun_coldbox_all_full = ifelse(xvac_avfun_coldbox_all==1 & q813==1, q807,
yvac_avfun_coldbox_all_full),
xvac_av_carrier = q809==1,
xvac_avfun_carrier_all = q809==1 & (q810>=1 & is.na(q810)==FALSE) & q811==1,
xvac_avfun_carrier_all_full = q809==1 & (q810>=1 & is.na(q810)==FALSE) & q811==1 & q813==1,
yvac_avfun_carrier_all =NA,
yvac_avfun_carrier_all = ifelse(xvac_avfun_carrier_all==1, q810,
yvac_avfun_carrier_all),
yvac_avfun_carrier_all_full =NA,
yvac_avfun_carrier_all_full = ifelse(xvac_avfun_carrier_all==1 & q813==1, q810,
yvac_avfun_carrier_all_full),
xvac_av_outreach = xvac_av_coldbox ==1 | xvac_av_carrier ==1,
xvac_avfun_outreach_all_full = xvac_avfun_coldbox_all_full ==1 | xvac_avfun_carrier_all_full==1
) %>%
mutate_if(is.logical, as.numeric)%>%
mutate_at(vars(starts_with("xvac_")),
~replace(., is.na(.)==TRUE, 0))%>%
mutate_at(vars(starts_with("xvac_av")),
funs(ifelse(xvac!=1, NA, .)) )
#lab var xvac_av_fridge "has fridge"
#lab var xvac_avfun_fridge "has functioning fridge"
#lab var xvac_avfun_fridgetemp "has functioning fridge with temp log"
#lab var xvac_av_coldbox "has coldbox"
#lab var xvac_avfun_coldbox_all "has functioning coldbox, all"
#lab var xvac_avfun_coldbox_all_full "has functioning coldbox with icepacks, all"
#lab var xvac_av_carrier "has carrier"
#lab var xvac_avfun_carrier_all "has functioning carrier, all"
#lab var xvac_avfun_carrier_all_full "has functioning carrier with icepacks, all"This code chunk can be skipped if no sampling weight is used.
# read sampling weight in the chartbook provided by the country. Makesure there are no duplicates
dtaweight<-read_excel(paste0(chartbookdir, "WHO_COVID19HospitalReadiness_ChartbookTest.xlsx"), sheet = "Weight")
names(dtaweight)<-tolower(names(dtaweight))
dtaweight<-dtaweight%>%
rename_all(.funs = funs(sub(" ", "", .)))%>%
select(facilitycode, weight)%>%
distinct(facilitycode, .keep_all=TRUE)
# check datasets
dim(dta)
dim(dtaweight)
str(dta$facilitycode)
str(dtaweight$facilitycode)
# check datasets
dta<-left_join(dta, dtaweight, by = c("facilitycode"))%>%
arrange(id)#/*this is generated from Lime survey*/
# confirm dimension
dim(dta)write.csv(dta, paste0("COVID19HospitalReadiness_", country, "_R", round, ".csv"))dtatemp<-dta%>%
mutate(
obs=1,
obs_ipt=NA,
obs_icu=NA,
obs_vac=NA,
obs_spcm=NA,
obs_test=NA,
obs_pcr=NA,
obs_ipt=ifelse( xipt==1, 1, obs_ipt),
obs_icu=ifelse( xicu==1, 1, obs_icu),
obs_vac=ifelse( xvac==1, 1, obs_vac),
obs_spcm=ifelse( xspcm==1, 1, obs_spcm),
obs_test=ifelse( xtest==1, 1, obs_test),
obs_pcr=ifelse( xpcr==1, 1, obs_pcr)
)
dtatempx<-dtatemp%>%select(country, round, month, year, starts_with("z"), starts_with("x"))
dtatempy<-dtatemp%>%select(country, round, month, year, starts_with("z"), starts_with("obs"), starts_with("y"))# Among all facilities
dtasummaryx<-dtatempx%>%
group_by(country, round, month, year)%>%
summarize_all(funs(mean(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(group="All", grouplabel="")
dtasummaryy<-dtatempy%>%
group_by(country, round, month, year)%>%
summarize_all(funs(sum(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(group="All", grouplabel="")
dtasummaryall<-left_join(dtasummaryx, dtasummaryy,
by = c("country", "round", "month", "year", "group", "grouplabel"))
# By residential area
dtasummaryx<-dtatempx%>%
group_by(country, round, month, year, zurban)%>%
summarize_all(funs(mean(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(
group="Location",
grouplabel="",
grouplabel= ifelse(zurban==0, "1.1 Rural" , grouplabel),
grouplabel= ifelse(zurban==1, "1.2 Urban" , grouplabel) )
dtasummaryy<-dtatempy%>%
group_by(country, round, month, year, zurban)%>%
summarize_all(funs(sum(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(
group="Location",
grouplabel="",
grouplabel= ifelse(zurban==0, "1.1 Rural" , grouplabel),
grouplabel= ifelse(zurban==1, "1.2 Urban" , grouplabel) )
colnames(dtasummaryy)
dtasummarylocation<-left_join(dtasummaryx, dtasummaryy,
by = c("country", "round", "month", "year", "group", "grouplabel"))
# By facility type
dtasummaryx<-dtatempx%>%
group_by(country, round, month, year, zlevel_hospital)%>%
summarize_all(funs(mean(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(
group="Level",
grouplabel="",
grouplabel= ifelse(zlevel_hospital==0, "2.1 Non-hospitals" , grouplabel),
grouplabel= ifelse(zlevel_hospital==1, "2.2 Hospitals" , grouplabel) )
dtasummaryy<-dtatempy%>%
group_by(country, round, month, year, zlevel_hospital)%>%
summarize_all(funs(sum(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(
group="Level",
grouplabel="",
grouplabel= ifelse(zlevel_hospital==0, "2.1 Non-hospitals" , grouplabel),
grouplabel= ifelse(zlevel_hospital==1, "2.2 Hospitals" , grouplabel) )
dtasummarylevel<-left_join(dtasummaryx, dtasummaryy,
by = c("country", "round", "month", "year", "group", "grouplabel"))
# By facility managing authority
dtasummaryx<-dtatempx%>%
group_by(country, round, month, year, zpub)%>%
summarize_all(funs(mean(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(
group="Sector",
grouplabel="",
grouplabel= ifelse(zpub==0, "3.2 Non-public" , grouplabel),
grouplabel= ifelse(zpub==1, "3.1 Public" , grouplabel) )
dtasummaryy<-dtatempy%>%
group_by(country, round, month, year, zpub)%>%
summarize_all(funs(sum(., na.rm = TRUE)))%>%
ungroup()%>%
mutate(
group="Sector",
grouplabel="",
grouplabel= ifelse(zpub==0, "3.2 Non-public" , grouplabel),
grouplabel= ifelse(zpub==1, "3.1 Public" , grouplabel) )
dtasummarysector<-left_join(dtasummaryx, dtasummaryy,
by = c("country", "round", "month", "year", "group", "grouplabel"))
# Append all
dim(dtasummaryall)
dim(dtasummarylocation)
dim(dtasummarylevel)
dim(dtasummarysector)
dtasummary<-rbind(dtasummaryall, dtasummarylocation, dtasummarylevel, dtasummarysector)
dim(dtasummary)
dtasummary<-dtasummary%>%
select(-starts_with("z"))%>%
mutate_at(vars(starts_with("x")), funs(round((.*100), 0)))%>%
mutate_at(vars(ends_with("_score"), starts_with("xocc"), xpcr_capacity),
funs(round((./100), 0)))%>%
arrange(country, round, group, grouplabel)
#move up identification variables
setcolorder(dtasummary, c("country", "round", "month", "year", "group", "grouplabel"))
colnames(dtasummary[ , 1:10])write.csv(dtasummary, paste0("summary_COVID19HospitalReadiness_", country, "_R", round, ".csv"))(Last updated: 2020-12-15 11:29:00)
END OF MARKDOWN FILE