Parsing MS Word Tables with Python

This is going to be a brief explainer of parsing tables in a docx file using python. I’m going to be using python-docx to do this. You can download the test file I’m using here (OneDrive link).

First, why am I doing this, and why would you want to do this? I’m working with some ~40 odd documents built off standard templates, each of which is 20-30 pages long, and contains around 300 data points about different buildings. Currently, this data lives these documents, which are updated annually. I would like for this data to live in a database, and to automate the annual update process by pushing the data from the database to merge fields in MS Word using python as an intermediary between SQL & MS Word (I’ll discuss this at a later point).

For the data to end up in a normalized format in a database, I need a way to strip it out of the existing word documents; it’d obviously be preferable to not have to do this manually. So, I went in search of a way to parse docx files using python, and found python-docx.

I quickly ran into something of a snag in that, most of the information online for getting data out of docx files was focused on working with paragraphs. My documents are almost entirely based around tables, not paragraphs. Working with tables is somewhat more challenging than paragraphs, but, it does end up having advantages for what I need to do.

To access paragraphs, you refer to document.paragraphs which returns a list of paragraph objects in the document. Each object then has further objects, one of which is text, so document.paragraphs[0].text will return the text of the first paragraph.

Tables are similar, but with more layers. Table has no object text, but instead has rows and columns, among other things. Rows and columns then have an object cells, and cells have an object text, which is how you access the text of your table. To get the text of the first cell in the first row of the first table you have to go to document.tables[0].rows[0].cells[0].text. The same can be done using …columns[0]… in place of rows. Using this you can get data out of tables into a data structure that makes sense for what you’re doing.

The solution I working with right now is to use the tables>rows>cells as a 3 point coordinate structure (cells are indexed left to right when accessed via rows). I constructed a series of lists containing the coordinates of the data I’m looking for, then wrapped those lists into a list of lists.

	from docx import *

	filename = 'demo_file.docx'

	try:
	document = Document(filename)
	except:
	f = open(filename, 'rb')
	document = Document(f)
	f.close()

	data1 = [0,0,0]
	data2 = [0,0,1]
	data3 = [0,1,1]
	data4 = [0,1,0]

	data_wrapper = [data1, data2, data3, data4]

	for data in data_wrapper:
	table = document.tables[data[0]]
	row = table.rows[data[1]]
	cell = row.cells[data[2]]
	text = cell.text
	print(text)

view raw parsingtables.py hosted with ❤ by GitHub

By iterating through data_wrapper and swapping out the coordinates, Im able to access all the data I need. For the purpose of this demonstration we’re just printing the text of each cell, as I take further steps in this project I’ll likely be putting data into a dictionary to be referenced by a key that will correspond with how it’ll be stored in SQL and then accessed by python doing the mail-merge down the line.

Since my documents are all built off one standard template, I should (emphasis on should) be able to do the coordinate mapping manually once and then use it across the entire document library to pull all the data in in one fell swoop.

My next task is parsing content control fields, which a lot of the data stored in these documents is contained within. Python-docx doesn’t seem read these fields as text, so I have to tinker a bit to figure out how to make everything happy.

Some resources the got me here:

This Stack Overflow answer, which does something a little different (if more robust) extracting data from a table
Python-docx API entry on tables
Automate the Boring Stuff’s intro to working with docx in python

Posted on March 27, 2019March 27, 2019 by B. Illis 0

A little bit of SQL to do something nobody needs to do

I wrote a little SQL snippet this week to fulfill a very niche use case, which I decided to share.

I administer a small database at work that holds departmental data. We use it to generate various reports and office automation. At a basic level, it feeds data into several spreadsheets. The database has the advantage of being harder to break than the half dozen interconnected spreadsheets that preceded it.

I’ve been working recently on expanding the functionality outwards. One of the things I wanted to do is be able to automate the creation of some internal documents directly from the database.

I’m going to do that by using MS Word’s MailMerge function and a little python script. But first, I needed to store the relevant data in the database (it currently resides in word documents). The documents I want to automate are called “Building Emergency Plans” which contain critical information about each of our building.

I created an SQL table to hold that data. It’s wide, over 300 columns, for each data point contained in our BEPs. From here what I wanted to do is test the functionality of the MailMerge and python (and make sure I had put the merge fields in the right spots in the template). So I needed some sample data.

But I didn’t want random strings, numbers, or even real data from one of our sites yet. I decided that the easiest way to test the merge would be to have the unique column names for each data point inserted into the table as data. That way for the column that should hold the location of the second elevator’s machine room (Elevator2_MachRm) it would pass Elevator2_MachRm through to Word.

