This week, we looked at data from the Atlas of Surveillance (https://atlasofsurveillance.org) from the Electronic Frontier Foundation (https://eff.org), an organization that has long thought about (and fought for) online civil rights.
This database was brought to my attention in John Oliver's most recent episode of Last Week Tonight (https://www.youtube.com/watch?v=lnBPhelCdWE). Oliver pointed to the numerous technologies being employed by government authorities in the United States, and the ways that those technologies are being used.
As a data person, I immediately went to the Atlas of Surveillance site, and was delighted to discover that the database is downloadable. This week, we'll thus look at surveillance technology in the US – who is using it, and what they're using.
Data and six questions
The data, as I mentioned above, is from the EFF's Atlas of Surveillance site at https://www.atlasofsurveillance.org/ . Clicking on the "download this dataset" button gives you the link https://www.atlasofsurveillance.org/download.csv, which then downloads a CSV version of the database. The database appears to be getting updates on a regular basis, so your results might well differ from mine. I'm going to use the version from August 5th, 2026.
Paid subscribers, both to Bamboo Weekly and to my LernerPython+data membership program (https://LernerPython.com) get all of the questions and answers, as well as downloadable data files, downloadable versions of my notebooks, one-click access to my notebooks, and invitations to monthly office hours.
Learning goals for this week include cleaning data, working with dates and times, joins, and plotting with Plotly.
Here are my solutions and explanations for this week's questions:
Create a Pandas data frame based on the file. Any columns containing dates (all of which have the word "date" in them) should be turned into datetime columns. (Do the best you can with the admittedly inconsistent date formats.) Remove any columns that contain only NaN values.
I started by loading up Pandas and Plotly:
import pandas as pd
from plotly import express as pxI then wanted to use read_csv to load the CSV file into a Pandas data frame. I wanted to indicate that the date columns should all be parsed, but there were two issues: First, the dates were in very mixed formats, and second, some of them were in illegal formats. I could have solved the first problem by passing date_format='mixed', which evaluates each date and does the best job it can. But sadly, if there are illegal strings in there, then Pandas can't handle them, even with 'mixed'. So I started off with a simple call to read_csv:
filename = 'data/bw-182-surveillance.csv'
df = (
pd
.read_csv(filename)
)We can use the regular pd.to_datetime method to parse dates, though. So after loading the CSV, I used assign to create three new columns based on the existing date columns. Here, I invoked pd.to_datetime, passing format='mixed' and also errors='coerce'. This basically says, "Look, the dates are in lots of different formats, and they're a real mess. Do what you can with them, and if you can't figure it out for a date, then replace it with NaT, short for "not a time," the datetime version of NaN:
df = (
pd
.read_csv(filename)
.assign(link_1_date = lambda df_: pd.to_datetime(df_['Link 1 Date'], format='mixed', errors='coerce'),
link_2_date = lambda df_: pd.to_datetime(df_['Link 2 Date'], format='mixed', errors='coerce'),
link_3_date = lambda df_: pd.to_datetime(df_['Link 3 Date'], format='mixed', errors='coerce'))
)I then removed the original date columns, since I didn't need them any more. I invoked drop, passing the columns keyword argument, along with a list of strings, the column names I wanted to remove.
But then I wanted to remove the columns that contained only NaN values. How could I do that? The most obvious method, dropna, normally removes rows, and considers any row with even one NaN to be a problem, and gets rid of it. You can set the thresh keyword argument to indicate the number of good values needed to keep the column around, but that seemed roundabout.
The real answer is to use the how keyword argument, and specifically how='all', which means that if 100% of the values are NaN, then it should be removed. And to indicate that this should happen to columns, rather than rows, I passed axis='columns'.
The full query:
df = (
pd
.read_csv(filename)
.assign(link_1_date = lambda df_: pd.to_datetime(df_['Link 1 Date'], format='mixed', errors='coerce'),
link_2_date = lambda df_: pd.to_datetime(df_['Link 2 Date'], format='mixed', errors='coerce'),
link_3_date = lambda df_: pd.to_datetime(df_['Link 3 Date'], format='mixed', errors='coerce'))
.drop(columns=['Link 1 Date', 'Link 2 Date', 'Link 3 Date'])
.dropna(axis='columns', how='all')
)This gave me a data frame with 15,150 rows and 20 columns.
What cities and states have the most reported purchases of surveillance equipment over the years? For each of the top five, what agencies have been making purchases, and what did they buy? Are only police departments making these purchases?
To find the cities and states with the greatest number of reported purchases, we'll use groupby.
The thing is, if we just use groupby on cities, then we might well end up with repeated values, for any city names that are in more than one state. We will thus want to group by both State and City, invoking the count method.
My goal wasn't just to find the top five cities (and their states), but also to use this in an additional query (as you'll soon see). I thus passed the as_index=False keyword argument, which kept State and City as regular columns, instead of using them as an index. This meant that the result of the groupby was a data frame, not a series:
(
df
.groupby(['State', 'City'], as_index=False)['AOSNUMBER'].count()
)I was only interested in finding the five cities and states with the greatest number of reported purchases. For that, I used nlargest . Because we're invoking nlargest on a data frame, we need to indicate which column we're using for our measurement; in this case, I used AOSNUMBER:
(
df
.groupby(['State', 'City'], as_index=False)['AOSNUMBER'].count()
.nlargest(5, columns='AOSNUMBER')
)This returned the five cities with the greatest total number of reports:
State City AOSNUMBER
1143 FL Tallahassee 32
449 CA Los Angeles 30
1188 GA Atlanta 30
6370 TX Houston 29
551 CA San Diego 26
I originally expected to use a pivot table here, but ended up using groupby, instead. So... my apologies for originally saying that pivot tables were included in the learning goals.
But how do we find out which agencies were making these purchases, and what they bought? We basically want to find all of the rows in df for these cities and states. I decided to do this by taking the data frame we got as a result of this query, and merging it with the original data frame.
You might already be familiar with join, which works similarly to the SQL command of the same name. merge does the same thing, but doesn't require that the matching column(s) be in the data frames' indexes.
So I can basically take the data frame I got back from this query, and invoke merge, telling it that I want to match the State and City columns on both the left and the right. Note that the default is to do a "left join," meaning that rows on the left determine what rows are joined on the right. Here's my query:
(
df
.groupby(['State', 'City'], as_index=False)['AOSNUMBER'].count()
.nlargest(5, columns='AOSNUMBER')
).merge(df,
on=['State', 'City'],
)The result was a data frame with 147 rows and 21 columns – a bit too much to show here. Where the column names overlapped from the left and right, we got suffixes to ensure that they have different names.
So... now what? Well, we can see what kinds of agencies have been buying surveillance equipment with value_counts:
(
df
.groupby(['State', 'City'], as_index=False)['AOSNUMBER'].count()
.nlargest(5, columns='AOSNUMBER')
).merge(df,
on=['State', 'City'],
)['Type of LEA'].value_counts()Here's the list:
Type of LEA count
Police 85
Sheriff 29
State Police/Highway Patrol 12
Fusion Center 4
Customs and Border Protection 3
District Attorney 2
Probation/Parole 2
Parking Enforcement 2
DMV 1
Fish and Game 1
Attorney General 1
Fish and Wildlife 1
Park Rangers 1
Transit Police 1
Marshal 1
Constables 1
It makes sense that police and sheriffs have been buying these tools. But we also see district attorneys, parking enforcement, and the DMV. Also, we see "Fish and Game" separate from "Fish and Wildlife," which points to the trouble we can get into with text vs. pre-set categories.
What are these places buying? We can check that, too:
(
df
.groupby(['State', 'City'], as_index=False)['AOSNUMBER'].count()
.nlargest(5, columns='AOSNUMBER')
).merge(df,
on=['State', 'City'],
)['Technology'].value_counts()Here, we get:
Technology count
Body-worn Cameras 33
Face Recognition 26
Automated License Plate Readers 24
Third-party Investigative Platforms 20
Drones 12
Real-Time Crime Center 8
Cell-site Simulator 5
Camera Registry 5
Fusion Center 4
Gunshot Detection 4
Video Analytics 3
Predictive Policing 3
So, lots of body cameras (to be expected, especially for US law enforcement nowadays), but a ton of face recognition, license-plate readers, and drones, as well as gunshot detection and predictive policing, both of which were mentioned on Last Week Tonight.