library(ggplot2)
library(ggrepel)
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(plotly)
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
library(tidyverse)
## ── Attaching packages ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.3.0 ──
## ✔ tibble  2.1.3     ✔ purrr   0.3.4
## ✔ tidyr   1.1.2     ✔ stringr 1.4.0
## ✔ readr   1.3.1     ✔ forcats 0.4.0
## Warning: package 'tidyr' was built under R version 3.6.2
## Warning: package 'purrr' was built under R version 3.6.2
## ── Conflicts ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ plotly::filter() masks dplyr::filter(), stats::filter()
## ✖ dplyr::lag()     masks stats::lag()
library(kableExtra)
## 
## Attaching package: 'kableExtra'
## The following object is masked from 'package:dplyr':
## 
##     group_rows
library(skimr)

Load the data

url = "2015_Street_Tree_Census_-_Tree_Data.csv"

df <- read.csv(url, header=TRUE,stringsAsFactors=FALSE)

head(df)
##   tree_id block_id created_at tree_dbh stump_diam curb_loc status health
## 1  180683   348711 08/27/2015        3          0   OnCurb  Alive   Fair
## 2  200540   315986 09/03/2015       21          0   OnCurb  Alive   Fair
## 3  204026   218365 09/05/2015        3          0   OnCurb  Alive   Good
## 4  204337   217969 09/05/2015       10          0   OnCurb  Alive   Good
## 5  189565   223043 08/30/2015       21          0   OnCurb  Alive   Good
## 6  190422   106099 08/30/2015       11          0   OnCurb  Alive   Good
##                            spc_latin      spc_common steward  guards
## 1                        Acer rubrum       red maple    None    None
## 2                  Quercus palustris         pin oak    None    None
## 3 Gleditsia triacanthos var. inermis     honeylocust    1or2    None
## 4 Gleditsia triacanthos var. inermis     honeylocust    None    None
## 5                    Tilia americana American linden    None    None
## 6 Gleditsia triacanthos var. inermis     honeylocust    1or2 Helpful
##   sidewalk        user_type problems root_stone root_grate root_other
## 1 NoDamage TreesCount Staff     None         No         No         No
## 2   Damage TreesCount Staff   Stones        Yes         No         No
## 3   Damage        Volunteer     None         No         No         No
## 4   Damage        Volunteer   Stones        Yes         No         No
## 5   Damage        Volunteer   Stones        Yes         No         No
## 6 NoDamage        Volunteer     None         No         No         No
##   trunk_wire trnk_light trnk_other brch_light brch_shoe brch_other
## 1         No         No         No         No        No         No
## 2         No         No         No         No        No         No
## 3         No         No         No         No        No         No
## 4         No         No         No         No        No         No
## 5         No         No         No         No        No         No
## 6         No         No         No         No        No         No
##             address postcode     zip_city community.board borocode
## 1 108-005 70 AVENUE    11375 Forest Hills             406        4
## 2  147-074 7 AVENUE    11357   Whitestone             407        4
## 3 390 MORGAN AVENUE    11211     Brooklyn             301        3
## 4 1027 GRAND STREET    11211     Brooklyn             301        3
## 5      603 6 STREET    11215     Brooklyn             306        3
## 6 8 COLUMBUS AVENUE    10023     New York             107        1
##     borough cncldist st_assem st_senate  nta           nta_name boro_ct
## 1    Queens       29       28        16 QN17       Forest Hills 4073900
## 2    Queens       19       27        11 QN49         Whitestone 4097300
## 3  Brooklyn       34       50        18 BK90  East Williamsburg 3044900
## 4  Brooklyn       34       53        18 BK90  East Williamsburg 3044900
## 5  Brooklyn       39       44        21 BK37 Park Slope-Gowanus 3016500
## 6 Manhattan        3       67        27 MN14     Lincoln Square 1014500
##      state latitude longitude      x_sp     y_sp council.district
## 1 New York 40.72309 -73.84422 1027431.1 202756.8               29
## 2 New York 40.79411 -73.81868 1034455.7 228644.8               19
## 3 New York 40.71758 -73.93661 1001822.8 200716.9               34
## 4 New York 40.71354 -73.93446 1002420.4 199244.3               34
## 5 New York 40.66678 -73.97598  990913.8 182202.4               39
## 6 New York 40.77005 -73.98495  988418.7 219825.5                3
##   census.tract     bin        bbl
## 1          739 4052307 4022210001
## 2          973 4101931 4044750045
## 3          449 3338310 3028870001
## 4          449 3338342 3029250001
## 5          165 3025654 3010850052
## 6          145 1076229 1011310031
skim(df)
Data summary
Name df
Number of rows 683788
Number of columns 45
_______________________
Column type frequency:
character 26
numeric 19
________________________
Group variables None

Variable type: character

skim_variable n_missing complete_rate min max empty n_unique whitespace
created_at 0 1 10 10 0 483 0
curb_loc 0 1 6 14 0 2 0
status 0 1 4 5 0 3 0
health 0 1 0 4 31616 4 0
spc_latin 0 1 0 34 31619 133 0
spc_common 0 1 0 22 31619 133 0
steward 0 1 0 7 31615 5 0
guards 0 1 0 7 31616 5 0
sidewalk 0 1 0 8 31616 3 0
user_type 0 1 9 16 0 3 0
problems 0 1 0 95 31664 233 0
root_stone 0 1 2 3 0 2 0
root_grate 0 1 2 3 0 2 0
root_other 0 1 2 3 0 2 0
trunk_wire 0 1 2 3 0 2 0
trnk_light 0 1 2 3 0 2 0
trnk_other 0 1 2 3 0 2 0
brch_light 0 1 2 3 0 2 0
brch_shoe 0 1 2 3 0 2 0
brch_other 0 1 2 3 0 2 0
address 0 1 1 40 0 408701 0
zip_city 0 1 5 19 0 48 0
borough 0 1 5 13 0 5 0
nta 0 1 4 4 0 188 0
nta_name 0 1 6 56 0 188 0
state 0 1 8 8 0 1 0

Variable type: numeric

skim_variable n_missing complete_rate mean sd p0 p25 p50 p75 p100 hist
tree_id 0 1.00 365205.01 2.081221e+05 3.00 186582.75 366214.50 546170.25 722694.00 ▇▇▇▇▇
block_id 0 1.00 313793.10 1.148390e+05 100002.00 221556.00 319967.00 404624.00 999999.00 ▆▇▂▁▁
tree_dbh 0 1.00 11.28 8.720000e+00 0.00 4.00 9.00 16.00 450.00 ▇▁▁▁▁
stump_diam 0 1.00 0.43 3.290000e+00 0.00 0.00 0.00 0.00 140.00 ▇▁▁▁▁
postcode 0 1.00 10916.25 6.515500e+02 83.00 10451.00 11214.00 11365.00 11697.00 ▁▁▁▁▇
community.board 0 1.00 343.51 1.157400e+02 101.00 302.00 402.00 412.00 503.00 ▂▃▆▇▃
borocode 0 1.00 3.36 1.170000e+00 1.00 3.00 4.00 4.00 5.00 ▂▃▆▇▃
cncldist 0 1.00 29.94 1.433000e+01 1.00 19.00 30.00 43.00 51.00 ▃▅▇▅▇
st_assem 0 1.00 50.79 1.897000e+01 23.00 33.00 52.00 64.00 87.00 ▇▅▅▆▃
st_senate 0 1.00 20.62 7.390000e+00 10.00 14.00 21.00 25.00 36.00 ▇▃▆▂▃
boro_ct 0 1.00 3404914.12 1.175863e+06 1000201.00 3011700.00 4008100.00 4103202.00 5032300.00 ▂▃▆▇▃
latitude 0 1.00 40.70 9.000000e-02 40.50 40.63 40.70 40.76 40.91 ▂▆▇▅▂
longitude 0 1.00 -73.92 1.200000e-01 -74.25 -73.98 -73.91 -73.83 -73.70 ▂▂▇▇▅
x_sp 0 1.00 1005279.86 3.428505e+04 913349.27 989657.84 1008386.23 1029991.28 1067247.62 ▂▂▇▇▅
y_sp 0 1.00 194798.42 3.290206e+04 120973.79 169515.15 194560.25 217019.57 271894.09 ▂▆▇▅▂
council.district 6519 0.99 30.03 1.430000e+01 1.00 19.00 30.00 43.00 51.00 ▃▅▇▅▇
census.tract 6519 0.99 11957.37 3.074574e+04 1.00 202.00 516.00 1417.00 157903.00 ▇▁▁▁▁
bin 9559 0.99 3495439.01 1.193275e+06 1000000.00 3031991.00 4020352.00 4263123.00 5515124.00 ▂▃▆▇▃
bbl 9559 0.99 3413413626.23 1.174892e+09 0.00 3011240055.00 4008560127.00 4105700010.00 5080500094.00 ▁▂▅▆▇ 
df %>% group_by(borough,  nta_name) %>% mutate( is_good = ifelse(status=='Alive' & health=='Good', 1, 0 ), is_fair = ifelse(status=='Alive' & health=='Fair', 1, 0 ), is_dead = ifelse(status != 'Alive', 1, 0), is_poor = ifelse(status == 'Alive' & health=='Poor', 1, 0) ) %>% summarize( count=n(),  n_good = sum(is_good), n_fair = sum(is_fair) , n_poor = sum(is_poor), n_dead = sum(is_dead) , fr_good = n_good/count, n_fair/count , n_poor / count,  n_dead/count , residual = 1- (n_good + n_fair + n_poor + n_dead) / count )  -> status_by_nta 

