Skip to content

@psychemedia /filemerger.R
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Simple data explorer for IW council local spending data
#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)
}
}
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
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)
})
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")
)
)
)
))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.