Using R, DHIS2 and the Google Visualization API

+ Using R, DHIS2 and the Google Visualization API + Google's Visualization API provides a very rich set of tools for the visualization of multi-dimensional data. In this simple example, we will show how to create a simple column chart with the Google Visualization API using the "googleVis" R package. Full information on the package can be found here.. The basic principle, as with the other examples, is to get some data from the DHIS2 database, and bring it into R, perform some minor alterations on the data to make it easier to work with, and then create the chart. + #Load some libraries +library(RPostgreSQL) +library(googleVis) +library(reshape) +#A small helper function to get a data frame from some SQL +dfFromSQL<-function (con,sql){ + rs<-dbSendQuery(con,sql) + result<-fetch(rs,n=-1) + return(result) +} + +#Get a database connection +user<-"postgres" +password<-"kfk3ep6" +host<-"127.0.0.1" +port<-"5432" +dbname<-"dhis2_demo" +con <- dbConnect(PostgreSQL(), user= user, password=password,host=host, port=port,dbname=dbname) +#Let's retrieve some ANC data from the demo database +sql<-"SELECT ou.shortname as province,i.shortname as indicator, +extract(year from p.startdate) as year, a.value FROM aggregatedindicatorvalue a +INNER JOIN organisationunit ou on a.organisationunitid = ou.organisationunitid +INNER JOIN indicator i on a.indicatorid = i.indicatorid +INNER JOIN period p on a.periodid = p.periodid +where a.indicatorid in (SELECT DISTINCT indicatorid from indicator where shortname ~*('ANC [123] Coverage')) and +a.organisationunitid in (SELECT DISTINCT idlevel2 from _orgunitstructure where idlevel2 is not null) +and a.periodtypeid = (SELECT DISTINCT periodtypeid from periodtype where name = 'Yearly')" +anc<-dfFromSQL(con,sql) +#Change these columns to factors so that the reshape will work more easily +anc$province<-as.factor(anc$province) +anc$indicator<-as.factor(anc$indicator) +anc$year<-as.factor(anc$year) +#Melt and cast the data into the correct format +anc.m<-melt(anc) +#Lets only look at 2010 data +anc.m.2010<-subset(anc.m,year == 2010) +#Create the cross tab to make it easy +#to work with in the chart +cast(anc.m.2010,province ~ indicator + year) +colnames(anc.m.2010.ct)<-c("province","ANC1","ANC2","ANC3") +#Create the column chart and plot it +anc2010.colchart<-gvisColumnChart(anc.m.2010.ct, + options=list(title="ANC Coverage 2010") +) +plot(anc2010.colchart) + The resulting graph is displayed below. + + + + + + + + Using packages like brew or Rapache, these types of graphs could be easily integrated into external web sites. +