status_by_nta %>% arrange( borough, desc(fr_good) ) %>% kable(digits = 2 ) %>% kable_styling(full_width = T, bootstrap_options = c("striped")) %>% column_spec(8, color = "red") %>%
column_spec(11, color = "brown")
borough nta_name count n_good n_fair n_poor n_dead fr_good n_fair/count n_poor/count n_dead/count residual
Bronx Schuylerville-Throgs Neck-Edgewater Park 5023 4329 398 124 172 0.86 0.08 0.02 0.03 0
Bronx Van Nest-Morris Park-Westchester Square 3143 2697 229 82 135 0.86 0.07 0.03 0.04 0
Bronx Soundview-Bruckner 1921 1639 185 44 53 0.85 0.10 0.02 0.03 0
Bronx Parkchester 790 673 91 9 17 0.85 0.12 0.01 0.02 0
Bronx Woodlawn-Wakefield 4083 3434 386 103 160 0.84 0.09 0.03 0.04 0
Bronx Pelham Bay-Country Club-City Island 3196 2681 318 86 111 0.84 0.10 0.03 0.03 0
Bronx Westchester-Unionport 2820 2355 257 97 111 0.84 0.09 0.03 0.04 0
Bronx Soundview-Castle Hill-Clason Point-Harding Park 3819 3169 429 108 113 0.83 0.11 0.03 0.03 0
Bronx Co-op City 2103 1734 250 46 73 0.82 0.12 0.02 0.03 0
Bronx Longwood 1572 1291 147 38 96 0.82 0.09 0.02 0.06 0
Bronx Williamsbridge-Olinville 3371 2740 357 98 176 0.81 0.11 0.03 0.05 0
Bronx Crotona Park East 1546 1246 177 31 92 0.81 0.11 0.02 0.06 0
Bronx Pelham Parkway 2312 1839 260 82 131 0.80 0.11 0.04 0.06 0
Bronx West Farms-Bronx River 1623 1286 196 50 91 0.79 0.12 0.03 0.06 0
Bronx Belmont 1637 1291 207 70 69 0.79 0.13 0.04 0.04 0
Bronx Van Cortlandt Village 2399 1891 311 77 120 0.79 0.13 0.03 0.05 0
Bronx North Riverdale-Fieldston-Riverdale 2019 1584 286 49 100 0.78 0.14 0.02 0.05 0
Bronx Morrisania-Melrose 2330 1818 269 90 153 0.78 0.12 0.04 0.07 0
Bronx Norwood 1981 1543 249 78 111 0.78 0.13 0.04 0.06 0
Bronx Allerton-Pelham Gardens 3843 2975 486 139 243 0.77 0.13 0.04 0.06 0
Bronx Eastchester-Edenwald-Baychester 2629 2033 300 94 202 0.77 0.11 0.04 0.08 0
Bronx Spuyten Duyvil-Kingsbridge 2017 1547 288 91 91 0.77 0.14 0.05 0.05 0
Bronx Marble Hill-Inwood 246 187 30 20 9 0.76 0.12 0.08 0.04 0
Bronx Hunts Point 3521 2664 478 151 228 0.76 0.14 0.04 0.06 0
Bronx Fordham South 1060 799 161 42 58 0.75 0.15 0.04 0.05 0
Bronx Bedford Park-Fordham North 1821 1372 272 77 100 0.75 0.15 0.04 0.05 0
Bronx Bronxdale 1550 1146 194 65 145 0.74 0.13 0.04 0.09 0
Bronx Kingsbridge Heights 1184 872 192 44 76 0.74 0.16 0.04 0.06 0
Bronx Mount Hope 2051 1508 322 96 125 0.74 0.16 0.05 0.06 0
Bronx Melrose South-Mott Haven North 2156 1567 328 102 159 0.73 0.15 0.05 0.07 0
Bronx East Concourse-Concourse Village 2498 1783 385 117 213 0.71 0.15 0.05 0.09 0
Bronx Mott Haven-Port Morris 2517 1782 473 122 140 0.71 0.19 0.05 0.06 0
Bronx East Tremont 2593 1814 495 142 142 0.70 0.19 0.05 0.05 0
Bronx University Heights-Morris Heights 2002 1392 389 81 140 0.70 0.19 0.04 0.07 0
Bronx West Concourse 2142 1474 373 121 174 0.69 0.17 0.06 0.08 0
Bronx Highbridge 1979 1351 333 113 182 0.68 0.17 0.06 0.09 0
Bronx Claremont-Bathgate 1706 1097 386 116 107 0.64 0.23 0.07 0.06 0
Brooklyn Woodhaven 6 6 0 0 0 1.00 0.00 0.00 0.00 0
Brooklyn Ozone Park 9 8 1 0 0 0.89 0.11 0.00 0.00 0
Brooklyn Starrett City 688 606 41 24 17 0.88 0.06 0.03 0.02 0
Brooklyn Williamsburg 1658 1423 168 19 48 0.86 0.10 0.01 0.03 0
Brooklyn Prospect Lefferts Gardens-Wingate 3169 2695 280 65 129 0.85 0.09 0.02 0.04 0
Brooklyn East Flatbush-Farragut 3008 2541 266 73 128 0.84 0.09 0.02 0.04 0
Brooklyn Ocean Hill 2340 1975 223 62 80 0.84 0.10 0.03 0.03 0
Brooklyn Bensonhurst West 4258 3591 394 117 156 0.84 0.09 0.03 0.04 0
Brooklyn Rugby-Remsen Village 3312 2774 307 93 138 0.84 0.09 0.03 0.04 0
Brooklyn Borough Park 5955 4951 602 164 238 0.83 0.10 0.03 0.04 0
Brooklyn Georgetown-Marine Park-Bergen Beach-Mill Basin 7442 6184 829 201 228 0.83 0.11 0.03 0.03 0
Brooklyn Bath Beach 1818 1510 193 41 74 0.83 0.11 0.02 0.04 0
Brooklyn Bay Ridge 7026 5792 736 194 304 0.82 0.10 0.03 0.04 0
Brooklyn Clinton Hill 3017 2485 342 72 118 0.82 0.11 0.02 0.04 0
Brooklyn Brownsville 3092 2542 328 66 156 0.82 0.11 0.02 0.05 0
Brooklyn Stuyvesant Heights 3879 3189 501 83 106 0.82 0.13 0.02 0.03 0
Brooklyn North Side-South Side 3484 2860 327 95 202 0.82 0.09 0.03 0.06 0
Brooklyn Windsor Terrace 2290 1868 302 57 63 0.82 0.13 0.02 0.03 0
Brooklyn Cypress Hills-City Line 3626 2945 390 134 157 0.81 0.11 0.04 0.04 0
Brooklyn Sunset Park East 3224 2616 317 107 184 0.81 0.10 0.03 0.06 0
Brooklyn Bensonhurst East 3348 2696 452 88 112 0.81 0.14 0.03 0.03 0
Brooklyn Erasmus 1395 1123 164 42 66 0.81 0.12 0.03 0.05 0
Brooklyn Dyker Heights 3248 2609 378 100 161 0.80 0.12 0.03 0.05 0
Brooklyn East New York (Pennsylvania Ave) 2991 2394 400 98 99 0.80 0.13 0.03 0.03 0
Brooklyn Flatlands 5589 4460 622 230 277 0.80 0.11 0.04 0.05 0
Brooklyn Flatbush 6116 4841 832 228 215 0.79 0.14 0.04 0.04 0
Brooklyn Sunset Park West 3243 2565 427 107 144 0.79 0.13 0.03 0.04 0
Brooklyn Greenpoint 3375 2658 492 130 95 0.79 0.15 0.04 0.03 0
Brooklyn East Williamsburg 2296 1787 311 81 117 0.78 0.14 0.04 0.05 0
Brooklyn Bedford 3702 2881 625 82 114 0.78 0.17 0.02 0.03 0
Brooklyn Park Slope-Gowanus 6314 4898 940 259 217 0.78 0.15 0.04 0.03 0
Brooklyn Crown Heights South 2120 1642 352 57 69 0.77 0.17 0.03 0.03 0
Brooklyn Bushwick South 4538 3513 676 161 188 0.77 0.15 0.04 0.04 0
Brooklyn Madison 3644 2804 459 174 207 0.77 0.13 0.05 0.06 0
Brooklyn Crown Heights North 6411 4911 1046 225 229 0.77 0.16 0.04 0.04 0
Brooklyn Ocean Parkway South 2537 1925 438 103 71 0.76 0.17 0.04 0.03 0
Brooklyn Midwood 4860 3679 713 272 196 0.76 0.15 0.06 0.04 0
Brooklyn Fort Greene 2150 1621 354 95 80 0.75 0.16 0.04 0.04 0
Brooklyn Prospect Heights 1535 1157 276 57 45 0.75 0.18 0.04 0.03 0
Brooklyn Brooklyn Heights-Cobble Hill 1767 1326 332 60 49 0.75 0.19 0.03 0.03 0
Brooklyn Kensington-Ocean Parkway 2295 1713 375 99 108 0.75 0.16 0.04 0.05 0
Brooklyn Carroll Gardens-Columbia Street-Red Hook 4346 3239 718 206 183 0.75 0.17 0.05 0.04 0
Brooklyn Homecrest 4247 3136 729 213 169 0.74 0.17 0.05 0.04 0
Brooklyn East New York 9594 6987 1665 523 419 0.73 0.17 0.05 0.04 0
Brooklyn Canarsie 5994 4286 1052 298 358 0.72 0.18 0.05 0.06 0
Brooklyn Gravesend 1412 1009 268 47 88 0.71 0.19 0.03 0.06 0
Brooklyn DUMBO-Vinegar Hill-Downtown Brooklyn-Boerum Hill 2937 2055 651 125 106 0.70 0.22 0.04 0.04 0
Brooklyn Brighton Beach 1281 844 249 80 108 0.66 0.19 0.06 0.08 0
Brooklyn Bushwick North 3451 2263 765 217 206 0.66 0.22 0.06 0.06 0
Brooklyn Sheepshead Bay-Gerritsen Beach-Manhattan Beach 5321 3439 1284 234 364 0.65 0.24 0.04 0.07 0
Brooklyn West Brighton 481 303 137 15 26 0.63 0.28 0.03 0.05 0
Brooklyn Seagate-Coney Island 1454 887 344 86 137 0.61 0.24 0.06 0.09 0
Manhattan Stuyvesant Town-Cooper Village 447 403 27 8 9 0.90 0.06 0.02 0.02 0
Manhattan Hudson Yards-Chelsea-Flatiron-Union Square 2988 2527 187 83 191 0.85 0.06 0.03 0.06 0
Manhattan Upper West Side 5881 4728 762 233 158 0.80 0.13 0.04 0.03 0
Manhattan Gramercy 1172 913 168 44 47 0.78 0.14 0.04 0.04 0
Manhattan Battery Park City-Lower Manhattan 1311 1014 195 55 47 0.77 0.15 0.04 0.04 0
Manhattan Central Harlem North-Polo Grounds 3515 2711 508 136 160 0.77 0.14 0.04 0.05 0
Manhattan Lincoln Square 2065 1564 381 63 57 0.76 0.18 0.03 0.03 0
Manhattan Upper East Side-Carnegie Hill 4673 3518 760 262 133 0.75 0.16 0.06 0.03 0
Manhattan Morningside Heights 2751 2071 437 134 109 0.75 0.16 0.05 0.04 0
Manhattan Hamilton Heights 2407 1808 380 108 111 0.75 0.16 0.04 0.05 0
Manhattan Chinatown 1474 1105 249 54 66 0.75 0.17 0.04 0.04 0
Manhattan Manhattanville 913 674 141 50 48 0.74 0.15 0.05 0.05 0
Manhattan East Village 1575 1151 254 101 69 0.73 0.16 0.06 0.04 0
Manhattan Turtle Bay-East Midtown 2253 1643 419 123 68 0.73 0.19 0.05 0.03 0
Manhattan Central Harlem South 2670 1922 500 159 89 0.72 0.19 0.06 0.03 0
Manhattan East Harlem North 2591 1851 371 176 193 0.71 0.14 0.07 0.07 0
Manhattan Lower East Side 1943 1377 359 109 98 0.71 0.18 0.06 0.05 0
Manhattan West Village 3837 2716 790 209 122 0.71 0.21 0.05 0.03 0
Manhattan SoHo-TriBeCa-Civic Center-Little Italy 2215 1521 448 132 114 0.69 0.20 0.06 0.05 0
Manhattan Clinton 2008 1361 400 123 124 0.68 0.20 0.06 0.06 0
Manhattan Washington Heights South 2990 2023 529 236 202 0.68 0.18 0.08 0.07 0
Manhattan Yorkville 2269 1524 418 191 136 0.67 0.18 0.08 0.06 0
Manhattan East Harlem South 2014 1339 452 86 137 0.66 0.22 0.04 0.07 0
Manhattan Murray Hill-Kips Bay 1746 1124 403 134 85 0.64 0.23 0.08 0.05 0
Manhattan Marble Hill-Inwood 1508 962 367 109 70 0.64 0.24 0.07 0.05 0
Manhattan Washington Heights North 2684 1712 629 178 165 0.64 0.23 0.07 0.06 0
Manhattan Lenox Hill-Roosevelt Island 2327 1430 559 226 112 0.61 0.24 0.10 0.05 0
Manhattan Midtown-Midtown South 1196 666 367 87 76 0.56 0.31 0.07 0.06 0
Queens Cypress Hills-City Line 18 16 2 0 0 0.89 0.11 0.00 0.00 0
Queens Whitestone 7620 6560 569 124 367 0.86 0.07 0.02 0.05 0
Queens Springfield Gardens North 2967 2554 200 55 158 0.86 0.07 0.02 0.05 0
Queens Murray Hill 7133 6111 602 142 278 0.86 0.08 0.02 0.04 0
Queens Hollis 2553 2173 205 57 118 0.85 0.08 0.02 0.05 0
Queens East New York 13 11 1 1 0 0.85 0.08 0.08 0.00 0
Queens St. Albans 7239 6058 617 140 424 0.84 0.09 0.02 0.06 0
Queens Kew Gardens Hills 5088 4232 565 119 172 0.83 0.11 0.02 0.03 0
Queens Ozone Park 3235 2687 313 65 170 0.83 0.10 0.02 0.05 0
Queens South Jamaica 4077 3386 392 100 199 0.83 0.10 0.02 0.05 0
Queens Auburndale 5332 4416 518 185 213 0.83 0.10 0.03 0.04 0
Queens East Flushing 3247 2688 301 92 166 0.83 0.09 0.03 0.05 0
Queens Springfield Gardens South-Brookville 3754 3102 395 103 154 0.83 0.11 0.03 0.04 0
Queens Briarwood-Jamaica Hills 3278 2705 346 83 144 0.83 0.11 0.03 0.04 0
Queens Rosedale 5372 4422 602 147 201 0.82 0.11 0.03 0.04 0
Queens Baisley Park 4815 3942 481 129 263 0.82 0.10 0.03 0.05 0
Queens Woodhaven 4254 3448 502 106 198 0.81 0.12 0.02 0.05 0
Queens Jamaica Estates-Holliswood 4254 3413 497 103 241 0.80 0.12 0.02 0.06 0
Queens Pomonok-Flushing Heights-Hillcrest 4985 3996 701 129 159 0.80 0.14 0.03 0.03 0
Queens Glen Oaks-Floral Park-New Hyde Park 6814 5457 855 230 272 0.80 0.13 0.03 0.04 0
Queens East Elmhurst 2165 1733 257 64 111 0.80 0.12 0.03 0.05 0
Queens Astoria 4393 3515 539 133 206 0.80 0.12 0.03 0.05 0
Queens Oakland Gardens 6059 4840 727 203 289 0.80 0.12 0.03 0.05 0
Queens Elmhurst-Maspeth 1996 1591 259 54 92 0.80 0.13 0.03 0.05 0
Queens Middle Village 5345 4237 738 114 256 0.79 0.14 0.02 0.05 0
Queens Bayside-Bayside Hills 9780 7752 1297 337 394 0.79 0.13 0.03 0.04 0
Queens North Corona 2264 1781 293 69 121 0.79 0.13 0.03 0.05 0
Queens Steinway 5436 4260 738 188 250 0.78 0.14 0.03 0.05 0
Queens Cambria Heights 3324 2598 415 132 179 0.78 0.12 0.04 0.05 0
Queens Jamaica 4252 3320 507 192 233 0.78 0.12 0.05 0.05 0
Queens Douglas Manor-Douglaston-Little Neck 7302 5694 1039 272 297 0.78 0.14 0.04 0.04 0
Queens South Ozone Park 7321 5708 973 240 400 0.78 0.13 0.03 0.05 0
Queens Forest Hills 7701 5970 1123 237 371 0.78 0.15 0.03 0.05 0
Queens Old Astoria 1341 1039 169 64 69 0.77 0.13 0.05 0.05 0
Queens Queensboro Hill 3020 2333 388 121 178 0.77 0.13 0.04 0.06 0
Queens Rego Park 2460 1900 350 84 126 0.77 0.14 0.03 0.05 0
Queens Ft. Totten-Bay Terrace-Clearview 2906 2240 441 110 115 0.77 0.15 0.04 0.04 0
Queens Queensbridge-Ravenswood-Long Island City 1366 1052 208 49 57 0.77 0.15 0.04 0.04 0
Queens Fresh Meadows-Utopia 2872 2207 447 96 122 0.77 0.16 0.03 0.04 0
Queens Richmond Hill 5101 3910 666 218 307 0.77 0.13 0.04 0.06 0
Queens Woodside 2739 2098 424 109 108 0.77 0.15 0.04 0.04 0
Queens Jackson Heights 6863 5213 1035 310 305 0.76 0.15 0.05 0.04 0
Queens Elmhurst 3307 2510 517 139 141 0.76 0.16 0.04 0.04 0
Queens Maspeth 3290 2478 525 87 200 0.75 0.16 0.03 0.06 0
Queens Bushwick North 4 3 1 0 0 0.75 0.25 0.00 0.00 0
Queens Lindenwood-Howard Beach 4627 3467 706 185 269 0.75 0.15 0.04 0.06 0
Queens Laurelton 4623 3458 695 248 222 0.75 0.15 0.05 0.05 0
Queens Flushing 3227 2360 557 135 175 0.73 0.17 0.04 0.05 0
Queens Queens Village 6856 4991 1064 339 462 0.73 0.16 0.05 0.07 0
Queens Glendale 4509 3271 764 184 290 0.73 0.17 0.04 0.06 0
Queens Hunters Point-Sunnyside-West Maspeth 5242 3793 948 290 211 0.72 0.18 0.06 0.04 0
Queens Bellerose 6246 4516 1198 224 308 0.72 0.19 0.04 0.05 0
Queens Corona 2070 1487 341 141 101 0.72 0.16 0.07 0.05 0
Queens Kew Gardens 2024 1418 388 129 89 0.70 0.19 0.06 0.04 0
Queens Far Rockaway-Bayswater 4972 3388 1067 256 261 0.68 0.21 0.05 0.05 0
Queens College Point 3474 2323 842 135 174 0.67 0.24 0.04 0.05 0
Queens Ridgewood 6481 4193 1393 507 388 0.65 0.21 0.08 0.06 0
Queens Georgetown-Marine Park-Bergen Beach-Mill Basin 7 4 3 0 0 0.57 0.43 0.00 0.00 0
Queens Hammels-Arverne-Edgemere 3187 1709 808 394 276 0.54 0.25 0.12 0.09 0
Queens Breezy Point-Belle Harbor-Rockaway Park-Broad Channel 4351 2271 1035 518 527 0.52 0.24 0.12 0.12 0
Staten Island Westerleigh 4954 4362 393 89 110 0.88 0.08 0.02 0.02 0
Staten Island New Brighton-Silver Lake 3539 3079 313 52 95 0.87 0.09 0.01 0.03 0
Staten Island Port Richmond 3084 2681 214 92 97 0.87 0.07 0.03 0.03 0
Staten Island New Dorp-Midland Beach 5452 4503 678 146 125 0.83 0.12 0.03 0.02 0
Staten Island Grymes Hill-Clifton-Fox Hills 2505 2064 303 71 67 0.82 0.12 0.03 0.03 0
Staten Island Old Town-Dongan Hills-South Beach 4952 4067 563 175 147 0.82 0.11 0.04 0.03 0
Staten Island Rossville-Woodrow 9251 7563 1008 271 408 0.82 0.11 0.03 0.04 0
Staten Island Grasmere-Arrochar-Ft. Wadsworth 2133 1734 283 67 49 0.81 0.13 0.03 0.02 0
Staten Island West New Brighton-New Brighton-St. George 3970 3188 411 157 214 0.80 0.10 0.04 0.05 0
Staten Island Charleston-Richmond Valley-Tottenville 8216 6568 996 349 303 0.80 0.12 0.04 0.04 0
Staten Island New Springville-Bloomfield-Travis 8446 6718 1086 338 304 0.80 0.13 0.04 0.04 0
Staten Island Arden Heights 6999 5547 907 280 265 0.79 0.13 0.04 0.04 0
Staten Island Oakwood-Oakwood Beach 5675 4403 900 169 203 0.78 0.16 0.03 0.04 0
Staten Island Stapleton-Rosebank 3840 2898 572 160 210 0.75 0.15 0.04 0.05 0
Staten Island Mariner’s Harbor-Arlington-Port Ivory-Graniteville 3776 2811 547 233 185 0.74 0.14 0.06 0.05 0
Staten Island Great Kills 10734 7931 1834 502 467 0.74 0.17 0.05 0.04 0
Staten Island Annadale-Huguenot-Prince’s Bay-Eltingville 12969 9365 2365 800 439 0.72 0.18 0.06 0.03 0
Staten Island Todt Hill-Emerson Hill-Heartland Village-Lighthouse Hill 4823 3187 1162 287 187 0.66 0.24 0.06 0.04 0 
df %>% filter( status == 'Alive' ) %>% group_by(borough, health) %>% summarize( xx=n() ) %>% 
  group_by(borough) %>% mutate( ptg = prop.table(xx ) ) %>% dplyr::select( borough, health, ptg ) %>% 
  pivot_wider(names_from = "borough", values_from = "ptg") %>% kable(digits = 2 ) %>% 
  kable_styling(full_width = T, bootstrap_options = c("striped"))
