I keep a running list of my favorite spreadsheet commands on the cunyjdata wiki. That’s a great resource.
From this week’s assignment:
- Convert it to CSV
- Open it in Excel
- Pivot – you probably don’t want column A.
features__properties__Activityas Row Labels and a count of
features__properties__FIDfor our Values
What have they been up to?
- Add the dates as a column label, and then right click on row 4 of the pivot table itself to
Download 2005-2010 Graduation Outcomes Data
Pivot, use Boroughs as row labels, Demographic as column labels, % of cohort as the Value. Play around. Add “Cohort” as a row label.
In groups of 3, what’s interesting about this data? What would you like to chart. How could you pivot to get to what you want to chart?
Google tracks searches for flu-related terms. Start at http://www.google.org/flutrends/ – it is worth reading up on how they produce this data so you have a sense of the limitations of it, but we’re just going to play with it.
Open it in a spreadsheet. Use
Data > Text to Columns to break it up if it isn’t already broken up. Hint: it is “comma delimited”
- In which week did which country have the most flu searches?
=Match(criterion, range, 0)
=Indirect(“A”&cell)to get date or re-order columns
- How much more did that country search for flu in that week than average?
- Order the countries by most flu searches (SUM…choose arbitrary 2012-13 to capture searches from all countries, Transpose countries-values to make a quick bar chart)
Noise Complaints by CD
Starting at the dataset of datasets, find 311 complaints and filter for noise.