MTCS Data Modeling Report as at 2022-04-28

Process flow

The following diagram outlines the modeling process. There are 5 main modeling steps: Age estimation, CRV deflation, Geocoding/GIS and left_joining planning year needs.

grViz("digraph flowchart {
      # node definitions with substituted label text
      node [fontname = Helvetica, shape = rectangle,penwidth=2.0]        
      tab1 [label = '@@1']
      tab2 [label = '@@2']
      tab3 [label = '@@3']
      tab4 [label = '@@4']
      tab5 [label = '@@5']
      tab6 [label = '@@6']
      tab7 [label = '@@7']
      tab8 [label = '@@8']
      

      # edge definitions with the node IDs
      tab1 -> tab2;
      tab1 -> tab3;
      tab1 -> tab4;
      tab1 -> tab5;
      tab1 -> tab6;
      tab2 -> tab7;
      tab3 -> tab7;
      tab4 -> tab7;
      tab5 -> tab7;
      tab6 -> tab7;
      tab7 -> tab8;
      }

      [1]: 'Load Data and rename columns'
      [2]: 'Estimate effective ages'
      [3]: 'Create renewal flags'
      [4]: 'Estimate and deflate CRV'
      [5]: 'Geocoding and GIS mapping'
      [6]: 'Joining planning year needs'
      [7]: 'Formatting'
      [8]: 'Results'

      
      ")

Overview

Map

Interactive map displaying project locations. Click on each number circle to enlarge and hover each blue marker to view project address.

leaflet(data) %>%
  addTiles(options = providerTileOptions(minZoom = 2)) %>%
  addMarkers(clusterOptions=markerClusterOptions(),
             lng = ~as.numeric(data$`Asset - Map Longitude`),
             lat= ~as.numeric(data$`Asset - Map Latitude`),
             label = (data$`Asset - Address 1`)) %>%
  setView(-79.3933,43.6626,6)


Records Distribution

Pie chart displaying the distribution of records among 14 MTCS agencies.

d <- data %>% group_by(data$`Agency - Agency Name`) %>% summarise(count=n()) 
colnames(d) <- c('Agency','Count')
d$Agency <- as.factor(d$Agency)
d <- data.frame(d)

hc <- d %>%
  hchart(
    "pie", hcaes(x = Agency, y = Count),
    name = "Projects"
    )
hc %>%  hc_title(text="Records Distribution") %>%
  hc_subtitle(text="Hover each colour segment to view details")


Raw Data

Interactive table of input data. Currently showing 4 columns.

df <- data %>% select(`Agency - Agency Name`,`Asset - Address 1`,`Asset - FCI (2 Year Window)`,`Asset - Replacement Value (CAD)`)
datatable(df)


FCI

Boxplot displaying FCI distribution of each agency.

hcboxplot(x = data$`Asset - FCI (3 Year Window)`, var = data$`Agency - Agency Name`, name = "Length", color = "#2980b9") %>%
  hc_title(text="FCI Distribution") %>%
  hc_subtitle(text="Hover each boxplot segment to view details.")


Renaming columns and creating flags

The following chunk of code is used to rename columns and identify buildings that are still in service.

#changing cell types to characters, and left join by SID and AID 
assetinventoryMTCSHistFlag$SID = as.character(assetinventoryMTCSHistFlag$SID)
joint_tables <- left_join(assetinventoryMTCSHistFlag,conditionparam, by = c("SID"="SID","AID"="AID"))