health Bronx Brooklyn Manhattan Queens Staten Island
Fair 0.14 0.15 0.18 0.15 0.14
Good 0.83 0.81 0.76 0.82 0.81
Poor 0.04 0.04 0.06 0.04 0.04
NA NA NA NA 0.00 
df %>% filter(health != '' ) %>% 
  dplyr::select( status, health, steward, borough, spc_common, tree_id) %>% 
  group_by(health, steward ) %>% 
  summarise(ct = n()) %>% 
  pivot_wider(names_from = "health", values_from = "ct") -> yy

yy
## # A tibble: 4 x 4
##   steward  Fair   Good  Poor
##   <chr>   <int>  <int> <int>
## 1 1or2    21902 115372  6283
## 2 3or4     2830  15606   747
## 3 4orMore   200   1361    49
## 4 None    71572 396511 19739
#df %>% filter(status == 'Alive' & health != '') %>% group_by(health, borough, spc_common, steward) %>% summarize( ct = n())

This result analyzes the marginal percentage of trees in all states of health grouped by borough. It shows that Queens, Brooklyn and Staten Island have very similar proportions of trees in good health at around 81%, trees in fair health at 14.5%. The defining criteria may in fact by percentile driven rather than observable criterion.

df %>% filter(health != '' ) %>% 
  dplyr::select( status, health, steward, borough, spc_common, tree_id) %>% 
  group_by(health, steward ) %>% 
  summarise(ct = n()) %>% 
  group_by( health) %>% mutate(ptg = 100* prop.table(ct)) %>% dplyr::select(health, steward, ptg) %>% 
  pivot_wider( names_from = "health", values_from = "ptg" ) %>%
  kable(digits = 1 ) %>%
  kable_styling(bootstrap_options = c("striped"))
