Not going to be posting here anymore

Hey everybody — I’ve decided to actually maintain http://www.andymboyle.com, so I’ll be posting from there now on.

I’ll keep this site up for awhile in the meantime, but update your RSS feeds and whatnot to follow me at my new dot com.

Thanks.

Long time no post

I haven’t posted on here in quite some time, and for the few of you who read this blog I apologize. I’m going to be a bit more active in the coming weeks.

I’ll be posting in the next couple of days some walk-throughs on a few stories I did that recently won me some awards.

One was for a piece I did in Arkansas — I found at least 850 felons who used a firearm to hunt. The other was a piece on defective bridges/bridge safety in Nebraska for KRNU radio, the University of Nebraska-Lincoln’s radio station.

Also: I now am a Twitterer. Check me out at http://twitter.com/andymboyle

In the meantime, for those of you reading this blog: Gimme a story idea, will ya?

Looking for story ideas

Hey all. As some of you might know, I received a fellowship to work as a reporter at the Omaha World-Herald next semester out of their Lincoln bureau.

Part of my job will be to find stories off the beaten path, or what we call in the biz “enterprise stories.” This means I won’t be covering press conference, legislative bill introductions, city council, county board, school board, or any other kind of meetings. No police and fire siren chasing. I’m looking to do as many stories with computer-assisted reporting as I can.

As one editor said: “(You) will produce articles that will be unique to the World-Herald. Scoops. Enterprise angles on issues in the news. Features. Profiles. Trends. There probably will be stories involving the Legislature or local governmental entities, but these stories will be enterprising angles to some issue.”

Stories that don’t normally make it into the newspaper. Good things. Bad things. People doing wrong, people doing okay.

So, my job is to come up with two interesting story ideas a week. Right now I’m trying to get a good list of stories, to pick your brain for what you want to read in the paper. No story is too dumb, too lame or whatever. Pitch anything and everything to me.

Here are some things to give you an idea what could be a story:

• Know a local business that’s doing something really innovative, or just plain interesting and hasn’t ever been covered before?
• Know anyone, including yourself, who has perhaps gotten royally screwed over by “the man,” whomever the man might be?
• Do you work in government and know if something shady is going on? Even something small, like people using first-rate mail all the time instead of sending it normal postage.
• Is there something in your neighborhood that’s not getting taken care of? Pot holes, signs missing, a dead body lying on the curb?
• Is the university screwing you over in some way, or are they doing something you don’t appreciate?

If any of these jar your brain for a story idea, let me know. If you want to tell me something in confidence and not be the source for why I know something, that’s fine, too. You can reach me at andymboyle (at) g mail dot com

Thanks.

Data visualization

Check out this site — http://www.swivel.com/

It’s pretty simple to use to make sweet maps and whatnot. You should try it, whomever you are, faithful reader.

Don’t want to do excess work in Excel?

Then here’s your link:    http://cleanupdata.com/

This site is totally sweet. Instead of having to use update commands to trim fields and move them to new places (Like when you’re taking names and putting them into multiple fields), you just do it easily on this. Watch the tutorial and wait to have your mind blown.

So. Awesome. Use it now.

Here’s another awesome link a friend sent me recently: http://www.getdropbox.com/

To explain what it does, here’s a portion of a chat with a good friend after I sent it to her. The bold words mean I changed them to make this blog child safe:

friend: is this saying that if i have a file on this computeri can link another computer to mineand get stuff from it?

6:25 PM me: YES

PC

or

MAC

friend: you’ve gotta be really kidding me

me: you can also share with friends

I’m not

friend: i’ve been waiting for this to happen

since high school

Cool Facebook visualization

Someone on a listserv I’m on sent out a link to this: http://www.facebook.com/video/video.php?v=37403547074&ref=nf

It’s a visual representation of different Facebook data, such as when people interact and when people add new friends. It’s a real nifty way to represent data. Perhaps someone could do something similar with the University of Nebraska-Lincoln’s Internet usage, and pinpoint where the most traffic is coming from visually?

More tools to help local journalists

I just added a new page on the blog — Public Records Law Links — so readers have a one-stop place for many state and local Web sites to help them find background information. I’ve included links to the local assessors office, court information, budget database searches and even how to track an airplane.

I’ll be adding more in the future, so be sure to check back.