#renaming columns and creating inService and Ownership flags
preparation <- data %>%
  #renaming
  rename(Grouping = `Agency - Agency Name`,Ownership=`Asset - Ownership`,Asset_Id = `Asset - ID`,Asset_Loc_Name=`Asset - Location Name`,
         AssetName=`Asset - Asset Name`,Address1=`Asset - Address 1`,City=`Asset - City`,PostalCode=`Asset - ZIP`,
         AssetType=`Asset - Type`,AssetUse=`Asset - Use`,Year_Constructed=`Asset - Year Constructed`,Year_Renovated=`Asset - Year Renovated`,
         FCI=`Asset - FCI`,FCI2=`Asset - FCI (2 Year Window)`,FCI3=`Asset - FCI (3 Year Window)`,ReplacementValue=`Asset - Replacement Value (CAD)`,
         Latitude=`Asset - Map Latitude`,Longitude=`Asset - Map Longitude`) %>%
  #Flags
  mutate(Organization = "MTCS",Address2=NA,TableId=NA,DataSet_Id=NA,Effective_Age=NA,CapacityType=NA,CapacityUnit=NA,Capacity=NA,UtilizationType=NA,UtilizationUnit=NA,Utilization=NA,DemandType=NA,DemandUnit=NA,Demand=NA,DimensionType=NA,DimensionUnit=NA,Dimension=NA,
         #ownership flag
         Owned=ifelse(Grouping=="Ontario Science Centre","Yes",
               ifelse(Grouping=="McMichael Canadian Art Collection","Yes",
               ifelse(Grouping=="Niagara Parks Commission","Yes",
               ifelse(Grouping=="Ontario Heritage Trust","Yes",
               ifelse(Grouping=="Ontario Place","Yes",
               ifelse(Grouping=="Ottawa Convention Centre","Yes",
               ifelse(Grouping=="Royal Ontario Museum","Yes",
               ifelse(Grouping=="Science North","Yes",
               ifelse(Grouping=="St. Lawrence Parks","Yes", 
               ifelse(Grouping=="Advisory Council to the Order of Ontario","Yes",
               ifelse(Grouping=="Metropolitan Toronto Convention Centre Corporation","Yes",
               ifelse(Grouping=="Ontario Honours Advisory Council","Yes",
               ifelse(Grouping=="Ontario Media Development Corporation","Yes",
               ifelse(Grouping=="Ontario Tourism Marketing Partnership Corporation","Yes",
               ifelse(Grouping=="Ontario Trillium Foundation","Yes", 
               ifelse(Grouping=="Province of Ontario Council for the Arts (Ontario Arts Council)","Yes",
               ifelse(Grouping=="Fort William","Yes",
               ifelse(Grouping=="Huronia Historical Park","Yes","No")))))))))))))))))),
         #service flag
         InService=ifelse(str_detect(AssetName,"Demolished|Obsolete"),"No","Yes")) %>%
         #AssetUse Column
         left_join(joint_tables, by = c("Grouping" = "Name"))


Effective Age

Estimation

The following chunk of code uses construction and rennovated years to estimate effective ages.

#Conditions/Agruments for effective age
preparation$Effective_Age <- ifelse(is.na(preparation$Effective_Age)=="FALSE",as.numeric(preparation$Effective_Age),
                                    ifelse(is.na(preparation$Effective_Age)=="TRUE" & is.na(preparation$Year_Renovated) =="FALSE" & ((preparation$Year_Renovated) - (preparation$Year_Constructed) >= preparation$UsefulLife)=="TRUE",(year+1-(preparation$Year_Renovated)),
                                           ifelse((is.na(preparation$Effective_Age))=="TRUE" & (is.na(preparation$Year_Renovated))=="FALSE" & ((preparation$Year_Renovated) -(preparation$Year_Constructed)<preparation$UsefulLife)=="TRUE",(year+1-(preparation$Year_Constructed)),
                                                  ifelse(is.na(preparation$Effective_Age)=="TRUE" & is.na(preparation$Year_Renovated)=="TRUE" | preparation$Year_Renovated==0 &   is.na(preparation$Year_Constructed)=="FALSE",(year+1-(preparation$Year_Constructed)),9999))))

Bar Chart

df_modelled <- preparation %>% group_by(Grouping) %>% summarise(Average.Age = mean(Effective_Age))

df_modelled %>% 
  hchart('bar', hcaes(x = 'Grouping', y = 'Average.Age')) %>%
  hc_title(text="Bar Chart displaying the average effective building age of each Agency") %>%
  hc_subtitle(text="Hover each bar to view details")


Renewal

Conditions

Identifying buildings that needs subsequent modelling.

