Spreadsheet

Quick Data Analytics & Put it all together

Pili Hu

​

e@hupili.net

​

Homeworks and Projects

Homework 5 - eCharts

• Your story?
• Why do you pick this chart to tell this particular story?
• What annotation do you leverage?
• How does animation help your story telling?

Project

• Project idea
• ​

Project

• Theme: A story with web-based interactive visualisation
• Deliverable:
• Website
• Report
• Presentation (April 29)
• Size of a group: 3 students
• Topic:
• Self proposed
• Mentor proposed

​

More about projects and mentors

Data in the News Room

“Interview” data for news stories

Touchable infographic for the blind

https://initiumlab.com/blog/20151015-3d-infographic-user-testing/

​

Turn data into sounds

Visualise/ Gamify the data

http://salary360.initiumlab.com/#/

​

Anyone want to make an updated version based on Hong Kong 2016 by-census data?

Demand in media industry

http://data-journalism-jobs.silk.co/

Updated to 2015

A Sample JD of “Data Journalist”

Graphics Dataviz UI Skills Design Illustrator InDesign Photoshop D3 Javascript HTML CSS GIS ArcGIS QGIS ArcGIS TopoJSON Three.js WebGL PostGIS Statistics Graphics. R�

Categories:

Graphics Design, Web Dev, Mapping, Web 3D, Analysis

http://data-journalism-jobs.silk.co/page/Data-Journalist-Bloomberg-UK

Non Programming Data Tools

Data Visualization

• Google Sheets
• Google Fusion table
• Google Drawing
• Tableau
• Baidu Echart
• Infogr.am
• Kumu
• Netlytic
• AI / PS

​

Data Collection:

• Scraper (Chrome extension)
• Copy and paste
• Google Cache
• Wayback Machine
• Import.io

​

Data Cleaning

• Google Refine
• Google Sheets
• PDF tables
• Small PDF
• Tabula

Data Analysis

• Google spreadsheet
• Basic manipulation
• Function
• Pivoting
• Charting
• Scripting (.gs)
• Google fusion table

​

Programming ensures long-term competitiveness

New tools every year…

Tools can change...

Tools may not be maintained...

Good news

• Google spreadsheet
• Basic manipulation
• Function
• Pivoting
• Charting
• Scripting (.gs)
• Google fusion table

​

90% work can be done by Spreadsheet!

Sample:

HK District Council Election Data

Meet the data

Data:

• From 1999 to 2015
• # of Candidates: 4392
• Name, occupation, party, camp, votes
• # of Constituencies: 2039
• Total votes, voting rate, count of voters, population

Meet the sources

Methodology:

• Automatic
• Scraping
• Semi-automatic:
• Copy-and-paste a few table from the website
• Data cleaning by human
• Manual input from books
• Labour intensive
• Investigation

Manpower overview

Manpower overview of the large data collection campaign

Distribution of sources & time

Notation: Source (man-hour)

Research/ investigation consumes significant more time

Online accessible/ (semi-) formatted data saves time

Importance of open data and knowledge sharing

Hong Kong District Council (Disco)

Final output:

• Power of each Camp:
• https://theinitium.com/project/20151012-hk-district-council-elections/
• Power of major parties:
• https://theinitium.com/project/20151019-hk-district-council-elections-2/
• Guide for 2015 election:
• https://theinitium.com/project/20151029-hk-district-council-elections-3/desktop.html

​

Sample: HK Disco Evolution - Camp

Source of Raw Data: https://github.com/initiummedia/hk_district_council_election/blob/master/1999~2015-Deistrict-Council-Election-Voting-Records.csv

Sample: HK Disco Evolution - Gender

Sample: HK Disco Evolution - Age

Camp Evolution on Map

Produced by Google Fusion table. Not covered in this workshop

Camp Evolution on Map - Sham Shui Po

Produced by Google Fusion table. Not covered in this workshop

Get the data

• Google spreadsheet: https://docs.google.com/spreadsheets/d/1qHt3SGT9uEWoUgCLPSFXeTxffyKR2JzTd_S_gc6diY8/edit#gid=54821065
• ​
• CSV on GitHub: https://github.com/initiummedia/hk_district_council_election/blob/master/1999~2015-Deistrict-Council-Election-Voting-Records.csv

