/federal-firearms-licensees

This project organizes the ATF's Listing of Federal Firearms Licensees for easy analysis and visualization.

Primary LanguagePython

Analyzing Federal Firearms Licensee Data

Basic Analysis

Question

Which 5 U.S. states have the most federal firearms licensees?

Query
select count(PremiseState), PremiseState from licensees group by PremiseState order by count(PremiseState) Desc limit 5;
Response
7253|TX
3691|FL
3005|CA
3005|PA
2951|OH

Question

Which 5 U.S. cities have the most federal firearms licensees?

Query
select count(*), PremiseCity, PremiseState from licensees group by PremiseCity, PremiseState order by count(*) Desc limit 5;
Response
391|HOUSTON|TX
282|PHOENIX|AZ
217|TUCSON|AZ
202|SAN ANTONIO|TX
173|COLORADO SPRINGS|CO

Question

Which town in North Dakota has the most federal firearms licensees?

Query
select count(*), PremiseCity, PremiseState from licensees where PremiseState = "ND" group by PremiseCity, PremiseState order by count(*) Desc limit 1;
Response
41|BISMARCK|ND

Question

Which 3 mailing addresses have registered the most federal firearms licenses?

Query
select count(*), MailStreet, MailCity, MailState from licensees group by MailStreet, MailCity, MailState order by count(*) Desc limit 3;
Response
1264|702 SW 8TH ST|BENTONVILLE|AR
632|702 SW 8TH ST DEPT 8916|BENTONVILLE|AR
341|PO BOX 92088|LOS ANGELES|CA

(Walmart, Walmart and Big 5 Sporting Goods)

Question

Which 3 federal firearms license types are the most issued?

Query
select count(*), LicType from licensees group by LicType order by count(*) Desc limit 3;
Response
55521|01
10076|07
8148|02

(Dealer/Gunsmith, Manufacturer, Pawnbroker)

Workflow

  • Create a SQLite database with a licensees table
touch dbFFLicensees.sqlite
sqlite3 dbFFLicensees.sqlite
sqlite3 dbFFLicensees.sqlite <createTable.txt
  • Parse the txt file and load each row into the SQLite database
python csv2sqlite.py
  • Explore the data with SQLite queries

To Do

  • Geocode each licensee using the Premise address attributes
  • Export the geocoded results as GeoJSON for visualization

Data

LicRegn
LicDist
LicCnty
LicType
LicXprdte
LicSeqn
LicenseName
BusinessName
PremiseStreet
PremiseCity
PremiseState
PremiseZipCode
MailStreet
MailCity
MailState
MailZipCode
VoicePhone