<jupyter_start><jupyter_text>[View in Colaboratory](https://colab.research.google.com/github/gregorydillon/awsjupyternotebooks/blob/master/bakMissing_Trees_in_311_reports_0B32pCCISawTmd1B1R1dsaVlnSDdzb0RnSVZlcXVWNTd1NWRJPQ.ipynb) Missing Trees in 311 reports(used a copy of monthly graffiti as a starting document)Constructing custom queries to conserve bandwith and computational resources[Socrata Developer Reference page](https://dev.socrata.com/docs/functions/date_trunc_ymd.html)  Setup<jupyter_code>import os
!pip install sodapy -q
from sodapy import Socrata
import pandas as pd
from IPython.display import Image, display
import time<jupyter_output><empty_output><jupyter_text>Pulling from the Cannonical official 311 requests databasethe data is from the SF311 cannonical sourcehttps://data.sfgov.org/City-Infrastructure/311-Cases/vw6y-z8j6and its jsonhttps://data.sfgov.org/resource/ktji-gk7t.jsonbase url:    data.sfgov.orgresource id: ktji-gk7t<jupyter_code>socrata_domain = 'data.sfgov.org'
socrata_dataset_identifier = 'ktji-gk7t'


# If you choose to use a token, run the following command on the terminal (or add it to your .bashrc)
! export SODAPY_APPTOKEN="<KEY>"
socrata_token = os.environ.get("SODAPY_APPTOKEN")
socrata_token = "<KEY>"

client = Socrata(socrata_domain, socrata_token)<jupyter_output><empty_output><jupyter_text>A big automation jump would be to scape for the latlong SoQL query for private reports within so many meters of a report  Added string interpolation into 3X """<jupyter_code>servicename="Tree Maintenance"
servicesubtype="Tree"
#latlong = "37.752326,-122.41732"
latlong = "37.772286, -122.418892"

#latlong_19th&mission =  37.760352322983, -122.419359315196
query = """
SELECT  
    
    media_url, requested_datetime, service_request_id

    
WHERE
    service_name = "{0}"
    AND
    starts_with(service_subtype, "{1}")
    AND
    within_circle(point,{2}, 40)
    AND
    requested_datetime > "2013-06-15"
    
  
ORDER BY
    requested_datetime DESC
    
    
LIMIT
    20


    

"""


query = (query.format(servicename,servicesubtype,latlong))
# print(newquery)
results = client.get(socrata_dataset_identifier, query=query)
for result in results:
  print(result['requested_datetime'])
  display(Image(result['media_url'], width=250))
  result['requested_datetime']
#   print (service_request_id)
#   print ("--"*20)
  #pass
  
 
  
  


df=pd.DataFrame.from_records(results)
df=df.iloc[1:]
df.head(2)


df.to_csv('search-results.csv', index=False)
!gsutil cp search-results.csv gs://warmhats2018/monthly.csv
!gcloud config set project chromebookpython
# !pip install --upgrade -q gspread
# !pip install gspread_dataframe as gd
# from google.colab import auth
# auth.authenticate_user()

# import gspread
# import gspread_dataframe as gd
# from oauth2client.client import GoogleCredentials

gc = gspread.authorize(GoogleCredentials.get_application_default())

# = gc.create('A new spreadsheet')

# Open our new sheet.
ws = gc.open_by_url('https://docs.google.com/spreadsheets/d/1gM2i6NR-38KEa8cWnExMXS5YXj4__WUpbkuD2vVCMvw/edit#gid=0').sheet1

gd.set_with_dataframe(ws,df)

# Append some data
#existing = gd.get_as_dataframe(ws)
#updated = existing.append(df)
#gd.set_with_dataframe(ws, updated)


# # Go to https://sheets.google.com to see your new spreadsheet.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1037989829494466, 'fraction_numerical': 0.039485492072988335, 'mean_word_length': 5.443159922928709, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 16, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '143457', 'license': 'Apache-2.0', 'n_tokens_mistral': 1303, 'n_tokens_neox': 1195, 'n_words': 249, 'path': 'bakMissing_Trees_in_311_reports_0B32pCCISawTmd1B1R1dsaVlnSDdzb0RnSVZlcXVWNTd1NWRJPQ.ipynb', 'repo_name': 'gregorydillon/awsjupyternotebooks', 'size': '3383'}