Variable Description / Conditions
Replacement value greater than 0
Effective age greater or equal to 0 and less or equal to 200
Facility Condition Index (FCI) greater or equal to 0 and less or equal to 1
Buildings owned by the Government 
preparation <- preparation %>%
  mutate(Renewal=ifelse(ReplacementValue>0 & !is.null(ReplacementValue) & Effective_Age>=0 & Effective_Age <=200 & !is.null(FCI2) & FCI2>=0 & FCI2<=1 & !is.null(FCI2) & Owned=="Yes","Yes","No"))

# Add in a missing city. This record has address bu the city is missing. City name is found using address.
preparation$City[preparation$Asset_Id == "AST-3738"] <- "Toronto"


Analysis

Bar chart dispalying projects from each agency that requires modelling.

preparation %>% 
  select(FCI2,Grouping,Renewal) %>%
  count(Renewal,Grouping) %>% 
  hchart('bar', hcaes(x = 'Renewal', y = 'n', group = "Grouping")) %>%
  hc_title(text="Renewal Analysis") %>%
  hc_subtitle(text="Hover each coloured bar to view details")


CRV Deflation

This section deflates CRV to July 1st, 2019 dollars.

#OEPtable <- read_excel("//etcptovspifs005/Infrastructure Policy/PAI/Input/MOH/OEPInflationRate.xlsx")
OEPtable <- read_excel("./input/MTCS/OEPInflationRate.xlsx")

elapsed_years <- function(end_date, start_date) { 
    ed <- as.POSIXlt(end_date)
    sd <- as.POSIXlt(start_date)
    abs(ed$year - sd$year)
}

deflateusingOEPtable <- function(value, assessmentdate, enddate){
  yr = mapply(elapsed_years,enddate,assessmentdate)
  OEPdeflate = value
  i <- 0
  while(i < yr){
    annualrate <- OEPtable[OEPtable$Year==year(assessmentdate)-i, "Multiplier"]
    OEPdeflate <- OEPdeflate / (1+(annualrate/100))
    i = i + 1
  }
  return(OEPdeflate)
}

monthlyrate = (1+   0.01618226)^(1/12)-1

inflateoep <- addcrvflag %>%
  mutate("CRV As of 2019-01-01_OEP" = mapply(deflateusingOEPtable, as.numeric(ReplacementValue),"2020-01-01", "2019-01-01"))%>%
  mutate("CRV As of 2019-01-01_OEP_SS" = mapply(deflateusingOEPtable, as.numeric(crvforss),"2020-01-01", "2019-01-01"))%>%
  mutate(`CRV As of 2019-01-01_OEP` = as.numeric(unlist(`CRV As of 2019-01-01_OEP`))*(1+monthlyrate)^6)%>%
  mutate(`CRV As of 2019-01-01_OEP_SS` = as.numeric(unlist(`CRV As of 2019-01-01_OEP_SS`))*(1+monthlyrate)^6)

df_crv <- inflateoep %>% group_by(Agencies=Grouping) %>% summarise(Deflated.CRV = sum(`CRV As of 2019-01-01_OEP`),Submitted.CRV=sum(ReplacementValue))

kable(df_crv) %>%
  kable_styling(bootstrap_options = c("striped", "hover"))
Agencies Deflated.CRV Submitted.CRV
Art Gallery of Ontario 207020331 208688637
Fort William 40244056 40568369
Huronia Historical Park 35696693 35984361
McMichael Canadian Art Collection 35123782 35406832
Niagara Parks Commission 571374143 575978650
Ontario Heritage Trust 153561675 154799175
Ontario Library Service North 1333932 1344682
Ontario Place 186365439 187867294
Ontario Science Centre 192801847 194355571
Ottawa Convention Centre 95812521 96584641
Royal Botanical Gardens 100798033 101610330
Royal Ontario Museum 235218465 237114010
Science North 101072849 101887361
St. Lawrence Parks 283824854 286112101


Geocoding

Step 1: Analysis

This section studies the total number of records without Lat/Long and attempt to populate those empty fields with google geocode.

A total of 281 projects are missing latitude/longitude.

