El Cartografo en su Laberinto

By • • Data Fest in Buenos Aires

Slides

Show the extracted slide text

Slide 1

EL CARTÓGRAFO
EN SU LABERINTO
How hack journalists managed to map out
a local crime database — and how you can too.




                                A remembrance by @palewire

Slide 2

¡Perdón!

Slide 3

I can’t speak Spanish, but I am a fan.

Slide 4

My name is Ben.
  I sometimes go by @palewire.

Slide 5

I work in
#DTLA.

Slide 6

Our team is called the Data Desk.
       We turn databases into news.

Slide 7

That means we write code.

Slide 8

But we still remember English.

Slide 9

We make things like...

Slide 24

TIMELINES

Slide 26

YOU GET THE POINT
  (Now back to the lecture at hand.)

Slide 28

CHECK IT OUT
http://maps.latimes.com/crime

Slide 36

LET’S REWIND

Slide 37

Hey. You.
Build us a
crime site.



                Meredith Artley
              latimes.com boss, circa 2008

Slide 38

WHAT WE WANTED
● Consolidate across departments

   ○ 15 PDs cover 88 cities in LA County

● Be more than a zillion points on a map

   ○ By spotting short term trends

   ○ Giving long-term context

   ○ And grouping crimes into geographic areas
     people can relate with. (Neighborhoods!)

Slide 39

WHAT WE WANTED
● Consolidate across departments

   ○ 15 PDs cover 88 cities in L.A. County

● Be more than a zillion points on a map

   ○ By spotting short term trends

   ○ Giving long-term context

   ○ And grouping crimes into geographic areas
     people can relate with. (Neighborhoods!)

Slide 40

WHAT WE WANTED
● Consolidate across departments

   ○ 15 PDs cover 88 cities in L.A. County

● Be more than a zillion points on a map

   ○ By spotting short term trends

   ○ Giving long-term context

   ○ And grouping crimes into geographic areas
     people can relate with. (Neighborhoods!)

Slide 41

WHAT WE WANTED
● Consolidate across departments

   ○ 15 PDs cover 88 cities in L.A. County

● Be more than a zillion points on a map

   ○ By spotting short term trends

   ○ Giving long-term context

   ○ And grouping crimes into geographic areas
     people can relate with. (Neighborhoods!)

Slide 42

WHAT WE WANTED
● Consolidate across departments

   ○ 15 PDs cover 88 cities in L.A. County

● Be more than a zillion points on a map

   ○ By spotting short term trends

   ○ Giving long-term context

   ○ And grouping crimes into geographic areas
     people can relate with. (Neighborhoods!)

Slide 43

WHAT WE WANTED
● Consolidate across departments

   ○ 15 PDs cover 88 cities in L.A. County

● Be more than a zillion points on a map

   ○ By spotting short term trends

   ○ Giving long-term context

   ○ And grouping crimes into geographic areas
     people can relate with. (Neighborhoods!)

Slide 44

You have a right to the
data.




                          THE LAW

Slide 45

You have a right to the
data.



But the police don’t
have to automate a
feed for you.



                          THE LAW

Slide 46

That put us here.

Slide 47

These two departments




Cover 69% of population and
      89% of territory

Slide 48

We’ll give a secured FTP
file to you, and only you,
becase you’re the
newspaper, you asked
nicely and you won’t go
away.




                            Los Angeles (city)
                            Police Department

Slide 49

We’ll post a file on the
WWW, for anybody, because
then we can point anybody
who asks for this data at it.




                                 Los Angeles County
                                Sheriff’s Department

Slide 50

UH OH


 !=

Slide 52

PLEASE STAND BY

Slide 53

Back here again.

Slide 54

Set up server to do the math.

Slide 55

Per capita for broad context
● All violent crimes

● In each neighborhood

● Over the past 6 months

● Divided into neighborhood population

● Results in crime rate per 10,000 people

● Repeat with property crimes

Slide 56

Per capita for broad context
● All violent crimes

● In each neighborhood

● Over the past 6 months

● Divided into neighborhood population

● Results in crime rate per 10,000 people

● Repeat with property crimes

Slide 57

Per capita for broad context
● All violent crimes

● In each neighborhood

● Over the past 6 months

● Divided into neighborhood population

● Results in crime rate per 10,000 people

● Repeat with property crimes

Slide 58

Per capita for broad context
● All violent crimes

● In each neighborhood

● Over the past 6 months

● Divided into neighborhood population

● Results in crime rate per 10,000 people

● Repeat with property crimes

Slide 59

Per capita for broad context
● All violent crimes

● In each neighborhood

● Over the past 6 months

● Divided into neighborhood population

● Results in crime rate per 10,000 people

● Repeat with property crimes

Slide 60

Per capita for broad context
● All violent crimes

● In each neighborhood

● Over the past 6 months

● Divided into neighborhood population

● Results in crime rate per 10,000 people

● Repeat with property crimes

Slide 63

Spikes for narrow context
● All violent crimes

Slide 64

Spikes for narrow context
● All violent crimes

● In each neighborhood

Slide 65

Spikes for narrow context
● All violent crimes

● In each neighborhood

● Over the past 7 days

Slide 66

Spikes for narrow context
● All violent crimes

● In each neighborhood

● Over the past 7 days

● Calculate deviation from that neighborhood’s
  weekly average over last 3 months

Slide 67

Spikes for narrow context
● All violent crimes

● In each neighborhood

● Over the past 7 days

● Calculate deviation from that neighborhood’s
  weekly average over last 3 months

● Measure against our editorial standard for alert

Slide 68

Spikes for narrow context
● All violent crimes

● In each neighborhood

● Over the past 7 days

● Calculate deviation from that neighborhood’s
  weekly average over last 3 months

● Measure against our editorial standard for alert

● Repeat with property crimes

Slide 72

Wired it up to the web.

Slide 73

The app stack, roughly

Slide 76

Every morning.

Slide 79

THAT’S NICE,
AMERICANO.
But we can’t get data.

Slide 80

We turn databases into news.
      (You remember this bit.)

Slide 81

We turn news into databases.
        (Try reversing it.)

Slide 83

CHECK IT OUT
http://projects.latimes.com/homicide

Slide 92

THE
END

Slide 93

Follow us.
datadesk.latimes.com

@LATdatadesk


github.com/datadesk

Recording