A ‘Show Me State’ after a lawsuit

One of my favorite newspapers is the Kansas City Star, and on Sunday they had an awesome story about their fights for some e-mails from the Missouri governor’s office. The paper, in conjunction with the Associated Press and the St. Louis Post-Dispatch, eventually sued to get the records. Here’s the lede of the story, which you can read here:

ST. LOUIS | The release of thousands of e-mails from top officials in Missouri Gov. Matt Blunt’s administration appears to partially vindicate a disgraced staff lawyer who was fired last year.

Scott Eckersley’s rise and fall — and proof of his longstanding claim that he had warned the administration over e-mail secrecy — are documented in a cache of 60,166 pages of e-mails released to an unprecedented consortium of media organizations, including The Kansas City Star.

I highly recommend you read the rest of the story. After my own disagreements with the administration of the University of Nebraska-Lincoln about some public records, I’m happy that it didn’t get to this level.

This type of journalism is what needs to happen more in Nebraska. I’d like to see someone request the e-mails sent by some of our governor’s staff and see what the response is. Hopefully they won’t threaten to have you escorted out of the Capitol by security.

Atlanta’s parking scofflaws

I haven’t updated the blog in quite awhile, but I come back with a vengeance. This time it’s about a story by Mike Maciag, one of my former roommates in Erie, Pa., while we both interned at the Erie Times-News. He set out to investigate Atlanta’s collection of parking fines, and he discovered the city had lost about $10.5 million in unpaid tickets.

He eventually discovered parking enforcement was extremely weak, and he managed to track down and interview one person who had $4,100 in unpaid tickets.

You can view his story here.

Mike’s always been a good pal, and more importantly a smart reporter who I can always depend on for help when I’ve got something data-related I’m working on. He has one major flaw: He likes the Bengals (“you know, America’s team?”) too much.

Here’s his e-mailed responses to some questions I had for him:

1. Explain to me how the story idea came about and why you decided to pursue it.

I had a list of ideas when I arrived at the AJC, and a story on parking tickets was one of the first I decided to pursue. I particularly thought it was worth investigating since the city had recently cut its parking management staff to only nine people. However, I wasn’t quite sure which direction to take the story until I got my hands on the data.

2. Where did you get the data from to do the story, and describe the process of getting it (how long did it take to receive it, what costs, what form did it come in)?

The paper requested records for all parking citations issued since 2003 from the Atlanta Municipal Court. It took many phone calls and e-mails, but we eventually received the data after about a month. Some reporters might not have the luxury of waiting this long, but I was fortunate enough to have editors who were patient and willing to let me spend time getting the data.

The records were exported out of the court’s database into a delimite text file. The fields included in the file were the date, citation number, license plate number (for the more recent data), name (only a small handful had names), fine amount, penalty amount, paid amount, payment date and payment type.

The cost was about $95.

3. What did you use to analyze the data and how long did it take?

I used Access for nearly all of the analysis. Some Excel spreadsheets were also used for smaller data sets that were generated from queries.

The analysis and data cleaning took no more than two days. The reporting took much longer.

4. Walk me through your analysis process.

The first step – often the most difficult with these types of projects – was making sense of the data. This required a few phone calls to the IT people at the court.

A couple of things came out of our conversation. First, I found out that all data prior to 2005 was unreliable and couldn’t be used for the story. In addition, a new system had been implemented in 2007, and only unpaid citations were transferred over. This meant I wouldn’t be able to calculate how much money was actually collected during most years.

But I still had more than enough good data for the story. In all, the file had about 725,000 rows (about 410,000 unique citations) for tickets issued since 2005.

After cleaning up the records, I performed a series of Access queries – everything from making a list of top offenders to determining how much was owed by out-of-state motorists.

To get the top scofflaws, I subtracted the paid amount from the sum of the fines and penalties. Using the ‘Group by’ function for each plate number, I added the totals (with the SUM function) to get the amount owed. This showed at least 179 people had debts of $1,000 or more.

Tracking down vehicle owners with outstanding citations was more difficult. Although the file contained a field for the name of licensees, nearly all the entries were blank. If I remember correctly, only about 5,000 actual names where in the whole file.

In some states, not having names isn’t a problem because you can go to the DMV and request information on an owner by providing the license plate number. But in Georgia, the law doesn’t allow for this, so I had to look for alternatives.