df <- (is.na(data$`Asset - Map Latitude`))
df <- data.frame(df)
df$df <- as.character(df$df)
names(df)<-"Lat/Long"
df2 <- cbind(data,df)
df2 <- df2 %>% mutate(`Lat/Long`=ifelse(`Lat/Long`=="FALSE","Not Missing","Missing"))

df2 %>% 
  count(`Agency - Agency Name`, `Lat/Long`) %>% 
  hchart('bar', hcaes(x = '`Lat/Long`', y = 'n', group = '`Agency - Agency Name`')) %>%
  hc_title(text="Bar chart displaying the number of records with & without lats/longs") %>%
  hc_subtitle(text="Hover each bar to view details")


Data

Table dispalying records without latitude and longitude

df2 <- df2 %>% filter(`Lat/Long`=="Missing") %>% select(`Agency - Agency Name`,`Asset - Asset Name`,`Asset - Address 1`,`Asset - FCI (3 Year Window)`)
datatable(df2,editable='cell',colnames=c("Agencies","Asset Name","Address","FCI"))


Step 2: Geocoding Scripts

Geocode script intaking Address and Postal Codes to obtain a set of appropriate Lat/Longs.

#geocode_results <- read_excel("//etcptovspifs005/Infrastructure Policy/PAI/Input/MTCS/2020MTCS_Geocode_results.xlsx")
geocode_results <- read_excel("./input/MTCS/2020MTCS_geocode_results.xlsx") 

havelatlong <- inflateoep %>% 
  filter(!is.na(Longitude)) %>%
  mutate(Final.Lat = Latitude)%>%
  mutate(Final.Long = Longitude)%>%
  mutate(Fill.In.LatLong = "No")%>%
  mutate(SID = as.numeric(SID))

filledlatlong <- geocode_results %>%
  mutate(Fill.In.LatLong = "Yes")%>%
  dplyr::select(-fulladdress, -`CRV As of 2019-01-01_OEP`, -`CRV As of 2019-01-01_OEP_SS`)

InflatedCRV <- inflateoep%>%
  select(Asset_Id , `CRV As of 2019-01-01_OEP`, `CRV As of 2019-01-01_OEP_SS`)

fillcomplete <- filledlatlong%>%
  left_join(InflatedCRV, by = c("Asset_Id" = "Asset_Id"))

#results <- union_all(filledlatlong, havelatlong)
results <- rbind(fillcomplete, havelatlong)


Step 3: Fixing Geocode Failure

Fixing data error generated by google geocodes.

#Assets with empty latitude ang longitude that was excluded earlier in the geocoding process
failed_GID1 <- results%>%
  filter(is.na(Longitude) & is.na(City) & is.na(Address1)& is.na(Final.Long))

building.latlong <- results %>%
  filter(!is.na(Final.Lat) & AssetType == 'Building')%>%
  dplyr::select(Grouping, Asset_Loc_Name, Final.Lat, Final.Long)

fix.site.latlong <- results %>%
  filter(results$Asset_Id %in% failed_GID1$Asset_Id & AssetType == 'Site')%>%
  dplyr::select(-Final.Lat, -Final.Long)%>%
  inner_join(building.latlong, by = c("Grouping" = "Grouping", "Asset_Loc_Name" = "Asset_Loc_Name" ))

Fort.Erie.latlong <- results %>% 
  filter(Grouping == "Niagara Parks Commission" & City == "Fort Erie" & grepl("Historic Fort Erie",AssetName))%>%
  dplyr::select(Latitude, Longitude)

fix.building.latlong <- results %>%
  filter(results$Asset_Id %in% failed_GID1$Asset_Id & AssetType != 'Site')%>%
  mutate(Final.Lat = Fort.Erie.latlong$Latitude)%>%
  mutate(Final.Long = Fort.Erie.latlong$Longitude)

geocode_success <- results%>%
  filter(!is.na(Final.Long))

final <- union_all(union_all(geocode_success,fix.site.latlong),fix.building.latlong)


Results

All 854 buildings are now plotted on the map with appropraite latitude and longitude.

