Choice of tools

R: Easy to learn scripting language well suited for data munging.
Python: Powerful, standard language with many useful tools when working with data.
Node.JS: Great tool for those who already know JavaScript.
ETL tools (Pentaho, Talend, etc)
Bash
SQL: May be useful for transforming large datasets or if you know SQL.
Most important consideration is to choose the right tool for the task.

General approach to bootstrapping

Create metadata from existing sources
Create metadata specific to DHIS2
Metadata often needs to be cleaned prior to import
Import, transform, shape and merge existing data with metadata

Goal for this excercise

Bootstrap a DHIS2 system with Global TB data in under 2 minutes and 30 seconds!
If you do not know R, don’t worry. Focus on WHAT is happening and how you could do it.
Do not forget about repetability and testability.
Download and install R and Rstudio if you would like to follow along or repeat the bootstrap.

Let’s get started

require(httr)
require(jsonlite)
require(assertthat)
require(rlist)
require(reshape2)
require(ggplot2)

We will load a few helpful libraries

httr: High-level library for interacting with a server over HTTP
- NodeJs = http
jsonlite: Interact with JSON resources.
- NodeJS = JSON
assertthat: A simple assertion library for testing.
- NodeJS = assert
rlist: A useful library for manipulating lists.
reshape: Data munging library for reshaping data.
ggplot2: Handy library for making graphs.

UID generation

generateUID <- function(codeSize = 11) {
  #Generate a random seed
  runif(1)
  allowedLetters <- c(LETTERS, letters)
  allowedChars <- c(LETTERS, letters, 0:9)
  #First character must be a letter according to the DHIS2 spec
  firstChar <- sample(allowedLetters, 1)
  otherChars <- sample(allowedChars, codeSize - 1)
  uid <- paste(c(firstChar,paste(
  otherChars, sep = "", collapse = "" )), sep = "", collapse = "")
  return(uid)
}

DHIS2 can generate a uid like y4yFsgveSfu for you at api/system/id
They are just 11 character strings which begin with a letter
Only letters and numbers are allowed

Logging in

We assume you have setup a totally clean instance of DHIS2 up and running.
Lets try and login.

startTime<-Sys.time()

setwd("/home/jason/development/dhis2-data-munging/")
baseurl<-"http://localhost:8080/dhis/"
username<-"admin"
password<-"district"
#This will give us a cookie we can use for later use. 

loginDHIS2<-function(baseurl,username,password) {
url<-paste0(baseurl,"api/me")
r<-GET(url,authenticate(username,password))
assert_that(r$status_code == 200L) }

loginDHIS2(baseurl,username,password)

## [1] TRUE

Looks like admin:district worked, so lets proceed.
You can adjust setwd to a directory on your machine.

Loading our organisation units

#Get the OUS
ous<-fromJSON("https://raw.githubusercontent.com/jason-p-pickering/dhis2-data-munging/master/ous/ous.json")
#Create the URL
url<-paste0(baseurl,"api/27/metadata?importStrategy=CREATE&atomicMode=NONE")
#Post to the metadata API as JSON
r<-POST(url,body=toJSON(ous,auto_unbox = TRUE),content_type_json())
assert_that(r$status_code == 200L)

## [1] TRUE

We load up a file of country boundaries as JSON.
POST this to the metadata API, turning off atomicMode for now.
We should get an HTTP 200 code if things went well.
Consult the docs for details.

Testing our organisation units are there

#Request all of the orgunit IDs from the server
url<-paste0(baseurl,"/api/organisationUnits?fields=id&paging=false")
ous_from_server<-fromJSON(content(GET(url),"text"))

We should have our organisation units in now.
Lets be sure that they are acutally there by asking the API for them and testing against out file.

assert_that(all.equal(sort(ous$organisationUnits$id) , 
                      sort(ous_from_server$organisationUnits$id)))

## [1] TRUE

Setting organisation unit levels

We should set some organisation unit levels, otherwise, the system will complain

organisationUnitLevels<-data.frame(level=c(1,2,3),
                                   name=c("Global","Continent","Country"))
#Generate some UIDs
organisationUnitLevels$uid<-sapply(rep(11,nrow(organisationUnitLevels)),generateUID)
url<-paste0(baseurl,"api/27/metadata?importStrategy=CREATE&atomicMode=NONE")
#Post to the metadata API as JSON
r<-POST(url,body=toJSON(list(organisationUnitLevels = organisationUnitLevels),
                        auto_unbox = TRUE),
                        content_type_json())
assert_that(r$status_code == 200L)

## [1] TRUE

That seems to have worked!

Set your user’s organisation unit

We need to set the organisation unit of our user, otherwise, the system will complain.
We can get our current user information, and assign it to the Global organisation unit.
After that, POST the metadata back and request that it be updated.

