Simple data explorer for IW council local spending data

filemerger.R

#DOWNLOAD SPENDING DATA FILES FOR 2013-14 FROM:
#https://www.iwight.com/Council/transparency/Our-Finances/Transparency-Our-Finances/Spending-and-Finance2

#inspired by http://psychwire.wordpress.com/2011/06/03/merge-all-files-in-a-directory-using-r-into-a-single-dataframe/
dataset=NULL
file_list <- list.files()
for (file in file_list){
  # if the merged dataset doesn't exist, create it
  if (!exists("dataset")){
    dataset <- read.csv(file, header=TRUE)
  }
  # if the merged dataset does exist, append to it
  if (exists("dataset")){
    temp_dataset <-read.csv(file, header=TRUE)
    dataset<-rbind(dataset, temp_dataset)
    rm(temp_dataset)
  }
}

global.R

require(shiny)
require(plyr)
Sys.setlocale('LC_ALL','C') 
#---inspired by http://psychwire.wordpress.com/2011/06/03/merge-all-files-in-a-directory-using-r-into-a-single-dataframe/
#file_list <- list.files(pattern='csv$')
#dataset=NULL
#for (file in file_list){
  # if the merged dataset doesn't exist, create it
#  if (!exists("dataset")){
#    dataset <- read.csv(file, header=TRUE)
#  }
  # if the merged dataset does exist, append to it
#  if (exists("dataset")){
#    temp_dataset <-read.csv(file, header=TRUE)
#    dataset<-rbind(dataset, temp_dataset)
#    rm(temp_dataset)
#  }
#}
#----

iw=read.csv('fulldata.csv')
iw$Amount=as.numeric(as.character(sub(',','',iw$Amount)))
iw$Date=as.Date(iw$Date,"%d.%m.%Y")
#iw$Date=as.Date(iw$Date,"%Y-%m-%d")
iw=iw[order(iw$Date),]

directorates=c('All',levels(iw$Directorate))
names(directorates)=directorates

server.R

require(ggplot2)