Due to the size of the table, I didn’t want to do this manually, which is how I ended up writing this loop, which completed the process automatically.

	–This SQL block loops through all the columns in a table and sets the that data in the first record equal to the column name
	–If your table is empty, you need to insert one record first
	–All of your columns need to be set a to a datatype that will except the column name (varchar, nvarchar, etc)

	DECLARE @loopcount int, @column int
	SET @loopcount = 1
	SET @column = (SELECT COUNT(*)
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE table_catalog = 'DatabaseName' –replace DatabaseName with your database name
	AND table_name = 'TableName' –replace TableName with your table name
	)
	WHILE @loopcount <= @column
	BEGIN
	DECLARE @query nvarchar(max), @cursor_col nchar(50), @data nvarchar(max)
	SET @cursor_col = (SELECT COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_CATALOG = 'DatabaseName' –replace DatabaseName with your database name
	AND TABLE_NAME = 'TableName' –replace TableName with your table name
	AND ORDINAL_POSITION = @loopcount
	)
	SET @data = ''''+@cursor_col+''''
	SET @query =
	'UPDATE TableName
	SET '+convert(varchar,@cursor_col)+' = '+@data+'
	WHERE PrimaryKey = 1' –set PrimaryKey as the Column holding the primary key and change 1 to the record you want to hold the column names
	SET @loopcount = @loopcount + 1
	EXEC(@query)
	END

view raw InstertColumnNamesAsData.sql hosted with ❤ by GitHub

I found this was such a niche thing to want to do that there was no cookie-cutter solution available on a forum (which isn’t the case for most of the things I do in SQL).

The first this we do in lines 7 – 11 is query the Information Schema to get the total number of columns in the table we’re working on, and assign @column as that number.

On line 12, the loop begins, and we tell SQL to continue looping as long as the counter is less than or equal to the total number of columns.

Skipping up to line 15, we designate the cursor column (@cursor_col), or the specific column in the table that we’re working on in each iteration of the loop. We do this by again querying the Information Schema, and ask it to select the column name in our table where the ordinal position is equal to our counter (@loopcount). To give you an idea of what we’re working with, this is what the output looks like if I query all the column names and ordinal positions.

Using the loop count to select each column via its ordinal position works out nicely.

After selecting the column we’re working with, on line 21 we the set @data = @cursor_col, with the addition of single quotes on each side so that SQL will interpret it as a string.

The last bit (line 22 – 25) is the real meat and potatoes. We set @query to contain a text string which we’ll later interpret as an update statement. The update statement tells SQL to update the first record (where PrimaryKey = 1) so that @cursor_column is equal to @data.

We then increment the loop count by 1 on line 26, and on line 27 tell SQL execute the string in @query. The loop then repeats until the condition on line 12 no longer applies.

Below is the before and after:

There are of course some disclaimers to throw in. First being, as various websites showing me how to do bits of this warn, this is dynamic SQL code, which is generally considered a security vulnerability, so use at your own risk. That said this use case doesn’t pose the same kind of risks typically associated with dynamic SQL.

Second, this will only work if all the columns in your table are of a datatype that will accept the name of the column, so in my case, varchar, nvarchar, etc. If you have columns of a different datatype you could do various workarounds with case statements or filter based on attributes from the information schema so it won’t throw an error.

Third, this will only work if you have a row already in the table. This is a new table so I manually inserted one row to hold the same data.

Fourth, you should set @loopcount equal to the ordinal position of the column you want to start on, when I executed this I started on the third column as the first two in my table contain keys.

Fifth, the single quotes for the strings are very tricky, and an enormous pain to debug if you mess them up. The easiest way to use them is to (if it doesn’t already) set your IDE / code editor to give you visual cues for what is falling inside vs. outside the single quotes as it’ll be interpreted by SQL.

Posted on February 27, 2019February 27, 2019 by B. Illis 0

You’re Wrong About Narcan

Some background: there are programs the give away free narcan (generic name naloxone) to the public. Naloxone is a drug which temporarily reverses the effects of an opioid overdose.

This seems like a good thing right? Get a life saving drug into the hands of people who need? Wrong. There has been near constant debate about this issue for years now. Some people (I hope merely a vocal minority) hate this idea. Lest you think I’m straw-manning you, here are some real Facebook statuses I found about narcan (excuse the spelling- these are copied verbatim)

Giving away free narcan so where do I pick up my free epi-pen that Ineed to save my life…. such BS
As a retired police officer I’m appalled at the use of tax dollars to supply narcan to drug users…
Yep, let’s shoot up, I got the free Narcan….tax payers footing the bill for people who have a blatant disregard for their own lives…
This is a JOKE to me! Free Narcan and I’m struggling to figure out how to get insulin cause I’m type1 diabetic!!!!!! I kno I’m goin to catch flack for this but ppl CHOOSE to do drugs, I didnt CHOOSE to be a diabetic!!!!

I have been involved in several arguments about naloxone now. I think people who are criticizing these programs are incredibly wrong headed about the whole issue. What follows is my answers to the most common objections to giving away naloxone.

“We should implement a three-strikes system: three narcans then you die”

There is no practical way to implement a ‘3-strikes’ policy or any strikes policy. There’s no central database of such things, and even if there was, it wouldn’t be set up for anyone to quickly query “how many doses of naloxone has John Smith had in the past 12 months?”. Even if there were- most narcan administrations (including by PD or EMS) will happen before they even know the patient’s name- let alone DOB and social and input it into the MDT. In the case of an overdose, most of the initial care will be done prior to even knowing who the patient is. When you roll up to an unresponsive the first priority is not getting the patients social and typing it into the computer.

