by hand or by band(saw)
on expectation vs. reality
For years, The Washington Post tried and failed to get data from the U.S. Drug Enforcement Administration on the shipments of opioid pain pills across the country. It started with an open records request and found it’s way to a courtroom a couple years ago. Alongside HD Media, owner of the Charleston Gazette-Mail and The Herald-Dispatch, we intervened in a case where nine years of the data had become available to the plaintiffs under seal.
After years of fighting, we were finally able to get our hands on it. We got seven years at first before eventually prying the other two years free as well. When we got the data from the plaintiffs, we were told they had done a lot of cleaning and standardizing to the data. They did not disappoint. The data we got was probably the cleanest I have gotten in my short career.
With as clean as the data was, I thought that naturally it would lead to an easy geocoding process for pharmacy addresses in the data. For the most part I was not wrong. But for a good portion of it I was very, very wrong.
One aspect of the data that was not particularly clean was addresses in the data. This is because for the plaintiffs, analysis did not really involve those and so they were left as they were input originally. This meant a lot backwards addresses, misspelled addresses and addresses that no longer exist. I went in thinking it wouldn’t be so bad and was sorely disappointed. Nothing is ever as easy as it seems. You have to be prepared for anything.
In this case, the anything was approximately 4,000 addresses in the data that we could not geocode with any confidence. Even after cleaning addresses to remove characters that the geocoder didn’t like, 4,000 or so were just sitting there, unidentifiable.
So I did a thing I was not expecting to do and that took more time than I had budgeted for geocoding: I hand-coded the latitude and longitude of those addresses with the help of a couple other folks. It was a mixture of things. Some of what I did was look for business records to find addresses. Often, the pharmacies were listed with a number missing or added accidentally into the address.
But the real hard part in all of it were the addresses that simply no longer existed. We don’t think about it often but towns and cities around the country change street names all the time. And many of those same places have colloquial names for streets. So when you have data that’s over a decade old, you might not be able to find everything.
So I came up with one of my favorite solutions I could think of: use an older mapping program. As it turns out, MapQuest was the ticket. With the exception of a few addresses, I was able to finish geocoding the remainder of the addresses and we were able to build more sophisticated analyses off the data.
While it was worth the time, it’s important to remember that reality almost never lives up to expectation and that you should always plan for contingencies.
A key rule of woodworking is measure twice, cut once. I will almost certainly have a whole newsletter devoted to that in the future, but not today.
Anyway, the old adage is in place for a number of reasons. You might measure wrong the first time. Wood warps, especially if it has excess moisture in it. There are plenty of reasons to ensure your measurements are correct before cutting.
So when the databae woodshop was commissioned to build a built-in bookshelf for a 16-foot-wide wall, I had to be sure to get it right. The thing about building in my small shop is that I have to get kind of creative in terms of big projects. I do not have that much space to build, let alone transport, something that large.
My approach was to build the shelves in four parts and then assemble them in place. So naturally, I measured everything out to four feet wide and built each the same as the last. When they were finally finished, I popped them all in the back of my pickup and drove them to the install.
When we lined them all up, I realized there was a problem. As it turns out, when you measure out four shelves at four feet a pop, it doesn’t always work it’s way out to be exactly 16 feet. Put together, the four shelves measured out to about 16 feet and 1/2 inch. That is, decidedly, not 16 feet. I went in thinking it wouldn’t be so bad and was sorely disappointed. Nothing is ever as easy as it seems. You have to be prepared for anything.
The reality at that point was that I needed to figure out how to get a half inch out of the shelves to fit the space. I thought on it and decided to take the end piece and cut a half inch from every shelf and then recombine it. Those shelves were heavy (500 pounds in total) so I decided to do it in the home. Once that was done, we got it installed and veneered everything so that the four shelves would appear to be one solid piece.
It’s not the first time I’ve needed to adapt to an unexpected reality and I’ve learned and relearned the lesson over and again. While it was worth the time, it’s important to remember that reality almost never lives up to expectation and that you should always plan for contingencies.
datawork
People moving out of Norfolk public housing are mostly ending up in other poor, racially segregated areas — “Less than 30% of those moving out of St. Paul’s by the end of 2020 who stayed in Norfolk ended up in “areas of opportunity.” Those are defined by the city as census tracts with less than 40% poverty (more than double the city average) and less than 62% nonwhite residents. The number is even lower for children from St. Paul’s, less than a quarter of whom have moved into areas of opportunity. Studies show — and city staff has acknowledged — that moving to neighborhoods with better economic opportunities dramatically improves kids’ economic prospects later in life.” [The Virginian-Pilot]
Fresno police stop Black drivers more often. Chief says it’s not racist, others disagree — “Black drivers in Fresno are stopped by police around twice the rate of white and Hispanic drivers, according to a Fresno Bee analysis of police department data from the first half of 2020. Black drivers were also searched, arrested, and handcuffed more often than other races, the data shows. “As a Black male driving in Fresno, when you see a police officer, there’s always the fear they’re going to pull you over,” said Marcel Woodruff, an organizer with Faith in the Valley. “These numbers give voice to a feeling a lot of us hold.”” [The Fresno Bee]
Data shows more white seniors in Arizona are getting COVID-19 vaccines than seniors of color. The inequity could get worse — “Data shows that white seniors in Arizona are getting more COVID-19 vaccine shots than seniors of color, which experts say is because of the additional barriers seniors of color face. Just 8% of the more than 1.5 million doses of the COVID-19 vaccine allocated statewide have gone to Latinos, even though according to Census data Latinos make up nearly 32% of the state's population, according to data posted on the Arizona Department of Health Services dashboard. Only 1.5% of the COVID-19 doses have gone to Black people, who make up 4.7% of Arizona's population. Native Americans have received 3.1% of COVID-19 vaccine doses, yet make up 4.6% of the population, followed by Asian people who have received 2.3% of COVID-19 vaccination doses, yet make up 3.3% of the population.” [The Arizona Republic]
The $14 million CEO: Drug distributor boosts executive’s pay despite historic opioid settlement — “In its Jan. 28 solicitation to investors, AmerisourceBergen’s board of directors recommended giving Collis a 2020 pay package worth $14.3 million, 24 percent more than he made the previous year. The board said he achieved “strong financial results” and earned a “stretch bonus” because the company surpassed every performance metric it tied to executive pay. The substantial payout was possible only because the Pennsylvania-based drug distributor relied on a controversial accounting method: excluding legal settlement costs from its year-end chief executive evaluation. By removing the settlement, AmerisourceBergen was able to turn its $3.4 billion loss from last year — the worst annual loss in the company’s 20-year history — into a $1.6 billion “adjusted” profit.” [The Washington Post]
woodwork
If you enjoyed the newsletter, please like, subscribe and share. This newsletter will always be free but if you’d like more exclusive content or to support the woodworking done by the databae woodshop, please consider contributing to my Patreon.