Street Trees

Each line of the CSV file contains 42 fields. We can display the lines as markers in a Google map if there are fields for latitude and longitude, or for the address. We’ll use fields 38 and 39.

  1. created_at
  2. tree_id
  3. block_id
  4. the_geom
  5. tree_dbh
  6. stump_diam
  7. curb_loc
  8. status: Alive, Dead, Stump
  9. health: Good, Fair, Poor
  10. spc_latin
  11. spc_common
  12. steward
  13. guards
  14. sidewalk
  15. user_type
  16. problems
  17. root_stone
  18. root_grate
  19. root_other
  20. trnk_wire
  21. trnk_light
  22. trnk_other
  23. brnch_ligh
  24. brnch_shoe
  25. brnch_othe
  26. address
  27. zipcode
  28. zip_city
  29. cb_num
  30. borocode
  31. boroname
  32. cncldist
  33. st_assem
  34. st_senate
  35. nta
  36. nta_name
  37. boro_ct
  38. state
  39. Latitude
  40. longitude
  41. x_sp
  42. y_sp

Output lines of text.

"""
streettrees.py

List the sidewalk trees on East Fifth Street in Manhattan,
from Second Avenue to First Avenue (building numbers 300 - 399 inclusive).
"""

import sys
import urllib.request
import csv   #Comma-Separated Values

#Database is at
#https://data.cityofnewyork.us/Environment/2015-Street-Tree-Census-Tree-Data/pi5s-9p35
url = "https://data.cityofnewyork.us/api/views/5rq2-4hqu/rows.csv"

try:
    infile = urllib.request.urlopen(url)
except urllib.error.URLError as error:
    print(error, file = sys.stderr)
    sys.exit(1)

sequenceOfBytes = infile.read() #Read whole file into one big sequenceOfBytes.
infile.close()

try:
    s = sequenceOfBytes.decode("utf-8")    #s is a string
except UnicodeError as unicodeError:
    print(unicodeError, file = sys.stderr)
    sys.exit(1)

lines = csv.reader(s.splitlines())

#Building numbers on East Fifth Street between Second and First Avenues
#in Manhattan go from 300 to 399 inclusive.

lines = [line for line in lines
    if line[26] == "10003"
    and line[7] == "Alive"
    and line[25].endswith("EAST 5 STREET")
    and int(line[25].split(maxsplit = 1)[0]) >= 300]

#Sort in order of increasing street number (from west to east).
lines.sort(key = lambda line: int(line[25].split(maxsplit = 1)[0]))

for line in lines:
    latitude = float(line[38])
    longitude = float(line[39])
    address = line[25]
    english = line[10] #name of species
    latin = line[9]

    print(f"{latitude:11.8f} {longitude:12.8f} {address} {english} ({latin})")

sys.exit(0)

40.72658025 -73.98864366 300 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72668676 -73.98860456 303 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72637933 -73.98823942 306 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72646883 -73.98838040 306 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72655075 -73.98828208 315 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72633629 -73.98813774 319 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72628559 -73.98801793 321 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72624222 -73.98791547 325 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72617727 -73.98769151 329 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72629044 -73.98766488 329 EAST 5 STREET Callery pear (Pyrus calleryana)
40.72613223 -73.98758510 333 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72608553 -73.98747475 334 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72602515 -73.98733209 336 EAST 5 STREET Sophora (Styphnolobium japonicum)
40.72610837 -73.98723323 339 EAST 5 STREET Callery pear (Pyrus calleryana)
40.72588803 -73.98700814 342 EAST 5 STREET common hackberry (Celtis occidentalis)
40.72584166 -73.98689858 342 EAST 5 STREET common hackberry (Celtis occidentalis)
40.72599951 -73.98697512 345 EAST 5 STREET American hophornbeam (Ostrya virginiana)
40.72593084 -73.98681230 347 EAST 5 STREET American hophornbeam (Ostrya virginiana)
40.72596650 -73.98689686 347 EAST 5 STREET American hophornbeam (Ostrya virginiana)