The transition to electronic medical records started over a decade ago and there’s still no reliable way to transmit records between different hospital systems, ems agencies, etc in a single county let alone a state. When you move a patient between healthcare facilities the most common way of moving the records is sending them with a folder full of paper copies. Further police records are on an entirely different system than any medical records.

“But I don’t think it’s fair that addicts are getting free Narcan”

Naloxone is not and was never “free” if it’s administered in a hospital or by EMS. The cost of naloxone is billed to the patient’s insurance the same as any other medicine or treatment. The “free” naloxone is either naloxone that’s being given away to the public as part of a public outreach program or sometimes naloxone that’s administered by a police officer.

“Why don’t we give away epi pens for free? Or insulin for diabetics? Or Chemo?”

First, people with epi pens and people who are insulin dependent self-administer. This means that the only person who needs to have an epi-pen or insulin syringe is them. Narcan is different. It can’t be self-administered because someone who is having an OD is by definition not going to be able to administer Narcan on themselves. So an effective public access Narcan program needs to saturate society with people who have Narcan, in hopes that during and overdose somebody near or the first first responder on scene will have naloxone and will act.

Second, Narcan is more or less harmless. If you administered it to me now, it wouldn’t do anything. You can however hurt or even kill someone with insulin or epinephrine. So you can’t just hand out epi-pens willy-nilly. It makes more sense to think of Narcan as like an AED for overdoses, as opposed to like insulin.

But cost cost cost the refrain continues. Can we really afford to save all these people? As it happens, the answer is yes. Naloxone as a drug is so cheap it’s basically free. Below is a table comparing the cost of naloxone to Humalog (a common insulin brand) and Epi-Pens.

Drug	Price	Source
Humalog Pen	$34/ml	Medicaid (NADAC)
Naloxone Syringe	$15/ml	Medicaid (NADAC)
Epi-Pen	$143/ea	Medicaid (NADAC)
Naloxone Nasal Spray	$63/ea	Medicaid (NADAC)

So why not epi-pens? (a) Epi is 2x-3x more expensive than narcan. (b) there also isn’t an epidemic of people with severe allergies dying because they don’t have epi pens. (c) epinephrine incorrectly administered can hurt or kill people. (d) epi-pen users can self-administer. (d) Many police officers also carry epi pens in their cruisers (and all ambulances do).

Why not insulin? (a) insulin is 2x as expensive as Narcan. (b) there isn’t an epidemic of diabetics dying from lack of insulin. (c) the dosage on insulin is important, so you can’t just hand out standard doses to people. (d) insulin users can self-administer. (e) insulin is not an emergency medication. People who are dependent on insulin need multiple doses on a regular basis in perpetuity.

And why not chemo? Well that’s such a dumb comparison I’m not even going to address it. But you could start by googling “average cost of chemo” and then comparing that number to the prices above.

“Yeah but still. Narcan is expensive, and these addicts did this to themselves”

The option you seem to be suggesting as an alternative to narcan administration is “everyone stands around and watches the overdose victim die then calls the coroner.” This is just never going to happen, not least of all it would be illegal for EMS providers to do that, and grossly immoral for anyone to do that. You could I suppose refuse to give police or the public narcan, but you don’t technically need naloxone to treat an overdose. Overdoses can often be treated by breathing for the victim for several hours until the drugs they took wear off. The killer during overdoses is respiratory arrest followed by cardiac arrest followed by death.

Since most police officers and bystanders are neither equipped nor trained to deliver effective rescue breaths, in many (most?) cases the victim will have stopped breathing, their heart will have stopped, and their brain will be being slowly choked of oxygen by the time the ambulance arrives. This means that the paramedics and EMTs will be walking into a full blown code.

So, what does working a code mean? Upon arrival, the EMS team will start doing CPR. While CPR is happening one of the members of the team will attach a set of EKG leads ($20) and a pair of defibrillation pads ($60-100). They will establish a line using an I/O needle ($90). At the very least push epinephrine ($40) and normal saline ($30). This will all be done whether the patient lives or dies.

And, if they live, what then? This would likely mean that upon arriving at the hospital the they would need to be placed on a mechanical ventilator (retailing for $5,000-10,000+) and be sent to the ICU ($600-1,500/day ). In 2015 the average total cost of an overdose admitted to the ICU was $92,408 .

This could have all been prevented by a police officer or member of the public administering a $60 dose of narcan before they went into full arrest. Some quick math tells me that for everyone OD narcan prevents from being sent to the ICU, we could buy about 1,600 more doses of naloxone. Seems like a stupid good deal for the taxpayers and the healthcare system to me.

Narcan is also the cheapest option for dealing with an overdose. Well, not the cheapest. The strictly cheapest option would be to let the victim die on the ground while everyone watched. But in limiting ourselves to options which are not grossly immoral and callous, Narcan is the most cost effective option. The cost of the pre-hospital supplies alone $230. This is 1500% more expensive than delivering a single syringe of naloxone, or 380% more expensive than delivering on nasal spray of naloxone. That’s the costs before the patient is even admitted to the hospital.

