Visualizing Bigfoot of the United States

2012-03-15PythonTableauVizDataPythonSQLTableauanalysisbigfootdata hackdatascreen scrapingcool shit

A fun little Viz I did about Bigfoot sightings. [

Check it out here.](/archive/finding-bigfoot-data-visualize-the-squatch/)

I guess you could file this under "Too Much Time on my Hands", but hey, people said that about my Metallica shit too, and that was pure genius. :) Basically, I scraped all the sighting info off of The Bigfoot Field Research Organization site with Python and the Scrapemark module - inserted it into a local SQL Server database, cleaned it up (good lord, what a f*cking mess of data in some places) and threw up a pretty nice looking Viz using Tableau Public. I'm pretty happy with it - its quite interactive and hopefully someone finds it fun or interesting. Not too shabby for a couple hours worth of work that was spawned from sitting on the couch watch a re-run of "Finding Bigfoot" on the Animal Planet - after a few beers - and laughing to myself,

[typography color="black" font="calibri" size="16"]"Hah, it'd be funny to map out some freaking Bigfoot sightings"[/typography]

On another note - after doing my research and harvesting the info... I wish that they'd re-vamp their site and not only display this "data" in a more visual / analytical way - BUT, more importantly, they should be collecting this data in a better way - validating fields, trying to make inferences and not just allowing anything to fly. Maybe its because I'm a data guy - and a bit of a purist at times, but still. It would be cool. Oh well.

I shouldn't stress over the data compilation of (possibly) mythical creatures. :)

[box type="info" style="rounded" border="none" icon="../uploads/2012/03/bigfooticon3.png"][typography color="black" font="Segoe UI" size="10"] If anyone cares about the Python script I used to "extract" this data, check it out below. It is the DEFINITION of 'quick and dirty', so be warned - I take no responsibility for anything.[/typography][/box] [cc lang="python"] from time import clock import time clock() import pprint, time import urllib, os, string, sys from scrapemark import scrape import pymssql mssql_db = pymssql.connect(host='localhost', user='sa', password='#####') mssql_cursor = mssql_db.cursor() mssql_cursor.execute("truncate table BigFootData.dbo.Sightings") mssql_db.commit() f = urllib.urlopen("http://www.bfro.net/gdb/") ##http://www.bfro.net html = f.read() f.close() feetmaster = scrape(""" {* [{{ [index].statename }}]({{ [index].statepage_link }}) |*} """,html) pp = pprint.PrettyPrinter(indent=4) #pp.pprint(feetmaster) for item in feetmaster: for i in feetmaster[item]: print 'Now working on', state_name = i['statename'] print state_name state_url = 'http://www.bfro.net'+i['statepage_link'] time.sleep(1) try: cf = urllib.urlopen(state_url) except: time.sleep(30) cf = urllib.urlopen(state_url) chtml = cf.read() cf.close() feetcounty = scrape(""" {* [{{ [county].countyname }}]({{ [county].county_link }}) |*} """,chtml) for citem in feetcounty: for c in feetcounty[citem]: county_name = c['countyname'] county_url = 'http://www.bfro.net/GDB/'+c['county_link'] print ' '+c['countyname'] #print ' '+county_url #time.sleep(1) try: sf = urllib.urlopen(county_url) except: time.sleep(30) sf = urllib.urlopen(county_url) shtml = sf.read() sf.close() feetsighting = scrape(""" {*[{{ [sighting].sighting_date }}]({{ [sighting].sighting_link }})*} """,shtml) #pp.pprint(feetsighting) for sitem in feetsighting: for s in feetsighting[sitem]: sighting_url = 'http://www.bfro.net/GDB/'+s['sighting_link']+'&PrinterFriendly=True' print ' '+s['sighting_link'] try: dsf = urllib.urlopen(sighting_url) except: time.sleep(30) dsf = urllib.urlopen(sighting_url) dshtml = dsf.read() dsf.close() dshtml = dshtml.decode("utf-8","ignore") dshtml = dshtml.replace("'","''").replace(' ',' ') feetdetailsighting = scrape(""" {* Report # {{ [s].report_number }} ({{ [s].class }}) *} {*
{{ [s].submitted }}

*} {*

YEAR:{{ [s].year }}

*} {*

SEASON:{{ [s].season }}

*} {*

MONTH:{{ [s].month }}

*} {*

DATE:{{ [s].date }}

*} {*

STATE:{{ [s].state }}

*} {*

COUNTY:{{ [s].county }}

*} {*

LOCATION DETAILS:{{ [s].location_details }}

*} {*

NEAREST TOWN:{{ [s].nearest_town }}

*} {*

NEAREST ROAD:{{ [s].nearest_road }}

*} {*

OBSERVED:{{ [s].observed|html }}

*} {*

ALSO NOTICED:{{ [s].also_noticed }}

*} {*

OTHER WITNESSES:{{ [s].other_witnesses }}

*} {*

OTHER STORIES:{{ [s].other_stories }}

*} {*

TIME AND CONDITIONS:{{ [s].time_and_conditions }}

*} {*

ENVIRONMENT:{{ [s].environment }}

*} """,dshtml) #pp.pprint(feetdetailsighting) # for e in feetdetailsighting: for t in feetdetailsighting[e]: #print ' '+t['year'] #print ' '+t['month'] try: mssql_cursor.execute("""insert into BigFootData.dbo.Sightings (bfroid, submitted, title, class, [month], [date], [year], season, state, county, locationdetails, nearesttown, nearestroad, observed, alsonoticed, otherwitnesses, otherstories, timeandconditions, environment, URL) values (rtrim(ltrim(%s)), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )""",(t['report_number'], t['submitted'], t['title'], t['class'], t['month'], t['date'], t['year'], t['season'], t['state'], t['county'], t['location_details'], t['nearest_town'], t['nearest_road'], t['observed'], t['also_noticed'], t['other_witnesses'], t['other_stories'], t['time_and_conditions'], t['environment'],sighting_url)) mssql_db.commit() except: print 'ERROR' print sys.exc_info()[0] print sys.exc_info()[1] errorz = str(sys.exc_info()[0]) + ' - ' + str(sys.exc_info()[1]) errorz = errorz.replace("'","''") mssql_cursor.execute("""insert into BigFootData.dbo.Sightings (state, county, month, season, year, locationdetails, URL) values (%s,%s,%s,%s,%s,'ERROR """+errorz+"""',%s)""",(t['state'], t['county'], t['month'], t['season'], t['year'],sighting_url)) mssql_db.commit() end_script = clock() mins = end_script / 60 print "Finished: %i minutes" % mins [/cc] It took about an hour to run, looping through all the pages and sub-pages until you are scraping the main detail page - I DID have some time.sleep(2) commands sprinkled in there to not overwhelm their website, but, as it turns out, their site is so slow it didn't really matter. Check out the Bigfoot Viz and enjoy! [box type="tick" style="rounded" border="full"]STILL, nowhere near as cool as my Metallica visualizations, but hey, I'm wicked biased on that one. :)[/box]