I ended up taking my list of top scofflaws and ran queries in Access to display all records for each plate number. For most plate numbers, there were more than 100 records, all of which didn’t have names. But some license plates did have one or two records with names, and that was all I needed.

Using the city’s online parking violation payment site, I entered the plate and citation numbers for the tickets that had names attached. Some of the search results included addresses for vehicle owners. I then handed over a couple of the names and addresses to our news
researcher, who compiled reports with contact information by running reverse address searches.

For one individual who I eventually contacted for the story, the phone numbers in the report were all incorrect. Instead, I decided to use the man’s employment history to track him down. Fortunately, he just happened to be listed as working for a company on LinkedIn. I called the phone number listed on the company’s Web site and sure enough, he answered.

5. The reporting process — what was the most difficult part about using the data when trying to contact people? Did they have trouble understanding what you found? Did anyone not believe your findings or dispute them?

After computing the amounts for unpaid citations, I went back to the court to check the numbers. I explained my process for adding and subtracting dollar amounts in different columns to compute the totals, and they verified my methodology. This really should be a required step for any type of data analysis.

The major finding was that drivers owed the city a whopping $10.5 million in fines and penalties issued since 2005. No one ever disputed this number. The city Department of Public Works had no stats for comparison, so there really wasn’t much they could do. A man with $4,100 in unpaid tickets was pretty shocked, though, when I told him how much he owed.

6. What was the hardest part about doing this story?

The most difficult part was gathering all the facts regarding the city’s collection process and parking enforcement. There are many players involved. Both parking enforcement officers and police can punish drivers, but it varies greatly depending on the circumstances. The city court collects the fines, but a collection agency is used after 90 days. There are also all the details about how notices are sent out and the specifics of the contract with the city.

7. If someone else wanted to try doing a similar story in their community, what hints or tricks would you have for them, and what mistakes did you make that they could learn from?

The story possibilities are endless if you’re able to get parking ticket data. You could do a story on unpaid citations if the numbers are high enough. If the data includes enough names, another possibility might be to see if any public officials have unpaid citations. A reporter also could determine where the most tickets are issued. I would’ve liked to do that for this story, but addresses weren’t included in the data.

The best advice I can give is to just make sure you understand the data and what is or isn’t reliable. This can save you a lot of time and headaches.

FEC data + NU employees = story

Last April, as part of the Daily Nebraskan’s week long series on this year’s presidential elections, I did a story on the donations from University of Nebraska employees: Click here

I found that:

“University of Nebraska employees donated $68,411 to various presidential candidates, which amounts to almost 8 percent of Nebraska’s donations…

Former Democratic candidate John Edwards received the most money – $23,923 – from NU employees. Obama received $22,206, and Sen. Hillary Clinton got $5,945.

On the Republican side, former contender Mike Huckabee raised $5,400, Ron Paul $2,901 and Mitt Romney $475. Current Republican frontrunner John McCain managed only $451. Rudolph Giuliani and Duncan Hunter followed him.

I used a listing I obtained from the University of Nebraska central administration of total compensation received by employees of the four universities in the system, and then I downloaded a listing of FEC donations.

I put both lists in two separate Microsoft Excel spreadsheets and then used the text-to-columns function to move first names, last names and middle initials to separate fields Then I put both spreadsheets into Microsoft Access and joined the first names and last names.

Not every name in either FEC data or the listing of NU employees contained middle names/initials, so instead of joining first, last and middle, I just joined first and last names, and then listed the field for “middle” from both spreadsheets.

There weren’t thousands of matches, so I was able to go through it line by line and take out people. Sometimes it would be Johnny P. Somebody in both the FEC and employee data, but the person would say he was a “lawyer” on FEC data, meanwhile he was a “janitor” in the employee data.

When I came up with the final numbers, I then sorted them by candidate and came up with my figures. I was able to find some people to call and they were all quite happy to chat with me. What was quite interesting was the low amount McCain received compared to other candidates, and the same for Clinton.

Add in some footwork by discussing the numbers with political scientists and I felt I had a solid story using pretty simple computer analytical techniques.

What I might do in the near future is match state employee data with FEC data. It’d be interesting to see how state employees donate — and whether many donated on the same day, which might mean there’s bundling.