“But a lot of addicts don’t have insurance and won’t pay their bills.”

Correct, many victims of overdoses are people who do not have any kind of insurance or the money to pay out of pocket for expensive medical treatment. So a large portion of the cost of treating overdoses is going to be eaten by the agencies/hospitals who provide that care. We, society as a whole, are at the end of the day eating the cost of treating many overdoses.

From a purely economic perspective, would you rather pay for:

A single syringe of naloxone that costs $15 which will likely save a person’s life and give them the opportunity to fight their addiction or
Thousands of dollars of pre-hospital care and tens of thousands of dollars of in-hospital care, where the patient may come out with brain damage or not come out at all.

“But I heard my police department is having trouble paying for Narcan.”

Yes. To the extent the there is a legitimate cost problem, it’s at the police department level. Police departments, especially small departments, may have trouble paying for naloxone kits & training for their officers. Across a small department serving a jurisdiction with a lot of overdose cases, the costs can certainly add up very quickly. Since police generally cannot bill (charge insurers) for medical care they provide, the town or city government has to pay all the costs of the naloxone.

But this is not a reason to not give police and the public naloxone. This is a reason to develop some mechanism by which police agencies and non-profits can share in the immense society benefits of police having naloxone. If police administering a $60 nasal spray when they arrive at an overdose is potentially preventing nearly $100,000 in hospital and pre-hospital medical costs per patient and hundreds or thousands of deaths, then it would seem to be in everyone’s interest to make sure all police officers have as much naloxone as the need. By ‘everyone’ I mean- hospitals, EMS agencies, insurance companies, Medicare, Medicaid, and then just all of us in the public.

If the hurdle is getting the necessary money to police departments and non-profits to purchase naloxone, then we can undoubtedly find a way to get either the naloxone or the money for naloxone to them. Maybe this means hospitals purchase naloxone in bulk and donate it to the police department(s) which serve the area they’re in. Maybe it means some cost sharing agreement where EMS agencies and hospitals will reimburse police agencies for doses that their officers deliver on scene. Maybe it means city governments move money from a public health, homeless assistance, or EMS agency fund to purchase naloxone for police officers, because police officers having naloxone will serve the purpose of those other funds.
But the “problem” is not being able to afford to get naloxone to police officer, the “problem” is connecting the wires across a complex society so that enough of the million of dollars in cost savings from naloxone end up getting naloxone to the people who need it.

“Yeah but even if they’re saved- there are still other costs at the hospital, and the success rates on resuscitation attempts on ODs is so low.”

Yes. The success rate on all resuscitation attempts are very low (though improving)- but the expenditure of EMS resources is going to happen in the process of the attempt. If they make it to the hospital, there will be more costs, and admitted to the ICU more costs, all regardless of what the outcome is. The total cost to the healthcare system will always be cheaper if there was an early narcan administration, just like the cost is always cheaper on a cardiac arrest if there was early CPR & defibrillation.

If there’s early administration and they’re conscious: they spend the night on a naloxone drip and can be discharged. Unless the victim is cold; there’s almost always going to be a resuscitation attempt on-scene- which is still more expensive than naloxone.

“Right, Drug abusers are already very expensive for society- the cost of incarcerating, the cost of crime- even without the medical costs should we really be paying for Narcan when addicts already cost so much money?”

A good first step if you’re worried about cost would be not incarcerating them in the first place. That’d save a lot of money right there. But that’s controversial. There are also options which would eliminate the need for addicts to commit crimes to either get their drugs or get money for their drugs, but those, likewise, are controversial, since they involve giving drugs or money for drugs to addicts. Starting with something cheap and non-controversial is a good place to start.

“Okay, but what we really need are treatment programs- people to come out of the shadows and get help- giving away free Narcan is just enabling them.”

About 85% of opioid addicts will relapse at least once in the process of getting clean . Due to the fact that a user’s opioid tolerance is a moving target (especially if you’re trying to kick it- your tolerance will change significantly) and the strength of available street drugs is likewise a moving target, the people who are most at risk for ODs are those who are trying to get clean and relapse. Daily users will be much more familiar with their tolerance and the strength of whatever they’re buying on the street than someone who was clean for eight months than slipped. So, there’s no practical or pragmatic justification for being opposed to narcan access programs. You’re going to end up letting a lot of people who are trying to get clean die.

If you want to stick with the “they made their choices” line you are going to be doing that at:

Great financial cost to society,
The cost of less people getting clean and surviving their addiction, and finally
The great moral cost of the tens of thousands of dead who could have been saved with a $60 medication (which in terms of medications, is so cheap it’s basically free)

“Yeah but come on- if you OD three times, that’s your fault at that point, I mean three times? Come on.”

An overdose is often the impetus for someone to seek treatment in the first place. So if they don’t survive their first overdose, they’re not going to get treatment. If they don’t survive a second overdose when they relapse, then they’re never going to complete their treatment program.

