If you will agree that the polar vehicular opposite of last week’s lithe, green, bipedal, needs-are-few bicycle is nothing but the lumbering, profligate, fume-shrugging car, then deciding to point that contraption at Manhattan’s viscous streets must stand in turn as the quintessence of the automotive idea gone wrong. Driving and spewing through that storied island is just another way of saying you can’t get there from here, and one wrong move – i.e,. damming an intersection, or executing a pull-over into any one of the island’s uncountable slew of forbidden territories can cost you big time – up to $115, and know, for example, that a New York-perpetrated “Failure of an intercity bus to properly display the operator’s name, address and telephone number” (to quote the city’s rule book; see this compendium of no-nos and their budgetary consequences) will rifle $515 from some company’s petty cash drawer. Hope they remembered to ask for a receipt.
It thus seemed to me that a log of actual New York parking delicts committed to spreadsheet form might bare a sociological insight or two; and by hailing a cab to the New York open data site (we’ve been there before, on April 18 and 25) and its ledger of violations, I found the data to be had.
Of which there is no shortage. It appears that because the violation summaries are released monthly as a the November data as of now are few, with the actual shortfall pushing back to October 29, the point in time from which the available data become atomically small. I thus placed an order for violation data bracketed by the two-week endpoints of October 15 and 28, seeing to it that each day of the week would be represented twice.
And yes, they’re a lot of data. If you’re downloading along with me, that sound you hear is your hard drive crying Ouch. 107 megabytes if you’re counting, but if you want to line up October 15 through the 28th click the small dark blue Filter button in the view’s upper right and click the eventuating Add a New Filter Condition button until you reach Issue Date is. Next click the second small down arrow, click “is between”
and then identify October 15 and 28 in the contiguous date fields. Then click away from the dates to enable them to process, and next click the lighter blue Export button and CSV beneath the Download As heading. The data should head into a spreadsheet, but you can do some household chores while you wait. (Of course you could ask for fewer days.)
Fourteen days, 346,687 violations – close to 25,000 tickets a day. Do the math and the bucks start to get get pretty big. As a percentage of all drivers the ratio of apparent malfeasance is perhaps low (about 250,000 people drive to work into Manhattan daily, and the city’s George Washington Bridge puts its macadam under the wheels of about 275,000 vehicles each weekday; and four other bridges support over 100,000 cars, these numbers drawn from this Robert Wagner Graduate School (New York University) review of the Manhattan daytime population), and it is difficult to know from here how many citations are disputed, but the absolute numbers are large either way. This simple pivot table, then, breaks out violations by each of the 14 days:
Row labels: Issue Date
Values: Issue Date (again, by Count)
I get
The 20th and 27th were Sundays, and note the pronounced decrement in Saturday infractions (the 19th and 26th) as well. For a more straightforward by-day aggregation, delete all the data in column A (summons number, a field you’re not likely to use), title the fledging field Day, write in A2
=WEEKDAY(E2)
and copy all the way down. This pivot table, then
Row Labels: Day
Values: Issue Date (Count)
Issue Date (PivotTable Tools > Options > Show Values As > % of Column Total)
yields:
The 1, of course, proxies Sunday. No, I can’t tell you why day 4, Wednesday, seems to fall significantly below its weekday fellows, but it does. There’s a story line for you.
Now if you’re wondering about Manhattan’s violation share – a crude but indicative index of that borough’s slice of New York’s traffic whole, try
Row Labels: Violation County
Values: Violation County (Count, of necessity; the data are textual)
Violation County (again by % of Column Total):
The County legend, for you out-of-towners:
Bx – Bronx
K – Kings, or Brooklyn
NY – Manhattan
Q – Queens
R – Richmond, or Staten Island.
Those surprise-fee results must yet be thought through nevertheless. People use their cars all across the city without necessarily aiming them toward a workplace, of course, and so in order to make greater sense of the totals an understanding of all traffic accumulations would have to put in place. Indeed – utterly motionless cars get nailed, too.
And what about the kind of cars likely to magnetize tickets to their windshield, operationalized here by the Plate Type field? The table is easy:
Row Labels: Plate Type
Values: Plate Type (Count)
You’ll learn that 244,471 citations, about 70.5% of the (allegedly) offending cars, were fired off to the PAS type, clearly passenger vehicles, with 76,557 more, about 22.8%, leveled at COMs, doubtless commercial vehicles (and if I’ve read these clarifications with all due understanding it appears as if both private cars and taxis qualify as PAS).
Those expectable figures account for about 93% of the ticket writers’ literary output, but the remaining 7 or so percent comprise all sorts of abbreviations about which I’m clueless, e.g,
While we can assume the 999s serve as stand-ins for some data entry snarl, I remain unenlightened about the other codes, and that’s an issue of sorts. Absent some sort of clarifying directory, we’re left to guess about the meanings of the abridgements above. In any case, the data pull up short of comparative certainty, because as with last week’s bicycle data we need to know how the ticket data squares with the ratios of passenger, commercial, etc., cars actually rolling atop the city’s roads. In short, we need denominators before the next our analytical step treads across the data.
The same caution stakes the Registration State breakout:
Row Labels: Registration State
Values: Registration State (Count)
Of course New York license registrants dominate the count – exactly 264,300, or 76% of the accused multitude, with New Jersey contributing a predictably second-place 36,987. But apart from the data-empty 99s, I see 62 rows worth of registrations – and when I last looked I counted but 50 united states. While it’s clear that PR means Puerto Rico, I’m not about the other eleven entries, some of which probably represent Canadian domains, e.g., BC/British Columbia). Again, we need more background information, as we do for the Vehicle Color field, in which we need to disentangle WHITE and WH, for example (although you’re asking why it’s worth associating vehicle color with ticket vulnerability, that’s a fair question. But who knows what correlations await?).
Lots of fields and violations, and lots of possibilities. And unlike so many of New York’s parking spaces, you can hold your spot in the data from 10:00 to 3:00.
spreadsheetjournalism 
Leave a Reply