steward Fair Good Poor
1or2 22.7 21.8 23.4
3or4 2.9 3.0 2.8
4orMore 0.2 0.3 0.2
None 74.2 75.0 73.6 
df %>% filter(health != '' ) %>% 
  dplyr::select( status, health, steward, borough, spc_common, tree_id) %>% 
  group_by(health, steward ) %>% 
  summarise(ct = n()) %>% 
  group_by( steward) %>% mutate(ptg = 100* prop.table(ct)) %>% dplyr::select(health, steward, ptg) %>% 
  pivot_wider( names_from = "health", values_from = "ptg" ) %>%
  kable(digits = 1 ) %>%
  kable_styling(bootstrap_options = c("striped"))
steward Fair Good Poor
1or2 15.3 80.4 4.4
3or4 14.8 81.4 3.9
4orMore 12.4 84.5 3.0
None 14.7 81.3 4.0 
df %>% filter(health != '' & spc_common != '') %>% 
  dplyr::select( status, health, steward, spc_common, tree_id) %>% 
  group_by(spc_common, health, steward  ) %>% 
  summarise(ct = n()) -> by_spc_health_steward

head(by_spc_health_steward, n=100)
## # A tibble: 100 x 4
## # Groups:   spc_common, health [30]
##    spc_common             health steward    ct
##    <chr>                  <chr>  <chr>   <int>
##  1 'Schubert' chokecherry Fair   1or2      220
##  2 'Schubert' chokecherry Fair   3or4        9
##  3 'Schubert' chokecherry Fair   None      488
##  4 'Schubert' chokecherry Good   1or2     1271
##  5 'Schubert' chokecherry Good   3or4      127
##  6 'Schubert' chokecherry Good   4orMore    11
##  7 'Schubert' chokecherry Good   None     2520
##  8 'Schubert' chokecherry Poor   1or2       66
##  9 'Schubert' chokecherry Poor   3or4        5
## 10 'Schubert' chokecherry Poor   4orMore     1
## # … with 90 more rows
#%>% 
# group_by( steward) %>% mutate(ptg = 100* prop.table(ct)) %>% select(health, steward, ptg) %>% 
#  pivot_wider( names_from = "health", values_from = "ptg" ) %>%
#  kable(digits = 1 ) %>%
#  kable_styling(bootstrap_options = c("striped"))
df %>% filter(health != '' ) %>% 
  dplyr::select( status, health, steward, borough, spc_common, tree_id) %>% 
  group_by(spc_common, health, steward ) %>% 
  summarise(ct = n()) %>% 
  group_by( steward) %>% mutate(ptg = 100* prop.table(ct)) %>% dplyr::select(health, steward, ptg) %>% 
  pivot_wider( names_from = "health", values_from = "ptg" ) %>%
  kable(digits = 1 ) %>%
  kable_styling(bootstrap_options = c("striped"))