#We want to be a global user
url<-paste0(baseurl,"/api/organisationUnits?filter=name:eq:Global&fields=id")
global_uid<-fromJSON(content(GET(url),"text"))
url<-paste0(baseurl,"api/me")
me<-fromJSON(content(GET(url),"text"))
url<-paste0(baseurl,"api/users/",me$id)
me<-fromJSON(content(GET(url),"text"))
me$organisationUnits<-list(list(id = global_uid$organisationUnits$id))
url<-paste0(baseurl,"api/27/metadata?importStrategy=UPDATE")
r<-POST(url,body=toJSON(list(users=list(me)),auto_unbox = TRUE),content_type_json() )
assert_that(r$status_code == 200L)

## [1] TRUE

Get the data elements

Lets load some data elements from the WHO TB Program.

des<-read.csv("https://extranet.who.int/tme/generateCSV.asp?ds=dictionary")

Data element names are restricted to 230 characters and they need to be unique
So, lets test for that.

des$name<-substring(des$definition,0,230)
#Check and be sure that no names are duplicated.
assert_that(Reduce("&",duplicated(des$name)) == FALSE)

## [1] TRUE

Certain fields like name,shortName,aggregationType and valueType are required.
Be sure to consult api/schemas/ endpoint for the details.

Loading the data elements

#Data elements
set.seed(94005004)
des_import<-data.frame(name=des$name
                       ,code=des$variable_name
                       ,shortName=des$variable_name,
                       aggregationType="SUM",
                       valueType="NUMBER",
                       domainType="AGGREGATE")
des_import$id<-sapply(rep(11,nrow(des_import)),generateUID)
url<-paste0(baseurl,"api/27/metadata?importStrategy=CREATE")
#Post to the metadata API as JSON
r<-POST(url,body=toJSON(list(dataElements = des_import),
                        auto_unbox = TRUE),
                        content_type_json())
#assert_that(fromJSON(content(r,"text"))$stats$created == nrow(des))

We create all of the required fields.
Consult api/schemas/dataElement for details.
Ensure require fields are present.
Its useful to pre-generate UIDs.

Data element groups

Our file looks like it has some data elemnent groups.
We parse these out and create a JSON object and import that.

#Get the groups and assign some UIDs.
de_groups<-data.frame(name=unique(des$dataset))
de_groups$id<-sapply(rep(11,nrow(de_groups)),generateUID)

#Lets create a map of data elements and which data element groups they belong to
foo<-merge(des[,c("variable_name","dataset")],
           des_import[,c("code","id")],
           by.x="variable_name",by.y="code")

Assign each data element to the appropriate group

