Spreadsheets are Handy, but pivot tables are incredibly useful.
Wells by County
The Department of Environmental Conservation publishes data on gas wells in New York State. Download it: How many wells are there per county?
- Start with
Data > Pivot Table Report— look at the cells Excel proposes to use. Does that include all of your data?
- Add Row — Use “COUNTY” for the rows. You should see a list of county names.
- Add Value — Use “API_WELLNO” for now.
- Check the formula — should excel
sumthem? Or find an
And there you have it. More things to play with:
- Try adding “SLANT” as a Column — horizontal (as opposed to vertical) wells are particularly controversial. Are there any concentrations of horizontal wells?
- How would you work out how much money each county is collecting in permit fees?
- Can you see any trends in the average permit fee in each county?
Last semester, Matt Surrusco found iCasualties.org — NYT has a nice profile of Michael White who trolls through news sites and official releases to build out a database of coalition forces deaths. Start with http://icasualties.org/OEF/OEF_US_Fatalities.xls and pivot by “Country of Death” and “Place of Death.”
This data needs some cleanup — we’ll work on that next week.
We used a function:
=YEAR() to find the year of each death. We also had to do
format > cells … and select
general to correct wonky display issues. If you right-click in your pivot table, you’ll see a “Refresh Data” option — you might need that if your year column is not showing up.
Some questions you could answer:
- What’s the most common age of death
- How many deaths, and at what age?
- What’s the most common age of death for members of the CIA? the Army?
- What rank and branch had the greatest number of casualties?