And what if someone overdoses three, four, five, six, seven, ten times- but after the eleventh time they get clean for good? At which overdose have they used up their chances? Where do you draw that line, and why? How many doses of a $15 medication is their life worth? How many shots at rehab? If we can’t put a price on human life then why are we putting artificial limits on how many times someone can OD before they’re irredeemable?

ODs are usually freak accidents, your dealer gave you fentanyl which was cut incorrectly, or you pushed a little too much because you have the shakes and don’t know what you’re doing with a syringe.

There is no moral quality the differentiates someone who ODs because of an unhappy accident and someone popping oxy every day, only that the person popping oxy hasn’t OD’ed and they can afford oxy. If you use long enough it will happen to everyone.

“Yeah but Narcan isn’t a solution, at best it’s just a band-aid”

Nobody is saying it’s a solution. It’s the cheapest and most effective intervention to both save lives and contain the social costs of addition. It should be non-controversial. Cheap noncontroversial interventions are a good place to start.

Band-aid, but band-aid are good things. Especially when they prevent death. A multi-trillion dollar investment into residential treatment programs and research into addiction is a little bit harder to sell than spending a couple thousand dollars to give police Narcan.

“Yeah but…”

Look, I guess if you want to leave addicts out to dry that’s your prerogative. But there is no financial or pragmatic justification for this. In fact, your position is more expensive and less effective than naloxone programs. If your twisted moral worldview makes you think that addicts don’t deserve to live, then say that but stop trying to hide behind just trying to be ‘fiscally responsible’. Say that you don’t think someone who overdosed isn’t entitled to the same level of emergency care we offer every member of our society.

Posted on December 17, 2018December 18, 2018 by B. Illis 0

Is This a Real Plan or Just a Fantasy? Five Characteristics of Fantasy Documents

In a previous post, we spoke briefly about fantasy documents a term coined by Lee Clarke of Rutgers. In this post, I wanted to dive a little deeper into this concept. There are, I think, five characteristics[1] of a fantasy document as described by Clarke.

Characteristics of Fantasy Documents:

A fantasy document tells the reader a story rather than describing reality[2]
A fantasy document plans by simile[3]
A fantasy document calls for actions which have never been done successfully and/or are not seriously prepared for operationally[4]
A fantasy document (tries to) protect a system or organization from criticism and scrutiny[5]
A fantasy document never admits a risk is unassessable or uncontrollable, rather asserts that every risk is not only understood but controlled[6]

Fantasy documents follow a story-like structure. They read like a script for everyone to follow, cleanly denoting timelines, designating actions to be taken, communication lines to be established, supplies to be requisitioned, and actions to be taken. In all reality, a true plan at scale would have to work something like a choose your own adventure novel; except it would fill bookcases upon bookcases trying to account for every any eventuality or combination of events. These plans would swell to the size of the reality they inhabit, like Borges’ unconscionable maps, making them exact, but functionally useless.

Fantasy documents don’t take that multi-path procedural tact. Instead, they lay out a narrative. ‘In the case of [x], we will do [y], within [h] hours we will do [z], within [d] days we will do [a]. Himself a perpetuator of his own fantasy documents, nuclear strategist Herman Khan mocked early nuclear war plans of the US. He said in On Thermonuclear War that the Army’s plans to, immediately after a nuclear exchange, begin embarking state-side units to sail overseas and fight a land war with the enemy, were patently absurd.

Khan, likely rightly, observed that in the event of general nuclear war what was left of domestic military formations would be immediately engaged in reconstruction & keeping domestic order, they would not be embarking to fight a land war in Europe. But the story of sneak attack followed by an extended land & sea battle is a story policymakers and civilians alike were familiar with.

In service of the story, fantasy documents plan by simile. [x] event which we’ve previously dealt with is like [y] event which we’re trying to plan for. Nuclear war is like conventional war but with bigger bombs. A big oil spill is like a little oil spill[7], we just need more response vessels. The evacuation of Long Island during a nuclear reactor meltdown is like the daily Long Island – NYC commute. Responding to a radiological emergency is like responding to a fire[8]. They often presume that a small emergency response will linearly scale to a large one.

These similes often lead to fantasy documents specifying actions to be taken which, would be impossible or nearly impossible to undertake, and are never seriously prepared for operationally. One clear example is civil defense plans in America. One of the plot points in the stories told by civil defense planners was the evacuation of urban areas if the government believed nuclear war was imminent. This would, planners thought, serve two purposes, first to get civilians out of harm’s way and second to put the United States on more secure footing to fight a win a nuclear war.

Never mind that the total evacuation of New York is something that had never been done before. Never mind that the millions of evacuees could be easily seen and then targeted by bombers. Simply envision for a moment the logistical challenges of moving the entire population of New York City out of New York City. Then the challenges of housing them somewhere far enough from Manhattan to be safe from a nuclear attack. This alone would stretch the resources of the government to the breaking point. Now imagine doing that simultaneously with the 10 largest American metro areas under a condition of imminent nuclear war. It simply strains credulity that urban evacuation could have ever been considered a serious policy, but it nonetheless was.