## Warning: Values are not uniquely identified; output will contain list-cols.
## * Use `values_fn = list` to suppress this warning.
## * Use `values_fn = length` to identify where the duplicates arise
## * Use `values_fn = {summary_fun}` to summarise duplicates
steward Good Poor Fair
1or2 c(0.00208976225471416, 0.885362608580564, 0.0529406437860919, 1.08667637245136, 0.183202490996608, 0.247288533474508, 0.00696587418238052, 1.92327786175526, 0.0188078602924274, 0.346203946864312, 0.279331554713459, 0.0766246160061857, 0.156732169103562, 0.0487611192766636, 0.0202010351289035, 0.264006631512222, 0.0160215106194752, 0.0912529517891848, 0.197134239361369, 0.00835904901885662, 0.179022966487179, 0.00278634967295221, 0.0431884199307592, 0.0473679444401875, 0.0250771470565699, 0.00696587418238052, 0.0752314411697096, 8.58683310462046, 0.05363723120433, 5.06001100608121, 0.0285600841477601, 0.896508007272373, 0.0515474689496158, 0.101701763062756, 0.0362225457483787, 0.323216562062456, 0.173450267141275, 0.498060004040207, 0.108667637245136, 0.47925214374778, 0.0195044477106654, 0.576077794882869, 0.0202010351289035, 0.0215942099653796, 0.013931748364761, 0.646433124124912, 0.201313763870797, 0.0215942099653796, 0.253557820238651, 0.00626928676414247, 0.0236839722200938, 0.436760311235258, 0.027166909311284, 0.250771470565699, 3.37287627910865, 0.59349248033882, 1.58403978907333, 0.196437651943131, 0.37197768133912, 0.266096393766936, 0.0125385735282849, 0.0299532589842362, 8.71988130150393, 0.116330098845755, 0.101701763062756, 0.204100113543749, 0.0877700146979945, 0.825456090612091, 4.43726185417639, 0.137924308811134, 0.676386383109148, 0.0696587418238052, 0.066872392150853, 3.7197768133912, 6.76247065625501, 0.149766294921181, 0.401234352905118, 0.0208976225471416, 0.0926461266256609, 1.05115041412122, 1.68852790180904, 0.0592099305502344, 0.0125385735282849, 0.0313464338207123, 0.00766246160061857, 0.0438850073489973, 0.0996120008080414, 0.0292566715659982, 0.0529406437860919, 0.00975222385533272, 5.28849167926329, 0.0390088954213309, 0.00208976225471416, 0.0383123080030928, 1.12498868045445, 0.00487611192766636, 0.0125385735282849, 1.62026233482171, 0.00626928676414247, 0.124689147864611, 0.0146283357829991, 0.42909784963464, 0.171360504886561, 0.218728449326748, 0.00696587418238052, 0.370584506502644, 0.00905563643709467, 0.216638687072034, 0.245895358638032, 0.0473679444401875, 1.11523645659912, 0.636680900269579, 0.0104488112735708, 3.06428805282919, 0.0278634967295221, 0.0125385735282849, 0.0445815947672353, 0.263310044093984, 1.19395083486002, 1.30610140919635, 0.1574287565218, 0.0202010351289035, 0.054333818622568, 0.0174146854559513, 0.122599385609897, 0.0550304060408061, 0.027166909311284, 0.00348293709119026, 0.0334361960754265, 0.0919495392074228, 0.249378295729223, 0.0334361960754265, 0.523137151096777) c(0.0459747696037114, 0.00278634967295221, 0.0585133431319964, 0.0153249232012371, 0.0278634967295221, 0.163698043285942, 0.000696587418238052, 0.0222907973836177, 0.0167180980377132, 0.0013931748364761, 0.00766246160061857, 0.00975222385533272, 0.013931748364761, 0.00278634967295221, 0.00557269934590441, 0.00766246160061857, 0.000696587418238052, 0.00905563643709467, 0.00208976225471416, 0.00487611192766636, 0.00487611192766636, 0.00208976225471416, 0.0013931748364761, 0.00557269934590441, 0.365708394574977, 0.00766246160061857, 0.247985120892746, 0.0410986576760451, 0.00208976225471416, 0.00417952450942831, 0.00626928676414247, 0.0236839722200938, 0.00975222385533272, 0.0313464338207123, 0.00487611192766636, 0.0654792173143769, 0.00348293709119026, 0.0522440563678539, 0.00278634967295221, 0.00278634967295221, 0.0619962802231866, 0.00626928676414247, 0.00417952450942831, 0.0125385735282849, 0.000696587418238052, 0.0390088954213309, 0.000696587418238052, 0.0146283357829991, 0.222907973836177, 0.0236839722200938, 0.0522440563678539, 0.0174146854559513, 0.0313464338207123, 0.0111453986918088, 0.000696587418238052, 0.00208976225471416, 0.238232897037414, 0.0146283357829991, 0.00696587418238052, 0.00905563643709467, 0.00696587418238052, 0.0522440563678539, 0.144890182993515, 0.0195044477106654, 0.027166909311284, 0.0111453986918088, 0.00208976225471416, 0.336451723008979, 0.319037037553028, 0.013235160946523, 0.0626928676414247, 0.0013931748364761, 0.00905563643709467, 0.0487611192766636, 0.362225457483787, 0.00278634967295221, 0.00208976225471416, 0.00487611192766636, 0.00417952450942831, 0.00835904901885662, 0.00487611192766636, 0.00417952450942831, 0.137924308811134, 0.0013931748364761, 0.00696587418238052, 0.0682655669873291, 0.000696587418238052, 0.000696587418238052, 0.10379152531747, 0.000696587418238052, 0.0125385735282849, 0.0013931748364761, 0.0118419861100469, 0.0118419861100469, 0.00835904901885662, 0.040402070257807, 0.0013931748364761, 0.0146283357829991, 0.0104488112735708, 0.0013931748364761, 0.0424918325125212, 0.0341327834936645, 0.000696587418238052, 0.114240336591041, 0.000696587418238052, 0.000696587418238052, 0.0013931748364761, 0.026470321893046, 0.0571201682955203, 0.0557269934590441, 0.0174146854559513, 0.00208976225471416, 0.00208976225471416, 0.000696587418238052, 0.00766246160061857, 0.00487611192766636, 0.00278634967295221, 0.000696587418238052, 0.000696587418238052, 0.000696587418238052, 0.0222907973836177, 0.00208976225471416, 0.0208976225471416) c(0.153249232012371, 0.013235160946523, 0.233356785109747, 0.0334361960754265, 0.0557269934590441, 0.0013931748364761, 0.42909784963464, 0.00208976225471416, 0.0640860424779008, 0.0515474689496158, 0.00348293709119026, 0.027166909311284, 0.0118419861100469, 0.0013931748364761, 0.0438850073489973, 0.00278634967295221, 0.0118419861100469, 0.0508508815313778, 0.00348293709119026, 0.0383123080030928, 0.0013931748364761, 0.00766246160061857, 0.0160215106194752, 0.00208976225471416, 0.0013931748364761, 0.0222907973836177, 1.53876160688786, 0.013931748364761, 0.700070355329242, 0.00278634967295221, 0.150462882339419, 0.00626928676414247, 0.0195044477106654, 0.0118419861100469, 0.0571201682955203, 0.039705482839569, 0.0759280285879476, 0.0174146854559513, 0.126082322701087, 0.0013931748364761, 0.0891631895344706, 0.00348293709119026, 0.00348293709119026, 0.00766246160061857, 0.124689147864611, 0.0208976225471416, 0.00626928676414247, 0.0522440563678539, 0.00766246160061857, 0.0738382663332335, 0.00208976225471416, 0.0626928676414247, 0.560752871681632, 0.105881287572184, 0.281421316968173, 0.0341327834936645, 0.0821973153520901, 0.05363723120433, 0.00487611192766636, 0.00487611192766636, 1.62165550965818, 0.0299532589842362, 0.0195044477106654, 0.0313464338207123, 0.0153249232012371, 0.165091218122418, 0.584436843901725, 0.0501542941131397, 0.109364224663374, 0.0215942099653796, 0.0111453986918088, 0.805951642901426, 1.4461154802622, 0.0181112728741893, 0.119813035936945, 0.00348293709119026, 0.0243805596383318, 0.200617176452559, 0.774605209080714, 0.013235160946523, 0.00557269934590441, 0.00487611192766636, 0.000696587418238052, 0.00975222385533272, 0.027166909311284, 0.0104488112735708, 0.0153249232012371, 0.00278634967295221, 0.810827754829092, 0.00348293709119026, 0.0146283357829991, 0.255647582493365, 0.000696587418238052, 0.385212842285643, 0.0013931748364761, 0.0285600841477601, 0.00417952450942831, 0.0480645318584256, 0.0487611192766636, 0.0362225457483787, 0.000696587418238052, 0.0633894550596627, 0.00208976225471416, 0.0299532589842362, 0.039705482839569, 0.0195044477106654, 0.179719553905417, 0.141407245902325, 0.0013931748364761, 0.573291445209917, 0.00208976225471416, 0.00487611192766636, 0.0870734272797565, 0.213155749980844, 0.202010351289035, 0.0466713570219495, 0.00835904901885662, 0.00975222385533272, 0.00417952450942831, 0.0285600841477601, 0.0146283357829991, 0.013235160946523, 0.00557269934590441, 0.013931748364761, 0.0529406437860919, 0.00557269934590441, 0.0863768398615184)
None c(0.000204992804752553, 0.516581867976434, 0.0262390790083268, 0.957111405389671, 0.116640905904203, 0.165634186240063, 0.00512482011881383, 1.57926456781367, 0.0178343740134721, 0.256445998745444, 0.248041293750589, 0.036693712050707, 0.383541537692027, 0.0358737408316968, 0.00922467621386489, 0.120330776389749, 0.00860969779960723, 0.0623178126447762, 0.215857423404439, 0.0149644747469364, 0.161534330145012, 0.00245991365703064, 0.0332088343699136, 0.025214114984564, 0.0116845898708955, 0.00717474816633936, 0.0567830069164572, 6.86623399518677, 0.0588329349639828, 3.39140096182624, 0.0213192516942655, 0.619898241571721, 0.0250091221798115, 0.0455084026550668, 0.0412035537552632, 0.276740286415947, 0.120535769194501, 0.433149796442145, 0.0371036976602121, 0.735924169061666, 0.0223442157180283, 0.310974084809623, 0.00819971219010213, 0.0354637552221917, 0.00696975536158681, 0.390101307444109, 0.129760445408366, 0.0284939998606049, 0.193718200491163, 0.00430484889980362, 0.0127095538946583, 0.204172833533543, 0.0106596258471328, 0.155999524416693, 2.24385124082145, 0.441759494241752, 2.19075810439054, 0.0860969779960723, 0.352177638564886, 0.340698041498743, 0.00799471938534957, 0.0139395107231736, 8.14805400330448, 0.124225639680047, 0.0362837264412019, 0.111311092980636, 0.0317738847366457, 0.482963047997015, 3.68126078774635, 0.0758473377584447, 0.353407595393402, 0.0532981292356638, 0.0268540574225845, 3.59270389609325, 12.8097953761823, 0.0713374960538885, 0.839240542656953, 0.0180393668182247, 0.150259725883621, 1.03890353448594, 3.79728671523629, 0.0358737408316968, 0.00676476255683426, 0.0256241005940692, 0.00245991365703064, 0.030748920712883, 0.0487882875311077, 0.0225492085227809, 0.0276740286415947, 0.0125045610899057, 7.58186387657793, 0.0379236688792223, 0.00553480572831894, 0.0118895826756481, 0.744533866861273, 0.011479597066143, 0.0153744603564415, 2.23175666534105, 0.0172193955992145, 0.0549380716736843, 0.021114258889513, 0.257060977159702, 0.16378925099729, 0.169939035139867, 0.00225492085227809, 0.207452718409584, 0.00676476255683426, 0.107006244080833, 0.180393668182247, 0.0305439279081304, 1.02844890144356, 1.73280417857333, 0.00491982731406128, 2.15221945709706, 0.0131195395041634, 0.00922467621386489, 0.0166044171849568, 0.321633710656756, 0.700870399448979, 1.43002980595381, 0.356072501855185, 0.0163994243802043, 0.0922467621386489, 0.0127095538946583, 0.12525060370381, 0.0282890070558523, 0.0233691797417911, 0.00061497841425766, 0.0330038415651611, 0.154359581978673, 0.190028330005617, 0.0202942876705028, 0.36611714928806) c(0.000204992804752553, 0.034848776807934, 0.0034848776807934, 0.0358737408316968, 0.00676476255683426, 0.0100446474328751, 0.0965516110384526, 0.00471483450930872, 0.0145544891374313, 0.0153744603564415, 0.000819971219010213, 0.0184493524277298, 0.00368987048554596, 0.000204992804752553, 0.0112746042613904, 0.000819971219010213, 0.0065597697520817, 0.00840470499485468, 0.00122995682851532, 0.00594479133782404, 0.00102496402376277, 0.00163994243802043, 0.000409985609505106, 0.00061497841425766, 0.00389486329029851, 0.305029293471799, 0.00717474816633936, 0.188388387567596, 0.000409985609505106, 0.0190643308419874, 0.00061497841425766, 0.00204992804752553, 0.00266490646178319, 0.01926932364674, 0.00696975536158681, 0.0198843020609977, 0.00225492085227809, 0.0723624600776513, 0.00143494963326787, 0.0231641869370385, 0.000409985609505106, 0.0030748920712883, 0.00102496402376277, 0.0377186760744698, 0.0061497841425766, 0.00225492085227809, 0.00512482011881383, 0.000204992804752553, 0.00061497841425766, 0.0149644747469364, 0.000204992804752553, 0.0147594819421838, 0.142469999303024, 0.017424388403967, 0.0703125320301257, 0.00737974097109191, 0.0295189638843677, 0.0149644747469364, 0.000409985609505106, 0.00061497841425766, 0.167069135873331, 0.0145544891374313, 0.0030748920712883, 0.00881469060435979, 0.00389486329029851, 0.02890398547011, 0.110901107371131, 0.0163994243802043, 0.0194743164514926, 0.00368987048554596, 0.00266490646178319, 0.235741725465436, 0.339263091865476, 0.00471483450930872, 0.111106100175884, 0.00061497841425766, 0.0100446474328751, 0.0405885753410055, 0.659461852888964, 0.00327988487604085, 0.00102496402376277, 0.00061497841425766, 0.00143494963326787, 0.00327988487604085, 0.00204992804752553, 0.00143494963326787, 0.000819971219010213, 0.208272689628594, 0.00184493524277298, 0.0034848776807934, 0.0491982731406128, 0.000819971219010213, 0.00061497841425766, 0.149439754664611, 0.00143494963326787, 0.00204992804752553, 0.0061497841425766, 0.0112746042613904, 0.00963466182337, 0.000204992804752553, 0.0137345179184211, 0.00061497841425766, 0.00491982731406128, 0.00737974097109191, 0.00225492085227809, 0.0381286616839749, 0.103316373595287, 0.0865069636055774, 0.00102496402376277, 0.000204992804752553, 0.00102496402376277, 0.0393586185124902, 0.0336188199794187, 0.0453034098503143, 0.0315688919318932, 0.00163994243802043, 0.0108646186518853, 0.000409985609505106, 0.0118895826756481, 0.00430484889980362, 0.000409985609505106, 0.00245991365703064, 0.0116845898708955, 0.0118895826756481, 0.00143494963326787, 0.013324532308916) c(0.100036488719246, 0.00368987048554596, 0.189003365981854, 0.0213192516942655, 0.022959194132286, 0.00102496402376277, 0.258905912402475, 0.00676476255683426, 0.0368987048554596, 0.0465333666788296, 0.00225492085227809, 0.0719524744681462, 0.00840470499485468, 0.00143494963326787, 0.0198843020609977, 0.00327988487604085, 0.0172193955992145, 0.0522731652119011, 0.00245991365703064, 0.0262390790083268, 0.00286989926653574, 0.00717474816633936, 0.00491982731406128, 0.00245991365703064, 0.00184493524277298, 0.0108646186518853, 1.25414597947612, 0.0227542013275334, 0.482963047997015, 0.00327988487604085, 0.0889668772626081, 0.00327988487604085, 0.00860969779960723, 0.00717474816633936, 0.061497841425766, 0.0135295251136685, 0.0742073953204243, 0.00573979853307149, 0.192488243662647, 0.00245991365703064, 0.0520681724071485, 0.00102496402376277, 0.00881469060435979, 0.00163994243802043, 0.0817921290962687, 0.0172193955992145, 0.00778972658059702, 0.0317738847366457, 0.00266490646178319, 0.00225492085227809, 0.0360787336364494, 0.00102496402376277, 0.0336188199794187, 0.360992329169246, 0.0686725895921053, 0.440119551803732, 0.0118895826756481, 0.0705175248348783, 0.0586279421592302, 0.000819971219010213, 0.00143494963326787, 1.21642730340165, 0.0344387911984289, 0.00676476255683426, 0.0264440718130794, 0.00778972658059702, 0.0920417693338964, 0.456108990574431, 0.0293139710796151, 0.0453034098503143, 0.00860969779960723, 0.0061497841425766, 0.63506770912341, 1.87445420665735, 0.0104546330423802, 0.31794384017121, 0.00430484889980362, 0.0371036976602121, 0.198433035000471, 1.62231305681171, 0.00860969779960723, 0.00143494963326787, 0.00532981292356638, 0.000409985609505106, 0.00573979853307149, 0.00840470499485468, 0.00512482011881383, 0.00471483450930872, 0.0030748920712883, 1.04279839777624, 0.00573979853307149, 0.00286989926653574, 0.144314934545797, 0.00184493524277298, 0.00245991365703064, 0.475173321416418, 0.00163994243802043, 0.00737974097109191, 0.00491982731406128, 0.0303389351033779, 0.0483783019216025, 0.0381286616839749, 0.000409985609505106, 0.0344387911984289, 0.000819971219010213, 0.0153744603564415, 0.0440734530217989, 0.00635477694732915, 0.12361066126579, 0.418185321695209, 0.00163994243802043, 0.39420116353916, 0.00184493524277298, 0.00266490646178319, 0.00717474816633936, 0.098191553476473, 0.110081136152121, 0.194128186100668, 0.0996265031097409, 0.00286989926653574, 0.0297239566891202, 0.00286989926653574, 0.0330038415651611, 0.00840470499485468, 0.00737974097109191, 0.00860969779960723, 0.0291089782748626, 0.0350537696126866, 0.00512482011881383, 0.0438684602170464)
3or4 c(0.662044518584163, 0.0469165406870667, 0.813220038575822, 0.166814366887348, 0.229369754470104, 0.0104258979304593, 1.73069905645624, 0.0312776937913778, 0.312776937913778, 0.14596257102643, 0.0677683365479852, 0.0781942344784445, 0.0886201324089037, 0.0156388468956889, 0.260647448261482, 0.099046030339363, 0.260647448261482, 0.0156388468956889, 0.239795652400563, 0.0104258979304593, 0.0469165406870667, 0.151175519991659, 0.041703591721837, 0.00521294896522963, 0.0625553875827556, 10.8377208987124, 0.0260647448261482, 3.41448157222541, 0.0260647448261482, 0.860136579262889, 0.0312776937913778, 0.0312776937913778, 0.0625553875827556, 0.213730907574415, 0.161601417922119, 0.333628733774696, 0.130323724130741, 0.265860397226711, 0.0208517958609185, 0.364906427566074, 0.0156388468956889, 0.0156388468956889, 0.0260647448261482, 0.469165406870667, 0.119897826200281, 0.0469165406870667, 0.265860397226711, 0.00521294896522963, 0.0156388468956889, 0.250221550331022, 0.0312776937913778, 0.302351039983319, 6.49533441067612, 0.443100662044519, 1.23025595579419, 0.182453213783037, 0.375332325496533, 0.192879111713496, 0.0312776937913778, 10.8846374393995, 0.0729812855132148, 0.0781942344784445, 0.260647448261482, 0.0677683365479852, 0.516081947557733, 4.62388573215868, 0.0625553875827556, 0.609915028931867, 0.0469165406870667, 0.041703591721837, 3.4301204191211, 5.41104102590836, 0.0625553875827556, 0.172027315852578, 0.0573424386175259, 0.0573424386175259, 1.16248761924621, 1.41270916957723, 0.0521294896522963, 0.00521294896522963, 0.0104258979304593, 0.0156388468956889, 0.0312776937913778, 0.198092060678726, 0.0260647448261482, 0.0312776937913778, 0.00521294896522963, 4.60303393629776, 0.0104258979304593, 0.00521294896522963, 0.0208517958609185, 0.933117864776104, 0.0260647448261482, 0.0208517958609185, 1.1259969764896, 0.0104258979304593, 0.0573424386175259, 0.0156388468956889, 0.479591304801126, 0.208517958609185, 0.213730907574415, 0.401397070322682, 0.187666162748267, 0.192879111713496, 0.0312776937913778, 1.04258979304593, 0.453526559974978, 0.0312776937913778, 4.48834905906271, 0.0521294896522963, 0.0156388468956889, 0.0364906427566074, 0.218943856539644, 1.42313506750769, 1.13642287442006, 0.125110775165511, 0.0208517958609185, 0.0469165406870667, 0.0729812855132148, 0.0312776937913778, 0.0260647448261482, 0.0469165406870667, 0.0521294896522963, 0.234582703435333, 0.0208517958609185, 0.938330813741333) c(0.0260647448261482, 0.041703591721837, 0.0104258979304593, 0.0208517958609185, 0.00521294896522963, 0.172027315852578, 0.0104258979304593, 0.00521294896522963, 0.00521294896522963, 0.0156388468956889, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.390971172392222, 0.00521294896522963, 0.156388468956889, 0.0364906427566074, 0.00521294896522963, 0.0208517958609185, 0.0104258979304593, 0.0156388468956889, 0.0573424386175259, 0.0312776937913778, 0.00521294896522963, 0.00521294896522963, 0.0156388468956889, 0.526507845488193, 0.00521294896522963, 0.0521294896522963, 0.00521294896522963, 0.0156388468956889, 0.0104258979304593, 0.1407496220612, 0.00521294896522963, 0.00521294896522963, 0.0104258979304593, 0.0208517958609185, 0.172027315852578, 0.0104258979304593, 0.0104258979304593, 0.41703591721837, 0.4222488661836, 0.0312776937913778, 0.00521294896522963, 0.0312776937913778, 0.198092060678726, 0.00521294896522963, 0.0104258979304593, 0.00521294896522963, 0.099046030339363, 0.00521294896522963, 0.0208517958609185, 0.00521294896522963, 0.0938330813741333, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.0364906427566074, 0.00521294896522963, 0.0156388468956889, 0.0729812855132148, 0.0260647448261482, 0.1407496220612, 0.00521294896522963, 0.0208517958609185, 0.0469165406870667, 0.0104258979304593, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.00521294896522963, 0.0104258979304593, 0.0156388468956889) c(0.0469165406870667, 0.00521294896522963, 0.177240264817807, 0.0260647448261482, 0.0573424386175259, 0.00521294896522963, 0.333628733774696, 0.0104258979304593, 0.0469165406870667, 0.0364906427566074, 0.00521294896522963, 0.0104258979304593, 0.0104258979304593, 0.0260647448261482, 0.00521294896522963, 0.0156388468956889, 0.0729812855132148, 0.00521294896522963, 0.041703591721837, 0.0156388468956889, 0.0156388468956889, 0.00521294896522963, 0.00521294896522963, 0.0156388468956889, 2.18943856539644, 0.00521294896522963, 0.406610019287911, 0.187666162748267, 0.00521294896522963, 0.00521294896522963, 0.0521294896522963, 0.0364906427566074, 0.0729812855132148, 0.0834071834436741, 0.00521294896522963, 0.0573424386175259, 0.0781942344784445, 0.0104258979304593, 0.00521294896522963, 0.0364906427566074, 0.00521294896522963, 0.0729812855132148, 0.00521294896522963, 0.0886201324089037, 1.03216389511547, 0.0625553875827556, 0.234582703435333, 0.0312776937913778, 0.0364906427566074, 0.0469165406870667, 0.00521294896522963, 1.4961163530209, 0.0208517958609185, 0.00521294896522963, 0.0364906427566074, 0.0260647448261482, 0.0834071834436741, 0.625553875827556, 0.0260647448261482, 0.0625553875827556, 0.00521294896522963, 0.0260647448261482, 0.901840170984726, 1.40228327164677, 0.0156388468956889, 0.0521294896522963, 0.00521294896522963, 0.0104258979304593, 0.27628629515717, 0.651618620653704, 0.0156388468956889, 0.00521294896522963, 0.0208517958609185, 0.0312776937913778, 0.0208517958609185, 0.69853516134077, 0.00521294896522963, 0.099046030339363, 0.00521294896522963, 0.00521294896522963, 0.182453213783037, 0.0208517958609185, 0.0573424386175259, 0.00521294896522963, 0.0104258979304593, 0.0729812855132148, 0.0469165406870667, 0.0104258979304593, 0.192879111713496, 0.0677683365479852, 0.703748110306, 0.00521294896522963, 0.00521294896522963, 0.0834071834436741, 0.328415784809467, 0.166814366887348, 0.0312776937913778, 0.00521294896522963, 0.0104258979304593, 0.0104258979304593, 0.0156388468956889, 0.00521294896522963, 0.00521294896522963, 0.0208517958609185, 0.0834071834436741, 0.114684877235052)
4orMore c(0.683229813664596, 0.062111801242236, 1.67701863354037, 0.124223602484472, 0.869565217391304, 2.91925465838509, 0.062111801242236, 0.186335403726708, 0.062111801242236, 0.186335403726708, 0.248447204968944, 0.062111801242236, 0.31055900621118, 0.186335403726708, 0.062111801242236, 0.062111801242236, 0.124223602484472, 0.186335403726708, 0.186335403726708, 10.6832298136646, 0.186335403726708, 3.91304347826087, 1.05590062111801, 0.062111801242236, 0.062111801242236, 0.807453416149068, 0.31055900621118, 0.559006211180124, 0.434782608695652, 0.062111801242236, 0.745341614906832, 0.062111801242236, 0.124223602484472, 0.372670807453416, 0.062111801242236, 0.062111801242236, 0.186335403726708, 0.062111801242236, 0.124223602484472, 0.248447204968944, 5.71428571428571, 0.434782608695652, 1.61490683229814, 0.186335403726708, 0.062111801242236, 0.372670807453416, 0.062111801242236, 9.1304347826087, 0.062111801242236, 0.31055900621118, 0.124223602484472, 0.31055900621118, 4.34782608695652, 0.062111801242236, 1.42857142857143, 0.124223602484472, 3.60248447204969, 4.72049689440994, 0.124223602484472, 0.31055900621118, 0.062111801242236, 1.05590062111801, 1.80124223602484, 0.062111801242236, 0.062111801242236, 0.062111801242236, 0.062111801242236, 0.124223602484472, 5.90062111801242, 0.124223602484472, 0.124223602484472, 1.18012422360248, 0.62111801242236, 0.062111801242236, 0.062111801242236, 0.31055900621118, 0.186335403726708, 0.31055900621118, 0.062111801242236, 0.434782608695652, 0.124223602484472, 0.93167701863354, 0.496894409937888, 0.124223602484472, 4.53416149068323, 0.062111801242236, 1.73913043478261, 0.869565217391304, 0.248447204968944, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.248447204968944, 0.496894409937888) c(0.062111801242236, 0.062111801242236, 0.186335403726708, 0.124223602484472, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.372670807453416, 0.062111801242236, 0.434782608695652, 0.248447204968944, 0.062111801242236, 0.248447204968944, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.186335403726708, 0.062111801242236, 0.062111801242236) c(0.248447204968944, 0.062111801242236, 0.062111801242236, 0.062111801242236, 0.186335403726708, 0.062111801242236, 0.062111801242236, 0.062111801242236, 1.36645962732919, 0.559006211180124, 0.062111801242236, 0.31055900621118, 0.062111801242236, 0.124223602484472, 0.434782608695652, 0.062111801242236, 0.062111801242236, 0.124223602484472, 0.062111801242236, 0.062111801242236, 0.124223602484472, 0.993788819875776, 0.062111801242236, 0.186335403726708, 0.062111801242236, 1.42857142857143, 0.062111801242236, 0.062111801242236, 0.434782608695652, 0.186335403726708, 0.745341614906832, 0.993788819875776, 0.683229813664596, 0.062111801242236, 0.559006211180124, 0.124223602484472, 0.31055900621118, 0.062111801242236, 0.062111801242236, 0.559006211180124, 0.186335403726708, 0.124223602484472, 0.062111801242236, 0.186335403726708) 
by_spc_health_steward %>% group_by(spc_common, steward) %>% summarize( val = sum(ct)) -> spc_steward_marginals
by_spc_health_steward %>% left_join( spc_steward_marginals, by = c("spc_common", "steward")) %>% mutate( health_score=ifelse(health=="Good", 2, ifelse(health=="Fair", 1, 0 )), pct =  ct / val) %>% ungroup('health') %>% dplyr::select( spc_common, health, health_score, steward, ct, pct) %>% pivot_wider(names_from = "steward", values_from = c("ct", "pct"), values_fill = 0 )   %>% arrange(spc_common, health_score) %>% 
dplyr::select( spc_common, health, health_score,  ct_None, pct_None, ct_1or2, pct_1or2, ct_3or4, pct_3or4, ct_4orMore, pct_4orMore) ->
spc_steward_health_probs_full

