Archive | Excel RSS feed for this section

Excel Geocoder

17 Apr

ESRI makes maps for Office. I thought this could be interesting and went to work on trying to insert a map in to a spreadsheet. I did not have much luck. Instead, I decided to throw together a quick macro that would geocode addresses in a spreadsheet and give back coordinates. I do not think VB Script can parse JSON, so the result is ugly, but you get the idea.

Start with a spreadsheet of addresses

sheet

 

Then create a Macro – I copied the code for reading the URL from Ryan Farley.

Sub Macro1()
Range(“A2”).Select
i = 2

Do Until IsEmpty(ActiveCell)

URL = “http://coagisweb.cabq.gov/arcgis/rest/services/locators/CABQ_NetCurr/GeocodeServer/findAddressCandidates?f=json&outSR=4326&street=” & ActiveCell.Value
Dim objHttp
Set objHttp = CreateObject(“Msxml2.ServerXMLHTTP”)
objHttp.Open “GET”, URL, False
objHttp.Send
Cells(i, 2).Value = objHttp.ResponseText
i = i + 1
ActiveCell.Offset(1, 0).Select
Set objHttp = Nothing
Loop

End Sub

The code starts at cell A2 and reads addresses until it reaches an empty cell. It takes the value and sends it to the ESRI REST endpoint for the City of Albuquerque Geocoding Service. It sets the cell next to it with the results. They should really be parsed, but I am too lazy and was just curious if it could be done. It can. The result is below.

results

I am still thinking of how to embed a web page in the sheet.

Advertisements

Schedule Revit and Non-BIM Data in Excel – Live

4 Feb

There is a plugin called from Ideate called Sticky that looks like it could be useful, but after watching the video I have come to the conclusion that this is an awful idea. The plugin allows you to bring “non-BIM” data in to Revit. From what I can tell, it provides a live link to an Excel spreadsheet in your Revit Project. This means you can drop the schedule on to a sheet and it will look nice – not like taking a screenshot or other workarounds. The part of this that irks me is that there is no link between the non-BIM data and the data in the linked spreadsheet. And, how much space is this link taking up in my model? I do not need my models to be any larger. Why not create a schedule with a live link between non-BIM data and data you have in a Database? Then you can PDF it and add it to any sheet you want. In my last post, I showed you how to create a view that had Revit data and data joined from another database. In this post, I will show you how to schedule them in Excel.

Get the Data

Open Excel and select the Data tab. Then choose From Other Source. You will be prompted for the database connection. As we have been using SQL Express for the previous posts, that is what I will stick with.

Other Source

When you click next, you will be able to choose the view you created in the previous post.

view

Click through until completed and place it at cell a1. You will then have a table in Excel of your data.

done

I know have non-BIM data merged with BIM data straight from Revit. It is not as pretty as the forms on the Ideate video though is it?

How to Format the Sheet

The data comes in as a single table. The rick to using this data with your existing forms and formatted documents is to link it. When you import the data, do so on a different sheet in your workbook. For example, you probably created your perfectly formatted schedule component on sheet 1, so bring the data in to sheet 2. Then you can copy and paste a link from a cell in the table to the correct cell in your schedule. Below I copied a link from cell c5 to h5. Now the data is exactly where I want it.

pastelink

Hey, You Said Live?

The data may not be live at the moment, so let’s check on it. Select the properties of your data connection and make sure you enable background refresh, set the refresh interval and why not refresh the data whenever you open the sheet.

refresh

You can also click the Refresh All button if you don’t feel like waiting for the refresh interval.

So I have a link in cell h5 just sitting there. I will change the data in the database and see what happens. If I change the 1/2 bathroom to “converted to full bathroom” the table and the link will both update within a minute – or I can refresh all when I made the change.

linkchanged

We did it. Now, whenever the Revit model is exported using DBLink, our spreadsheets will all update with the correct information. We can then use Adobe for printing and adding to our Revit sheets.

 

ESRI REST and Python: pandas.DataFrame

13 Jan