leaflet(final) %>%
  addTiles(options = providerTileOptions(minZoom = 2)) %>%
  addMarkers(clusterOptions=markerClusterOptions(),
             lng = ~as.numeric(final$Final.Long),
             lat= ~as.numeric(final$Final.Lat),
             label = (final$Address1)) %>%
  setView(-79.3933,43.6626,6)


GIS Mapping

Mapping latitude and longitude onto Shapefiles and obtain census division information (CSDUID). Results attached below.

AssignGIDs <- function(Longitude, Latitude) { 
  ontario <- readOGR("./input/Shape Files/map.shp")
  coords = data.frame(cbind(Longitude,Latitude))
  names(coords)<- c("long","lat")
  coordinates(coords) <- ~long+lat
  coords@proj4string <- ontario@proj4string
  #Assigning GIDs
  #Left Join to include all.
  fix <- data.frame(st_join(st_as_sf(coords),left=TRUE,st_as_sf(ontario),join=st_intersects)) %>% select(-geometry)
  #Requires geos 3.6.1 on server, having issues installing.
  # fix <- data.frame(st_join(st_as_sf(coords),left=FALSE,st_as_sf(ontario),join=st_nearest_feature)) %>% select(-geometry)
  #Using  HidePlots global variable to skip generating plots when building GUT.
  if(!exists("HidePlots")) {
    #to address 'figure margins too large' errors
    par(mar=c(1,1,1,1))
    map <- plot(ontario, border = "grey")
    axis(1)
    axis(2)
    points(coords, col = "red", pch=19)
  }
  return(fix)
}

gid <- AssignGIDs(final$Final.Long, final$Final.Lat)
## OGR data source with driver: ESRI Shapefile 
## Source: "C:\Users\Stevie\Desktop\New folder (3)\input\Shape Files\map.shp", layer: "map"
## with 575 features
## It has 18 fields

final <- cbind(final,gid)

#Converting variables back to numeric 
convert_numeric <- final %>%
  mutate(`Asset - Size`=as.numeric(`Asset - Size`),
         Year_Constructed=as.numeric(Year_Constructed),
         Year_Renovated=as.numeric(Year_Renovated),
         FCI=as.numeric(FCI),
         FCI2=as.numeric(FCI2),
         FCI3=as.numeric(FCI3),
         ReplacementValue=as.numeric(ReplacementValue),
         Effective_Age=as.numeric(Effective_Age),
         `CRV As of 2019-01-01_OEP`=as.numeric(`CRV As of 2019-01-01_OEP`),
         UsefulLife=as.numeric(UsefulLife),
         crvforss=as.numeric(crvforss))


Formatting

Dropping columns and renaming variables

Cleaning the table, renaming fields and dropping some unecessary columns.

#dropping unused columns
trimresult <- subset(convert_numeric,select=-c(Sector,Beta,Sector_SubsectorID,AgeAtSOGR,TargetMedianCondition,TargetLowerDecileCondition,TargetConditionUnit,RehabThreshold,
                                               Epsilon,SOGR,Failure,TargetConditionUnit,Historic,SubSector,AssetSubclassId,AssetClass.y,AssetClass.x,Organization.y,
                                               Ownership.y))

#Renaming columns
trimresult <- trimresult %>%
  rename(Agency.AgencyName=Grouping,
         Asset.LocationName=Asset_Loc_Name,
         Asset.Name=AssetName,
         Ministry.Asset.ID=Asset_Id,
         Asset.Size.Sq.ft=`Asset - Size`,
         Asset.Address=Address1,
         Asset.City=City,
         Asset.Postal.Code=PostalCode,
         Asset.Ownership.Status=Ownership.x,
         Asset.Type=AssetType,
         Asset.Use=AssetUse,
         Asset.Year.Constructed=Year_Constructed,
         Asset.Year.Renovated=Year_Renovated,
         FCI.6Yr.Calculation=FCI,
         FCI.4Yr.Calculation=FCI3,
         FCI.3Yr.Calculation=FCI2,
         Asset.Current.Replacement.Value=ReplacementValue,
         CRV.As.of.2019.OEP=`CRV As of 2019-01-01_OEP`,
         CRV.As.of.2019.OEP.SS=`CRV As of 2019-01-01_OEP_SS`,
         CRV.Used.By.SS=crvforss) %>%
  mutate(Currency.Year=year)