# Use this probs_full to store the health as text string.
spc_steward_health_probs_full %>% select( -health) -> spc_steward_health_probs

spc_steward_health_probs
## # A tibble: 392 x 10
##    spc_common health_score ct_None pct_None ct_1or2 pct_1or2 ct_3or4
##    <chr>             <dbl>   <int>    <dbl>   <int>    <dbl>   <int>
##  1 'Schubert…            0     170   0.0535      66   0.0424       5
##  2 'Schubert…            1     488   0.154      220   0.141        9
##  3 'Schubert…            2    2520   0.793     1271   0.816      127
##  4 American …            0      17   0.104        4   0.0404       0
##  5 American …            1      18   0.110       19   0.192        1
##  6 American …            2     128   0.785       76   0.768        9
##  7 American …            0     175   0.0304      84   0.0424       8
##  8 American …            1     922   0.160      335   0.169       34
##  9 American …            2    4669   0.810     1560   0.788      156
## 10 American …            0      33   0.0467      22   0.0661       2
## # … with 382 more rows, and 3 more variables: pct_3or4 <dbl>,
## #   ct_4orMore <int>, pct_4orMore <dbl>
spc_steward_health_probs %>% dplyr::select(!starts_with("ct_")) %>% 
  pivot_longer( cols=starts_with("pct_"), names_to = "val", values_to = "pct" ) %>%
  arrange(spc_common, val, health_score) -> spc_steward_health_long

