Senate Voting Data:
Complete Part 2 of the SQLite tutorial. It’s long; start early.
Create a new database called fec (the .sqlite gets added on when you create it). Download this CSV file of contributors to Wisconsin Rep. Tammy Baldwin during the second quarter of 2011. Import it into the fec database, creating a table called baldwin. Look at the CSV file in Excel before you define the fields in SQLite – be sure to define the zip field as VARCHAR, not INTEGER. Just define the date field as VARCHAR for now.
Once you’ve done that, write queries to do the following:
- Show the contributors from Hawaii
- Show contributors from Philadelphia who gave at least $1,000
- Show contributors who listed their occupation as CEO, with the largest amount first
Reading: This Tulsa World story on lottery ticket sales.
Send me both the fec.sqlite file and the SQL from your queries (I can check the results).
First Critique: USA Today story on federal funding for smaller airports (and accompanying map). In reviewing this material, pay particular attention to how data drives the story and how the reporters used *and* explained it. Would you have done the same? Did they pay too much attention to some aspects and not enough to others? Was it clear to you what they did to arrive at their conclusions? Please critique both the story and map in at least 400 words. When in doubt about what to say, be specific. Don’t say, “I liked it.” Tell me why.
Excel: Using the Census Bureau’s Quarterly Summary of State & Local Tax Revenue, download the excel version of Table 1 (Latest National Totals of State & Local Taxes). Then calculate the percentage of the total revenue that individual income, property, tobacco and alcohol taxes each represent back to 2005. Remember that formulas in Excel begin with an equals sign. Are there any unusual percentages worthy of exploration? Send me the finished spreadsheet.
Reading: NPR story and graphic on bird-strike data. Be prepared to discuss in class.
Text data: Using the tools we used in class, do a Many Eyes word tree and a word frequency index on this 2009 Senate speech by Sen. Inhofe. Then do an in-cell bar chart like we did in class (you can do this in Excel or Google spreadsheets, using the REPT() function). Save the results and email them, either as a link (or in the case of the word frequency index, you can copy the results into Excel or a Google spreadsheet). Do the results suggest any possible avenues of reporting?
Excel: Using the CSV version (the green CSV icon is on the right above the table) of this FEC candidate summary file, open it in Excel and answer the following questions using sorting and filtering:
- Which candidate has raised the greatest amount of individual contributions?
- How many Florida challengers are there? How many of them have total contributions of at least $1,000?
Please email me the Inhofe speech excel file, a link to your ManyEyes word tree and the FEC excel file along with your answers in the email text.