Skip to content

Instantly share code, notes, and snippets.

View StephenOTT's full-sized avatar
:shipit:
...

Stephen Russett StephenOTT

:shipit:
...
188 followers · 117 following
  • Canada
View GitHub Profile
@StephenOTT
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
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
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
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
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
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
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
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
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
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