Identifier

The following chunk of code is used to create MOI asset and record IDs.

#Add in flags for CRV and FCI reporting
trimresult$CRV.Report <- ifelse(trimresult$InService == "Yes" & trimresult$Owned == "Yes", "Yes", "No")

trimresult$FCI.Report <- ifelse(trimresult$InService == "Yes" & trimresult$Owned == "Yes", "Yes", "No")

#ID
trimresult <- trimresult %>% unite("MOI.Asset.ID",c("Agency.AgencyName","Asset.Name","Ministry.Asset.ID"),sep="",na.rm=TRUE, remove = FALSE)
trimresult <- generateRecordID(trimresult)


Planning Year Needs

Flowchart

Planning year needs of each agency is displayed in the following spline chart.

forecast <- readRDS("./input/MTCS/MTCS.Forecast.rds")

forecast2 <- forecast %>% select(-c(Location,SheetName,Asset))
forecast2[is.na(forecast2)] <- 0

d <- forecast2 %>% group_by(Agency) %>% summarise("2020"=sum(Renewal.Requirements.2020),"2021"=sum(Renewal.Requirements.2021),"2020"=sum(Renewal.Requirements.2022),"2023"=sum(Renewal.Requirements.2023),"2024"=sum(Renewal.Requirements.2024),"2025"=sum(Renewal.Requirements.2025),"2026"=sum(Renewal.Requirements.2026),"2027"=sum(Renewal.Requirements.2027),"2028"=sum(Renewal.Requirements.2028),"2029"=sum(Renewal.Requirements.2029),"2030"=sum(Renewal.Requirements.2030))

d <- gather(d,"Year","PY.Needs",2:11) %>% mutate(Year=as.numeric(Year))

d %>% hchart("spline",hcaes(x = Year,y=PY.Needs,group=Agency)) %>% hc_title(text="Planning Year needs") %>% hc_subtitle(text="Hover each line to view details and click on each agency to apply filters")

Joining VFA data

The following chunk of code joins Planning year needs data to 2020 PAI.

#Asset Names for AST 3278 and AST 331 are not unique - require modifications
trimresult <- trimresult  %>%
  mutate(Asset.Name=ifelse(Ministry.Asset.ID=="AST-3278","Gazebo (1)",Asset.Name)) %>%
  mutate(Asset.Name=ifelse(Ministry.Asset.ID=="AST-331","Gazebo (2)",Asset.Name))

forecast <- forecast %>%
  mutate(Asset=ifelse(SheetName=="s_1135Requirement Forecast Re","Gazebo (1)",Asset)) %>%
  mutate(Asset=ifelse(SheetName=="s_1137Requirement Forecast Re","Gazebo (2)",Asset)) 

#Joining
trimresult <- trimresult %>%
  left_join(forecast,by=c("Asset.LocationName"="Location","Asset.Name"="Asset"))

#Editing Columns for GUT (Positions of Columns are TBD)
trimresult["Sector"] <- "Other"
trimresult["Ministry"] <- "MTCS"
trimresult ["Asset.Size.Sq.M"]<- trimresult$Asset.Size.Sq.ft * 0.3048
trimresult$MOI.Generated.CRV <- NULL
trimresult$FCI.Estimate.Method <- "None"
names(trimresult)[names(trimresult) == 'FCI.3Yr.Calculation'] <- 'Submitted.3Yr.FCI'