Display the output on a Google map.

No longer any reason to sort the lines. Then output the lines to a new CSV file. The newline = None prevents the output lines from being double spaced. 

"""
streettrees2.py

Output a CSV file of the sidewalk trees on East Fifth Street in Manhattan,
from Second Avenue to First Avenue (building numbers 300 - 399 inclusive).
"""

import sys
import csv   #Comma-Separated Values
import urllib.request

#Database is at
#https://data.cityofnewyork.us/Environment/2015-Street-Tree-Census-Tree-Data/pi5s-9p35
url = "https://data.cityofnewyork.us/api/views/5rq2-4hqu/rows.csv"

try:
    infile = urllib.request.urlopen(url)
except urllib.error.URLError as error:
    print(error, file = sys.stderr)
    sys.exit(1)

sequenceOfBytes = infile.read()  #Read whole file into one big sequenceOfBytes.
infile.close()

try:
    s = sequenceOfBytes.decode("utf-8")    #s is a string
except UnicodeError as unicodeError:
    print(unicodeError, file = sys.stderr)
    sys.exit(1)

inputLines = csv.reader(s.splitlines())

try:
    outfile = open("streettrees.csv", "w", newline = None)
except BaseException as error:
    print(error, file = sys.stderr)
    sys.exit(1)

writer = csv.writer(outfile)
fieldIndices = [38, 39, 25, 10, 9]   #Want to output only 5 of the 42 fields.

#Read the first line from the input file.  It's a line of 42 headers.
headers = next(inputLines)                           #headers is a list of 42 strings.
selectedHeaders = [headers[i] for i in fieldIndices] #selectedHeaders is a list of 5 strings.
writer.writerow(selectedHeaders)

#Building numbers on East Fifth Street between Second and First Avenues
#in Manhattan go from 300 to 399 inclusive.

outputLines = [
    [inputLine[i] for i in fieldIndices]   #Create a list of 5 strings.
    for inputLine in inputLines
    if inputLine[26] == "10003"
    and inputLine[7] == "Alive"
    and inputLine[25].endswith("EAST 5 STREET")
    and int(inputLine[25].split(maxsplit = 1)[0]) >= 300
]

writer.writerows(outputLines) #outputLines is a list of lists, each containing 5 strings.
outfile.close()
sys.exit(0)

You should now have a new file named streettrees.csv on your Mac containing the following lines, each containing five comma-separated fields. 

Latitude,longitude,address,spc_common,spc_latin
40.72613223,-73.9875851,333 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72628559,-73.98801793,321 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72633629,-73.98813774,319 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72617727,-73.98769151,329 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72624222,-73.98791547,325 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72637933,-73.98823942,306 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72658025,-73.98864366,300 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72646883,-73.9883804,306 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72608553,-73.98747475,334 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72602515,-73.98733209,336 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72588803,-73.98700814,342 EAST 5 STREET,common hackberry,Celtis occidentalis
40.72584166,-73.98689858,342 EAST 5 STREET,common hackberry,Celtis occidentalis
40.72593084,-73.9868123,347 EAST 5 STREET,American hophornbeam,Ostrya virginiana
40.7259665,-73.98689686,347 EAST 5 STREET,American hophornbeam,Ostrya virginiana
40.72629044,-73.98766488,329 EAST 5 STREET,Callery pear,Pyrus calleryana
40.72599951,-73.98697512,345 EAST 5 STREET,American hophornbeam,Ostrya virginiana
40.72655075,-73.98828208,315 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72610837,-73.98723323,339 EAST 5 STREET,Callery pear,Pyrus calleryana
40.72668676,-73.98860456,303 EAST 5 STREET,Sophora,Styphnolobium japonicum