[{“name”:[“Community engagement”],“id”:[“V9b7hWU2ZPa”],“dataElements”:[{“id”:[“Rfjg6LEMGyb”]},{“id”:[“OBwq1fzkTm4”]},{“id”:[“v8gJOREcVnC”]},{“id”:[“A5

Post to the metadata API as JSON like the othe metadata

Transforming and importing the data

Lets get TB estimates and case notifications from WHO

tb<-read.csv("https://extranet.who.int/tme/generateCSV.asp?ds=estimates",
             stringsAsFactors=FALSE)
tb_cases<-read.csv("https://extranet.who.int/tme/generateCSV.asp?ds=notifications",
                   stringsAsFactors = FALSE)
knitr::kable(tb[1:2,c("iso3","year","e_pop_num","e_inc_100k")])

iso3	year	e_pop_num	e_inc_100k
AFG	2000	19701940	190
AFG	2001	20531160	189

The data is in wide format, and we need to have it in long format
Long format means we need a single data element/period/organisation unit combination on a single row.
Removing NULLS will make things faster
Merge the two data frames

tb<-reshape2::melt(tb,id.vars=c("country","iso2","iso3","iso_numeric",
                                "g_whoregion","year"))
tb<-tb[!is.na(tb$value),]
tb_cases<-reshape2::melt(tb_cases,id.vars=c("country","iso2","iso3",
                                            "iso_numeric","g_whoregion","year"))
tb_cases<-tb_cases[!is.na(tb_cases$value),]
tb<-rbind(tb,tb_cases)
knitr::kable(tb_cases[1:2,])

	country	iso2	iso3	iso_numeric	g_whoregion	year	variable	value
18	Afghanistan	AF	AFG	4	EMR	1997	new_sp	618
19	Afghanistan	AF	AFG	4	EMR	1998	new_sp	1833

Merging it with the organisation units

Now the data is in so called longformat
Lets merge (INNER JOIN) with the list of countries we loaded earlier.

tb<-merge(tb,des_import[,c("code","id")],by.x="variable",by.y="code")
knitr::kable(tb[1:2,])

variable	country	iso2	iso3	iso_numeric	g_whoregion	year	value	id
all_conf_xdr	Afghanistan	AF	AFG	4	EMR	2015	1	hkZEgnPCNis
all_conf_xdr	Albania	AL	ALB	8	EUR	2015	0	hkZEgnPCNis

And, we can ask the server for the organisation units, and merge these as well.

#We need to get the Country codes
r <- GET(paste0(baseurl,"api/27/organisationUnits?paging=false&filter=level:eq:3&fields=id,code"))
r<- httr::content(r, "text")
ous<-jsonlite::fromJSON(r,flatten=TRUE)$organisationUnits
names(ous)<-c("ou_code","ou_id")
#Merge/INNER JOIN the OUs with the data
tb<-merge(tb,ous,by.x="iso3",by.y="ou_code")
knitr::kable(tb[1:2,])

iso3	variable	country	iso2	iso_numeric	g_whoregion	year	value	id	ou_id
ABW	e_pop_num	Aruba	AW	533	AMR	2005	100031	goH6vP5XtCq	wFtLm5IujWA
ABW	e_pop_num	Aruba	AW	533	AMR	2006	100830	goH6vP5XtCq	wFtLm5IujWA

Fine tuning prior to import

We only need certain columns. Consult the docs
Remove NULL values
Data values should be converted to characters.

tb_out<-tb[,c("id","year","ou_id","value")]
tb_out<-tb_out[!is.na(tb_out$value),]
tb_out<-plyr::colwise(as.character)(tb_out)
names(tb_out)<-c("dataElement","period","orgUnit","value")
knitr::kable(head(tb_out))

dataElement	period	orgUnit	value
goH6vP5XtCq	2005	wFtLm5IujWA	100031
goH6vP5XtCq	2006	wFtLm5IujWA	100830
HLT8faniodR	2011	wFtLm5IujWA	0
RqKNafpPB3o	2000	wFtLm5IujWA	10
VjkTb4G6rwX	2012	wFtLm5IujWA	1
XGMfURZiW2V	2008	wFtLm5IujWA	0.64

Data import and analytics

After getting the data into shape, import it and trigger analytics.

#Import the data, skipping checks for existing values
url<-paste0(baseurl,"api/27/dataValueSets?preheatCache=true&skipExistingCheck=true")
r<-POST(url,body=toJSON(list(dataValues = tb_out),auto_unbox = TRUE),content_type_json())
#Lets trigger analytics
url<-paste0(baseurl,"api/27/resourceTables/analytics")
r<-POST(url)

## No encoding supplied: defaulting to UTF-8.
## No encoding supplied: defaulting to UTF-8.
## No encoding supplied: defaulting to UTF-8.
## No encoding supplied: defaulting to UTF-8.

What did we end up with?

Lets compare Total of new and relapse cases and cases with unknown previous TB treatment history between South Africa and Sierra Leone.

#Period dimensions
start_year<-min(unique(tb_cases[tb_cases$variable == "c_newinc",c("year")]))
end_year<-max(unique(tb_cases[tb_cases$variable == "c_newinc",c("year")]))
years<-paste(seq(start_year,end_year),sep="",collapse=";")
#Data element dimension
c_newinc<-fromJSON(content(GET(paste0(baseurl,"api/dataElements?filter=code:eq:c_newinc&fields=[id,name]")),"text"))$dataElements$id
#Orgunit dimension
sa<-fromJSON(content(GET(paste0(baseurl,
                          "api/organisationUnits?filter=name:eq:South%20Africa&fields=[id,name]")),
                     "text"))$organisationUnits$id
sl<-fromJSON(content(GET(paste0(baseurl,
                          "api/organisationUnits?filter=name:eq:Sierra%20Leone&fields=[id,name]")),
                     "text"))$organisationUnits$id
#Assemble the URL
url<-paste0(baseurl,"api/27/analytics.json?")
url<-paste0(url,"dimension=ou:",sa,";",sl)
url<-paste0(url,"&dimension=pe:",years)
url<-paste0(url,"&filter=dx:",c_newinc)
url<-paste0(url,"&displayProperty=NAME&skipMeta=false")
data<-fromJSON(content(GET(url),"text"))
#We need to munge the data a bit to get it into a suitable form.
metadata<-do.call(rbind,lapply(data$metaData$items,
                               data.frame,stringsAsFactors=FALSE))
metadata$from<-row.names(metadata)
this_data<-data.frame(data$rows)
names(this_data)<-data$headers$name
this_data$ou<-plyr::mapvalues(this_data$ou,metadata$from,
                              metadata$name,warn_missing=FALSE)
this_data$value<-as.numeric(as.character(this_data$value))/1000

Lets look at the data

#And, create the graph
g <- ggplot(data=this_data,aes(x=pe,y=value,group=ou, color=ou)) 
g <- g + geom_line() 
g <- g + geom_point()
g <- g +ggtitle("Reported TB cases in Sierra Leone and South Africa") 
g <- g + labs(x = "Year", y = "Number of cases (thousands)",fill = NULL)
g <- g + theme(axis.text.x = element_text(angle = 90, hjust = 1))
g

Bootstrap completed in 1.372812 mins

Bootstrapping DHIS2

Introduction

Choice of tools

General approach to bootstrapping

Goal for this excercise

Let’s get started

UID generation

Logging in

Loading our organisation units

Testing our organisation units are there

Setting organisation unit levels

Set your user’s organisation unit

Get the data elements

Loading the data elements

Data element groups

Transforming and importing the data

Merging it with the organisation units

Fine tuning prior to import

Data import and analytics

What did we end up with?

Lets look at the data

Summary