Introduction

• Welcome to class

• What is EDA?

• Why EDA?

• Data Visualization

• Iterative Process

• Case Study: Problem

• Case Study: Hypothesis Formulation

• Case Study: Data Analysis (I)

• Case Study: Data Analysis (II)

• Case Study: Data Visualization

• Recap: EDA

Data Transformation

• Introduction

• Quiz: Data Tables (I)

• Data Tables (II)

• Tidy Data

• Pivot / Unpivot (I)

• Pivot / Unpivot (II)

• Common Data types

• Data Transformation (I)

• Data Transformation (II)

• Google Sheets

• Setting Display Language

• Keyboard Shortcuts

• Exercise: Keyboard Shortcuts

• Formula Syntax & Name range

• Exercise Guide

• Exercise: Formula Syntax, Name Range

• Reference Types

• Exercise: Reference Types

• IF

• Exercise: IF

• Nested IF

• Exercise: Nested IF

• IFS

• Exercise: IFS

• AND / OR

• Exercise: OR

• Exercise: OR+AND

• Exercise: IF+OR+AND

• SWITCH

• Exercise: SWITCH

• Information Functions (I)

• Information Functions (II)

• Exercise: Information Functions

• LEN / TRIM

• LEFT / RIGHT / MID

• UPPER / LOWER / PROPER

• Exercise: Text Functions (I)

• REPLACE / SUBSTITUTE

• SEARCH

• Exercise: Text Functions (II)

• Combining Text

• Splitting Text to Columns

• Exercise: Text Functions (III)

• Date Value

• Create Date

• Setting Locale Date / Time

• Exercise: Date / Time (I)

• Date Elements

• EOMONTH

• Date Calculation

• Exercise: Date / Time (II)

• INDEX

• MATCH

• INDEX + MATCH

• Exercise: INDEX + MATCH

• VLOOKUP

• HLOOKUP

• Exercise: VLOOKUP / HLOOKUP

• Importing Data from CSV

• Exercise: Importing Data from CSV

• Importing Data from HTML

• Exercise: Importing Data from HTML

• Get Ready

Analyzing One Variable

• Analyzing a Numerical Variable

• Mean vs. Median

• Spread

• Distribution

• Outliers

• Summarizing Data

• Exercise: Summarizing Data

• Tips: Status Bar

• Conditional COUNT / SUM / AVERAGE

• Exercise: Conditional COUNT / SUM / AVERAGE

• Filtering Rows

• Visualizing Distributions

• Histogram

• Exercise: Histogram

• Robust Statistics (I)

• Robust Statistics (II)

• Exercise: Robust Statistics

• Categorical Data

• Frequency Table

• Exercise: Frequency Table, PivotTables

• Visualizing Comparison, Composition

• Bar Chart / Pie Chart

• Exercise: Bar Chart / Pie Chart

• Case Study: Facebook Data

• Case Study: Analyzing One Variable

• Exercise: Analyzing Reactions

• Exercise: Analyzing Post Types

• Always look at actual data!

Analyzing Multiple Variable

• Analyzing Two Variables

• Correlation

• Scatter Plot

• Exploring Numerical x Numerical

• Exercise: Exploring Numerical x Numerical

• Contingency Table

• Heat Map

• Exploring Categorical x Categorical

• Exercise: Exploring Categorical x Categorical

• Categorical x Numerical

• Values and Dimensions

• Visualizing Comparison, Composition

• Exploring Categorical x Continuous

• Exercise: Exploring Categorical x Continuous

• Visualizing Time-Series

• Exploring Time Series

• Exercise: Exploring Time Series

• Exploring Multiple Variables

• Exercise: Exploring Multiple Variables

• Case Study: eJeab vs. Drama-Addict

• Project: eJeab vs Drama-Addict

Understanding Data

• Unit of Analysis

• Correlation & Causation (I)

• Quiz: Correlation & Causation (II)

• Correlation & Causation (III)

• Summary

Basic Concepts

• Course Overview

• Introduction

• Relational Databases

• SQL Command Overview

• Table Schema

• Data Types

• Which Data Type?

• Quiz: Which Data Type?

• Solutions

• Google BigQuery

• Google BigQuery Example

Querying Data

• Getting Set Up

• SELECT one column

• SELECT multiple columns

• SELECT all columns

• SELECT with a column alias using AS

• SELECT without FROM

• SELECT a specific value as a column

• ORDER BY, LIMIT

• SELECT a limited number of rows

• SELECT distinct values in a column

• SELECT distinct combinations of fields

• Sort rows

• Select the 3 top-grossing movies from the list

• Selection criteria

• Use WHERE clause to filter data based on boolean expression

• Comparison Operators

• Exercises

• SELECT in Summary

• Quiz

Data Transformation

• Arithmetic Calculations

• Perform a calculation

• Dividing Numbers

• Mathematical Functions

• Rounding Numbers

• Type Casting

• Dates and Times

• Date Functions

• Datetime Functions

• Quiz

• String Functions

• Conditional Expressions

• Quiz

Aggregating Data

• Aggregate Functions

• Summarizing Tables

• More Aggregrate Functions

• Quiz

• GROUP BY

• Filtering

• Filtering Rows

• Filtering Groups

• GROUP BY Conditional Expressions

• Grouping Sets

• GROUP in Summary

• Quiz

Combining Tables

• Working with multiple tables

• Joining Tables

• Types of Joins

• INNER JOIN

• LEFT/RIGHT (OUTER) JOIN

• FULL JOIN

• Join Syntax

• Example

• Sequences of JOINs

• Order of Execution

• Complex Queries

• Subqueries

• Quiz

• Conclusion