Then follow these directions, starting at Import your data. Here’s a summary.

  1. Go to Google maps. Sign in at upper right corner if you are not already signed in.
  2. On the left, pull down the menu (three horizotal bars) and select “Your places”.
  3. Select MAPS.
  4. In lower left corner, press CREATE MAP.
  5. Click on the words “Untitled map” and give your new map a title and a description. Press the blue Save button.
  6. Press the blue word Import. Select the streettrees.csv file created by your Python program.
  7. When it says “Choose columns to position your placemarks”, check the latitude and longitude columns. Then press the blue Continue button.
  8. When it says “Choose a column to title your markers” select the spc_common column (the English name of the species). Then press the blue Finish button.
  9. Click on Share. Change “Private” to “Public on the Web”. Press the blue Save and Done buttons. To get a link to your new map, press the top three dots and select “Embed on my site”.

Click on the blue placemarks in the following map. See if you can find the three American hophornbeams.

Things to try

  1. Instead of sending the output to the output file streettrees.csv, send the output to the IDLE window as we always do. You can then copy the output into a new file named streettrees.csv that you create with TextEdit.app.

    Create the outputfile like this: 

    outfile = io.StringIO(newline = None)   #and import io
    Just before you close the outfile, say 
    print(outfile.getvalue())
  2. Do the work in pandas. The precedence of the Python operator & is higher than that of ==. That’s why we need parentheses around each comparison. 
    """
streettrees3.py

Output a CSV file of the sidewalk trees on East Fifth Street in Manhattan,
from Second Avenue to First Avenue (building numbers 300 - 399 inclusive).
"""

import sys
import pandas as pd

#Database is at
#https://data.cityofnewyork.us/Environment/2015-Street-Tree-Census-Tree-Data/pi5s-9p35
url = "https://data.cityofnewyork.us/api/views/5rq2-4hqu/rows.csv"

try:
    df = pd.read_csv(url)   #df is a pandas DataFrame.
except BaseException as error:
    print(error, file = sys.stderr)
    sys.exit(1)

try:
    outfile = open("streettrees.csv", "w", newline = None)
except BaseException as error:
    print(error, file = sys.stderr)
    sys.exit(1)

#Add two columns to the DataFrame.
df[["number", "street"]] = df["address"].str.split(n = 1, expand = True) #like Python str split
df["number"] = pd.to_numeric(df["number"], errors = "coerce")            #coerce to NaN

mask = (                       #mask is a pandas Series containing Trues and Falses.
    (df["zipcode"] == 10003)   #or (df.zipcode == 10003)
    & (df["status"] == "Alive")
    & (df["street"] == "EAST 5 STREET")
    & (df["number"] >= 300)
)

columns = ["Latitude", "longitude", "address", "spc_common", "spc_latin"]
df[mask].to_csv(outfile, columns = columns, header = True, index = False)
outfile.close()
sys.exit(0)

    Latitude,longitude,address,spc_common,spc_latin
40.72613223,-73.98758509999999,333 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72628559,-73.98801793,321 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72633629,-73.98813774,319 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72617727,-73.98769151,329 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72624222,-73.98791547,325 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72637933,-73.98823942,306 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72658025,-73.98864366,300 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72646883,-73.9883804,306 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72608553,-73.98747475,334 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72602515,-73.98733209,336 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72588803,-73.98700814,342 EAST 5 STREET,common hackberry,Celtis occidentalis
40.72584166,-73.98689858,342 EAST 5 STREET,common hackberry,Celtis occidentalis
40.72593084,-73.9868123,347 EAST 5 STREET,American hophornbeam,Ostrya virginiana
40.7259665,-73.98689686,347 EAST 5 STREET,American hophornbeam,Ostrya virginiana
40.72629044,-73.98766488,329 EAST 5 STREET,Callery pear,Pyrus calleryana
40.72599951,-73.98697512,345 EAST 5 STREET,American hophornbeam,Ostrya virginiana
40.72655075,-73.98828208,315 EAST 5 STREET,Sophora,Styphnolobium japonicum
40.72610837,-73.98723323,339 EAST 5 STREET,Callery pear,Pyrus calleryana
40.72668676,-73.98860456,303 EAST 5 STREET,Sophora,Styphnolobium japonicum