I have been working in JavaScript a lot recently and have missed coding in python. I wanted to work on something that would have real world value so I decided to see if I could load an ESRI REST endpoint in to a pandas DataFrame. This post will only scratch the surface of what you can do with data in python but should give you an idea of what is possible and allow you to imagine some really interesting possibilities. I have posted all of the code as a static IPython notebook and on Python Fiddle for easy copy+paste.

Getting Data

We are going to start by grabbing some open data from the City of Albuquerque – I will use the Crime Incidents REST service. The first step is to make the request and read it.

import urllib, urllib2
param = {‘where’:’1=1′,’outFields’:’*’,’f’:’json’}
url = ‘http://coagisweb.cabq.gov/…/APD_Incidents/MapServer/0/query ?’ + urllib.urlencode(param)
rawreply = urllib2.urlopen(url).read()

If you print rawreply, you will see a long string. We know it is a string because if you print rawreply[0] you will see { as the result. To confirm it, you can type(rawreply) and get back str.

Working with the Data

Now we need to convert it to JSON so that we can do something with the data.

import simplejson
reply = simplejson.loads(rawreply)
print reply[“features”][0][“attributes”][“date”]
print reply[“features”][0][“attributes”][“CVINC_TYPE”]

The above code will return something like

1405468800000
LARCENY ALL OTHER

How many features do we have? 24,440.

print len(reply[“features”])
reply[“features”][:3]

The above code will give you the count and show you the first three results. You can grab subsets of the data by splicing. If you want records 2 through 4, you can reply[“features”][2:5].

Let’s get to the good stuff.

Pandas DataFrame

JSON is great, but let’s put the data in to a table (DataFrame). First, a disclaimer. ESRI REST services return a deeply nested JSON object. Converting this to a DataFrame is more difficult than:

import pandas.io.json as j
jsondata = j.read_json(the URL)

But it is not much more difficult. We just need to recreate the JSON object grabbing what we are interested in – the attributes of the features. We need a loop that will  create an array of dictionaries. Each dictionary will be the attributes of a single feature.

count = 0
myformateddata=[]
while (count < len(reply[“features”])):
mydict={}
for key, value in reply[“features”][count][“attributes”].iteritems():
mydict[key]= value
myformateddata.append(mydict)
count = count + 1

The code above initializes a counter, array and a dictionary. It then loops until there are no more features. The loop reads the attributes of the feature and creates a dictionary for each attribute and its value. Now we have an array of dictionaries. If you len(myFrame) you will get 24,440. That is the same number of records we had in our JSON object.

We have all our data and it is an array. We can now create a DataFrame from it.

from pandas import DataFrame as df
myFrame = df(data=myformateddata)
myFrame

The code above imports the tools we need, assigns the frame to a variable and then prints it. You should see a table like the image below.

DataFrame with Crime Incidents from ESRI REST endpoint

DataFrame with Crime Incidents from ESRI REST endpoint

Data Manipulation

So what can we do with a DataFrame? First, let’s save it. We can export the DataFrame to Excel (The City of Albuquerque provides open data in several formats, but other sources may only provide you with the REST API so this would be a valuable method for converting the data).

from pandas import ExcelWriter
writer = ExcelWriter(‘CrimeIncidents.xlsx’)
myFrame.to_excel(writer,’Sheet1′,index=False)
writer.save()

Great! the data has been exported. Where did it go? The code below will return the directory with the file.

import os
os.getcwd()

You can open the Excel file and work with the data or send it to someone else. But let’s continue with some basic manipulation in the DataFrame.

How many of each type of incident do we have in our data?

myFrame[“CVINC_TYPE”].value_counts()

Incidents Summarized

Incidents Summarized

Now I would like a barchart of the top 5 most frequent Incident Types.

import matplotlib.pyplot as plt
incidentType=myFrame[“CVINC_TYPE”].value_counts()[:5]
incidentType.plot(kind=’barh’,rot=0)

My Bar Chart of the 5 most common Incidents

My Bar Chart of the 5 most common Incidents

Lastly, let’s filter our results by date.

import datetime
import time
myDate=datetime.datetime(2015,1,10)
milli=time.mktime(myDate.timetuple()) * 1000+ myDate.microsecond / 1000

The above code creates a date of January 10, 2015. It then converts it to milliseconds – 1420873200000. If we pass the date to the DataFrame we can grab all the Incidents after January 10, 2015.

myFrame[(myFrame.date>milli)]

DataFrame for Incidents After January 10, 2015

DataFrame for Incidents After January 10, 2015

Now you know how to connect to an ESRI REST endpoint, grab the data, convert it to JSON and put it in a DataFrame. Once you have it in the DataFrame, you can now display a table, export the data to Excel, plot a barchart and filter the data on any field.

Python and SQLite3

18 Dec

I need to generate a report every month. This report needs to have 30 names on it and I need to make sure than none of the people have appeared in previous months. I used Python and SQLite to store my items and to verify them. Here is what I did:

I wrote a script to empty all the data from an Excel sheet in to a SQLite Database Table. Then I wrote my program. The if __name__ == “__main__”: takes my command line arguments (load or scan and the data file.xls) and calls the appropriate function. If there is an error, it prints out the correct usage.

if __name__ == “__main__”:

if (len(sys.argv) < 3):

print “USAGE: warrant.py load/scan excelfile.xls”

elif sys.argv[1]==”load”:

load(sys.argv[2])

elif sys.argv[1]==”scan”:

scan(sys.argv[2])

else: print “Error…..Unknown Argument. \n\nUSAGE: warrant.py load/scan excel.xls”

The function scan() runs through an excel sheet and compares the names to the database. Currently, if the same last and first appear it prints out the info for the person. I will fix this by using ss# as another filter.

The load() function takes an excel file and uploads them to the database. No error checking. It will crash if the name already exists, but the list should have been cleaned with scan() first. I need to fix this anyway.

I hate error checking. My programs are usually for me so I don’t care if they fail because I can read the error and track it down. But now that I need anyone to be able to use it,  I cant have this – when the excel file doesn’t exist:

Error when Python can't find the Excel file.

Error when Python can’t find the Excel file.

Instead, I need to catch the errors and fail gracefully – like when the commands entered are wrong:

Catching an error in usage - gracefully.

Catching an error in usage – gracefully.

Fixed the error if the file is not found by using from os.path import exists then updating the if name=main to include:

elif sys.argv[1]==”load”:
           if exists(sys.argv[2]):
                  load(sys.argv[2])
           else:
                  print “file not found”

So here is my code thus far:

import sqlite3 as lite
import sys
from os.path import exists
from xlrd import open_workbook

def load(data):
    wb=open_workbook(data)
    sheet=wb.sheet_by_index(0)
    con = lite.connect(‘warrant.db’)
    with con:
        cur = con.cursor()
        for rownum in range(sheet.nrows):
            fname=sheet.cell(rownum,0).value
            lname=sheet.cell(rownum,1).value
            dob=sheet.cell(rownum,2).value
            ss=sheet.cell(rownum,3).value
            cur.execute(“INSERT INTO previous VALUES(fname,lname,dob,ss)”)
            print “Added: “+fname+”, “+lname+”, “+dob+”, “+ss

def scan(data):
    wb=open_workbook(data)
    sheet=wb.sheet_by_index(0)
    con = lite.connect(‘warrant.db’)
    with con:
        cur = con.cursor()
        for rownum in range(sheet.nrows):
            lastname=sheet.cell(rownum,1).value
            firstname=sheet.cell(rownum,0).value
            query=”SELECT * FROM previous WHERE lname='”+lastname+”‘ AND fname='”+firstname+”‘”
            cur.execute(query)    
            while True:
                row=cur.fetchone()
                if row==None:
                    break
                print row[0], row[1], row[2], row[3]

if __name__ == “__main__”:
    if (len(sys.argv) < 3):
        print “\n*************************\n\n”+”USAGE: warrant.py load/scan excelfile.xls\n\n”+”*************************”
    elif sys.argv[1]==”load”:
        if exists(sys.argv[2]):        
            load(sys.argv[2])
        else:
            print “\n***********************\nFile: “+ sys.argv[2] +” doesn’t exist.\n***********************\n”
    elif sys.argv[1]==”scan”:
        if exists(sys.argv[2]):
            scan(sys.argv[2])
        else:
            print “\n***********************\nFile: “+ sys.argv[2] +” doesn’t exist.\n***********************\n”
    else:
        print “\n*************************\n\n”+”Error…..Unknown Argument. \n\nUSAGE: warrant.py load/scan excel.xls\n\n”+”*************************”

 

Web Based Spreadsheet with CherryPy and XLWT

15 Nov

An example of a simple spreadsheet through the web. Uses a Table and Form.

The code:

import cherrypy
from cherrypy.lib.static import serve_file
import xlwt

class SHP(object):

def index(self):
return “””<HTML><HEAD><TITLE>SPREADSHEET</TITLE></HEAD>
<BODY><FORM NAME=’sheet’ action=’paul’>
<table border=’1′>
<tr><td><input type=”text” name=”r0c0″></td><td><input type=”text” name=”r0c1″></td><td><input type=”text” name=”r0c2″></td></tr>
<tr><td><input type=”text” name=”r1c0″></td><td><input type=”text” name=”r1c1″></td><td><input type=”text” name=”r1c2″></td></tr>
<tr><td><input type=”text” name=”r2c0″></td><td><input type=”text” name=”r2c1″></td><td><input type=”text” name=”r2c2″></td></tr>
</table><BR>
<input type=’submit’ Value=’SUBMIT’>”””

index.exposed = True

def paul(self,r0c0,r0c1,r0c2,r1c0,r1c1,r1c2,r2c0,r2c1,r2c2):
wb=xlwt.Workbook()
ws=wb.add_sheet(‘Web Sheet’)
ws.write(0,0,r0c0)
ws.write(0,1,r0c1)
ws.write(0,2,xlwt.Formula(r0c2))
ws.write(1,0,r1c0)
ws.write(1,1,r1c1)
ws.write(1,2,xlwt.Formula(r1c2))
ws.write(2,0,r2c0)
ws.write(2,1,r2c1)
ws.write(2,2,xlwt.Formula(r2c2))
wb.save(‘Spreadsheet.xls’)

return “Download Spreadsheet: <a href=’/get?filepath=C:\Documents and Settings\user\Desktop\Spreadsheet.xls’>Your Spreadsheet</a>”

paul.exposed = True

def get(self, filepath):
return serve_file(filepath, “application/x-download”, “attachment”)
get.exposed = True

cherrypy.config.update({‘server.socket_host’: ‘127.0.0.1’,
‘server.socket_port’: 8000,
})

cherrypy.quickstart(SHP())

Python, Point Shapefile and Qgis: Part II

24 Oct

In my first post, I showed how to create a shapefile from an excel sheet – but it was hard coded by cell. Not very useful.  Here is the code to loop through the sheet and create shapefile, then send a text telling me the job finished. The last thing to do is render the shapefile – Mapnik would work for this.
Here is the code:

import shapefile
import xlrd
import smtplib
book = xlrd.open_workbook(“NAME OF XLS FILE.XLS”)
sh = book.sheet_by_index(0)
w = shapefile.Writer(shapefile.POINT)
w.field(‘Type’)
w.field(‘Address’,’C’,’40’)

def readRow():

for rownum in range(sh.nrows):
rows=sh.row_values(rownum)
w.point(sh.cell_value(rowx=rownum, colx=0), sh.cell_value(rowx=rownum, colx=1))
w.record(sh.cell_value(rowx=rownum, colx=2),sh.cell_value(rowx=rownum, colx=3))
print ‘Reading Record: ‘+ str(rownum)

else:
print ‘Complete…’

readRow()

w.save(‘shapenull’)
message = ‘Job Complete’
server = smtplib.SMTP( ‘smtp.gmail.com’, 587 )
server.starttls()
server.login( ‘YOUREMAIL@gmail.com’, YOUR PASSWD’ )
server.sendmail( ‘YOUR PHONE #@mms.att.net’, ‘TO PHONE #@mms.att.net’, message )

The code opens the excel file formatted as: Lat, Long, Type, Address. Creates a point shapfile with fields for type and address.  Create a function that loops through the excel sheet. The Long, Lat, Type and Address are always in the same column so we only need to replace the row number of our cell and we do that by using the rownum variable in the loop.

While running, it will print out the current rownumber then print Complete.. when done. Lastly, It will send a text message saying that it has finished.

Simple.  Less than 25 lines of code. Set this to run as a task in Windows or as a Cron Job.

Python, Point Shapefile and Qgis

23 Oct

I have been getting data in a CSV and bringing it to Qgis using ‘Import Delimited Text.’  I could probably write a Python script in Qgis to do this automatically, but I only recently started reading the Qgis Python Cookbook. I have some experience with a Python Library for shapefiles already, so I will show how to create a point shapefile using this library.

Download Shapefile.py. All my maps are in WGS84, making this very easy. Open Notepad or your IDE and write the following:

import shapefile
w = shapefile.Writer(shapefile.POINT)
w.point(-106.505325, 35.069018)
w.point(-106.509378, 35.067741)
w.point(-106.527414, 35.108816)
w.field(‘type’)
w.field(‘street’)
w.record(‘res burg’,’central’)
w.record(‘auto burg’,’menaul’)
w.record(‘auto theft’,’eubank’)
w.save(‘point’)

I have three points in Albuquerque, with two fields: type and street. Running this file will create a shapfile without a PRJ. That’s alright, open the shapefile in Qgis. You should be asked for the projection. Choose WGS 84. It will display correctly. Now the points are on the map.

Right click on the shapefile in the table of contents and save as… Make sure the CRS is set to WGS 84.

Now when you open the shapefile you will not need to specify a projection.

Simple, right? Well, I hate hard coding the coordinates. I want to read from a file and everyone seems to send me data in Excel files so I downloaded XLRD and XLWT to read and write Excel files.

Here is how to add two points from an Excel Spreadsheet – you will want to run this as a loop to load all the data instead of hard coding it again. I will leave that up to you. (UPDATE: I can’t leave you hanging. Here is a new post with the code for looping). Here is the code to read the Excel file and write the shapefile.

book = xlrd.open_workbook(“test.xls”)
sh = book.sheet_by_index(0)
w = shapefile.Writer(shapefile.POINT)
w.point(sh.cell_value(rowx=0, colx=0), sh.cell_value(rowx=0, colx=1))
w.point(sh.cell_value(rowx=1, colx=0), sh.cell_value(rowx=1, colx=1))
w.field(‘type’)
w.field(‘street”)
w.record(sh.cell_value(rowx=0, colx=2),sh.cell_value(rowx=0, colx=3))
w.record(sh.cell_value(rowx=1, colx=2),sh.cell_value(rowx=1, colx=3))
w.save(‘fromExcel’)

Is this any easier than importing a CSV and creating the shapefile? No. But what it lets me do is schedule the Python file to run at intervals and update the shapefile even if I am at home sleeping. When graveyard shift comes in, they can see the latest data without me having to create it. It also allows me to add other functionality and python scripts.

I can filter the CSV by type or by location for example. Better yet, when a point in the shapefile exceeds some value, I can send a text to my phone – like this:

import shapefile
import xlrd
import smtplib
book = xlrd.open_workbook(“realtime.xls”)
sh = book.sheet_by_index(0)
w.record(sh.cell_value(rowx=1, colx=2))
w.save(‘writeANDreadANDshapefile’)

x = sh.cell_value(rowx=1, colx=2)
if x > 100:
message = “At Point: “+ str(sh.cell_value(rowx=1, colx=0)) +”,” + str(sh.cell_value(rowx=1, colx=1)) +”Levels equal: “+str(sh.cell_value(rowx=1, colx=2))
server = smtplib.SMTP( “smtp.gmail.com”, 587 )
server.starttls()
server.login( ‘YourAccount@gmail.com’, ‘Your Password’ )
server.sendmail( ‘From Phone Number@mms.att.net’, ‘ToPhoneNumber@mms.att.net’, message )

else:
print ‘Levels are normal’

All the concatenation of Message =”….” will create a string that says: “At Point (-106.4567, 35.3445) Levels Equal: 125” but will fill in the numbers using the actual coordinates and value.

There are programs – like CrimeView – that do this sort of thing automatically. But the way I showed was free and can be modified as you wish. Trying modifying the code of a proprietary software solution….Good Luck and Happy Hacking.