#renaming Planning Year needs for GUT (Naming changed as of June 15th)
trimresult <- trimresult  %>%  rename(PY.Needs.2020=Renewal.Requirements.2020,PY.Needs.2021=Renewal.Requirements.2021,PY.Needs.2022=Renewal.Requirements.2022,PY.Needs.2023=Renewal.Requirements.2023,PY.Needs.2024=Renewal.Requirements.2024,PY.Needs.2025=Renewal.Requirements.2025,PY.Needs.2026=Renewal.Requirements.2026,PY.Needs.2027=Renewal.Requirements.2027,PY.Needs.2028=Renewal.Requirements.2028,PY.Needs.2029=Renewal.Requirements.2029,PY.Needs.2030=Renewal.Requirements.2030,PY.Needs.2031=Renewal.Requirements.2031,PY.Needs.2032=Renewal.Requirements.2032,PY.Needs.2033=Renewal.Requirements.2033,PY.Needs.2034=Renewal.Requirements.2034,PY.Needs.2035=Renewal.Requirements.2035,PY.Needs.2036=Renewal.Requirements.2036,PY.Needs.2037=Renewal.Requirements.2037,PY.Needs.2038=Renewal.Requirements.2038,PY.Needs.2039=Renewal.Requirements.2039)

Results

Preparing results for the Grand Unified Table. Click the link below to download the resulting dataset.

MTCS <- trimresult %>% 
  
  #FCI
  rename(Submitted.3Yr.FCI=Submitted.3Yr.FCI) %>%
  mutate(MOI.Generated.FCI=NA,Final.FCI=Submitted.3Yr.FCI) %>%
  mutate(FCI.Rating=ifelse(Final.FCI<=0.05,"A",ifelse(Final.FCI>0.05 & Final.FCI<=0.10,"B",ifelse(Final.FCI>0.10 &Final.FCI<=0.3,"C",ifelse(Final.FCI>0.3 & Final.FCI<=0.6,"D",ifelse(Final.FCI>0.6,"E","ERROR")))))) %>%
  mutate(FCI.Category=ifelse(Final.FCI<=0.1,"GOOD",ifelse(Final.FCI>0.1 & Final.FCI<=0.3,"FAIR",ifelse(Final.FCI>0.3,"POOR","ERROR")))) %>%
  
  #Latitude/Longitude %>%
  rename(Submitted.Lat=Latitude,Submitted.Long=Longitude) %>%
  mutate(MOI.Generated.Lat=Final.Lat,MOI.Generated.Long=Final.Long) %>%
  
  #CRV
  rename(Submitted.CRV=Asset.Current.Replacement.Value)%>%
  mutate(MOI.Generated.CRV=NA)%>%
  mutate(Final.CRV=Submitted.CRV)%>%
  #CRV.Used.By.SS needed?
  
  #Age
  rename(MOI.Generated.Age=Effective_Age)%>%
  mutate(Final.Age=MOI.Generated.Age)%>%
  left_join(data,by=c("Ministry.Asset.ID"="Asset - ID"))%>%
  mutate(Submitted.Age=NA) %>%
  mutate(Age.Estimate.Method="Using Year_Constructed and Year_Renovated to estimate an effective age") %>%

  
  #Columns selection (Removed MOI.Generated.CRV as of June 15th)
  select(Currency.Year,Agency.AgencyName,Sector,Asset.LocationName,Asset.Name,Ministry,Ministry.Asset.ID,Asset.Size.Sq.ft,Asset.Size.Sq.M,Asset.Address,Asset.City,Asset.Postal.Code,Asset.Ownership.Status,Asset.Type,Asset.Use,Asset.Year.Constructed,Asset.Year.Renovated,FCI.6Yr.Calculation,FCI.4Yr.Calculation,Submitted.3Yr.FCI,MOI.Generated.FCI,Final.FCI,FCI.Rating,FCI.Category,Submitted.Age,MOI.Generated.Age,Final.Age,Age.Estimate.Method,Submitted.CRV,Final.CRV,CRV.As.of.2019.OEP,CRV.As.of.2019.OEP.SS,Submitted.Lat,Submitted.Long,MOI.Generated.Lat,MOI.Generated.Long,Final.Lat,Final.Long,CSDUID:CMATYPE,Renewal,InService,Owned,CRV.Report,FCI.Report,PY.Needs.2020:Non.Renewal.Requirements.2039,MOI.Asset.ID,MOI.Record.ID,SheetName)


# Link to csv
fname <- sprintf("%s.csv", Sys.Date())
write.csv(MTCS, file = fname)
rmarkdown::output_metadata$set(rsc_output_files = list(fname))

Link to Excel


Ministry of Infrastructure 2020