Spreadsheet

(solves 90% problems)

Basic Cell Operation

• Cell operation
• Cell naming
• First alphabet is column name
• Second digit is row number
• e.g. A1, C3, ...

Keyboard Shortcuts

https://support.google.com/docs/answer/181110?hl=en

Learn by use.

Can come back to this page later.

​

Auto Fill

• Relative & Absolute cell locationing
• Use “$” to lock column or row
• Data type and fill
• Try date type for example

Formula

• Arithmetic
• + - * /
• SUM(), AVERAGE(), MIN(), MAX()
• String
• LEFT(), MID(), RIGHT(), ...
• CONCATENATE()
• SPLIT()
• Others:
• COUNTIF()
• IF()
• VLOOKUP()
• …

​

Find help

1. Function list:

https://support.google.com/docs/table/25273?visit_id=1-636249304870053806-836666939&hl=en&rd=2

​

2. Live help document

​

JOIN()

IF()

Random Sampling

• =RANDOM()
• Sort
• Clip # of elements you want

Conditional Formatting

Charting

• Insert chart
• Modify chart meta data
• Embed chart

http://datavis.studio/week1-sample/

Pivot Table

Key concepts:

• Group by row, column
• Grouping can be nested
• Filter records
• Aggregate grouped and filtered records into values
• Sort groups

Pivot Table - flexible arrangements

Group Exercise

Group Exercise

Use what learned so far to reproduce the sample and try to go beyond

Data source:

https://docs.google.com/spreadsheets/d/1qHt3SGT9uEWoUgCLPSFXeTxffyKR2JzTd_S_gc6diY8/edit?usp=drive_web

Sample:

http://datavis.studio/week9-sample/

Quizzes and Tricks for Table

Cross Tab Reference

• Usage: =’Sheet 1’!A1
• Common case:
• Usually you don’t edit pivot table results directly because it is automatically generated
• Use cross-tab reference to copy the whole result table to another Tab

Edit filtered values?

Common practice:

• Add a new column for (very complex) filter criterion
• Use pivot table to extract by the criterion column
• Use cross-tab reference to copy pivot result to a new working area

Live filter:

• Good to explore interactively
• Persistence is an issue: next time someone might filter to different criteria

​

VLOOKUP()

Horizontal lookup?

• VLOOKUP is for vertical lookup
• Try to tweak for horizontal lookup

General parameters change upon auto-fill?

Question:

• When using auto-fill function, cell-ID (e.g. “C3”) will change according to the auto-fill direction.
• What if you need other parameters to change?
• E.g. for first 100 rows, multiply by 2, for next 100 rows, multiply by 3, ..

​

Answer:

• Use CELL formula to retrieve the column or row number.

Lists (columns) intersection/ difference

Use COUNTIF

Quick auto-fill

Problem:

• Dragging is time consuming and error prone when there are many rows and columns.

​

Solution:

• Select the cells you want to auto-fill and press Command+D (Ctrl+D)

IMAGE()

SPLIT()

=SPLIT()

Time functions

• TODAY()
• NOW()

Add multiple rows/columns

• Highlight the number of rows or columns you want to add
• Right click and do “Insert N columns/ rows …”

Random Sampling

• =RANDOM()
• Sort
• Clip # of elements you want

Error handling

Error is common during computation:

• Divide by zero
• Missing value
• Ill-formatted values

However, your data workflow should continue.

Solution:

• =IFERROR()

TRANSPOSE()

Keywords Counting

• SPLIT()
• TRANSPOSE()
• pivot table

Custom function

1

2

3

Basically you can do anything with Javascript.

https://developers.google.com/apps-script/guides/sheets/functions

​

Recap

Recap

• Spreadsheet
• Pivot table
• Web-based visualisation
• End-to-end production

Common Questions

Less (color) is more (story)

https://gloriasong.github.io/homework5/

​

After

Before

Homework review & class discussion

https://gloriasong.github.io/homework5/