Temporal Heat Map
TUTORIAL
By Alejandro Gimnez-Santana
1. Open
your
crime
data
spreadsheet
in
Microsoft
Excel.
Work
with
the
Date
and
Hour
column
to
create
a
heat
map
for
hourly
and
daily
incidents
over
a
specific
time
period
(e.g.
a
calendar
year).
2. Create
a
new
column
(e.g.,
Day
Format).
Then
use
the
Excel
function
{=DATEVALUE(Cell)}
to
convert
the
Date
column
to
a
day
of
the
week
(e.g.,
Sunday,
Monday).
Convert
all
cells
in
the
new
column
to
this
value
format.
R i s k t e r r a i n m o d e l i n g . c o m
2015
All
Rights
Reserved.
|
1
3. Convert
all
numeric
Day
Format
cell
values
(created
in
Step
#2)
to
text
format
via
the
Excel
function
{=TEXT(WEEKDAY(Cell),"ddd")}.
4. Create
a
new
column
(e.g.,
Hour
Format).
Use
the
Excel
function
{=HOUR(Cell)}
to
convert
all
12-
hour
values
(HH:MM)
in
each
cell
of
the
column
to
24-hour
format
(HH).
R i s k t e r r a i n m o d e l i n g . c o m
2015
All
Rights
Reserved.
|
2
5. Using
a
new
sheet,
Copy
the
Day
of
the
week
and
Hour
columns
you
created
in
the
previous
steps
and
then
use
the
Paste
Special
function
to
paste
them
in
the
new
sheet;
keep
only
the
Values.
IMPORTANT:
Change
the
Cell
Format
in
the
new
sheet
to
Text.
6. Use
the
Recommended
Pivot
Tables
function
in
Excel
(Insert
>
Recommended
PivotTables)
to
create
a
Pivot
table
with
the
data
of
the
two
columns
(e.g.
Day
and
Hour).
R i s k t e r r a i n m o d e l i n g . c o m
2015
All
Rights
Reserved.
|
3
7. Using
the
Conditional
Formatting
function
(Home
>
Conditional
Formatting
>
Color
Scales),
symbolize
the
summary
pivot
table
you
created
in
Step
#6
to
produce
a
Heat
Map.
R i s k t e r r a i n m o d e l i n g . c o m
2015
All
Rights
Reserved.
|
4