Hello, I am a project manager of a large American city agency that owns property and rooftop assets.
I have been attempting to use nesting functions for the range of data in one large dataset that covers large acres of ground areas (sites known as "developments"); building structures; and building rooftop items (roof fan assets).
I would like to compute a nesting scheme wherein all roof fan assets (incl. related observation info) are nested in a single row for every building to which it belongs; and every building (incl. any chosen observation info) is nested in a single row for every site to which it belongs; and that this nesting scheme can be written to an xlsx format Excel datasheet with all the list-columns of the nested rows in a fully readable and concatenated list.
In addition, I would like to be able to attach summary rows based on additional observations made while tracking info based on the roof fans, buildings and developments.
I was able to complete a data scheme close to this about 2 years ago using the "map2_df" function and a series of data "merges"; unfortunately it turned out to be a fluke because several months later I was not able to recreate the results.
Please find the reprex to my code attached. I tried to keep it as clean, direct and brief as possible.
#Load libraries
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(lookup)
library(readxl)
library(stringr)
library(reprex)
DvlmtStatusData<- read_excel("H:/NYCHA Analysis/R Studio Projects/OMAR Data/OMAR Project Data/GIS_PM_RoofFanVentilationSystemsProjects.xlsx",
sheet = "Development Status")
DvlmtStatusData1 <- DvlmtStatusData[-262,]
glimpse(DvlmtStatusData1)
#> Rows: 261
#> Columns: 52
#> $ `Borough Grouping` <chr> "Bronx", "Brooklyn", "Br…
#> $ Consolidation <chr> "1010 East 178th Street"…
#> $ `Development Name` <chr> "1010 East 178th Street"…
#> $ TDS <dbl> 180, 242, 233, 154, 214,…
#> $ TDS_Num <dbl> 180, 242, 233, 154, 214,…
#> $ `In Project` <chr> "Yes", "No", "Yes", "Yes…
#> $ Phase <chr> "Phase 2", "Not Assigned…
#> $ `Phase - Detail` <chr> "Phase 2 - Batch 3", "-"…
#> $ `Roll Out Order` <chr> "16", "-", "8", "19", "1…
#> $ `Current Status Rank` <dbl> 12, 1, 12, 12, 12, 1, 12…
#> $ `Current Status` <chr> "Fans Installed", "Bypas…
#> $ `Development Complete Date` <chr> "44601", "-", "44672", "…
#> $ `Status Simplified` <chr> "Fans Installed", "Bypas…
#> $ `Engineering Docs Received` <chr> "Yes", "No", "Yes", "Yes…
#> $ `Engineering Docs Reconciled` <chr> "Yes", "No", "Yes", "Yes…
#> $ `Asbestos Investigation Complete` <chr> "Completed", "N/A", "Com…
#> $ `Asbestos Status` <chr> "Asbestos Abated", "Not …
#> $ `Total Buildings` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, …
#> $ `Total Buildings with Asbestos` <chr> "0", "-", "0", "0", "0",…
#> $ `Engineer Name` <chr> "LiRo", "Not Assigned", …
#> $ `Contractor Assigned` <chr> "Yes", "No", "Yes", "Yes…
#> $ `Contractor Name` <chr> "Sahara", "Not Assigned"…
#> $ `Development Bypassed` <chr> "No", "Yes", "No", "No",…
#> $ `Reason for Bypassing` <chr> "-", "Capital Flag", "-"…
#> $ `Ready to Assign to Contractor` <chr> "No", "No", "No", "No", …
unique(DvlmtStatusData1$TDS_Num)
#> [1] 180 242 233 154 214 235 156 203 185 265 150 118 31 85 187 256 125 202
#> [19] 91 60 198 92 165 311 345 52 160 54 243 271 189 138 46 346 264 157
#> [37] 325 16 304 86 303 113 286 166 164 58 206 80 134 176 334 307 308 335
#> [55] 336 123 236 94 239 238 216 232 69 199 312 70 190 155 148 82 582 111
TDS’ code which is based on ‘TDS_Num’ should be a vector of 3 digit numerical
#characters for data consistence
unique(DvlmtStatusData1$TDS)
#> [1] 180 242 233 154 214 235 156 203 185 265 150 118 31 85 187 256 125 202
#> [19] 91 60 198 92 165 311 345 52 160 54 243 271 189 138 46 346 264 157
#> [37] 325 16 304 86 303 113 286 166 164 58 206 80 134 176 334 307 308 335
#> [55] 336 123 236 94 239 238 216 232 69 199 312 70 190 155 148 82 582 111
#> [73] 41 224 237 338 215 208 263 57 15 29 207 59 197 309 136 252 225 171
#> [91] 100 25 281 87 68 40 142 75 262 147 367 362 184 78 360 159 72 365
#> [109] 168 526 109 316 139 120 64 17 67 30 161 76 152 122 186 201 101 348
#Create 3 digit vector of TDS#code to prepare for data joining with argument that makes sure
#the system doesn't ignore zero-padding
DvlmtStatusData1$TDS <- sprintf("%03.0f", as.numeric(DvlmtStatusData1$TDS_Num))
unique(DvlmtStatusData1$TDS)
#> [1] "180" "242" "233" "154" "214" "235" "156" "203" "185" "265" "150" "118"
#> [13] "031" "085" "187" "256" "125" "202" "091" "060" "198" "092" "165" "311"
#> [25] "345" "052" "160" "054" "243" "271" "189" "138" "046" "346" "264" "157"
#> [37] "325" "016" "304" "086" "303" "113" "286" "166" "164" "058" "206" "080"
#> [49] "134" "176" "334" "307" "308" "335" "336" "123" "236" "094" "239" "238"
#> [61] "216" "232" "069" "199" "312" "070" "190" "155" "148" "082" "582" "111"
#> [229] "356" "240" "261" "399" "343" "355" "061" "146" "315" "074" "023" "062"
#> [241] "293" "523" "329" "330" "331" "141" "116" "559" "341" "246" "124" "002"
#> [253] "112" "127" "033" "182" "178" "151" "173" "174" "163"
DvlmtStatusData2 <- DvlmtStatusData1 %>%
select(`Borough Grouping`, TDS, Consolidation,
`Development Name`, `Total Buildings`, `Total Fans`, `In Project`, `Development Bypassed`,
`Reason for Bypassing` , `Fans Installed`,`Fans Retained`,`Non-Residential`)
View(DvlmtStatusData2)
glimpse(DvlmtStatusData2)
#> Rows: 261
#> Columns: 12
#> $ `Borough Grouping` <chr> "Bronx", "Brooklyn", "Bronx", "Manhattan", "Bro…
#> $ TDS <chr> "180", "242", "233", "154", "214", "235", "156"…
#> $ Consolidation <chr> "1010 East 178th Street", "Reid Apartments", "C…
#> $ `Development Name` <chr> "1010 East 178th Street", "104-14 Tapscott Stre…
#> $ `Total Buildings` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 7, 6, 3, 1, 1,…
#> $ `Total Fans` <dbl> 13, 4, 5, 4, 13, 8, 13, 13, 11, 6, 8, 59, 7, 22…
#> $ `In Project` <chr> "Yes", "No", "Yes", "Yes", "Yes", "No", "Yes", …
#> $ `Development Bypassed` <chr> "No", "Yes", "No", "No", "No", "Yes", "No", "Ye…
#> $ `Reason for Bypassing` <chr> "-", "Capital Flag", "-", "-", "-", "Private Ma…
#> $ `Fans Installed` <dbl> 12, 0, 4, 4, 0, 0, 13, 0, 0, 6, 6, 35, 7, 22, 7…
#> $ `Fans Retained` <chr> "1", "-", "1", "0", "13", "-", "0", "-", "-", "…
#> $ `Non-Residential` <chr> "0", "-", "0", "0", "0", "-", "0", "-", "-", "0…
#Create data subset of site locations to prepare test sample
DvlmtStatusData3 <- DvlmtStatusData2 %>%
filter(TDS %in% c("002", "015", "016", "017", "020"),)
str(DvlmtStatusData3)
#> tibble [5 × 12] (S3: tbl_df/tbl/data.frame)
#> $ Borough Grouping : chr [1:5] "Brooklyn" "Manhattan" "Manhattan" "Manhattan" ...
#> $ TDS : chr [1:5] "016" "015" "017" "020" ...
#> $ Consolidation : chr [1:5] "Brownsville" "Chelsea" "Johnson" "Lincoln" ...
#> $ Development Name : chr [1:5] "Brownsville" "Elliot" "Johnson" "Lincoln" ...
#> $ Total Buildings : num [1:5] 14 4 3 4 1
#> $ Total Fans : num [1:5] 14 8 4 4 2
#> $ In Project : chr [1:5] "Yes" "Yes" "Yes" "No" ...
#> $ Development Bypassed: chr [1:5] "No" "No" "Yes" "Yes" ...
#> $ Reason for Bypassing: chr [1:5] "-" "-" "New Confirmed Fans" "Capital Flag" ...
#> $ Fans Installed : num [1:5] 4 6 0 0 0
#> $ Fans Retained : chr [1:5] "10" "0" "4" "0" ...
#> $ Non-Residential : chr [1:5] "0" "2" "0" "0" ...
#Read in project data tracked at a bldg status level
BldgBreakdownPrjData <- read_excel("H:/NYCHA Analysis/R Studio Projects/OMAR Data/OMAR Project Data/GIS_PM_RoofFanVentilationSystemsProjects.xlsx",
sheet = "BldgBreakdown")
dim(BldgBreakdownPrjData)
#> [1] 1401 38
View(BldgBreakdownPrjData)
glimpse(BldgBreakdownPrjData)
#> Rows: 1,401
#> Columns: 38
#> $ `Building ID` <chr> "180.01", "242.01", "233.01", "154…
#> $ Dummy <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ `Property Management` <chr> "Bronx", "Brooklyn", "Bronx", "Man…
#> $ Consolidation <chr> "1010 East 178th Street", "Reid Ap…
#> $ `Development Name` <chr> "1010 East 178th Street", "104-14 …
#> $ Address <chr> "1010 EAST 178TH STREET, BRONX, NY…
#> $ Phase <chr> "Phase 2", "Not Assigned", "Phase …
#> $ `Phase - Detail` <chr> "Phase 2 - Batch 3", "-", "Phase 2…
#> $ `Roll-Out Order` <chr> "16", "-", "8", "19", "13", "-", "…
#> $ TDS <chr> "180", "242", "233", "154", "214",…
#> $ Engineer <chr> "LiRo", "Not Assigned", "H2M", "H2…
#> $ Contractor <chr> "Sahara", "Not Assigned", "TryStat…
#> $ `Status Rank` <dbl> 12, 1, 12, 12, 12, 1, 12, 1, 1, 12…
#> $ `Development Status` <chr> "Fans Installed", "Bypassed due to…
#> $ `Asbestos Investigation Completed` <chr> "Completed", "N/A", "Completed", "…
#> $ `Asbestos Status` <chr> "Asbestos Abated", "Bypassed", "As…
#> $ `Asbestos Filing Completed` <chr> "Completed", "-", "Not Completed",…
#> $ `Fan Delivery Date` <chr> "2/3/2022", "-", "10/12/2021", "Co…
#> $ `Total Building Fans` <dbl> 13, 4, 5, 4, 13, 14, 13, 13, 11, 6…
#> $ Notes <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ Validation <chr> "True", "True", "True", "True", "T…
#> $ `Fans Procured` <dbl> 12, 0, 4, 4, 0, 0, 13, 0, 0, 6, 6,…
#> $ `Fans Pending Installation` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Outside Initiative` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ Bypassed <dbl> 0, 4, 0, 0, 0, 14, 0, 13, 11, 0, 0…
#> $ `Fans Installed` <dbl> 12, 0, 4, 4, 0, 0, 13, 0, 0, 6, 6,…
#> $ LabelingStatus <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ ReplacementRoofFanSecurement <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ `ReplacementRoofFanSecurement Date` <dttm> NA, NA, NA, NA, NA, NA, NA, NA, N…
BldgBreakdownPrjData1 <- BldgBreakdownPrjData %>%
select(TDS,`Development Name`, Consolidation, `Building ID`,
`Development Status`,`Property Management`, Address, Dummy,
Phase,`Phase - Detail`,
`Total Building Fans`,
`Fans Retained`,`Fans Installed`, `Non-Residential`)
View(BldgBreakdownPrjData1)
BldgBreakdownNested<- BldgBreakdownPrjData1 %>%
nest_by(TDS, `Property Management`, `Development Name`, Consolidation)
colnames(BldgBreakdownNested)[5] <- "Nested Bldg Data"
BldgBreakdownNestedSample <- BldgBreakdownNested[c(1:5),]
glimpse(BldgBreakdownNestedSample)
#> Rows: 5
#> Columns: 5
#> Rowwise: TDS, Property Management, Development Name, Consolidation
#> $ TDS <chr> "002", "015", "016", "017", "020"
#> $ `Property Management` <chr> "Brooklyn", "Manhattan", "Brooklyn", "Manhattan"…
#> $ `Development Name` <chr> "Williamsburg", "Elliot", "Brownsville", "Johnso…
#> $ Consolidation <chr> "Williamsburg", "Chelsea", "Brownsville", "Johns…
#> $ `Nested Bldg Data` <list<tibble[,10]>> [<tbl_df[1 x 10]>], [<tbl_df[4 x 10]>], [<tbl_df…
DvlmtBldgSampleJoin <- full_join(DvlmtStatusData3, BldgBreakdownNestedSample, by = "TDS")
#Load in project tracking at individual roof fan asset level
suppressWarnings({ProjectRecordData <- read_excel("H:/NYCHA Analysis/R Studio Projects/OMAR Data/OMAR Project Data/GIS_PM_RoofFanVentilationSystemsProjects.xlsx",
sheet = "ProjectRecord", col_types = c("text",
"text", "text", "text", "text", "text",
"text", "text", "text", "text", "text",
"text", "text", "date","date","numeric",
"text", "text", "text", "text", "text",
"text", "text", "text", "numeric",
"numeric", "numeric", "text", "text",
"text", "text", "text", "text", "numeric",
"text", "numeric", "numeric", "numeric",
"numeric", "text", "text", "text",
"text", "numeric", "numeric", "text",
"text", "numeric", "text", "text",
"text", "text", "text", "text", "text",
"text", "numeric", "numeric", "text",
"numeric", "text", "text", "numeric",
"text", "text", "text", "text", "numeric",
"numeric", "numeric", "numeric",
"numeric", "numeric", "numeric",
"numeric", "numeric", "numeric",
"numeric", "numeric", "numeric",
"numeric", "numeric", "numeric",
"text", "text", "text", "text", "text",
"text", "text"))
})
glimpse(ProjectRecordData)
#> Rows: 10,772
#> Columns: 90
#> $ `Borough Grouping` <chr> "Bronx", "Bronx", "Bronx", "Bronx"…
#> $ `Managed By` <chr> "1010 EAST 178TH STREET", "1010 EA…
#> $ Development <chr> "1010 EAST 178TH STREET", "1010 EA…
#> $ `Managed By (Proper)` <chr> "1010 East 178th Street", "1010 Ea…
#> $ `Development (Proper)` <chr> "1010 East 178th Street", "1010 Ea…
#> $ `Roof Fan Asset ID` <chr> "180.01.16", "180.01.01", "180.01.…
#> $ AssetNumber <chr> "718070", "727933", "726596", "723…
#> $ `AssetPost-Analysis` <chr> "TRUE", "TRUE", "TRUE", "TRUE", "T…
#> $ `Asset #` <chr> "718070", "727933", "726596", "723…
#> $ `Engineering Labelling` <chr> "EF-10", "EF-1", "EF-3", "EF-2", "…
#> $ `EF Tag Actual Stamp` <chr> "EF-10", "EF-1", "EF-3", "EF-2", "…
#> $ AssetLabelStatus <chr> "Labeled", "Labeled", "Labeled", "…
#> $ LabelingProjectPhase <chr> "Possible QA Check", "Possible QA …
#> $ DateAssetLabeled <dttm> 2022-07-15, 2022-07-15, 2022-07-1…
#> $ DateAssetReLabeled <dttm> NA, NA, NA, NA, NA, NA, NA, NA, N…
#> $ Dummy <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ Latitude <chr> "40.8410699579073", "40.8409058844…
#> $ Longitude <chr> "-73.8802458982962", "-73.88044420…
#> $ RoofFanSecurementStatus <chr> "N/A: Fans Installed - Fan to be R…
#> $ GravityDamperCondition <chr> "N/A", "Intact", "Intact", "Intact…
#> $ `Damper Space Length(in.)` <chr> "N/A", "Not Measured", "Not Measur…
#> $ `Damper Space Width(in.)` <chr> "N/A", "Not Measured", "Not Measur…
#> $ `Total Dimensions(in.)` <chr> "N/A", "Not Measured", "Not Measur…
#> $ DateSecured <chr> "N/A", "44944", "44944", "44944", …
#> $ `Capital Flag` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `RAD Flag` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Private Flag` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Location Bypassed` <chr> "No", "No", "No", "No", "No", "No"…
#> $ `Reason for Bypassing` <chr> "-", "-", "-", "-", "-", "-", "-",…
#> $ Reconciled <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Building ID` <chr> "180.01", "180.01", "180.01", "180…
#> $ `Building Text Check` <chr> "TRUE", "TRUE", "TRUE", "TRUE", "T…
#> $ `Building Number` <chr> "1", "1", "1", "1", "1", "1", "1",…
#> $ `Building Fan Count` <dbl> 13, 13, 13, 13, 13, 13, 13, 13, 13…
#> $ `Building Address` <chr> "1010 EAST 178TH STREET", "1010 EA…
#> $ `Development Fan Count` <dbl> 13, 13, 13, 13, 13, 13, 13, 13, 13…
#> $ `Unique Consolidation Count` <dbl> 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Unique Development Count` <dbl> 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Unique Development Building Count` <dbl> 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ TDS_num <chr> "180", "180", "180", "180", "180",…
#> $ TDS_text <chr> "180", "180", "180", "180", "180",…
#> $ `TDS Text Check` <chr> "TRUE", "TRUE", "TRUE", "TRUE", "T…
#> $ `Asset Verified` <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Time Clock Present` <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ `Clock Bypassed` <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ `Engineering Rank` <chr> "10", "1", "3", "2", "4", "5", "6"…
#> $ `Replacement Roof Fan Model` <chr> "None", "G-100-VG", "G-100-VG", "G…
#> $ `Attachment/Extension Required?` <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ `Fan to Be Retained` <chr> "Yes", "No", "No", "No", "No", "No…
#> $ `Retention Type` <chr> "Engineering", "-", "-", "-", "-",…
#> $ `Roll-Out Order` <chr> "16", "16", "16", "16", "16", "16"…
#> $ Phase <chr> "Phase 2", "Phase 2", "Phase 2", "…
#> $ `Phase Detail` <chr> "Phase 2 - Batch 3", "Phase 2 - Ba…
#> $ `Development Status Rank` <chr> "12", "12", "12", "12", "12", "12"…
#> $ `Fan Status` <chr> "Fans Installed - Fan to be Retain…
#> $ `Fan Status_Full` <chr> "0) Fan to be Retained", "12) Fans…
#> $ `Installation Date` <dbl> NA, 44601, 44601, 44601, 44601, 44…
#> $ `Install Age` <dbl> NA, 357, 357, 357, 357, 357, 357, …
#> $ `Install Quarter` <chr> "-", "Q1 - 2022", "Q1 - 2022", "Q1…
#> $ `Fan Installation Month` <dbl> NA, 44601, 44601, 44601, 44601, 44…
#> $ `Engineer Assigned` <chr> "LiRo", "LiRo", "LiRo", "LiRo", "L…
#> $ `Asbestos Filing Status` <chr> "No Asbestos", "Completed", "Compl…
#> $ `Fan Delivery Date` <dbl> 44595, 44595, 44595, 44595, 44595,…
#> $ `Contractor Name` <chr> "Sahara", "Sahara", "Sahara", "Sah…
#> $ `Secondary Contractor` <chr> "No", "No", "No", "No", "No", "No"…
#> $ `Asbestos Flag` <chr> "Asbestos Abated", "Asbestos Abate…
#> $ `Asbestos Tested` <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Asbestos Tested #` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ `Asbestos Flag #` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `In Procurement` <dbl> 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ `Bypass #` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Not Yet Assigned` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ Asbestos <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Initial Asbestos` <dbl> 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ `Non-Residential` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `To Be Retained` <dbl> 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Preliminary Preparation` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Order Initiated` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Fans Ordered` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Fans Installed` <dbl> 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ `Outside Initiative` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ `Not on Manufacturer List` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
#> $ Validation <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
#> $ `Assigned to Engineer` <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Site Visit Completed` <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Engineering Docs Received` <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Contractor Assigned` <chr> "Yes", "Yes", "Yes", "Yes", "Yes",…
#> $ `Fan Order Initiated` <chr> "No", "Yes", "Yes", "Yes", "Yes", …
#> $ `Fan Order Confirmed` <chr> "No", "Yes", "Yes", "Yes", "Yes", …
#> $ `Fans Installed2` <chr> "No", "Yes", "Yes", "Yes", "Yes", …
colnames(ProjectRecordData)
#> [1] "Borough Grouping" "Managed By"
#> [3] "Development" "Managed By (Proper)"
#> [5] "Development (Proper)" "Roof Fan Asset ID"
#> [7] "AssetNumber" "AssetPost-Analysis"
#> [9] "Asset #" "Engineering Labelling"
#> [11] "EF Tag Actual Stamp" "AssetLabelStatus"
#> [13] "LabelingProjectPhase" "DateAssetLabeled"
#> [15] "DateAssetReLabeled" "Dummy"
#> [17] "Latitude" "Longitude"
#> [19] "RoofFanSecurementStatus" "GravityDamperCondition"
#> [21] "Damper Space Length(in.)" "Damper Space Width(in.)"
#> [23] "Total Dimensions(in.)" "DateSecured"
#> [25] "Capital Flag" "RAD Flag"
#> [27] "Private Flag" "Location Bypassed"
#> [29] "Reason for Bypassing" "Reconciled"
#> [31] "Building ID" "Building Text Check"
#> [33] "Building Number" "Building Fan Count"
#> [35] "Building Address" "Development Fan Count"
#> [37] "Unique Consolidation Count" "Unique Development Count"
#> [39] "Unique Development Building Count" "TDS_num"
#> [41] "TDS_text" "TDS Text Check"
#> [43] "Asset Verified" "Time Clock Present"
#> [45] "Clock Bypassed" "Engineering Rank"
#> [47] "Replacement Roof Fan Model" "Attachment/Extension Required?"
#> [49] "Fan to Be Retained" "Retention Type"
#> [51] "Roll-Out Order" "Phase"
#> [53] "Phase Detail" "Development Status Rank"
#> [55] "Fan Status" "Fan Status_Full"
#> [57] "Installation Date" "Install Age"
#> [59] "Install Quarter" "Fan Installation Month"
#> [61] "Engineer Assigned" "Asbestos Filing Status"
#> [63] "Fan Delivery Date" "Contractor Name"
#> [65] "Secondary Contractor" "Asbestos Flag"
#> [67] "Asbestos Tested" "Asbestos Tested #"
#> [69] "Asbestos Flag #" "In Procurement"
#> [71] "Bypass #" "Not Yet Assigned"
#> [73] "Asbestos" "Initial Asbestos"
#> [75] "Non-Residential" "To Be Retained"
#> [77] "Preliminary Preparation" "Order Initiated"
#> [79] "Fans Ordered" "Fans Installed"
#> [81] "Outside Initiative" "Not on Manufacturer List"
#> [83] "Validation" "Assigned to Engineer"
#> [85] "Site Visit Completed" "Engineering Docs Received"
#> [87] "Contractor Assigned" "Fan Order Initiated"
#> [89] "Fan Order Confirmed" "Fans Installed2"
colnames(ProjectRecordData)[41] <- "TDS"
#Nest roof fan data based on "site" information based on 3 digit TDS code
NestedRoofFans <- ProjectRecordData %>%
nest_by(TDS, `Development (Proper)`, `Managed By`, `Borough Grouping`)
#Create sample of nested roof fan data
NestedRoofFansSample <- NestedRoofFans %>%
filter(TDS %in% c("002", "015", "016", "017", "020"),)
colnames(NestedRoofFansSample)[5] <- "Nested Roof Fan Data"
#Join sample data of nested roof fans to joined data of dvlmts &bldg level nested data
DvlmtBldgRoofFanJoin <- full_join(DvlmtBldgSampleJoin, NestedRoofFansSample, by= "TDS")
#Rename columns
names(DvlmtBldgRoofFanJoin)[1] <- "Borough Grouping"
names(DvlmtBldgRoofFanJoin)[3] <- "Consolidation"
#Re-arrange columns for best data efficiency
DvlmtBldgRoofFanJoin2 <- DvlmtBldgRoofFanJoin %>%
select(1,2,4,3,7:9,5,6,10:12,15,19)
#The following itemized list-columns represent building data and unique building roof fan asset data
#that are all part of the same site
print(DvlmtBldgRoofFanJoin2[[13]][[2]])
#> [1] "Chelsea"
print(DvlmtBldgRoofFanJoin2[[14]][[2]])
#> [1] "Manhattan"
#For instance, how do I use the shared key column data among the
#unique building roof fan asset columns, respective buildings and respective sites to
#build out this data-nesting scheme?
Thank you in advance for any and all suggestions or advice.