shinyServer(function(input, output,session) {
  
  output$dyn_searchterm_sa <- renderUI({
    u=parseQueryString(session$clientData$url_search)
    if ('serviceArea' %in% names(u)) q=u$serviceArea
    else q=''
    textInput("searchterm_sa", "Service Area:",q)
  })
  
  output$dyn_searchterm_et <- renderUI({
    u=parseQueryString(session$clientData$url_search)
    if ('expensesType' %in% names(u)) q=u$expensesType
    else q=''
    textInput("searchterm_et", "Expenses Type:",q)
  })
  
  output$dyn_searchterm_sn <- renderUI({
    u=parseQueryString(session$clientData$url_search)
    if ('supplierName' %in% names(u)) q=u$supplierName
    else q=''
    textInput("searchterm_sn", "Supplier name::",q)
  })
  
  sData <- reactive({
    tmp=iw
    if (input$directorate !='All') tmp=tmp[tmp$Directorate==input$directorate,]
    if (nchar(input$searchterm_sa)>0) tmp=tmp[grep(input$searchterm_sa,tmp$Service.Area,ignore.case=T),]
    if (nchar(input$searchterm_et)>0) tmp=tmp[grep(input$searchterm_et,tmp$Expenses.Type,ignore.case=T),]
    if (nchar(input$searchterm_sn)>0) tmp=tmp[grep(input$searchterm_sn,tmp$Supplier.Name,ignore.case=T),]
    if (nchar(input$amount_min)>0) tmp=tmp[tmp$Amount>=as.numeric(as.character(input$amount_min)),]
    if (nchar(input$amount_max)>0) tmp=tmp[tmp$Amount<=as.numeric(as.character(input$amount_max)),]
    droplevels(tmp)
    #iw[grep('elderly',iw$Service.Area,ignore.case=T),]
  })
  
  output$itemspreview <- renderTable({
   tmp=sData()
   tmp=tmp[order(-tmp$Amount),]
   tmp[1:10,c(1:2,5:8)]
  },include.rownames=FALSE,include.colnames=T)
  
  output$serviceareaspreview <- renderText({
    tmp=sData()
    paste(levels(tmp$Service.Area),collapse='; ')
  })
  
  output$saPlot  <- renderPlot({
    tmp=ddply(sData(),.(Directorate),mutate,dirCumAmount=cumsum(Amount))   
    
    g=ggplot(tmp)+geom_line(aes(x=Date,y=dirCumAmount,col=Directorate))
    g=g+xlab(NULL)+ylab('Cumulative amount (£)')
    if (nchar(input$total_min)>0) g=g+geom_hline(yintercept=as.numeric(as.character(input$total_min)))
    print(g)
  })
  
  saSummer <- reactive({
    tmp=sData()
    tmp=ddply(tmp,.(Directorate),summarise,sumTot=sum(Amount))
    tmp=tmp[order(-tmp$sumTot),]
    if (nchar(input$total_min)>0) tmp=tmp[tmp$sumTot>=as.numeric(as.character(input$total_min)),]
    if (nchar(input$total_max)>0) tmp=tmp[tmp$sumTot<=as.numeric(as.character(input$total_max)),]
    tmp
  })
  
  output$saTable <- renderTable({
    saSummer()
  },include.rownames=FALSE,include.colnames=T)
  
  output$saPlot2  <- renderPlot({
    tmp=ddply(sData(),.(Directorate,Expenses.Type),mutate,dirCumAmount=cumsum(Amount))
    
    g=ggplot(tmp)+geom_line(aes(x=Date,y=dirCumAmount,col=Expenses.Type))+facet_wrap(~Directorate)
    g=g+xlab(NULL)+ylab('Cumulative amount (£)')
    
    if (nchar(input$total_min)>0) g=g+geom_hline(yintercept=as.numeric(as.character(input$total_min)))
    
    print(g)
  })
  
  saSummer2<- reactive({
    tmp=sData()
    tmp=ddply(tmp,.(Directorate,Expenses.Type),summarise,sumTot=sum(Amount))
    tmp=tmp[order(-tmp$sumTot),]
    
    if (nchar(input$total_min)>0) tmp=tmp[tmp$sumTot>=as.numeric(as.character(input$total_min)),]
    if (nchar(input$total_max)>0) tmp=tmp[tmp$sumTot<=as.numeric(as.character(input$total_max)),]
    
    tmp
  })
  
  output$saTable2 <- renderTable({
    saSummer2()
  },include.rownames=FALSE,include.colnames=T)
  
  output$saPlot3  <- renderPlot({
    if (nchar(input$searchterm_et)==0){
      tmp=ddply(sData(),.(Directorate,Service.Area),mutate,dirCumAmount=cumsum(Amount))
      g=ggplot(tmp)+geom_line(aes(x=Date,y=dirCumAmount,col=Service.Area))+facet_wrap(~Directorate)
    } else {
      tmp=ddply(sData(),.(Directorate,Expenses.Type,Service.Area),mutate,dirCumAmount=cumsum(Amount))
      g=ggplot(tmp)+geom_line(aes(x=Date,y=dirCumAmount,col=Service.Area))+facet_wrap(~Directorate+Expenses.Type)
    }
    
    g=g+xlab(NULL)+ylab('Cumulative amount (£)')
    if (nchar(input$total_min)>0) g=g+geom_hline(yintercept=as.numeric(as.character(input$total_min)))
    print(g)
  })
  
  saSummer3<- reactive({
    tmp=sData()
    if (nchar(input$searchterm_et)==0)
      tmp=ddply(tmp,.(Directorate,Service.Area),summarise,sumTot=sum(Amount))
    else tmp=ddply(tmp,.(Directorate,Expenses.Type,Service.Area),summarise,sumTot=sum(Amount))

    tmp=tmp[order(-tmp$sumTot),]
    
    if (nchar(input$total_min)>0) tmp=tmp[tmp$sumTot>=as.numeric(as.character(input$total_min)),]
    if (nchar(input$total_max)>0) tmp=tmp[tmp$sumTot<=as.numeric(as.character(input$total_max)),]
    
    tmp
  })
  
  output$saTable3 <- renderTable({
    saSummer3()
  },include.rownames=FALSE,include.colnames=T)
  
  output$saPlot4  <- renderPlot({
    tmp=sData()
    tmp=ddply(sData(),.(Supplier.Name,Service.Area),mutate,dirCumAmount=cumsum(Amount))
    
    g=ggplot(tmp)+geom_line(aes(x=Date,y=dirCumAmount,col=Service.Area))+facet_wrap(~Supplier.Name)
    g=g+xlab(NULL)+ylab('Cumulative amount (£)')
    if (nchar(input$total_min)>0) g=g+geom_hline(yintercept=as.numeric(as.character(input$total_min)))
    print(g)
  })
  
  output$saPlot4b  <- renderPlot({
    tmp=sData()
    tmp=ddply(sData(),.(Supplier.Name,Directorate),mutate,dirCumAmount=cumsum(Amount))
    
    g=ggplot(tmp)+geom_line(aes(x=Date,y=dirCumAmount,col=Directorate))+facet_wrap(~Supplier.Name)
    g=g+xlab(NULL)+ylab('Cumulative amount (£)')
    if (nchar(input$total_min)>0) g=g+geom_hline(yintercept=as.numeric(as.character(input$total_min)))
    print(g)
  })
  
  saSummer4<- reactive({
    tmp=sData()
    tmp=ddply(tmp,.(Directorate,Expenses.Type,Supplier.Name),summarise,sumTot=sum(Amount))
    tmp=tmp[order(-tmp$sumTot),]
    
    if (nchar(input$total_min)>0) tmp=tmp[tmp$sumTot>=as.numeric(as.character(input$total_min)),]
    if (nchar(input$total_max)>0) tmp=tmp[tmp$sumTot<=as.numeric(as.character(input$total_max)),]
    
    tmp
  })
  
  output$saTable4 <- renderTable({
    saSummer4()
  },include.rownames=FALSE,include.colnames=T)
  
  saSummer4b<- reactive({
    tmp=sData()
    tmp=ddply(tmp,.(Directorate,Service.Area,Supplier.Name),summarise,sumTot=sum(Amount))
    tmp=tmp[order(-tmp$sumTot),]

    if (nchar(input$total_min)>0) tmp=tmp[tmp$sumTot>=as.numeric(as.character(input$total_min)),]
    if (nchar(input$total_max)>0) tmp=tmp[tmp$sumTot<=as.numeric(as.character(input$total_max)),]
    
    tmp
  })
  
  output$saTable4b <- renderTable({
    saSummer4b()
  },include.rownames=FALSE,include.colnames=T)
})

