Please note these steps below are not always completely rote instructions. Instead, they should offer you the broad contours to acclimate to new terrain.

Spreadsheet One: Danish Bicycles

  1. Go to Google Sheets and File > Make a Copy (make sure you have a google account and are signed in)
  2. Go to File > Download spreadsheet file as a CSV.
  3. Go to Overpass Turbo: https://overpass-turbo.eu/
  4. Search for Copenhagen, Denmark
  5. Go to Wizard. Search for cafe. Run. Export as a kml file.
  6. Go to Wizard. Search for park. Run. Export as a kml file.
  7. Go to carto.db (create an account).
  8. Go to Datasets > Connect to Dataset > Import the CSV you downloaded
  9. In Map Wizard, change marker type to IMG > Maki icons > choose bicycle icon
  10. Add layer, connect to dataset, and choose cafe kml export. In Map Wizard, change marker type to IMG > Maki icons > choose coffee cup
  11. Add layer, connect to dataset, and choose park kml export. This is a polygon instead of a point. Make the polygons light green to show how green Copenhagen is!
  12. Other possible overpass searches: trees, flower shops, & other springtime favorites.

Spreadsheet Two: Birds of New York, April 2016

  1. Go to Google Sheets and File > Make a Copy (make sure you have a google account and are signed in)
  2. Select  Row 1. Go to View > Freeze 1 row.
  3. Try out some conditional formatting. Select a column with variables in it. Go to Format > Conditional Formatting > Format Cells If.. > for text or conditional matches or Color Scale for sequential or diverging numerical highlighting.

Spreadsheet Three:  Summer Olympics Medals Winners

  1. Go to Google Sheets and File > Make a Copy (make sure you have a google account and are signed in)
  2. Select  the space between Column A and Row 1 to select the entire spreadsheet.
  3. Go to Data > Filter. Filters now appear along each column in your header row. Click on the the downward blue arrow indicating Filter. Clear the selection and choose some an item to filter, such as on sport or gender.
  4. Once you have your filter applied, go back to the space between Column A and Row 1, click on it to select your entire spreadsheet, and go back to Data > Sort Range > Click on “Data has header row” > and choose a column that has numerical values in it. Sort Z to A for descending order

Spreadsheet Four: Cherry Blossoms in Japan

1. go to drive.google.com
2. Go to the red NEW button
3. Click on it, go down to More > Connect to Apps
4. Search for “Fusion Tables
5. Then follow the same steps, but this time you will see Fusion Tables added to your list of apps.
6. You can search public datasets here and export them to Google Sheets.
7. Search for “Cherry blossoms Japan”
8. The first table The Bloom of Cherry Blossoms 2016 that comes up looks good. These tables are often Wikipedia, so of course, you’ll need to verify what you scrape is okay to use for academic work.
9. Export this table to Google Sheets.
10. To do this, go to the row number and right-click on it, and select Hide row.
11. Next, control-click on row 2 and insert 1 row above it. This will be the header row.
12. Label your headers: City; First bloom; Full bloom
13. Next, we need to Split our City column, which also has in it the Prefecture for Japan.
14. Control-click on Column A  and Insert three Columns to the right of it.
15. In cell C3, type in “=split(A2,“(“, TRUE)”
16.Select this cell. Grab the blue handle in the lower corner. Double click on it or drag down to copy and apply the formula conditionally.
17. Next, go to Edit > Find and Replace and find all of the )’s. Replace with nothing.
18. Copy-and-paste Cols. B through C into Col. I and Paste Special > Paste Values Only. Delete Col. A.
19. Select the pasted elements and move them into B through C.
20. Now we need to add a Column that tells us the time span for the peak bloom, and another one that gives us the day of the first bloom, from 1 to 365, so we can measure that against latitude (the question is, do flowers first bloom later in northern climates)?
21. Hide rows that don’t have values in them for our key variables. You can always fill these in later with more research.
20. Select the City Col. Go to Add-Ons > Get Add Ons > Search for Awesome Geocode. Use this Add-on to get Latitude and Longtitude data. Select the City Col. and run the Awesome Geocode.

These steps are getting pretty detailed. Fortunately, you can find the formulas pre-written for you on the spreadsheet. You can try writing your own formula and copying it on adjacent empty columns. Follow along with me for the rest as make a chart, merge it with more data about the Japanese prefectures in Fusion Tables, and then bring this into Tableau to do things we can’t do in Google Sheets.

 

Link to cherry blossom image (Search for My Tableau Repository/ Shapes/ ..): cherry-blossom

Tableau workbook: http://tabsoft.co/1VX51rG

 

Spreadsheet Five: Baby Names

Tableau workbook: http://tabsoft.co/1SOgBSo

 

For your reference

Data-Visualization-Resources