Khan, a physicist by training, was frustrated nearly to the point of rage over what he saw as unrealistic and ineffectual civil & military planning in the early cold war. In one famous incident during a meeting in the Pentagon, Khan slammed the table saying in extreme frustration “Gentlemen, you do not have a war plan. You have a Wargasm!”[9] referring to the military’s doctrine at the time of massive retaliation. But by looking at the plans in Clarke’s framing as fantasy documents instead of Khan’s framing as ‘plans which should guide operations,’ they begin to make more sense.

The actual function of a fantasy document is often not to plan for anything, but rather, in part, to shield an organizations or institution from criticism. Oil spill containment plans assure the public & environmental groups that the oil company is in fact prepared to respond to, contain, and resolve even a massive spill. Civil defense plans assure the public that their government is ready and able to protect them in the case of a nuclear war. The story that nuclear war plans tell is that the government or the military stands ready an able to not only fight, but win a nuclear war.

As Clarke details at length, the United States government claimed to be able to protect 80% of the American population in the event of nuclear war, a number which, as near as could be determined, had no basis in reality[10]. It was stated in a single report and then repeated as fact for years.

Fantasy documents never admit an inability to control risk. The reality was, most likely, that the potential damage from a nuclear exchange was completely unbounded, and there was little to nothing the United States government could do to control that risk other than trying to prevent nuclear war to begin with through deterrence. The risks involved in a nuclear exchange were unassessable and uncontrollable.

The reality was, that in the event of a 200,000 barrel oil spill neither the oil company nor the government would have the ability to contain the oil or prevent ecological damage. The very act of shipping oil by supertanker had created an uncontrollable risk. How could planners admit that to themselves let alone the public at large? A fantasy document wouldn’t be fantasy if it didn’t purport the ability to control the uncontrollable.

Now that we have a good background on what a fantasy document is, next up we’ll talk about some uses of fantasy documents and why writing them may not be as bad as it seems.

Footnotes

[1] I should emphasize these are drawn from my reading Clarke’s work but this is not a set of criteria he explicitly lays out anywhere in his book. I have tried to include robust page references so that a reader can follow where I’m drawing from.
[2] Clarke, Lee. Mission Improbable: Using Fantasy Document to Tame Disaster. 16
[3] Clarke. 74
[4] Clarke. 39; Clarke p.78
[5] Clarke. 41
[6] Clarke. 142
[7] Clarke. 78
[8] Clarke. 89
[9] Kaplan, Fred. The Wizards of Armageddon. 222-223
[10] Clarke. 35-38

Posted on October 9, 2018February 7, 2019 by B. Illis 0

Emergency Planning: Foresight or Folly?

Anyone who has ever filled an admin role in emergency services or emergency management is familiar with the veritable binders of plans, procedures, and guidelines for obscure & unlikely events that exist in most organizations.

My own experience is that more often than not when one of those binders gets pulled off the shelf in the midst of an emergency, what is contained in those pages is usually not particularly helpful, and often gets ignored entirely.

It is remarkably unhelpful in that often in organizations I’ve worked for, the only people who’ve read those plans are the people who wrote them heavens knows when and the people responsible for updating them every x many years. If the operations personnel, the people on the ground doing the response, have never seen the organization’s plan, one might wonder what the purpose of having the plans at all is.

However, maybe the purpose of these plans was never to guide operations, but they served another purpose. Shedding light on this question, J. Anderson writes in the Journal of Homeland Security Affairs;

These are what Lee Clarke has called “fantasy documents,” that is, documents that do not actually guide operations, but rather serve as reassurances that the organization has taken the problem seriously and stands ready to deliver… Schemes of prediction and preparation fall short of reality. Reflecting on the response to Hurricane Sandy, FEMA Administrator Fugate wrote, “We still plan for what we are capable of doing. We still train and exercise for what we can manage. We must plan, train, and exercise even bigger to fracture the traditional mindset.[1]

In my current job, where I do in-house emergency planning for a private organization, I think about this a lot. What exactly is the purpose of these documents I’m writing? Do they serve an operational purpose? Are they primarily for a regulatory or legal compliance? Are they merely an insurance policy to point to amorphous plans when asked by stakeholders or the public how we will respond to one thing or another? Are they an exercise in thinking through contingencies? A receipt that we have certain data on file?

I should add, I don’t think any of those functions are prima facie illegitimate pursuits. Though I do think that those of us commissioning, writing, and approving plans should have a clearheaded approach to what we’re doing. As such, the normative and descriptive functions of our plans (what the plans or documents should do vs. what the plans or documents actually do) is a topic which deserves some examination and discussion.

So given that, what should a plan be? Should we be writing much more detailed plans, trying to address every possible contingency with careful step by step procedures? Or should our plans be walked back to be much more general heuristics guiding response?

Is it legitimate for a plan to have little operational value and primarily be an exercise in thinking about possible contingencies and taking them seriously? Or does a plan need to guide operations?