ui.R

shinyUI(pageWithSidebar(
  
  headerPanel("IW Council Spending Explorer - Proof of Concept"),
  
  sidebarPanel(
    selectInput("directorate", "Directorate:",directorates),
    uiOutput("dyn_searchterm_sa"),
    uiOutput("dyn_searchterm_et"),
    uiOutput("dyn_searchterm_sn"),
    textInput("amount_min", "Min amount (line item, £):"),
    textInput("amount_max", "Max amount (line item, £)"),
    textInput("total_min", "Min total (l£):"),
    textInput("total_max", "Max total (£)"),

    submitButton(text = "Search")
    
  ),
  
  mainPanel(
    tabsetPanel(
      tabPanel("Preview",
               tableOutput("itemspreview"),textOutput('serviceareaspreview')
      ),
      tabPanel("Directorate",
               plotOutput("saPlot"),tableOutput("saTable")
      ),
      tabPanel("Expenses Type",
               plotOutput("saPlot2"),tableOutput("saTable2")
      ),
      tabPanel("Service Area",
                plotOutput("saPlot3"),tableOutput("saTable3")
      ),
      tabPanel("Suppliers",
               plotOutput("saPlot4b"),plotOutput("saPlot4"),tableOutput("saTable4"),tableOutput("saTable4b")
      )
    )
  )
  
))