Coverage for src/crimepy/nibrs/tables.py: 95%
138 statements
« prev ^ index » next coverage.py v7.17.0, created at 2026-10-30 18:01 +0000
1"""Joins the tables of NIBRS incidents into flat tables for analysis.
3The master files spread each incident over several tables (its offenses,
4victims, offenders, and arrestees), and an analysis usually needs them
5together. Each builder here returns one flat table with one row for each
6incident, victim, offense, offender, or arrestee, and the details of its
7incident and agency:
9* `incidents`: one row for each incident, with its agency; the number of its
10 offenses, victims, offenders, and arrestees; whether it had an offense
11 against a person, property, or society, and an offense of each category;
12 whether a firearm was used; whether and when someone was arrested; and
13 whether it was cleared.
14* `victims`: one row for each victim, with its incident and agency, and the
15 age, sex, race, and ethnicity of the first offender that the victim is
16 related to, with the relationship.
17* `offenses`: one row for each offense, with its incident and agency.
18* `offenders`: one row for each offender, with its incident and agency.
19* `arrestees`: one row for each arrestee, with its incident and agency.
21`build` loads what a builder needs and calls it. Each builder takes the
22segments as `amos` datasets or data frames and returns an `amos.Dataset`,
23whose `data` is the built table (and which can be given a `label`, `task`,
24and `groups` for modeling):
26```python
27import crimepy
29victims = crimepy.nibrs.tables.build('victims', 2022, states = 'DE')
30victims.data.head()
31```
33The tables of an incident share the columns in `KEYS`. Agencies are joined
34by the ORI of the batch header that each record follows ("agency_ori"), and
35their columns get the prefix "agency_" (such as "agency_population").
37Contents:
38 AGENCY_COLUMNS: the columns of agencies that are joined to incidents.
39 BUILDERS: the builders, by name.
40 KEYS: the columns that identify an incident.
41 arrestees: one row for each arrestee.
42 build: loads the tables that a builder needs and builds its table.
43 incidents: one row for each incident.
44 offenders: one row for each offender.
45 offenses: one row for each offense.
46 victims: one row for each victim.
48"""
50from __future__ import annotations
52import functools
53import re
54from collections.abc import Callable, Mapping, Sequence
55from typing import Any
57import amos
58import numpy as np
59import pandas as pd
61from .. import utilities
62from . import codes, store
64KEYS: list[str] = ['data_year', 'ori', 'incident_number']
65# Columns of agencies that are joined to incidents, with their new names.
66AGENCY_COLUMNS: dict[str, str] = {
67 'city': 'agency_city',
68 'agency_type': 'agency_type',
69 'population': 'agency_population',
70 'population_group': 'agency_population_group',
71 'core_city': 'agency_core_city',
72 'division': 'agency_division',
73 'region': 'agency_region',
74 'fips_county_1': 'agency_fips_county',
75 'msa_1': 'agency_msa',
76 'months_active': 'agency_months_active'}
77# Weapon codes of firearms, and their labels.
78_FIREARMS: frozenset[str] = frozenset(
79 {'11', '12', '13', '14', '15'}
80 | {codes.CODEBOOKS['weapons'][c] for c in ('11', '12', '13', '14', '15')})
81# Codes and labels of incidents that were not cleared exceptionally.
82_NOT_CLEARED: frozenset[str] = frozenset({'N', 'Not Applicable'})
83# Columns of the administrative segment that are joined to other tables.
84_INCIDENT_COLUMNS: tuple[str, ...] = (
85 'incident_hour', 'report_date', 'cleared_exceptionally',
86 'exceptional_clearance_date', 'cargo_theft')
89""" Decorators """
92def _dataset_builder(
93 builder: Callable[..., pd.DataFrame]) -> Callable[..., amos.Dataset]:
94 """Makes a builder take datasets or data frames and return a dataset.
96 The builder is written for data frames. The returned function accepts
97 each segment as an `amos.Dataset` or a data frame, and the keyword
98 arguments "label", "task", and "groups" of `amos.Dataset.create`.
100 Args:
101 builder: a function that builds a data frame from data frames.
103 Returns:
104 The function, which returns an `amos.Dataset`.
106 """
108 @functools.wraps(builder)
109 def build_dataset(
110 segments: Mapping[str, Any],
111 *args: Any,
112 label: str | None = None,
113 task: str | None = None,
114 groups: Sequence[str] | str | None = None,
115 **kwargs: Any) -> amos.Dataset:
116 """Builds the table and returns it as a dataset.
118 Args:
119 segments: the tables of the incidents (as datasets or data
120 frames), by name. Tables that are `None` are left out.
121 *args: arguments for the builder.
122 label: the label of the dataset. Defaults to `None`.
123 task: "classify" or "regress". Defaults to `None`.
124 groups: the groups of the dataset. Defaults to `None`.
125 **kwargs: arguments for the builder.
127 Returns:
128 A dataset whose `data` is the built table.
130 """
131 frames = {
132 name: utilities.to_frame(table) for name, table in segments.items()
133 if table is not None}
134 return amos.Dataset.create(
135 builder(frames, *args, **kwargs), label = label, task = task,
136 groups = utilities.to_names(groups) or None)
138 return build_dataset
141""" Builders """
144@_dataset_builder
145def incidents(
146 segments: Mapping[str, pd.DataFrame],
147 *,
148 categories: bool = True) -> pd.DataFrame:
149 """Returns one row for each incident.
151 Args:
152 segments: the tables of the incidents (as datasets or data
153 frames), by name. "administrative" is
154 needed. "offenses", "victims", "offenders", "arrestees", and
155 "agencies" are used if they are there.
156 categories: whether to add a column for each category of offense
157 (such as "has_assault_offenses"), saying whether the incident had
158 an offense of that category. Defaults to `True`.
160 Raises:
161 KeyError: if there is no "administrative" table.
163 Returns:
164 The incidents (once each, though a few are in the master files more
165 than once), with the columns of the administrative segment and:
166 "incident_weekday" and "incident_month"; the agency's columns
167 (see `AGENCY_COLUMNS`); "offense_count", "victim_count",
168 "offender_count" (offenders whose sequence number is not 0,
169 which means unknown), and "arrestee_count"; "against_person",
170 "against_property", and "against_society"; "firearm";
171 "arrested", "first_arrest_date", and "days_to_arrest"; and
172 "cleared" (by an arrest or exceptionally).
174 """
175 # A few incidents are in the master files more than once (193 of the 12
176 # million in 2023), so only the first record of each is kept.
177 data = _require(segments, 'administrative').drop_duplicates(KEYS)
178 dates = pd.to_datetime(data['incident_date'])
179 data['incident_weekday'] = pd.Categorical(
180 dates.dt.day_name(),
181 categories = [
182 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
183 'Saturday', 'Sunday'])
184 data['incident_month'] = dates.dt.month.astype('Int64')
185 data = _join_agencies(data, segments.get('agencies'))
186 offense_table = segments.get('offenses')
187 if offense_table is not None: 187 ↛ 189line 187 didn't jump to line 189 because the condition on line 187 was always true
188 data = _join_offense_summary(data, offense_table, categories)
189 counted = (('victims', 'victim_count'), ('offenders', 'offender_count'))
190 for name, column in counted:
191 table = segments.get(name)
192 if table is not None: 192 ↛ 190line 192 didn't jump to line 190 because the condition on line 192 was always true
193 if name == 'offenders':
194 table = table[table['offender_sequence'].fillna(0) > 0]
195 data = _join_counts(data, table, column)
196 arrests = segments.get('arrestees')
197 if arrests is not None: 197 ↛ 207line 197 didn't jump to line 207 because the condition on line 197 was always true
198 data = _join_counts(data, arrests, 'arrestee_count')
199 first = arrests.groupby(KEYS, observed = True)['arrest_date'].min()
200 data = data.merge(
201 first.rename('first_arrest_date').reset_index(), on = KEYS,
202 how = 'left')
203 data['arrested'] = data['arrestee_count'] > 0
204 data['days_to_arrest'] = (
205 data['first_arrest_date'] - pd.to_datetime(data['incident_date'])
206 ).dt.days.astype('Int64')
207 exceptional = data['cleared_exceptionally'].astype('object')
208 cleared = exceptional.notna() & ~exceptional.isin(_NOT_CLEARED)
209 if 'arrested' in data.columns: 209 ↛ 211line 209 didn't jump to line 211 because the condition on line 209 was always true
210 cleared = cleared | data['arrested']
211 data['cleared'] = cleared.astype('boolean')
212 return data
215@_dataset_builder
216def victims(segments: Mapping[str, pd.DataFrame]) -> pd.DataFrame:
217 """Returns one row for each victim.
219 Args:
220 segments: the tables of the incidents (as datasets or data
221 frames), by name. "victims" is needed.
222 "administrative", "offenders", and "agencies" are used if they
223 are there.
225 Raises:
226 KeyError: if there is no "victims" table.
228 Returns:
229 The victims, with the incident's columns (see `_INCIDENT_COLUMNS`),
230 the agency's columns, and, from the first offender that the
231 victim is related to: "offender_sequence", "relationship",
232 "offender_age", "offender_sex", "offender_race", and
233 "offender_ethnicity".
235 """
236 data = _with_incident(_require(segments, 'victims'), segments)
237 offenders_ = segments.get('offenders')
238 if offenders_ is not None: 238 ↛ 251line 238 didn't jump to line 251 because the condition on line 238 was always true
239 data['offender_sequence'] = data['offender_1'].astype('Int64')
240 data['relationship'] = data['relationship_1']
241 columns = [
242 c for c in (
243 'offender_age', 'offender_sex', 'offender_race',
244 'offender_ethnicity')
245 if c in offenders_.columns]
246 people = offenders_[[*KEYS, 'offender_sequence', *columns]]
247 people = people.drop_duplicates([*KEYS, 'offender_sequence'])
248 people = people.astype({'offender_sequence': 'Int64'})
249 data = data.merge(
250 people, on = [*KEYS, 'offender_sequence'], how = 'left')
251 return data
254@_dataset_builder
255def offenses(segments: Mapping[str, pd.DataFrame]) -> pd.DataFrame:
256 """Returns one row for each offense.
258 Args:
259 segments: the tables of the incidents (as datasets or data
260 frames), by name. "offenses" is needed.
261 "administrative" and "agencies" are used if they are there.
263 Raises:
264 KeyError: if there is no "offenses" table.
266 Returns:
267 The offenses, with the incident's and agency's columns.
269 """
270 return _with_incident(_require(segments, 'offenses'), segments)
273@_dataset_builder
274def offenders(segments: Mapping[str, pd.DataFrame]) -> pd.DataFrame:
275 """Returns one row for each offender.
277 Args:
278 segments: the tables of the incidents (as datasets or data
279 frames), by name. "offenders" is
280 needed. "administrative" and "agencies" are used if they are
281 there.
283 Raises:
284 KeyError: if there is no "offenders" table.
286 Returns:
287 The offenders, with the incident's and agency's columns.
289 """
290 return _with_incident(_require(segments, 'offenders'), segments)
293@_dataset_builder
294def arrestees(segments: Mapping[str, pd.DataFrame]) -> pd.DataFrame:
295 """Returns one row for each arrestee of a Group "A" incident.
297 Args:
298 segments: the tables of the incidents (as datasets or data
299 frames), by name. "arrestees" is
300 needed. "administrative" and "agencies" are used if they are
301 there.
303 Raises:
304 KeyError: if there is no "arrestees" table.
306 Returns:
307 The arrestees, with the incident's and agency's columns, and
308 "days_to_arrest".
310 """
311 data = _with_incident(_require(segments, 'arrestees'), segments)
312 data['days_to_arrest'] = (
313 pd.to_datetime(data['arrest_date'])
314 - pd.to_datetime(data['incident_date'])).dt.days.astype('Int64')
315 return data
318BUILDERS: dict[str, Callable[..., amos.Dataset]] = {
319 'arrestees': arrestees,
320 'incidents': incidents,
321 'offenders': offenders,
322 'offenses': offenses,
323 'victims': victims}
324# The tables that each builder uses.
325_NEEDS: dict[str, tuple[str, ...]] = {
326 'arrestees': ('arrestees', 'administrative', 'agencies'),
327 'incidents': (
328 'administrative', 'offenses', 'victims', 'offenders', 'arrestees',
329 'agencies'),
330 'offenders': ('offenders', 'administrative', 'agencies'),
331 'offenses': ('offenses', 'administrative', 'agencies'),
332 'victims': ('victims', 'administrative', 'offenders', 'agencies')}
335""" Public Functions """
338def build(
339 table: str,
340 years: Any,
341 *,
342 states: Sequence[str] | str | None = None,
343 folder: Any = None,
344 decode: bool = True,
345 label: str | None = None,
346 task: str | None = None,
347 groups: Sequence[str] | str | None = None,
348 **kwargs: Any) -> amos.Dataset:
349 """Loads the tables that a builder needs and builds its table.
351 Args:
352 table: the name of a builder in `BUILDERS` ("incidents", "victims",
353 "offenses", "offenders", or "arrestees").
354 years: a year, years, or a range of years (see
355 `crimepy.utilities.to_years`).
356 states: postal abbreviations or numeric state codes of the states to
357 keep. Defaults to `None`, which keeps every state.
358 folder: the data folder. Defaults to `None`, in which case it is
359 `crimepy.utilities.data_folder()`.
360 decode: whether to replace codes with their labels. Defaults to
361 `True`.
362 label: the column that models predict (such as "cleared").
363 Defaults to `None`.
364 task: "classify" or "regress". Defaults to `None`, in which case
365 `amos` infers it from the label.
366 groups: columns that identify groups of rows (such as
367 "agency_ori"). Defaults to `None`.
368 **kwargs: arguments for the builder (such as `categories` for
369 `incidents`).
371 Raises:
372 KeyError: if `table` is not a builder.
374 Returns:
375 A dataset whose `data` is the built table.
377 """
378 if table not in BUILDERS:
379 message = (
380 f'{table!r} is not a table to build: use one of '
381 f'{sorted(BUILDERS)}')
382 raise KeyError(message)
383 segments = {
384 name: store.load_frame(
385 name, years, states = states, folder = folder, decode = decode)
386 for name in _NEEDS[table]}
387 return BUILDERS[table](
388 segments, label = label, task = task, groups = groups, **kwargs)
391""" Private Functions """
394def _join_agencies(
395 data: pd.DataFrame,
396 agencies: pd.DataFrame | None) -> pd.DataFrame:
397 """Joins the columns of agencies to records by their batch header's ORI.
399 Args:
400 data: records with "data_year" and "agency_ori" (or "ori").
401 agencies: the table of agencies, or `None`.
403 Returns:
404 `data`, with the agency's columns (see `AGENCY_COLUMNS`).
406 """
407 if agencies is None: 407 ↛ 408line 407 didn't jump to line 408 because the condition on line 407 was never true
408 return data
409 present = {k: v for k, v in AGENCY_COLUMNS.items() if k in agencies.columns}
410 table = agencies[['data_year', 'ori', *present]].rename(
411 columns = {'ori': '_agency', **present})
412 table = table.drop_duplicates(['data_year', '_agency'])
413 key = 'agency_ori' if 'agency_ori' in data.columns else 'ori'
414 existing = [c for c in present.values() if c in data.columns]
415 joined = data.drop(columns = existing).merge(
416 table, left_on = ['data_year', key],
417 right_on = ['data_year', '_agency'], how = 'left')
418 return joined.drop(columns = '_agency')
421def _join_counts(
422 data: pd.DataFrame,
423 table: pd.DataFrame,
424 column: str) -> pd.DataFrame:
425 """Joins the number of rows of `table` for each incident.
427 Args:
428 data: one row for each incident.
429 table: rows of incidents (such as their victims).
430 column: name of the column of counts.
432 Returns:
433 `data`, with the counts (0 for incidents with no rows).
435 """
436 counts = table.groupby(KEYS, observed = True).size().rename(column)
437 joined = data.merge(counts.reset_index(), on = KEYS, how = 'left')
438 joined[column] = joined[column].fillna(0).astype('Int64')
439 return joined
442def _join_offense_summary(
443 data: pd.DataFrame,
444 table: pd.DataFrame,
445 categories: bool) -> pd.DataFrame:
446 """Joins what the offenses of each incident were.
448 Args:
449 data: one row for each incident.
450 table: the offenses.
451 categories: whether to add a column for each category of offense.
453 Returns:
454 `data`, with "offense_count", "against_person", "against_property",
455 "against_society", "firearm", and (if `categories`) a column for
456 each category of offense.
458 """
459 data = _join_counts(data, table, 'offense_count')
460 flags = pd.DataFrame(table[KEYS])
461 against = table['crime_against'].astype('object')
462 for target in ('Person', 'Property', 'Society'):
463 flags[f'against_{target.lower()}'] = (against == target).to_numpy()
464 weapons = [c for c in table.columns if re.fullmatch(r'weapon_\d', str(c))]
465 firearm = np.zeros(len(table), dtype = bool)
466 for column in weapons:
467 firearm |= table[column].astype('object').isin(_FIREARMS).to_numpy()
468 flags['firearm'] = firearm
469 if categories and 'offense_category' in table.columns: 469 ↛ 473line 469 didn't jump to line 473 because the condition on line 469 was always true
470 category = table['offense_category'].astype('object')
471 for name in sorted(category.dropna().unique()):
472 flags[f'has_{_snake(name)}'] = (category == name).to_numpy()
473 summary = flags.groupby(KEYS, observed = True).any()
474 joined = data.merge(summary.reset_index(), on = KEYS, how = 'left')
475 for column in summary.columns:
476 joined[column] = joined[column].astype('boolean').fillna(False)
477 return joined
480def _require(segments: Mapping[str, pd.DataFrame], name: str) -> pd.DataFrame:
481 """Returns the table `name` of `segments`.
483 Args:
484 segments: tables, by name.
485 name: the name of the table that is needed.
487 Raises:
488 KeyError: if there is no such table.
490 Returns:
491 The table.
493 """
494 if name not in segments or segments[name] is None:
495 message = f'the {name!r} table is needed'
496 raise KeyError(message)
497 return segments[name]
500def _snake(text: str) -> str:
501 """Returns `text` in snake case.
503 Args:
504 text: a name, such as "Assault Offenses".
506 Returns:
507 The name in lower case with underscores between its words, such as
508 "assault_offenses".
510 """
511 words = re.sub(r'[^0-9a-zA-Z]+', '_', text).strip('_')
512 return words.lower()
515def _with_incident(
516 data: pd.DataFrame,
517 segments: Mapping[str, pd.DataFrame]) -> pd.DataFrame:
518 """Returns records with the columns of their incident and agency.
520 Args:
521 data: records of incidents (such as their victims).
522 segments: the tables of the incidents, by name. "administrative" and
523 "agencies" are used if they are there.
525 Returns:
526 The records, with the incident's columns (see `_INCIDENT_COLUMNS`)
527 and the agency's columns.
529 """
530 data = data.copy()
531 administrative = segments.get('administrative')
532 if administrative is not None: 532 ↛ 538line 532 didn't jump to line 538 because the condition on line 532 was always true
533 columns = [
534 c for c in _INCIDENT_COLUMNS
535 if c in administrative.columns and c not in data.columns]
536 incident = administrative[[*KEYS, *columns]].drop_duplicates(KEYS)
537 data = data.merge(incident, on = KEYS, how = 'left')
538 return _join_agencies(data, segments.get('agencies'))