spc_steward_health_long
## # A tibble: 1,568 x 4
##    spc_common             health_score val            pct
##    <chr>                         <dbl> <chr>        <dbl>
##  1 'Schubert' chokecherry            0 pct_1or2    0.0424
##  2 'Schubert' chokecherry            1 pct_1or2    0.141 
##  3 'Schubert' chokecherry            2 pct_1or2    0.816 
##  4 'Schubert' chokecherry            0 pct_3or4    0.0355
##  5 'Schubert' chokecherry            1 pct_3or4    0.0638
##  6 'Schubert' chokecherry            2 pct_3or4    0.901 
##  7 'Schubert' chokecherry            0 pct_4orMore 0.0833
##  8 'Schubert' chokecherry            1 pct_4orMore 0     
##  9 'Schubert' chokecherry            2 pct_4orMore 0.917 
## 10 'Schubert' chokecherry            0 pct_None    0.0535
## # … with 1,558 more rows
(spc_steward_health_long %>% mutate(val = str_replace(val, "pct_", "")) -> spc_steward_health_long )
## # A tibble: 1,568 x 4
##    spc_common             health_score val        pct
##    <chr>                         <dbl> <chr>    <dbl>
##  1 'Schubert' chokecherry            0 1or2    0.0424
##  2 'Schubert' chokecherry            1 1or2    0.141 
##  3 'Schubert' chokecherry            2 1or2    0.816 
##  4 'Schubert' chokecherry            0 3or4    0.0355
##  5 'Schubert' chokecherry            1 3or4    0.0638
##  6 'Schubert' chokecherry            2 3or4    0.901 
##  7 'Schubert' chokecherry            0 4orMore 0.0833
##  8 'Schubert' chokecherry            1 4orMore 0     
##  9 'Schubert' chokecherry            2 4orMore 0.917 
## 10 'Schubert' chokecherry            0 None    0.0535
## # … with 1,558 more rows
spc_steward_health_long  %>% 
  ggplot(aes(fill=as.factor(health_score), x=val, y = pct )) + 
  geom_bar(position="dodge", stat="identity") +
  facet_wrap(~spc_common, ncol=4 )

