StephenOTT’s gists

StephenOTT / gist:3909199

Created October 18, 2012 00:35

Excel function to grab the file name and extension from a URL

=REPLACE(A1,1,LOOKUP(2^15,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")

StephenOTT / gist:4260308

Created December 11, 2012 17:01

Find and Replace values in column with array values - Excel

	Sub Macro1()

	findArray = Array("/01/", "/02/", "/03/", "/04/", "/05/", "/06/", "/07/", "/08/", "/09/", "/10/", "/11/", "/12/")
	replArray = Array("/Jan/", "/Feb/", "/Mar/", "/Apr/", "/May/", "/Jun/", "/Jul/", "/Aug/", "/Sep/", "/Oct/", "/Nov/", "/Dec/")

	For i = 0 To UBound(findArray)
	Selection.EntireColumn.Select
	Selection.Replace What:=findArray(i), Replacement:=replArray(i), LookAt:=xlPart, _
	SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
	ReplaceFormat:=False

StephenOTT / gist:6254185

Last active December 21, 2015 05:08

Sample of code for converting date/times to a proper format for import into Mongodb

	issues.each do \|y\|
	y["created_at"] = DateTime.strptime(y["created_at"], '%Y-%m-%dT%H:%M:%S%z').to_time.utc
	y["updated_at"] = DateTime.strptime(y["updated_at"], '%Y-%m-%dT%H:%M:%S%z').to_time.utc
	if y["closed_at"] != nil
	y["closed_at"] = DateTime.strptime(y["closed_at"], '%Y-%m-%dT%H:%M:%S%z').to_time.utc
	end
	end

StephenOTT / gist:6303243

Created August 22, 2013 04:30

Health Inspection XML sample from a single Restaurant

	<?xml version="1.0" encoding="UTF-8"?>
	<?xml-stylesheet type="text/xsl" href="/templates/xslt/inspections/inspections_details_en.xsl"?>
	<response>
	<result numFound="1" start="0">
	<doc>
	<str name="app_id">fsi</str>
	<str name="fs_fa_en">Ottawa West</str>
	<str name="fs_fa_fr">Ottawa Ouest</str>
	<str name="fs_facd">OTW</str>
	<str name="fs_faid">9DC7C571-6145-47B1-B837-85E1F6A26244</str>

StephenOTT / gist:6303277

Created August 22, 2013 04:39

Health Inspection Parsed XML into Ruby Hash/Arrays - Single Restaurant

	[{"numFound"=>"1",
	"start"=>"0",
	"doc"=>
	[{"str"=>
	{"app_id"=>"fsi",
	"fs_fa_en"=>"Ottawa West",
	"fs_fa_fr"=>"Ottawa Ouest",
	"fs_facd"=>"OTW",
	"fs_faid"=>"9DC7C571-6145-47B1-B837-85E1F6A26244",
	"fs_fcr"=>"YES",

StephenOTT / gist:6312264

Created August 22, 2013 20:19

health inspection Single record with multiple inspection comments and question text

	<?xml version="1.0" encoding="UTF-8"?>
	<?xml-stylesheet type="text/xsl" href="/templates/xslt/inspections/inspections_details_en.xsl"?>
	<response>
	<result numFound="1" start="0">
	<doc>
	<str name="app_id">fsi</str>
	<str name="fs_fa_en">Ottawa West</str>
	<str name="fs_fa_fr">Ottawa Ouest</str>
	<str name="fs_facd">OTW</str>
	<str name="fs_faid">9DC7C571-6145-47B1-B837-85E1F6A26244</str>

StephenOTT / gist:6312278

Created August 22, 2013 20:20

Ruby version of xml parsed for health inspection with multiple inspections and comment/qtext xml version: https://gist.github.com/StephenOTT/6312264

	{"numFound"=>"1",
	"start"=>"0",
	"doc"=>
	{"str"=>
	{"app_id"=>"fsi",
	"fs_fa_en"=>"Ottawa West",
	"fs_fa_fr"=>"Ottawa Ouest",
	"fs_facd"=>"OTW",
	"fs_faid"=>"9DC7C571-6145-47B1-B837-85E1F6A26244",
	"fs_fcr"=>"YES",

StephenOTT / gist:6446293

Created September 5, 2013 05:07

Old code for converting date formats from XML format into proper format for Mongodb - Originally built for use with City of Ottawa Health Inspection data but was dropped in favour of xpath and conversion during variable creation

	def convertDatesForMongo(parsedXML)

	# Fixes Date Strings in Facility/Restarant information
	# If statement is used to ensure that the date is not null otherway the strptime would throw a exception if it was null
	# If Statement is only used because of data inconsistancies with Health Inspection Data
	if parsedXML["doc"]["str"]["fs_fcr_date"] != nil
	parsedXML["doc"]["str"]["fs_fcr_date"] = DateTime.strptime(parsedXML["doc"]["str"]["fs_fcr_date"][0..-5], '%Y-%m-%d %H:%M:%S').to_time.utc
	end

	if parsedXML["doc"]["str"]["fs_fefd"] != nil

StephenOTT / gist:6458046

Created September 6, 2013 00:27

Analysis methods for Mongodb for Health Data

	def analyzeRestaurantNameCount
	return restaurantNameCount = @coll.aggregate([
	{ "$project" => {doc:{str:{fs_fnm: 1}}}},
	{ "$group" => {_id: "$doc.str.fs_fnm", number: { "$sum" => 1 }}},
	{ "$sort" => {"_id" => 1 }}
	])
	end

	def analyzeRestaurantCategoryCount
	restaurantCategoryCount = @coll.aggregate([

StephenOTT / gist:6614278

Last active December 23, 2015 09:28

Sample of groupdate question Causing formatting changes Comment out require 'groupdate' to see different in terminal output

	require 'date'
	require 'groupdate'


	class DateManipulate

	def initialize
	@creationDateCount = [{"_id"=>{"year"=>2012, "month"=>10}, "number"=>53}, {"_id"=>{"year"=>2012, "month"=>12}, "number"=>58}, {"_id"=>{"year"=>2011, "month"=>8}, "number"=>8}, {"_id"=>{"year"=>2012, "month"=>11}, "number"=>75}, {"_id"=>{"year"=>2011, "month"=>9}, "number"=>58}, {"_id"=>{"year"=>2013, "month"=>9}, "number"=>67}, {"_id"=>{"year"=>2013, "month"=>6}, "number"=>561}, {"_id"=>{"year"=>2012, "month"=>9}, "number"=>102}, {"_id"=>{"year"=>2013, "month"=>5}, "number"=>609}, {"_id"=>{"year"=>2012, "month"=>7}, "number"=>84}, {"_id"=>{"year"=>2010, "month"=>12}, "number"=>1}, {"_id"=>{"year"=>2013, "month"=>7}, "number"=>595}, {"_id"=>{"year"=>2013, "month"=>3}, "number"=>253}, {"_id"=>{"year"=>2012, "month"=>6}, "number"=>129}, {"_id"=>{"year"=>2012, "month"=>2}, "number"=>95}, {"_id"=>{"year"=>2012, "month"=>4}, "number"=>50}, {"_id"=>{"year"=>2013, "month"=>8}, "number"=>736}, {"_id"=>{"year"=>2013, "month"=>1}, "number"=>242

Stephen Russett StephenOTT