Source : Grants for the period between 1999 to 2015 from Ontario Trillium Foundation Grant Data: http://otf.ca/open
---
title: "Ontario Trillium Foundation"
output:
flexdashboard::flex_dashboard:
orientation: rows
social: menu
source: embed
---
# Intro text {.sidebar}
Ontario Trillium Foundation is an agency of the Government of Ontario and one of Canada's largest granting foundations. With a budget of over $136 million, the OTF funds ~1000 projects every year.
```{r setup, include=FALSE}
library(flexdashboard)
library(readxl)
library(lubridate)
library(ggplot2)
library(plotly)
library(plyr)
library(dplyr)
library(tidyr)
```
```{r loadData, cache=TRUE}
Trillium_2000_2015 <- read_excel("Trillium_2000_2015.xlsx",
col_types = c("date", "date", "numeric",
"numeric", "numeric"))
Trillium_2000_2015$ApprovalDate <- ymd(Trillium_2000_2015$ApprovalDate)
Trillium_2000_2015$SubmissionDate <- ymd(Trillium_2000_2015$SubmissionDate)
newdata <- Trillium_2000_2015[order(Trillium_2000_2015$ApprovalDate),]
newdata_selected <- select(newdata, ApprovalDate,AmountAwarded,AmountApplied)
newdata_selected$AmountAwarded <- newdata_selected$AmountAwarded/1000000
newdata_selected$AmountApplied <- newdata_selected$AmountApplied/1000000
newdata_sep <- separate(newdata_selected, ApprovalDate, c("year", "month", "day"), sep = "-")
newdata_YY_MM <- select(newdata_sep, year,month,AmountAwarded)
newdata_YY <- select(newdata_sep, year,AmountAwarded)
newdata_agg_MM <- aggregate(AmountAwarded ~ month + year, newdata_YY_MM , sum)
# use it to pick color http://colllor.com/
# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
cbbPalette <- c( "#1E4835", "#29654A", "#358260", "#419F75","#51B88A","#6EC49D","#8BD0B1","#7DCAA7","#D92626","#473E0B","#F1E6A7","#F1E6A7", "#E3CD4F", "#E3CD4F","#DEC32B", "#E1C83D", "#9F8B19")
#png(filename = "powerconsumption.png")
theme_set(theme_bw()) # BACK gROUND COLOR
```
# MGMT {data-icon="fa-map"}
Row {data-height=600}
-----------------------------------------------------------------------
### Frequency of Approvals
```{r}
p <- ggplot(newdata_agg_MM, aes(x=month, y=AmountAwarded)) +
#geom_line(aes(colour=year, group=year), size=1) + # colour, group both depend on cond2
geom_point(aes(colour=year), size=3) + # colour depends on cond2
scale_colour_manual(values=cbbPalette) + # larger points, different shape
theme(axis.text.x = element_text(angle = 90, hjust = 1, vjust = -0.5))+
labs(title = "Frequency of Approvals") +
labs(y = "Awarded Amount ( Million $)", x = "Month")
ggplotly(p)
```
> Source : Grants for the period between 1999 to 2015 from Ontario Trillium Foundation Grant Data: http://otf.ca/open
# TREND {data-icon="fa-map"}
Row {data-height=1000}
-----------------------------------------------------------------------
### Trend of Generosity
```{r}
newdata_sep <- separate(newdata_selected, ApprovalDate, c("year", "month", "day"), sep = "-")
newdata_YY <- select(newdata_sep, year,AmountAwarded,AmountApplied)
newdata_agg_YY <- aggregate(cbind(AmountAwarded,AmountApplied) ~ year, newdata_YY , sum)
newdata_agg_YY$year <- as.Date(newdata_agg_YY$year, "%Y") # aassuming Nov 11, since I formated on that day
newdata_agg_YY$ratio <- round( 100 * newdata_agg_YY$AmountAwarded / newdata_agg_YY$AmountApplied, 2)
newdata_agg_YY_plot <- newdata_agg_YY[c(1:16),]
p <- ggplot(newdata_agg_YY_plot, aes(x = year, y = ratio)) +
geom_line(colour = "grey75") +
labs(title = "Trend of Generosity",
y = "Amount Awarded / Amount Applied X 100 (%)", x = "Year" )
ggplotly(p)
```
# GRANTS {data-icon="fa-map"}
Row {data-height=1000}
-----------------------------------------------------------------------
### Ask Low Get High
```{r}
grpData <- newdata_YY %>% mutate(AmountApplied = AmountApplied * 1000000, AmountAwarded = AmountAwarded * 1000000)
grpData$Groups <- cut(grpData$AmountApplied, c(500, 25000,50000,75000,150000, 2150000))
#table(grpData$Groups)
levels(grpData$Groups) <- c("500 ~ 25,000", "25,000 ~ 50,000", "50,000 ~ 75,000",
"75,000 ~ 150,000", "150,000 ~ 2,150,000")
# added here this line for the next plot
grpData$ratio <- round( 100 * grpData$AmountAwarded / grpData$AmountApplied, 2)
grpData_agg_YY <- aggregate(ratio ~ year + Groups, grpData , mean)
grpData_agg_YY$year <- as.Date(grpData_agg_YY$year, "%Y") # aassuming Nov 11, since I formated on that day
library(plotly)
cbbPalette <- c( "#ff4747", "#00f51d", "#b80000", "#0c00b8","#8e2edc","#1b0aff","#ffdb70","#ffc71f","#f5b800","#008f11","#009E73","#7a70ff", "#ff1f1f", "#140000")
#png(filename = "powerconsumption.png")
theme_set(theme_bw()) # BACK gROUND COLOR
p <- ggplot(grpData_agg_YY, aes(x=year, y=ratio)) +
geom_line(aes(colour=Groups, group=Groups), size=0.5) + # colour, group both depend on cond2
geom_point(aes(colour=Groups), size=1) + # colour depends on cond2
scale_colour_manual(values=cbbPalette) + # larger points, different shape
labs(title = "Ask Low Get High") +
labs(y = "Amount Awarded / Amount Applied X 100 (%)", x = "Year")
ggplotly(p)
```
# PRGM AREA {data-icon="fa-map"}
Row {data-height=1000}
-----------------------------------------------------------------------
### PROGRAM AREAS
```{r}
Trillium_2000_2015_SocSer <- read_excel("Trillium_2000_2015_SocSer.xlsx",
col_types = c("date", "numeric", "numeric",
"numeric", "text"))
Trillium_2000_2015_SocSer$ApprovalDate <- ymd(Trillium_2000_2015_SocSer$ApprovalDate)
newdata <- Trillium_2000_2015_SocSer[order(Trillium_2000_2015_SocSer$ApprovalDate),]
newdata_sep <- separate(newdata, ApprovalDate, c("year", "month", "day"), sep = "-")
newdata_sep$ratio <- round(100 * newdata_sep$AmountAwarded / newdata_sep$AmountApplied, 2)
newdata_sep_YY <- aggregate(ratio ~ year + ProgramArea, newdata_sep , mean)
newdata_sep_YY$year <- as.Date(newdata_sep_YY$year, "%Y") # aassuming Nov 11, since I formated on that day
cbbPalette <- c( "#ff4747", "#00f51d", "#b80000", "#0c00b8","#8e2edc","#1b0aff","#ffdb70","#ffc71f","#f5b800","#008f11","#009E73","#7a70ff", "#ff1f1f", "#140000")
#png(filename = "powerconsumption.png")
theme_set(theme_bw()) # BACK gROUND COLOR
p <- ggplot(newdata_sep_YY, aes(x=year, y=ratio)) +
geom_line(aes(colour=ProgramArea, group=ProgramArea), size=0.5) + # colour, group both depend on cond2
geom_point(aes(colour=ProgramArea), size=1) + # colour depends on cond2
scale_colour_manual(values=cbbPalette) + # larger points, different shape
labs(title = "Disbursement in Program Areas") +
labs(y = "Amount Awarded / Amount Applied X 100 (%)", x = "Year")
ggplotly(p)
```
# PRJ. PERIOD {data-icon="fa-map"}
Row {data-height=1000}
-----------------------------------------------------------------------
### PROJECT PERIOD
```{r}
trData <- read_excel("Trillium_2000_2015_SocSer.xlsx",
col_types = c("date", "numeric", "numeric",
"numeric", "text"))
trData$ApprovalDate <- ymd(trData$ApprovalDate)
trData <- trData[order(trData$ApprovalDate),]
trData$Prj.Period <- cut(trData$PlannedDates, c(1,11,12,60))
#table(trData$Prj.Period)
levels(trData$Prj.Period) <- c("1 ~ 11", "12", "13 ~ 60")
trData <- separate(trData, ApprovalDate, c("year", "month", "day"), sep = "-")
trData$ratio <- round(100 * trData$AmountAwarded / trData$AmountApplied, 2)
trData_YY <- aggregate(ratio ~ year + Prj.Period, trData , mean)
trData_YY$year <- as.Date(trData_YY$year, "%Y") # aassuming Nov 11, since I formated on that day
cbbPalette <- c( "#ff4747", "#00f51d", "#b80000", "#0c00b8","#8e2edc","#1b0aff","#ffdb70","#ffc71f","#f5b800","#008f11","#009E73","#7a70ff", "#ff1f1f", "#140000")
#png(filename = "powerconsumption.png")
theme_set(theme_bw()) # BACK gROUND COLOR
p <- ggplot(trData_YY, aes(x=year, y=ratio)) +
geom_line(aes(colour=Prj.Period, group=Prj.Period), size=0.5) + # colour, group both depend on cond2
geom_point(aes(colour=Prj.Period), size=1) + # colour depends on cond2
scale_colour_manual(values=cbbPalette) + # larger points, different shape
labs(title = "Disbursement by Program Period") +
labs(y = "Amount Awarded / Amount Applied X 100 (%)", x = "Year")
ggplotly(p)
```