Maybe the folloing grouping of bars is more informative.

head(spc_steward_health_probs_full) 
## # A tibble: 6 x 11
##   spc_common health health_score ct_None pct_None ct_1or2 pct_1or2 ct_3or4
##   <chr>      <chr>         <dbl>   <int>    <dbl>   <int>    <dbl>   <int>
## 1 'Schubert… Poor              0     170   0.0535      66   0.0424       5
## 2 'Schubert… Fair              1     488   0.154      220   0.141        9
## 3 'Schubert… Good              2    2520   0.793     1271   0.816      127
## 4 American … Poor              0      17   0.104        4   0.0404       0
## 5 American … Fair              1      18   0.110       19   0.192        1
## 6 American … Good              2     128   0.785       76   0.768        9
## # … with 3 more variables: pct_3or4 <dbl>, ct_4orMore <int>,
## #   pct_4orMore <dbl>
spc_steward_health_probs_full %>% 
 dplyr::select( -health_score) %>%
 dplyr::select(!starts_with("ct_")) %>%
  pivot_longer( cols=starts_with("pct_"), names_to = "val", values_to = "pct" ) %>%
  mutate(val = str_replace(val, "pct_", "")) %>%
  arrange(spc_common, val, health) %>%
  ggplot(aes(fill=val, x=health, y = pct )) + 
  geom_bar(position="dodge", stat="identity") +
  facet_wrap(~spc_common, ncol=4 )

(x1 = df %>% filter(health != '', spc_common != '', status=='Alive')  %>% dplyr::select(tree_id, borough, status, health, spc_common, steward) %>% group_by(spc_common, borough, health , steward) %>% summarize( ct = n()) )
## # A tibble: 4,554 x 5
## # Groups:   spc_common, borough, health [1,808]
##    spc_common             borough  health steward    ct
##    <chr>                  <chr>    <chr>  <chr>   <int>
##  1 'Schubert' chokecherry Bronx    Fair   1or2       22
##  2 'Schubert' chokecherry Bronx    Fair   3or4        1
##  3 'Schubert' chokecherry Bronx    Fair   None       67
##  4 'Schubert' chokecherry Bronx    Good   1or2      144
##  5 'Schubert' chokecherry Bronx    Good   3or4       10
##  6 'Schubert' chokecherry Bronx    Good   4orMore     1
##  7 'Schubert' chokecherry Bronx    Good   None      301
##  8 'Schubert' chokecherry Bronx    Poor   1or2        4
##  9 'Schubert' chokecherry Bronx    Poor   None       25
## 10 'Schubert' chokecherry Brooklyn Fair   1or2       76
## # … with 4,544 more rows
head(x1)
## # A tibble: 6 x 5
## # Groups:   spc_common, borough, health [2]
##   spc_common             borough health steward    ct
##   <chr>                  <chr>   <chr>  <chr>   <int>
## 1 'Schubert' chokecherry Bronx   Fair   1or2       22
## 2 'Schubert' chokecherry Bronx   Fair   3or4        1
## 3 'Schubert' chokecherry Bronx   Fair   None       67
## 4 'Schubert' chokecherry Bronx   Good   1or2      144
## 5 'Schubert' chokecherry Bronx   Good   3or4       10
## 6 'Schubert' chokecherry Bronx   Good   4orMore     1
# Borough View
(bv = x1 %>% group_by(spc_common, borough) %>% summarize( ct = sum(ct)) %>% pivot_wider(names_from = borough, values_from = ct, values_fill = 0 ) )
## # A tibble: 132 x 6
## # Groups:   spc_common [132]
##    spc_common             Bronx Brooklyn Manhattan Queens `Staten Island`
##    <chr>                  <int>    <int>     <int>  <int>           <int>
##  1 'Schubert' chokecherry   575     1308       163   2013             829
##  2 American beech            31       83        22     88              49
##  3 American elm            1471     2587      1698   1709             510
##  4 American hophornbeam     185      366        84    295             151
##  5 American hornbeam        295      343        85    525             269
##  6 American larch            12        8         7     10               9
##  7 American linden         2132     4023      1583   4769            1023
##  8 Amur cork tree            32       46         8     71              26
##  9 Amur maackia             466      729        59    675             268
## 10 Amur maple               431      263        30    671             654
## # … with 122 more rows
bv %>% pivot_longer(!spc_common, names_to = "borough", values_to = "ct") %>% ggplot(aes(x = borough, y = spc_common, fill = log(ct) )) + 
  geom_tile() + scale_fill_viridis_c() +
  geom_text(aes(label=ct) )

What proportion of trees are in good fair or poor health according to the health variable

(x2 = x1 %>% group_by(borough, spc_common, health ) %>% summarize( ct2 = sum(ct)) %>% group_by(borough, spc_common) %>% mutate( sct2 = sum(ct2) , pct = ct2 / sct2 ) )
## # A tibble: 1,808 x 6
## # Groups:   borough, spc_common [655]
##    borough spc_common             health   ct2  sct2    pct
##    <chr>   <chr>                  <chr>  <int> <int>  <dbl>
##  1 Bronx   'Schubert' chokecherry Fair      90   575 0.157 
##  2 Bronx   'Schubert' chokecherry Good     456   575 0.793 
##  3 Bronx   'Schubert' chokecherry Poor      29   575 0.0504
##  4 Bronx   American beech         Fair       7    31 0.226 
##  5 Bronx   American beech         Good      21    31 0.677 
##  6 Bronx   American beech         Poor       3    31 0.0968
##  7 Bronx   American elm           Fair     248  1471 0.169 
##  8 Bronx   American elm           Good    1176  1471 0.799 
##  9 Bronx   American elm           Poor      47  1471 0.0320
## 10 Bronx   American hophornbeam   Fair      28   185 0.151 
## # … with 1,798 more rows

Test case

my_spc = 'American hornbeam'

x2 %>% filter(spc_common == my_spc) %>% ungroup() %>% dplyr::select(borough, health, pct ) %>%
  pivot_wider(names_from = health, values_from = pct, values_fill = 0)
## # A tibble: 5 x 4
##   borough        Fair  Good   Poor
##   <chr>         <dbl> <dbl>  <dbl>
## 1 Bronx         0.207 0.715 0.0780
## 2 Brooklyn      0.131 0.802 0.0671
## 3 Manhattan     0.153 0.788 0.0588
## 4 Queens        0.109 0.836 0.0552
## 5 Staten Island 0.100 0.851 0.0483