Should we abandon the ‘planning’ enterprise entirely and instead direct resources towards excising & training the field staff in hopes that with a highly trained & experienced staff the spontaneous response will be better than any plan a schmuck like me can write parked behind a computer for weeks on end could be?

I don’t have full answers, but I have some ideas that may illuminate the planning process, which I intend to share over the coming weeks in (hopefully) bite-sized chunks as I have time to write about them. I’m hoping that by sharing some of what I’ve been thinking about, I can get some input from others with different perspectives & experiences.

[1] https://www.hsaj.org/articles/10661

Posted on October 4, 2018October 31, 2018 by B. Illis 1

Citizen App Review

BLUF: Citizen is an app that gives users real-time information on crimes & emergencies near them. The app is well designed for the average citizen but isn’t ideal for professionals in public safety or emergency response. Citizen’s information on incidents comes from scanner feeds.

About the Author: I am an Emergency Manager at a Manhattan-based University System. I have a professional background in EMS and an academic background in security policy.

Recently on twitter, I was made aware of an app called Citizen which provides real-time crime reports to users based on geo-location and allows them to stream live video of the incident. I’ll do a quick tour of the app and then share some thoughts.

Citizen is currently available in the New York and San Francisco metropolitan areas. The UI is slick and will feel familiar to users of Facebook & Instagram. On load, you see “Stories” across the top which are like small news packages Citizen produces summarizing previous events. Below that is the map, with red dots marking recent incidents, and in the lower half of the screen is a list of incidents.
Screenshot_2018-05-22-14-19-24

Pressing on an incident opens up a new screen with details. There is a timeline of the incident with updates and clarifications. There is also a set of four buttons, allowing you to react, share, contribute media, and ‘warn’ your contacts.

Starting from the left-hand side, React does more or less what you’d expect. Share opens up the OS sharing interface and allows you to post or send a link to the incident in question. The link resolves to a web page with basic details on the incident and a prompt for the visitor to download the app. Example here.

The Record functionality allows you to live stream through the app if (a) the incident is active and (b) you are close enough to the incident. This footage is then marked a ‘verified.’ Other users can watch your live stream as it happens as well as a recording of it afterward.

Screenshot_2018-05-22-16-42-50 The Warn button seems to act like the share button. It allows you to send a ‘warning’ SMS message to people in your contacts list. The text includes a short default message and a link to the incident.

On each incident, there is also a chat section, in which users can discuss the incident.

There is no search or filter function within the app. The list of incidents is generated based on what part of the map you’re focused on. For instance, if I zoom all the way into where I am, I see only two incidents. The ‘Recent’ events list is generated based on incidents which occurred within the past seven days in the area of the map you’re focused on. A partial selection of older incidents appear in the ‘Trending’ list.

Citizen’s website says it sends out notifications when an incident occurs near you, but having had this app for several days in Manhattan, I’ve never gotten one notification. That may have been serendipity, but I’m not sure. There are no settings of any kind in the app, let alone notification settings.

Screenshot_2018-05-22-14-20-44 Screenshot_20180522-170344 This leads me to one of my general frustrations with this app and apps like it. Slightly ironically an app designed to increase transparency is in and of itself quite opaque. The Citizen ecosystem is nearly hermetically sealed. There is no way I can find to get videos of incidents out of the app or to view them at all without having the app installed.

Citizen is not explicit about where it gets the information it pushes to the app and how it chooses what information to publish. My guess based on intuition, their FAQ section, and a job posting is that they have employees monitoring scanner feeds and typing up dispatch information.

Their website indicates that their operations team makes some editorial decisions in what they put on the app, saying quote;

“Incidents discussed on emergency communications channels are vetted by our central operations team – a group of analysts with backgrounds in public safety, writing and other relevant fields. Our team is trained to make on-the-fly decisions about what goes into the app based on complex criteria and supplementary research.“

They go on to say,

“Citizen excludes vague complaints of ‘suspicious persons’ or ‘suspicious behavior,’ in order to avoid instances of racial profiling. Our operations team also removes any non-pertinent descriptions of suspects, when those details are irrelevant to the incident.“

Which is certainly their prerogative, but it’s worth noting that what you’re getting is not an unfiltered write-up of scanner traffic.

One of the things that make Citizen different than other similar services (I’m thinking here of Breaking News Network) is that Citizen is much more restrictive with user contribution. Citizen, as the name suggests, is designed for regular people as opposed to professionals. Breaking News Network encourages any user to “report” incidents, which are vetted and supplemented by a small staff of dispatchers. While the BNN user interface is undoubtedly less user-friendly, it offers a much higher level of functionality.

For Citizen, I don’t see its current iteration as being particularly useful to me. If I could speak to the Citizen team, my feature wish-list would be as follows;

