Recently I’ve been digging into Julia for technical and statistical computing. It’s still in its early stages and things break frequently, but I find it a lot more amenable to my style of thinking than the more established technical languages like Matlab/Octave or R.

I wanted to take the full hbg-crime.org dataset and see which neighborhoods experienced crimes at which hours. The full CSV set has timestamps and classification by neighborhood already, so we just need to convert the timestamp into an hour of the day (0 through 23) and then group them by neighborhood.

To read and manipulate the data, we’re going to use DataFrames — it gives us the NA type for missing values (not uncommon in the dataset while I’m still tuning the geolocation) and nice functions for dealing with columns directly. For the timestamps, we’re going to use Datetime.

using DataFrames
using Datetime

Let’s read the data in:

julia> data = readtable("reports.csv")
DataFrame with 868 rows, 7 columns
Columns:

Start             306 non-null values    
End               868 non-null values    
Description       868 non-null values    
Address           868 non-null values    
Lat               841 non-null values    
Lon               841 non-null values    
Neighborhood      802 non-null values    

Then let’s add a function that will map a column of timestamps into a column of integer hours:

formatter = "yyyy-MM-ddTHH:mm:ss"

function eachHour(m)
    map(h -> Datetime.hour(Datetime.datetime(formatter, h)), m)
end

To create a new table with a column called “Hour” with the results of that function in it, we’re going to use the @transform macro:

withHours = @transform(data, Hour => eachHour(End))

Now to group the results down we’re going to use the by function from DataFrames’ Split-Apply-Combine:

results = by(withHours, ["Neighborhood", "Hour"], nrow)

We’re just passing by a source, a list of columns we want to split on, and a function to apply to their combination (nrow, which just counts them). And the results are just what we wanted:

julia> results
119x3 DataFrame:
            Neighborhood hr x1
[1,]                  NA  0  4
[2,]      "allison-hill"  0 18
[3,]          "downtown"  0  5
[4,]           "midtown"  0 10
[5,]            "uptown"  0 13
[6,]                  NA  1  3
[7,]      "allison-hill"  1 20
[8,]          "downtown"  1  9
[9,]           "midtown"  1  1
[10,]           "uptown"  1  5
[11,]                 NA  2  4
[12,]     "allison-hill"  2 24
[13,]         "downtown"  2  7
[14,]          "midtown"  2  6
[15,]           "uptown"  2  4
[16,]                 NA  3  2
[17,]     "allison-hill"  3 13
[18,]         "downtown"  3  1
[19,]          "midtown"  3  6
[20,]           "uptown"  3  5
  :
[100,]          "uptown" 19  7
[101,]    "allison-hill" 20 22
[102,]        "downtown" 20  1
[103,]         "midtown" 20  1
[104,]          "uptown" 20  7
[105,]                NA 21  3
[106,]    "allison-hill" 21 20
[107,]        "downtown" 21  2
[108,]         "midtown" 21  1
[109,]          "uptown" 21  6
[110,]                NA 22  5
[111,]    "allison-hill" 22 12
[112,]        "downtown" 22  4
[113,]         "midtown" 22  6
[114,]          "uptown" 22  7
[115,]                NA 23  1
[116,]    "allison-hill" 23 17
[117,]        "downtown" 23  2
[118,]         "midtown" 23  5
[119,]          "uptown" 23  8