Show the timestamped transcript
  1. Bueno, les quiero presentar a Ben, que si no lo conocen, se van a ser fans a partir
  2. de ahora también, así como ya hicieron de Ryan.
  3. Ben Welch es periodista y programador, es data producer en Los Angeles Times, ya lo venimos
  4. siguiendo hace un par de años, somos sus fans y bueno, este año fueron finalistas
  5. también con nosotros de los Data Journalism Awards, los venimos siendo desde el 2009 más
  6. o menos los que están haciendo con datos, van mucho a la conferencia de NICAR, donde
  7. es un activo participante y es un gran referente en Estados Unidos por supuesto y bueno, la
  8. Comunidad Internacional que no es muy grande, así que es un lujo tenerlo a él acá en
  9. la universidad.
  10. Así que bueno, Ben, muchas gracias por haber viajado 2 huelos de 7 horas, o sea, 14 horas
  11. para estar acá con nosotros.
  12. Pero quiero comenzar mi expresión con una apología a todos los de ustedes en la audiencia,
  13. muy rápido, vamos a poner esto fuera de la cara, o puedo hacer esto, oh, no, no, no,
  14. oh, mi garganta, me ha arruinado mi garganta, quiero decir que no puedo hablar español
  15. y me disculpe por eso hoy, así que voy a dar la conversa en inglés, lo adoro español
  16. y lo adoro mucho, así que me he marcado a un idioma español, así que tengo mucho
  17. respeto por eso y me disculpo que no puedo hacer eso muy efectivamente hoy.
  18. Y me dio la conversa, lo que me esperaba, fue un idioma español en español, no sé
  19. si ha funcionado o no, pero bueno, mi nombre es Ben Welsch y estoy aquí desde Los Angeles,
  20. voy a ir por Pailwire online, y eso es donde puedes encontrarme en Twitter y GitHub y todo
  21. ese tipo de cosas, y estoy aquí desde downtown Los Angeles, donde trabajo en Los Angeles Times,
  22. que es un newspaper de daily print que puedes ver aluminado ahí en el centro, debajo de la
  23. skyline, y también un website de 24 horas que incluye un equipo que trabajo, que se llama
  24. el Data Desk.
  25. Y puedes preguntar, ¿qué es el Data Desk?
  26. El Data Desk es un equipo de reportadores y programas que están informadamente arreglados
  27. en el informe, no es ningún tipo de esfuerzo masivo, es gente que hace data analysis para
  28. nuevas historias offline, usando programas de computador, es gente que hace mapas y interactivas
  29. cosas, y websites como Ryan ha hablado conmigo, es gente que trabaja en nuestro departamento de
  30. graficas, y que son los producentes de web, y que todos vienen juntos, bajo la bantería
  31. de lo que llamamos el Data Desk, para hacer lo que voy a decir de manera general, es
  32. tomar databases, más frecuentemente, de publicas, y los tipos de cosas de open data, van a ver
  33. mucho en esta conferencia, pero no siempre, y intentar convertirlos en los materiales de
  34. raw materiales, en, oh, thank you, this work, whoa, and try to convert those raw materials
  35. of the open data, the public data, the data that we create into a finished product of
  36. news, and in some cases those can be front page stories and others, it can be, take other
  37. forms, but that largely means that we write code, we're a group of people in a newsroom
  38. surrounded by editors and reporters and page designers, who sit there and write computer
  39. code all day, right, I like to call us the house cats, because we sort of, we hang out
  40. in the office, we don't move outside the building as much as others, but we're still all part
  41. of the broader team of making news, and just because we know how to write in Python or
  42. JavaScript or HTML or all of those other things that you're all familiar with, that doesn't
  43. mean we forgot how to write English or we don't work on news stories or write them directly,
  44. for instance this is a story that appeared I think three Sundays ago in the Los Angeles
  45. Times here on the right, written by one of my colleagues Doug Smith, in partnership
  46. with two reporters, which is a data analysis that found that there's a list of probably
  47. hundreds of buildings in Los Angeles made out of a certain type of non-ductile concrete
  48. it's called, that are likely to collapse if a major earthquake ever hits the center of
  49. the city, you know, larger than the earthquake of 20 years ago in Northridge, and that series,
  50. which has sparked a lot of controversy and pushes to reform the building regulations in
  51. the city over the last few weeks, really all started with a nerd in his cubicle and
  52. a list and a database that they once acquired, cleaned up, put a lot of love and attention
  53. into, and then went out and verified out in the world that all sorts of truth checking
  54. and then converted into this finished product, which was, you know, a page news story, right?
  55. And I'm going to show you a bunch of other examples just very quickly of things like that
  56. that we do, not all of them news stories, and then I'm going to focus in on one of those
  57. projects, which was a crime database site that we made, that I was asked to talk to
  58. about you guys, for you guys, and we're going to kind of walk through that in depth, sort
  59. of how that site came up with and was built, and talk about it in detail, and then when
  60. I finish that, we'll be open to questions for anything you might want to ask regarding
  61. that or anything more broadly as well, okay?
  62. So that's kind of what you're in for, hope it doesn't sound too bad, right?
  63. So just real quickly, sort of a tour of the data desk, just like that front page story
  64. I showed you, we put a lot of effort into big projects, into investigative work based
  65. on data that doesn't always even look like data when it comes out.
  66. So here's a story that ran about a year ago in the Los Angeles Times, and it's about
  67. the toll of prescription drugs in America, prescription drugs now kill more people overdoses
  68. in the United States than do cars, car fatalities, because there's been a huge uptick in the number
  69. of people who are addicted and overdose and take too many, and what our data analysis found
  70. was getting a small sample of several large counties in Southern California, analyzing
  71. it very closely, we were able to find that a very small number of doctors account for
  72. a very outsized number of overdose deaths, so you have a small number of irresponsible
  73. doctors and they don't account for all of the deaths, but they account for a very large
  74. proportion, and that was a statistical finding that came out of really closely analyzing this
  75. public database that we found that then ultimately was converted into a photography project in
  76. some sense in profiles of the people who died, and a very sort of humanized story, but at
  77. its core, and if you were to read the whole thing, you would see that there's a really
  78. kind of hard nutted data analysis in there as well, and that's what we're trying to do
  79. a lot of times, is bridge those two things, and a story like that had some great impact,
  80. you can see here on the right, our governor just a few weeks ago signed several new bills
  81. that will tighten the oversight of prescription drugs in California, and to hopefully identify
  82. these doctors who are irresponsible sooner before it goes too bad.
  83. Another recent example would be a big issue in sports in the United States is the long
  84. lasting toll of playing our national pastime, professional football, where the number of
  85. brain injuries that people accrue is part of playing that sport that lasts them their
  86. whole life and often lead to early death, they're getting lots of attention in the media, there's
  87. lots of anecdotal and emotional and sort of first hand coverage of this issue, and it's
  88. being pushed across almost all of major American media, and what we decided was to take a more
  89. data driven look at that, we know that in the state of California there was sort of a
  90. loophole in the workers compensation law that allowed NFL players to make their injury
  91. claims there even if they didn't play for a California team, and we were able to acquire
  92. that database via public records request, analyze it, and try to find something more insightful
  93. to tell our readers on the front page of the paper like you'd see here, but it's not all
  94. just stories like that, here's another recent story that was an analysis of the street quality
  95. in Los Angeles that took the form of a front page story by my colleague Ben Poston, but
  96. also of an interactive online map I worked on where people could zoom and click and check
  97. out the letter grades of the streets all across the city.
  98. Another big data thing we did in recent years was an analysis of teachers and how effective
  99. they are at raising their students test scores, which took the form of front page stories,
  100. but also an online database that has a page for every teacher in the district allowing
  101. parents, students, other teachers, administrators, really anyone in the public to access teacher
  102. by teacher, the data that formed the basis for making those major claims that we made
  103. on the front page.
  104. Another example is one I worked on, which is analyzing 911 response times in the city.
  105. Our fire department made an embarrassing admission that their statistics were all wrong and they
  106. didn't really know what they were, so we set out to analyze it ourselves, got the data,
  107. developed our own method, wrote a large series of stories which resulted in front page stories
  108. like this, but also online maps that allow people to interact with the data, and that series
  109. resultably just now resulted in the fire chief being put out of office.
  110. But there's other things that aren't even stories, like election results maps for the presidential
  111. election recently in the United States, county by county, in my home state, Iowa, the most
  112. important state, and D3 graphics like this, which you can learn here later at the conference
  113. where you make interactive graphics that run as a part of a story, things that aren't even
  114. necessarily hard news stories, a list of the 101 best restaurants in Los Angeles, which
  115. is the sort of thing in the United States every city's publication is going to run, typically
  116. not thought of as a database, but really kind of is a database at its core.
  117. There's 101 entries, they all have a certain amount of set of things, a name, a location,
  118. a type of food that they serve, a photo associated with them, and so the process of assembling
  119. that data and building it into a website is something one of my colleagues, Anthony Pesci,
  120. put a lot of work into.
  121. And also just sort of the standard interactive graphics that you see more and more of, they're
  122. intended to stand alone, independent of any story, as sort of a shareable, interesting
  123. of the moment kind of graphic.
  124. This is how much money we pay Major League Baseball players in the United States by team.
  125. And also things like a Snow Folly, like long scrolling, pretty photo, like type things
  126. that are the current vogue, here's one we did over the last week.
  127. We also falls on our team to do things like that.
  128. So the type of work can range from that sort of deep dive investigative analysis I told
  129. you to sort of the cherry on top of a web presentation that requires a little more technical
  130. skill and freedom of movement with code than the base CMS, the base publishing system
  131. of our site allows, right?
  132. And we also do things like we build little publishing tools outside the CMS.
  133. Let's say we want to quickly publish a document via document cloud and have it hosted on our
  134. site.
  135. We have little systems that allow you to do that, to make timelines, to make sortable
  136. tables, and that's the sort of process of software development where we're making little
  137. in-house tools to make quickly publishing something based on data easier to do.
  138. And a lot of that open source code that we developed as part of that process is on GitHub,
  139. which is a really great website, I'm assuming most people here know it.
  140. I also describe it as sort of Facebook for Nerds, right, it's where you can go and be
  141. part of a social network that's built around a code and the code that people are publishing
  142. as open source, or even the private projects that you publish in-house, you can host that
  143. way.
  144. It's a great way to discover and contribute to open source projects.
  145. But you get the point, right?
  146. So that's kind of basically what we do more broadly in all those range of things, and I'm
  147. happy to talk about any of those things individually or any questions you might have later.
  148. But when they set up the event, I was told that crime is an interesting topic and crime
  149. data is an interesting topic here in Buenos Aires and Argentina at large, and so they
  150. was asked to talk a little bit about a website that we had developed a few years ago, which
  151. we call Crime LA, and what it is, it's a crime mapping database system that takes the
  152. daily crime reports processed by our local police departments and maps them, analyzes
  153. them, and publishes them on the web for the public.
  154. And you can check it out right now if you have your computer up or later if you want
  155. to or at any time at this URL, which is just maps.lax.com, slash crime, right?
  156. And it has a bunch of different types of pages you might see.
  157. Here's a page about a crime.
  158. So over the last three years, there's been several hundred thousand crimes recorded,
  159. and every one of them is in a database and comes out on a page not like this, which tells
  160. you where it was, what happened, or what was reported to have happened, who reported it,
  161. etc., etc.
  162. There's a page for more than 200 neighborhoods in the county of Los Angeles, so we've decided
  163. to kind of slice and dice the data and present it to our readers in sort of geographically
  164. recognizable areas that they know rather than just throwing them on the map.
  165. So here's crimes in Highland Park, and you can slide that slider and go back in time,
  166. and the crimes will come on and go off, and you can see what's happening.
  167. Here's it has like the most recent crimes.
  168. So here's like a list of the most recent crimes that would appear just below that map.
  169. But in addition to the list, it also provides some automated analysis that tells you here's
  170. the total, here's how it compares to the recent history, here's how it compares to the nearby
  171. neighbors of that neighborhood.
  172. And then in that analysis, it figures out, are there an unusual amount of crimes statistically
  173. in this neighborhood compared to what it typically sees?
  174. And if so, that could trigger something like an alert, and then we automatically publish
  175. with each data update a blog post.
  176. This is written entirely by a computer that says, hey Los Angeles, here are all the neighborhoods
  177. that in the latest crime reports are experiencing an uptick for their history in crime with a
  178. little map and tables, etc., etc.
  179. And those are kind of the short-term analysis it does.
  180. There's long-term analysis as well for each neighborhood, where over a longer period of
  181. time the crimes are totaled, they're ranked against other neighborhoods, it's split out
  182. by what types and shown over months.
  183. And that gives you a little broader context, which you could then say map the neighborhoods,
  184. the gray ones are what we still don't have data.
  185. You could map the neighborhoods based on their level of crime per capita.
  186. And you could rank them just like this and see which neighborhoods have the most violent
  187. crime.
  188. Okay, but so how did all this happen?
  189. How does a site like this come together?
  190. How could you yourself make something like it?
  191. And that's what I'm going to start to try to tell the story of now by rewinding back
  192. to how a site like that all got started.
  193. And it all started really when Meredith Artley, who used to be my boss, she now works at CNN,
  194. had hired a couple of us geeks onto the website, and she said, hey geeks, I want a crime map.
  195. And that's the sort of broad instruction you might get from an editor who's not particularly
  196. technical, but who's smart enough to hire technical people, which is I know this is possible,
  197. other places do it, let's do it for our local area, and let's maybe do something better
  198. or different than what other people have already done, right?
  199. And so the instruction starts out broad like that.
  200. And we went through a process where we started figuring out, like, well, okay, what would
  201. that take, and what form do we want to shape it into, right?
  202. And so a big structural problem that you have in Los Angeles is what I call kind of the
  203. bureaucratic terrain, which is LA County has like 10 million people, right?
  204. But in that big county of Los Angeles, there are 88 cities, the largest of which is Los
  205. Angeles City, which you've probably heard of, policed by the Los Angeles Police Department.
  206. But there's many other police departments that are independent, the police smaller cities,
  207. and there's also a county sheriff's department, the police's, lots and lots of others, right?
  208. And so we knew that was going to be our big problem, is each of them is each individually
  209. collecting data and publishing data.
  210. If they're going to be our source, we're going to have to go to them, right?
  211. Each individually, which is difficult and is probably not unlike an issue you might face
  212. with your regional estates as well, right?
  213. And maybe your cities too, it depends, you know, they're our source.
  214. And we also knew that our big goal for what we wanted to make, and I would encourage anyone
  215. making a crime map to set this as a goal, which is to be more than just like a million
  216. points on the map.
  217. In particular, at the time we were working on this, but even still so today, most crime
  218. mapping software, that's really what it is.
  219. As you get all the data from the police, you come up with a goofy icon for each of the
  220. different crime types, you know, here's a picture of somebody with a ski mask on, on
  221. an icon, and here's a picture of a knife on an icon, maybe with some little blood coming
  222. off it, or something silly like that.
  223. And then you just throw them on the map and you say, hey, you're welcome, you know, go
  224. make sense of it world.
  225. And I think that, you know, as journalists, we should aspire to do more than just kind
  226. of throw it out there.
  227. We should aspire to throw it out there and shape it into something that, you know, somebody
  228. who doesn't have as much time and isn't paid to do this, right, can just like quickly kind
  229. of learn things from it and see insights.
  230. And you know, one thing we wanted to do, like you saw in those examples, was to spot short
  231. term trends.
  232. I mean, that's something people want to know about crime, is, is in the recent past, is
  233. there some unusual crime activity that I should know about if I live in this place or I know
  234. someone who lives there or I'm interested in it, right?
  235. And the second thing is long term context, which is, okay, so let's say there have been
  236. a lot of crimes recently or not in this neighborhood, but is that usual?
  237. What is the typical level of crime over a long period of time in an area?
  238. And that's the sort of thing people want to know, want about the area they live, but
  239. maybe two about places they're considering moving, right, or places that they're interested
  240. in being active about the level of police protection about, right?
  241. And then the other thing we wanted to do is besides just throwing them on the map was
  242. to shape them into geographic areas that people can relate to, right?
  243. And that's why we love the neighborhood's data.
  244. So where we have developed through a crowdsourcing process with our readers because there are
  245. no oficial neighborhoods for the city of Los Angeles.
  246. We've developed our own set of neighborhoods that are cross mapped with census data that
  247. allows us to take any data that's a point, cross reference those two things, and then
  248. add it up and analyze it, and be able then to dish it out to our readers as well in that
  249. form so that something like the number of aggravated assaults can not just be city wide, it can
  250. not just be, oh, in police district six, which is what the cops might shape it as, right,
  251. no human, who's not a cop, would recognize, but can be presented as no, aggravated assaults
  252. downtown, right, in a geographic unit of analysis that's more recognizable to people.
  253. And we do that not just for crime.
  254. We did it for the pavement story that I showed you earlier.
  255. We did it for the fire department thing.
  256. We do it for the census data.
  257. We do it for all kinds of stuff.
  258. And there's probably something like that that you can use as well, too, and using some of
  259. the GIS tools that'll be discussed at the conference, like CardoDB, or PostGIS, or whatever
  260. you like, Google Fusion Tables, you can actually start to achieve that kind of point in polygon
  261. analysis that's necessary to aggregate point data into something that's more relatable.
  262. But okay, so those are our goals, and we know that we got all these departments, but how
  263. do we actually, like, start on that road to get it?
  264. In the state of California, which is where Los Angeles is, there's a law, and it's a
  265. pretty good law.
  266. It's not a great law, but it's a pretty good law, and it's called the California Public
  267. Records Act.
  268. And it's sort of like, you've probably heard of FOIA, right, which is the federal United
  269. States law governing the United States federal government on the records it has to release
  270. to the public on request.
  271. Every U.S. state also has its own, like, similar law, which governs the state government
  272. and local governments in the state, and ours is the California Public Records Law.
  273. And according to that law, and according to legal decisions that have happened afterwards
  274. after it was passed, we have the right to the crime data.
  275. Yeah, right?
  276. So the cops, they gotta give it to us, they gotta give it to us, I can just write an email
  277. or a letter, and I can say, hey coppers, give me the data, right, and then they're gonna
  278. give me the data, and then I'm gonna make a great map, and it's gonna be awesome, right?
  279. But there's one problem.
  280. They have to give me the data after I make a request, and after they have 10 days to
  281. respond to that request, which is written into the law, and then if 10 days wasn't enough,
  282. they're allowed to file for a 14-day extension, and say, well, you know, we're really busy,
  283. or your request is very difficult, so it's gonna take us a little longer than 10 days,
  284. we need another 14, then the 14 will go by, and I'll say, well, I'm counting weekends,
  285. and they'll say, no, no, we're not counting weekends, and I'll say, yeah, but the law says
  286. calendar days, calendar days are weekends, and they'll say, huh, or they won't respond,
  287. and then several more days they'll go by, and then I'll have to call somebody, and then
  288. it'll, you know, go on and on, and then I'll CC, like, 8 people on email, including their
  289. boss, and I'll be like, yeah, where's my request, and all that'll happen, and then,
  290. hey, I'll get the data, and it'll have been like 2 months, right, and oh gosh, I asked
  291. for the last 6 months of data, but that was 2 months ago, oh, gosh, and oh, what if I want
  292. another dump tomorrow, do I have to file another request, right, and so that's where the law
  293. really falls down for making something like a crime map, where you want to update every
  294. single day with the latest crimes, is the law does not require the government to publish
  295. the data in an open format that regularly updates, that anyone can regularly access,
  296. which is the kind of thing you guys are all looking for when we say open data. The law
  297. just requires them to respond to my request within those, within the rules and regulations
  298. that are set up, and so that was the big hurdle we face, is that the police departments we
  299. were going for didn't have to give it to us in a way that we needed to build the site
  300. we wanted to build, they just had to give it to us in a way that the law required, which
  301. wasn't good enough, right, so that's where we were, you know, we needed something that
  302. they didn't have to give us, so we sort of turned into beggars, and you think, well okay,
  303. if there's 15 police departments, we go to all 15, that's going to take forever, right,
  304. and some of them are like really behind the times, so how can we get the most bang for
  305. the buck, and the tactic that we kind of adopted to do that was we went to the 2 biggest ones,
  306. which is the Los Angeles Police Department, you know, Joe Friday and all that, and the Los
  307. Angeles County Sheriff's Department, who are the two largest in the county, they're the
  308. two most advanced, they're the two biggest, they have LAPD, is actually famous for its
  309. crime mapping that it uses for its own internal analysis, we're told, and they cover, you know,
  310. a huge section of the population and of the territory, and so we kind of decided, you know,
  311. the outset, let's just go after those two guys, and let's try to get them on board,
  312. and so what that meant was like setting up meetings with people who work there, the people
  313. who run their data, and sort of like saying, hey, let's like make this happen, it'll be
  314. a good thing for your goals, for our goals, let's, you know, collaborate and kind of have
  315. it happen, and the Los Angeles Police Department responded this way, you know, they said, well
  316. we don't really trust everybody to give this data, you know what I mean, to put it out open,
  317. but you guys are the newspaper, and you're asking really nice, and like, you know, your editor
  318. came, and like, you know, it's really friendly, and you know, we like data, we're into it,
  319. and we want to look like we're really advanced, and pushing the envelope, and all that stuff,
  320. so we'll do it, but only you can have the data, right, not in the words of our former chief,
  321. not someone in their underwear, right, which in the United States is our image for a blogger,
  322. for some reason, the blogger is always described as being in his underwear, or her underwear,
  323. you know, in the basement, right, alone, but that was the actual image he used, and so
  324. they said, yeah, we'll give it to you, and it'll come like this, and it'll only be for
  325. the last six months, because we have our own Los Angeles Police Department interpretation
  326. of the California Public Records Act, which says, it only applies to contemporaneous records,
  327. which we define as the last six months, so even though you think you get all the data,
  328. we actually say, no, it's probably just the last six months, but we'll give it to you
  329. in this feed that you like, so you're going to complain, and we said, no, we won't complain,
  330. we'll take it, and they set up the feed, and I wrote the code that goes in every night,
  331. and starts pulling it out.
  332. The Sheriff's Department, not as technically advanced or famous about their data analysis
  333. at the LAPD, took what I think was a more enlightened position, and they said, you know
  334. what, this is going to take us a few months to like, make this happen, it can't happen
  335. right away, but if we're going to do the work, why should we do the work just for you?
  336. We get requests for this kind of thing all the time.
  337. Let's instead write the code that'll publish our file up to our Sheriff's Department website
  338. as a CSV, you know, a simple spreadsheet format that anybody can go get, and then Ben, you
  339. can go write your code to put that in, and then the next person who shows up and asks
  340. me for the data, I can just point them to that link and not do any work, and take a
  341. long lunch.
  342. I said, hey, you know, that's a pretty good idea.
  343. And so the Sheriff's Department took that different approach, right?
  344. And then my job became, as the nerd, as the house cat, was to sit in my little cubicle
  345. and write the code that sucks in those data files every day and starts looking at them,
  346. right?
  347. But like a really important thing to remember is, you know, you don't want to be, when you're
  348. working with government data, you don't want to be, you know, we all want to be data journalists,
  349. right?
  350. Big term, it's now in vogue, I love it, you know, we're data journalists.
  351. Be a data journalist, don't be a data stenographer, right?
  352. So you guys know what that's, so a stenographer is the person who sits in court and just like
  353. retypes everything everybody says, no matter how stupid it is, right?
  354. And don't be that type of person.
  355. When you get those data feeds from the government, you got to look at them skeptically, critically,
  356. analyze them before you choose to publish them, otherwise you'll just be a stenographer
  357. to what, you know, what that data is saying, good, bad, or ugly.
  358. And what I discovered looking at those files, much to my surprise, and actually my frustration
  359. because I wanted to make a website, was that the LAPD's data, that file from the big experts,
  360. right, that was published in the FTP for just for me, when I took all those rows and rows
  361. of data, and I did a really complex data mining operation, which is called, you know, addition,
  362. right?
  363. I just added them all up, and I said, okay, so they say there's been this many over the
  364. last six months, and this is where they say it's happened, and this is how many they were,
  365. and I can really drill down, and ooh, I can map and do all this analysis, but I just added
  366. it up.
  367. I saw that, hey, this isn't the same numbers that they report to the FBI, so under our
  368. federal system, the LAPD is reported, is required to give big aggregate totals to the federal
  369. government, to our federal law enforcement agency, which is called the FBI, and that
  370. is under this goofy system that's called UCR for Uniform Crime Reports, right, you maybe
  371. heard of J. Edgar Hoover, he was sort of like a, the tyrant-like figure that ran the FBI,
  372. but one good thing that came out of that was data standards, and so all the local police
  373. departments are required to report their totals up to the federal government in what's called
  374. UCR, and I just found that they didn't equal each other, man, that was really weird, right?
  375. And what I also found is that in the same time I'm racing to do this, build this website,
  376. the LAPD has hired a private contractor to build their own website, and they use the same
  377. file.
  378. Gee, that's weird, so what that then resulted in was a front page story in the Los Angeles
  379. Times, which told people what that weird situation ended up being, which is that the LAPD is one,
  380. giving us a file, two, paying someone else to make a map for you to read, right, that's
  381. missing nearly half of all the crimes that actually occurred in that year, right, and
  382. so this was something that just sort of accidentally came out of building the website, it wasn't
  383. an investigation we set out to do, but just by taking that government data release and
  384. just doing basic integrity checks on it, we found a new story, and then when you know
  385. it's front page, wow, that's fun, you know, my name's in the paper, my mom's really proud,
  386. my mom doesn't want to look at the websites, she doesn't care, but when your name's in
  387. the paper, oh, you know, mom's proud, but unfortunately, that kind of short circuited this like longer
  388. journey we had been on to build the website, you know, we can't just like, now that we
  389. know it's wrong, which geez Christ, we can't like turn it into a website, that would be
  390. irresponsible, and the LAPD police chief, he was a very smooth operator, thanked us for
  391. our investigation, said, of course, this is a problem, we will pull down the site, which
  392. was the right thing to do, and we will fix the data file to the LA Times, and obviously
  393. they couldn't do that overnight, they fired the contractors, some time went by, I worked
  394. on other projects, this thing stood around, and we're talking, we're over a year past
  395. that first slide when Meredith Artley said, we want a crime map, right, just in the acquisition
  396. phrase, but ultimately we do, we get a file again from the LAPD, and I'm back in my cubicle,
  397. you know, taking the files from both the departments, and they each have their own data standards,
  398. their own like column names, their own things they'll give me or won't give me, and I'm
  399. thinking, well how am I going to build one map for everybody out of these different data
  400. sources, well there's this FBI system, which can be our standard, so part of the code and
  401. the database that you build, sucks in these two files every day, standardizes them according
  402. to this federal thing of the eight major crime types, they call them in the US, and then
  403. puts that into like a consolidated LA Times kind of like data table of all the crimes,
  404. right, and then you sort of set up a little web server connected to that, and you're writing
  405. even more code, and then you start writing a lot of math, so we have all the, we have
  406. the big table that has all the crime reports, but man, we want to slice them and dice them
  407. by neighborhood, we want to do these long-term, short-term statistics, and what that requires
  408. is, is in your code, as each day's data file gets loaded, having it calculate, and then
  409. ultimately publish, but first calculate those statistics that we use on the pages I showed
  410. you earlier, right, and so just to walk through what those stats are really shortly, it's
  411. like, so our broad goal is to give you this broad context about, you know, what is violent
  412. crime like in Hollywood, in Echo Park, in Venice, in downtown, for all the neighborhoods
  413. as a whole, where we have crime data, and so one, you have these four of the eight major
  414. crimes by the FBI, four of them are violent, homicide, aggravated assault, robbery, and rape,
  415. and you sort of bundle them up into what we call our sort of violent crimes group or
  416. basket, which is the same thing the FBI does, so again, we're using their federal standard,
  417. we're not out there inventing anything crazy, right, you then map all of those, so each
  418. crime has a point related to it, and you have the computer using, you know, geometry basically,
  419. just sort of pull out of the database all the violent crimes over, say, six months for each
  420. of the 200 plus neighborhoods, and now you have a number, right, for each one, you could
  421. rank that and just do the counts, but you have an issue, which is not all neighborhoods
  422. are created equal, some are bigger than others, so like a very simple per capita statistic
  423. allows us to give you the violent crime rate in each of those neighborhoods, and we're able
  424. to do that because those neighborhoods that I told you about earlier, the intel inside
  425. of the neighborhoods, isn't that we drew the lines just right so everybody agrees and they're
  426. beautiful, and there's no contention about the borders, no, what the great thing about the
  427. neighborhoods is, is it's connected to our US census data, so we actually know for these
  428. sub-city areas that we've defined what their populations are, or if you just go out and
  429. start drawing neighborhoods, it could be totally disconnected from your census information, so
  430. we sort of use the census blocks, is what they're called in the United States, these areas defined
  431. by the census, they're very small, we sort of use them like Legos, right, to sort of put
  432. together the neighborhoods and then maybe cut them in half and split the population between
  433. two, and then at the end, that geographic boundary of the neighborhood we have has all this rich
  434. metadata that comes from our census associated with us, and a really simple but powerful
  435. thing that allows you to do is something like this, which is just to say, for this neighborhood
  436. for downtown LA, this thing that you recognize and can talk about and makes total sense,
  437. here's the violent crime rate, and that gives you a really powerful way to one show and talk
  438. about lots of things, like in the LAFD, in the fire department 911 thing I showed you earlier,
  439. it turned out the slowest neighborhood, the last neighborhood, Bel Air, has the highest
  440. income neighborhood, and so that allows you to not just show something interesting to people,
  441. but to say it to them in a way that anybody can understand, or that you can write on the
  442. front page of the newspaper, the richest neighborhoods have the slowest 911 response, you know what
  443. I mean, and that, and it's because you've built up this foundation of geographic data
  444. linked to rich metadata that you're able to ultimately make that synthesis, and say something
  445. that your mom can understand, or that the guy two beers in at the bar can understand, or
  446. you know, whatever, or even your graphics editor, no, but, and so, and then we essentially
  447. repeated the exact same thing with property crimes as well, and that's how we've got the
  448. sort of broad context, and that also allows you to make maps like this and those rankings
  449. I showed you and whatever, but then we also wanted to get at the newsiness of short term
  450. crime and things that have happened recently, but we wanted to do it in a statistically
  451. sound way, so again, let's say I want to know what neighborhoods are having an unusual amount
  452. of violent crime like now, or like in the last week, cause that's news, right, that's
  453. a lot of work though, we don't have enough reporters to like go out to all the neighborhoods
  454. and talk to people and figure that out, but the crime can, this can help, so again you
  455. take all the violent crimes, you slice them up by the neighborhood, but this time instead
  456. of over the last six months, you look over the most recent seven days that you have available,
  457. which we defined as our recent period, which is just the statistical decision we made, it's
  458. totally debatible, we then calculated, we compared that most recent week to the weekly
  459. average in that neighborhood over the last three months, so in, you know, in downtown
  460. there's been this many crimes per week over the last three months, and here's how many
  461. have been in the last seven days, you know, what is the most recent week's deviation from
  462. that weekly average, right, as a statistic you can calculate, we calculated that, so
  463. that gives you another number, and then we editorially defined a certain standard deviation
  464. and a certain absolute number of crimes, it has to deviate this much from the mean, it
  465. has to be at least three crimes for it to qualify for what we call our alerts, right,
  466. and so if the neighborhood has relative to its own past, experienced a standard deviation
  467. above this or that, and met the standard alert into the blog post, up and out, so there's
  468. a little alert flag that will appear on the page, you know, you can see it explained in
  469. here, and then it goes into the blog post, well, and the end product is something we hope
  470. is really simple for people to get, which is like, hey, check it out, here's a neighborhood
  471. that's having more crime than usual, right, and maybe if it's really crazy a reporter
  472. would look into it or whatever, but behind that thing that we're trying to make that's
  473. easy to understand is just sort of a basic statistical algorithm or, you know, equation
  474. or whatever you want to call it, where we've defined strictly the rules of what we're going
  475. to call news, so we're taking that kind of news judgment and we're converting it into
  476. computer code and then running it over the moving data stream that comes in every day
  477. from these police departments, right, and so that all then happens, so, you know, every
  478. night there's a cron script, not, you know, to pull the two files, standardize them, stuff
  479. them in one database, get them in the web server, then the web server does all this math
  480. down here that I was telling you about, and then that happens, and then, finally, you actually
  481. have to make something for people to use out on the internet, right, after you've done all
  482. that, and do all that development in House 2, and we use kind of a common stack of tools
  483. very similar, I think, to the ones they use at the Texas Tribune, that includes at its
  484. core a post-GIS database, which is, you guys familiar with it, it's a modification of the
  485. common open source Postgres database that allows you to do geometry on it, so, you know, you
  486. can ask a database, give me all the records where the name field equals ben, right, but
  487. with a post-GIS database, you can say give me all the records that are near or within
  488. a certain boundary of this point, or who fall within this neighborhood, so it allows you
  489. to write that kind of SQL database interactions with a geometric questions, as opposed to
  490. strict semantic questions, and it's very popular, you know, if you've used Instagram, you've
  491. used post-GIS, right, so the same tools that we use to make news was used to make those
  492. people billionaires, and exact same software, exact same tools, they just used it for, you
  493. know, a different purpose, and it's used on a lot of things, and then on top of that
  494. is the application layer, where all the rules of what gets pulled out where, to put on what
  495. pages get written, and that we also do in Python and Django, there's sort of the web
  496. server and all the caching, like that's the part where all my stress is, it's like right
  497. there, in that third layer where you sort of make sure that it can manage all the web
  498. traffic you're gonna get in a way that doesn't crash, and then finally there's the actual
  499. user interface for the people, which we just use basic common stuff that you all familiar
  500. with HTML, JavaScript, and a number of JavaScript libraries for mapping and other things that make
  501. it easier, I'd really recommend Leaflet, which is just sort of blown out the field in the
  502. last couple of years as a JavaScript mapping tool, and so that's how we get it from the
  503. server to the user, and that's how you end up on a page like this, which again is the
  504. home page that we started of at maps.lax.com slash crime, where you can go, and the basic
  505. thing on the page is guess what, the neighborhoods that have most recently had an alert, but if
  506. you hover on a neighborhood, click it, you'll get that neighborhoods report, you can search
  507. your address, it'll jump you to that neighborhoods page, where you'll get all the information
  508. that we talked about and showed earlier, and so, also results in a front page story where
  509. we say, yeah, we now have a crime map, here's a big long term thing, you should go check
  510. it out, right, here's the email I get every morning that tells me that yes, everything
  511. went A-OK, and the crime data has published and come in, there's a number of diagnostic
  512. screens that kind of give me a little more information about what exactly got loaded
  513. and what happened, or if something went wrong, that I need to go work on it, but that's
  514. kind of part of the process as you get these little reports, and then those automated blog
  515. posts get written, and then my favorite thing is then using this database to not just allow
  516. someone to go look up data because they're curious, or to provide kind of a little more
  517. contextual analysis for somebody who's house hunting, but to also really try to better
  518. inform the day-to-day crime coverage that we do in the paper. I mean, I'm not an expert
  519. on Argentine media, but I would bet that you have some of the same things happen that happen
  520. in the United States, which is that a small number of spectacular crimes tend to get exponentially
  521. more attention than the run-of-the-mill things that happen that'll be in a database like
  522. this, and people like to attach their own storylines to what those crimes mean and what
  523. they tell you about the places where they are that are based on folklore and previous big
  524. things like this that have happened, or whatever, and often aren't supported by a lot of empirical
  525. evidence, right, and so what really gets me excited is when a crime happens and we're
  526. able to using the database the same day that the crime happens, or very shortly thereafter
  527. provide more context to our readers about what that means, so if you're familiar with
  528. the neighborhood of Hollywood, which is where the Hollywood Walk of Fame is, right, there's
  529. a, you know, if you've ever been there, it's not really that nice, and it is, act, there's
  530. a stabbing there and a killing in recent times that got tons and tons of attention, and what
  531. we were able to do, they're gonna write this front page story anyway, you know what I mean,
  532. they're editors and reporters, they're gonna write it, but you can then insert yourself
  533. into that process and contribute a couple of paragraphs that say, well, here's the actual
  534. long-term rates, which in this case it actually was high, right, this crime does represent
  535. a significant amount of violent crime that happens in that neighborhood, or you can try
  536. to challenge that internally, and we've done that on a couple other occasions as well, where
  537. the police like to flood the zone and kind of tell people that either this neighborhood,
  538. that they're sort of, they try to tell their own story using the statistics, right, so statistics
  539. are things that the government uses to tell stories, and you can, one, validate or maybe
  540. challenge those stories when you're able to do your own independent analysis, and crime
  541. is a really great example of that, that I don't think it's done enough, that we don't
  542. use the data to really step into those really changing stories, and I think that we could
  543. definitely do a better job of it, but to me, that's what's really exciting, is being able
  544. to get beyond just putting it out there, and to really challenge those top-line narratives
  545. in the media, okay, yeah, blah, blah, blah, right, so all this was based on, and started
  546. with my friend the California Public Records Act, and our ability and the goodwill of our
  547. police departments to ultimately participate, right, now I know not everybody has that,
  548. and I personally don't know the situation in Buenos Aires, I mean, please someone tell
  549. me afterwards, or we can talk about it, I'd be happy, but I know in many places, that's
  550. not the case, one, you don't have the law on your side, two, you don't have the administrators
  551. on your side, or either or, and you're kind of screwed, right, so, I mean, remember I
  552. started with this, we take these databases and turn them into news, and that's primarily
  553. what we do, and what people like us do, but it's not all we do, right, there's a different
  554. approach you can take in more difficult situations, and this is kind of flip, but it's true, instead
  555. of turning databases into news, you turn the news into a database, right, like starting
  556. from scratch, and if you think about it, there's lots of projects that you see like that,
  557. and one that we do that's related to crime, that is exactly that, is a separate crime site
  558. that we keep, and actually a blog, that's called the Homicide Report, and it's been around
  559. about five years, and it records each and every person who is killed by another person in
  560. Los Angeles County, and here's, you know, you can check it out if you want to see it
  561. at projects.laxtimes.com slash homicide, that map looked a little old, I do admit, but
  562. I promise you the site will, in the next month, have a total facelift and be a lot cooler,
  563. but this is what it currently is, is right there, and it's still a really great and very
  564. informative site, and it includes, you know, a page, a blog post, for each and every person
  565. who's died, but a blog post is really just a blob of text, and what you can do when you
  566. stop approaching news is a blob of text, and start thinking about everything critically
  567. more as data, as you can say, in that death, Michael Jackson, right, which happened in the
  568. city, right, there's more than just the blob of text, there's the name of the person who
  569. died, there's the photograph of that person, there's their date of birth and their age,
  570. there's the date that they died, we can take that point of the address, and we can convert
  571. that into our neighborhood using the system, there's his gender, there's how he died, there's
  572. the police involved, there's his ethnicity, there's where he died, and there's other things
  573. we actually collect, too, and so you could take that, and you could structure the story,
  574. right, and you can see you can do the same thing, not just for celebrities and front-page
  575. deaths, but for, you know, here's someone who died recently for people, for, you know,
  576. someone few people are familiar with, and you know, really anyone, and you can start taking
  577. that same structured approach to a lot of different people, and we take that to really everyone,
  578. and so we have a database that we build, we built from really nothing, right, from the
  579. scraps of public records and things that go out, that we're able to assemble, that go
  580. into a database system that's behind the website, where all of that structured information
  581. is sort of put in and refined and grown and nurtured over time, and then once you have
  582. a database like that, you're able to, one, quickly build through a templating system,
  583. a blog and a page for every person, right, which we just saw, but also do all kinds
  584. of analysis, so here's the neighborhood where I live, like, so I live like, see this is
  585. the best house shopping website, right there, and so, you know, so you can see over the
  586. last five years or whatever, here's all the people that have died, we can do the same
  587. per capita rates, you can see the most recent, you can, when you're writing the blog post
  588. about someone who died, say, this person died in downtown, and this neighborhood has had
  589. this many, can be written right into the newspaper, the most recent one was here or there, there
  590. hasn't been a, there hasn't been a homicide in the neighborhood where this one occurred
  591. in the last, you know, six years, you can provide that kind of context instantly to
  592. new crimes, and you can provide a lot of really broad, long-term context to readers about
  593. the shape of something over a long period of time, which we were able to do here using
  594. some data from a previous project to show the long-term decline in homicides in Los Angeles,
  595. which results in a front page story, right, and another example of this, this isn't just
  596. us, and we were talking about this yesterday, is, you know, is the drug war in Mexico.
  597. So I really think that, you know, one of the things that's helped reframe the drug war
  598. in Mexico as strictly a law enforcement story, or as a story about who's the coolest narco
  599. or whatever, has been the effort by the media primarily in Mexico to document the toll, the
  600. regular people, you know, people involved, people not involved, all the people who are
  601. dying as part of this, and that's really a database-building project, right, where data
  602. wasn't available, that a lot of different people in the media and in the government there
  603. have done, particularly Law Reforma, and so this is a map that's several years old, the
  604. actual number now is much, much higher than this, this is a map from 09, but this is where
  605. we were able to take that structured data of all the deaths related to the drug war that
  606. Law Reforma had collected via the Transborder Institute, which is a great group, and we
  607. were able to map over time and geographically and give a big number for people about the human
  608. toll of this war, right, and we were only able to do that because somebody made a database
  609. where there wasn't one, right, and they took the news and they built it slowly over time
  610. and that became the raw materials, not just, I mean, for, you know, everyone in the media
  611. and in the government to have real data, and so I guess the point of telling this little
  612. fable is just to get at, you know, if you don't have the open data, it can really limit
  613. you a lot, but there's ways to work around it, and what's necessary is one, you know,
  614. focus and drive to get at something that's important and lots and lots of wherewithal
  615. to make it happen and to build those systems, and you know, if that's the situation you're
  616. in, I think that the model in America of the homicide database, which not just we do, but
  617. it's done in Washington D.C., in Chicago, in many other cities, is something that could
  618. be effective or you are too, and there's actually some people who try to sell software
  619. that does this, you know, I won't get into that whole thing, but I think that if it's
  620. not being done here and you're concerned about homicides, I think that's one thing that's
  621. happening in the United States that I would encourage you to think about, okay, and so
  622. I mean, that's the end of my little spiel, right, and I'm happy to talk about this, about
  623. anything else, about any other questions you got, just want to say quick off, you can
  624. find our major projects at datadesk.lattimes.com, you can follow all the crap we put out at
  625. LAT Datodesk on Twitter, and you can check out our code and point out all our bugs,
  626. please, on GitHub, okay, and so, that's it.
  627. My main fear when I'm using databases is that you miss out on the difficulties that you
  628. have finding that information, when you split the person that's collecting the information,
  629. the subjectivity to it, what difficulties you had in encountering that information, and
  630. you kind of lose the contact between the person using it and the person collecting it.
  631. How do you work to avoid that happening, to make numbers, absolute numbers, to see what's
  632. the exception to the...
  633. How do you keep from dehumanizing them, turning them into just another number kind of thing?
  634. Well, with numbers, you just don't get the feel if that information is absolutely accurate,
  635. or it's just almost accurate, or if there was an exception or something funny you found
  636. when you were doing the numbers, but it's really not...
  637. We would leave it out, so our kind of policy is if it's not verified, it's not from an
  638. official source, or the people directly involved with the person or something that's verifiable,
  639. we'll just omit it, right, and so there's some cases where you don't have complete data
  640. about a lot of the people, right, and so it's sort of us, hodgepodge, assembling the official
  641. sources that do exist, and from our own work in the field, to build the database, and so
  642. there's a good number of people about which there's very sketchy information, right, but
  643. we also have a full-time reporter who works on the homicide report, and her job is to go out
  644. and interview people and talk to people and get the photographs and then write stories about
  645. a smaller number that we're able to do that amount of research, and so the reality is,
  646. is that for most people who are killed or die in these circumstances, they don't get all that
  647. attention because we don't have all the resources, right, but we try to direct it where we can
  648. to, one, make sure that there's a bare minimum amount of information about everybody, which
  649. otherwise does not exist, right, you know what I mean, they're not counted, they really aren't,
  650. right, they're not, particularly in Los Angeles, which is a really big city,
  651. which even though it's down, there's still a lot of homicides, only a small number
  652. are ever going to be a brief in the newspaper or make the local news, right, they would otherwise
  653. go unmentioned, and so even in the cases where you have very little, many of the people close
  654. to the victim are often very appreciative just that there's something, right, we also provide
  655. comments associated with each post that's generated so that people are able to have conversations,
  656. many of them, very frank, on those pages, and so it's, I wish we could do more,
  657. and I wish we could get more, but we can't, and with the resources we have, we really have
  658. the database developer who does, does the coding that, you know, is a big capital investment that
  659. builds a system that can work for, you know, a while without them working on it every day,
  660. and then that reporter who kind of also is inputting things, collecting things,
  661. writing stories for the paper, for, you know, for the web as well that are more narrative,
  662. you know, more traditional stories, right, that someone who's not in the numbers could relate to.
  663. Bueno, mi pregunta tiene que ver con hace un par de años un político argentino presentó un mapa
  664. del delito, pero no tuvo mucha repercusión porque la gente no cooperaba en el aporte de datos.
  665. En la otra parte del aspecto que tiene que ver con este planteo, lo que quiero, a lo que quiero
  666. llegar es sobre toda la exposición que hizo es que hay de la parte de los ciudadanos en el aporte
  667. o si hay algún aplicativo donde desde los teléfonos celulares se pueda dar más reportes,
  668. si hay alguna investigación en eso o cómo es la cultura en todo caso.
  669. No, me refiero a toda esta investigación que le está mostrando es lo que se hace como reporte
  670. Pero ellos lo están tomando de información de la policía,
  671. they are getting the information from the police department.
  672. Okay, understand.
  673. Here in Argentina some crimes are not reporting to the police.
  674. So he's asking if you have another way of getting information besides police reports
  675. to add to your map.
  676. We don't have any sophisticated whiz-bang technology that makes that easier,
  677. but I think that that's a really really good thing to keep in mind when you're analyzing crime data
  678. is no matter where you are there's an under count because of the people who don't report,
  679. right, which is probably a very very large number of crimes and there's also what I call
  680. politely attrition, the number of people who are frustrated in their process to make a crime report
  681. and ultimately just don't, you know what I mean, they're making a call to the police department
  682. and they get frustrated with dealing with someone and it just never comes together.
  683. And so I think it's important to keep in mind that there's a really big number of uncounted crimes
  684. out there and I don't have the solution to that problem. I think that making a system
  685. where people can anonymously report crime perhaps or where they can,
  686. where it's easier for them to report it than it currently is with the police are potential fixes
  687. for that. I think that, you know, police IT in a place where police want to get those reports,
  688. information technology for the police department is a potential fix for that.
  689. Why do you have to get on the phone and go through an interview over the phone to make this report
  690. to the police? Couldn't you yourself enter basic information into a form and then be contacted
  691. as a verification and speed up that process to reduce the attrition?
  692. But, you know, this is a huge problem, particularly with crimes against women. I mean,
  693. we know this with domestic abuse and rapes that there's one women are, you know, in an abusive
  694. relationship and they're afraid to report because the consequences are for many other reasons.
  695. But, you know, I don't need to explain it. And I don't have the solution. I don't.
  696. But we should all recognize, particularly when we're describing these statistics,
  697. that that's a reality and we shouldn't present them as something they aren't.
  698. Homicides are a little different. It's more difficult to hide the body. You know,
  699. those sort of things tend to get counted, though it depends where you live, I suppose.
  700. But it's a huge problem.
  701. ¿Qué tal? Mi pregunta es sencilla. ¿Cómo es la evolución de los datos? Supongamos que hay
  702. un asesinato, se lo informa la policía, pero resulta que después evoluciona la investigación
  703. y fue un suicidio o fue un atentado o fue bajo otras causas. ¿Cómo actualizan esta información?
  704. ¿Lo llevan a la actualización?
  705. ¿Qué pasa si un asesinato ha sido reportado como un tipo de asesinato, pero luego,
  706. durante la evolución de la investigación, se descubrió que ha sido otro tipo de asesinato?
  707. ¿Cómo updates tu database?
  708. Right, so this is one of the trickier things of getting a system like this going,
  709. is managing amendments or changes to existing records and or the deletion,
  710. like if they decide it's not a crime and they remove it or additions of new ones, right?
  711. And in the actual code writing process and in getting the data system set up at the outset,
  712. you need to be really careful and responsible with how you do it.
  713. So the way it works is each of the two departments I get it from
  714. manage it kind of slightly differently. So the LAPD, remember their little six month thing
  715. that they're very fond of, they only want to give me the last six months.
  716. So every day the file updates and it's sliced from their database of every date in the last six
  717. months. I then, and it has a unique ID, right? I pull the similar time period from my database
  718. and I evaluate each record looking for amendments in any field, right? That's part of the code that
  719. does the load. And then if there's an amendment, it changes the record and it also logs each
  720. version as well. We don't publish that, but privately in the database it's logging every
  721. version of the individual records. And so we're able to check within that six months, though
  722. because they have that six month rule on their side, if a crime older than six months is amended,
  723. I don't get it, right? And it's not in our crime site. And it's for that reason that when we calculate
  724. our long term statistics for the rankings, we use a six month period because it's only within
  725. that six months that I can, you know, it's probably a very small number that change, but I don't know.
  726. And since I don't know, I'm not going to pretend I do, right? And so that's why we worked on that
  727. thing. Now the sheriff's department, though they don't have the reputation, do a smarter thing.
  728. When they, when they give me the daily slice, they give me all amendments in the last six months,
  729. in the last six months. So rather than filtering on the crime date, they're filtering on the edited
  730. date, that their database keeps every time there's a change. And so that allows me to be more,
  731. you know, certain that you're getting it. But in my code, I have to make sure that I do all those
  732. changes, otherwise my data is going to get out of date and be wrong. There's also a process for
  733. removing crimes that have been deleted. And if a crime has been deleted, I check it in the database,
  734. we keep the record, we actually keep it on the map, and you can click on it and it'll say deleted.
  735. And then we'll explain to you that the police have deleted this crime, but we will omit it from the
  736. statistics, right? So when we calculate the numbers, we won't count it, but we'll still show it to you
  737. so you don't like come to our site one day, see a crime and come back and then it's gone and think
  738. that we're playing some game. You know what I mean? We're just going to be transparent about
  739. it got deleted. And that's a real pain in the butt, you have to write a lot of code.
  740. But it's worth doing.
  741. Hi, do you have like a system to test the accuracy of the information that you receive,
  742. even if it's from a government agency? How do you know to trust that that information
  743. is accurate? Sort of broadly as opposed to just the crime data?
  744. Yeah, so like yeah, so broadly, there's a couple, you know, things you can do that are just kind of,
  745. you know, they're not even really tricks, it's just like basic stuff that, you know,
  746. people call integrity tests or whatever. To me, the most basic thing is if you're getting line by
  747. line data that's provided to you by a source and they themselves publish some total somewhere
  748. or add it up somewhere, can you recreate the total that they've made? And I almost, if I can find
  749. that, that will always be my first goal is to say, can I recreate the analysis
  750. that they themselves have done? It doesn't mean their analysis is right,
  751. you know what I mean? But it means you understand how they do what they do and how they get there.
  752. And then if it does match, you know, you got a pretty good sense you're on track with them.
  753. And if it doesn't match, you get to ask some really interesting questions.
  754. Why is this? And then you can, and then that process you'll learn a lot, you know,
  755. and there's usually only a small number of people in the government, you know, agency that
  756. actually know how that happens. And once you get to talk to one of them, you can ask them
  757. all kinds of questions, you can learn all kinds of stuff, and you're able to kind of get some more.
  758. So to me, it's just like adding it up. It's, you know, even like just the total number of rows,
  759. you know what I mean? It's not like you're doing anything fancy. Like, do I have the same
  760. number of rows that they do? If they're omitting things, which people often are,
  761. for different reasons, omitting things from analysis, you know, you got to figure out
  762. what those are, right? It's like what I discovered looking at the fire department database and a
  763. lot of their problems, is they were omitting different things at different times, and they didn't
  764. know why, because different people were doing it, and it wasn't standardized, and we were really
  765. pressing them on it, writing all these stories, and then I got one of the memos that they wrote
  766. in that time period that we were pressuring them, and the memo about how to use the database said,
  767. what do we exclude? Question mark, you know what I mean? And so don't always assume that there's
  768. really like a system. You know, the people with the government generate this data, they're people
  769. just like you, they have, you know, often deadlines just like you, and people clujen
  770. stuff, you know what I mean? Numbers get clujes sometimes, particularly when it's not,
  771. though Argentina may be an exception, particularly when it's not like the really high level
  772. economic data that everyone is very closely watching, right? But, um, does that answer your
  773. question? Yeah, thank you. Okay. ¿Alguna otra? Dejamos acá.
  774. Do you guys write about what works, what are the best practices when a place where the crime goes
  775. down in a place where you reported high crime rates before? Sort of a good news kind of story,
  776. like on what works. Um, you know, probably not as much as we should, you know what I mean?
  777. The reality about crime, and this is probably my opinion, but you know, crime in the United
  778. States is going down over a really long term period, according to official crime reports,
  779. you know what I mean? Like way down, you know what I mean? You saw that story with 300 homicides,
  780. it was over a thousand like in the 90s. And so that's the big long term story about crime data
  781. in the United States. And I think that, that the media in the U.S. covers that a lot because
  782. politicians talk about it a lot, police chiefs talk about it a lot. It is remarkable. And that
  783. kind of big picture, like good news story about crime going down is very, very commonly told in
  784. the U.S. media. However, why? Right? Like, why is the crime going down is a debated thing? And that,
  785. you know, the mayor is going to tell you, it's because he and the police chief are such great
  786. mayors and police chiefs, right? And they're, they've reformed the police department to be more
  787. enlightened and da, da, da, da. Well, okay, that may be true, but major crime is going down in every
  788. city in the United States, and you're not mayor everywhere. And that mayor in that other town,
  789. he's in jail, so I mean, he wasn't a very good mayor, right? So there's some structural things
  790. happening in the, in at least the U.S., they're deeper, you know what I mean, than like one mayor
  791. or one approach to policing that are happening. And, and a lot of different ways we try to get at
  792. that, we've used our data to do a couple stories, like about, like areas within high crime neighborhoods
  793. that have lots of crimes, smaller, like sub neighborhood, like block, you know, areas that have
  794. like next to none, and like, why is that? And we've gone and done stories about a couple places
  795. like that to see what, what is that place like that leads to that happening, right? And there's
  796. some reasons, but I think that the, the really big ticket question in the U.S. about why crime is
  797. going down is still to be answered, and maybe can't be answered, you know, via some mix of economics
  798. and sociology and politics that kind of leads to it all. There's one person who wrote a controversial
  799. magazine article a few months ago claiming it's the reduction in lead and paint. There's less
  800. lead paint in, in like, in poor, in poor neighborhoods, and so fewer people have lead poisoning,
  801. and lead poisoning leads to all this other stuff, you know what I mean? And that's a theory,
  802. but how do you test it? How do we ever sort this out? I don't know if we'll really ever know the
  803. answer. Thank you, Ann.

Downloads

Slides PDF · Recording video · Extracted slide text · Timestamped transcript