Open up the ecosystem. If I can’t share an incident quickly with colleagues who don’t have the app, its usefulness is severely limited. Links shared out of the app should ideally resolve to a web page with all the incident information.
A greater suite of notification options. As an emergency manager for a university, I would like to get push notifications for anything that happens within in a specified radius of one of our campuses not just wherever I happen to be standing.
Search functionality within the app. Filtering incidents via zooming & scrolling on the map isn’t ideal, and there’s quite a bit of lag in getting the incident list to update.
Give me some mechanism to save or flag incidents, so I can go back and look at them again later
A web/desktop interface

That said, my impression from Citizen’s website is that they are not aiming their product at users like me. The feature set I want may not be what their broader user base needs or wants.

I’m not clear on the what Citizen’s business strategy is. Paying to monitor scanner feeds 24/7 is not an inexpensive endeavor. At least right now I don’t see how Citizen is bringing in any revenue. Something the Citizen team may want to consider is creating a sister product for people like me. My organization may be willing to pay for a version of Citizen with the feature set I listed above.

Despite my gripes with the app, Citizen’s incident reporting is excellent. The best I’ve seen recently available to the general public for free. For the average person in Manhattan or San Francisco, it’s excellent. For the private sector professional, with some added functionality, it would be a valuable addition to your OSINT resources.

Screenshot_2018-05-23-11-56-35 Update (05/23/2018)- This morning I received my first push notification from the Citizen App since installing it. The incident was a reported bank robbery approximately a mile from my

current location. I received the notification about five minutes after the incident was first reported, when the police were canvasing for the suspect.

Interestingly, I did not receive a notification for an earlier reported assault that was much closer to me. Capture+_2018-05-23-11-58-04

Posted on May 22, 2018May 31, 2018 by B. Illis 0

(Re)Exploring the Square Crop

On Friday I had the privilege to attend a talk by dance photographer Lois Greenfield. During the talk she discussed her choice in her most recent gallery to use square cropping (1:1 ratio), as opposed to the more conventional 4:3 or 5:7. Some of her images had actually been shot on a square frame in the days of film. Since the transition to digital, square frames have been largely out of style replaced by the more common 4:3/ 5:7. However, with the rising popularity of Instagram, and the resurgence of Polaroid, square cropping is back in the public eye. Looking at the Louis Greenfield gallery, her choice to use a 1:1 crop ratio created interest and worked well for the type of images she was displaying. When I returned home, I decided to try my hand at a 1:1 crop on some of my photos. In the past I’ve nearly always stuck with the default ratio delivered to me by the camera, and re-cropping a little whenever I would get prints. I don’t know why I never experimented with other ratios, but I’ve just always used the in camera ratio since it was the one the images were delivered in.

I started with my most recent shots, some dance photography for my Intermediate photography class.
These are low-res copies of the images I used for the sake of readability and quick loading times. Clicking on the pictures will link you to a Full-Res copy hosted on my gallery.

This is the original Cropping, followed with a 1:1 crop

Here’s another dance shot, with the 1:1 crop

So this far I’m very happy with the way a 1:1 crop works for dance photography. It seems by the nature of dance that a 4:3 crop creates a lot of wasted space. The 1:1 crop eliminates the wasted space on either side of the dancers, allowing the audience to focus in on the dancers.

Next, I’m going to move on to a few portraits and candids of single subjects I’ve done over the years:

I’m also pleased with the portraits. Similar to the dance photography, a 1:1 crop eliminates a lot of unused space and focuses the image in on the subject.

Now I’m going to move on to some landscapes/ nature photography

I’m not sure how I feel about this one, I could take it or leave it honestly. While I find the graded transition in the color of the sky appealing in the 1:1 crop, I think that the removal of the road and power lines does take away from the overall composition.

I like this one a little bit better. Having a clearly defined subject seems to help.

Lets look at another, this one is a landscape again, I cropped it on the right side of the frame to keep the building in. This is a picture of my college campus, and the building is clearly identifiable as the university center,

This one, I like, but it feels a little off to me. The crop has completely changed the composition of the shot. In the first shot, the image is a landscape with a building in it, but the second seems more like the building is the subject of the image. I started playing with some adjustments in Lightroom, and tried some black and white filters. I’m not sure if my own preconceptions about a 1:1 ratio being associated with black and white are shading my opinion, but I like the B&W filters combined with the 1:1 ratio.

Here’s the image above, B&W and color side by side

I used the Red High-contrast Filter in Adobe Lightroom. The lack of color allowed me to further accentuate the contrast in the image. I can make the blacks blacker and whites whiter. While I liked the color in the original image, now looking at the B&W, I think that for the 1:1 ratio the B&W is a better fit.

Since I liked it with this image, I decided to go back and explore it’s usage in some of the other images.

This one was achieved with the Red Filter Preset.

Green Filter

All in all, I’ve found this to be a very interesting exercise. I am pleased by the results. In the future I think I may consider a 1:1 ratio for my images, along with more experimentation with B&W Filters. If you want to see a few more examples of 1:1 cropping you can head over to the applicable folder on my website. There are several other images that I left out of this post for the sake of brevity.

Continue reading →

Posted on February 2, 2015May 9, 2018 by B. Illis 0

Brendan Illis