1

Applied Data Analysis (CS401)

Robert West

Lecture 2

Handling data

Announcements

2

• Register your teams (3 people) here by tomorrow
• May shuffle till after Homework 2, then fixed (incl. project)
• Homework 1 to be released in tomorrow’s lab session
• Due October 11, 23:59 (i.e., in 2 weeks)
• Interested in preparing course notes in LaTeX?
• Message @sharbat on Mattermost
• Remuneration: extra credit, karma

3

1st hour

refresher on data operations

​

2nd hour

data wrangling

The big picture

4

Key concept: structured data

​

​

A data model is a collection of concepts for describing data.

​

A schema is a description of a particular collection of data, using a given data model.

​

A toy model and schema

6

Meteorological measurements

​

• Concepts in data model:�numbers, samples, vectors, matrices
• Samples are vectors of numbers; time series is matrix obtained by stacking vectors
• Schema: column 1 is integer and has time stamp; col 2 is float and contains temperature, etc.

Examples of data models

7

• Relational model
• Document model
• Network model
• …

The relational model

• The relational model is ubiquitous:
• MySQL, PostgreSQL, Oracle, DB2, SQLite, …
• You use it many times every day
• Data represented as tables (“relations”) describing
• enitities,
• relationships between entities
• Most of the data we will use can be “reduced” to the relation model

​

​

8

What is a relation?

Relation: made up of 2 parts:

Schema: specifies name of relation, plus name and type of each column

Students(sid: string, name: string, login: string, age: integer, gpa: real)

Instance: the actual data at a given time

#rows = cardinality

#fields = degree / arity

​

​

9

Example: instance of students relation

10

Cardinality = 3, degree = 5 , all rows distinct

​

​

​

​

SQL ex.

relation-list: A list of relation names

target-list: A list of attributes of tables in relation-list

qualification: Comparisons combined using AND, OR and NOT.

• Comparisons are Attr op const or Attr1 op Attr2, where op is one of =≠<>≤≥

DISTINCT: optional keyword indicating that the answer should not contain duplicates.

• In SQL SELECT, the default is that duplicates are not eliminated! (Result is called a “multiset”)

​

​

11

SELECT [DISTINCT] target-list

FROM relation-list

WHERE qualification

SELECT DISTINCT names

FROM students

WHERE age >= 19

Joins and inference

Chaining relations together is the basic inference method in relational DBs. It produces new relations (effectively new facts) from the data:

​

​

12

SELECT S.name, M.mortality

FROM Students S, Mortality M

WHERE S.Race=M.Race

M

S

13

Aggregations and GroupBy

• One of the most common operations on data tables is aggregation (count, sum, average, min, max,…).
• They provide a means to see high-level patterns in the data, to make summaries of it, etc.
• You need ways of specifying which columns are being aggregated over, which is the role of a GroupBy operator.

14

Aggregations and GroupBy

15

SELECT sid, name, AVG(gpa)

FROM Students

GROUP BY sid

SQL is a declarative language

• SQL provides language for core data manipulations
• You think about what you want, not how to compute it

16

SQL implementations

etc.

17

SQL and “SQL”

• The declarative-programming principles of SQL are widespread, even where it’s less obvious

18

“SQL”: Pandas/Python

• Series: a named, ordered dictionary
• The keys of the dictionary are the indexes
• Built on NumPy’s ndarray
• Values can be any NumPy data type object
• DataFrame: a table with named columns (like relation in relational model)
• Represented as a dict (col_name -> series)
• Each Series object represents a column

19

Pandas operations (cf. Friday lab)

map() functions

filter (apply predicate to rows)

sort/group by

aggregate: sum, count, average, max, min

Pivot or reshape

Relational:

union, intersection, difference, cartesian product (CROSS JOIN), select/filter, project, join: natural join (INNER JOIN), theta join, semi-join, etc.

​

20

Pandas vs. SQL

+ Pandas is lightweight and fast.

+ Natively Python, i.e., full SQL expressiveness plus the expressiveness of Python, especially for function evaluation.

+ Integration with plotting functions like Matplotlib.

​

- Tables must fit into memory.

- No post-load indexing functionality: indices are built when a table is created.

- No transactions, journaling, etc.

- Large, complex joins are slower.

21

“SQL”: Apache Pig

• Started at Yahoo! Research
• Features:
• Expresses sequences of MapReduce jobs
• Under the hood: entirely different from relational databases like MySQL
• On surface: provides relational operators like SQL�(JOIN, GROUP BY, etc.)

22

Pig example

Suppose you have user info in one file, website logs in another, and you need to find the top 5 pages most visited by users aged 18-25.

23

Load Users

Load Pages

Filter by age

Join on name

Group on url

Count clicks

Order by clicks

Take top 5

Example from http://wiki.apache.org/pig-data/attachments/PigTalksPapers/attachments/ApacheConEurope09.ppt

In MapReduce

24

In Pig

25

Users = load ‘users’ as (name, age);�Filtered = filter Users by � age >= 18 and age <= 25; �Pages = load ‘pages’ as (user, url);�Joined = join Filtered by name, Pages by user;�Grouped = group Joined by url;�Summed = foreach Grouped generate group,� count(Joined) as clicks;�Sorted = order Summed by clicks desc;�Top5 = limit Sorted 5;

�store Top5 into ‘top5sites’;

“SQL”: Unix command line

26

cat users.txt \

| awk ‘$2 >= 18 && $2 <= 25’ \

| join -1 1 -2 1 pages.txt - \

| cut -f 4 \

| sort \

| uniq -c \

| sort -k 1,1 -n -r \

| head -n 5

Other data models: document model

​

​

• Document model

<contact>� <id>656</id>� <firstname>Chuck</firstname>� <lastname>Smith</lastname>� <phone>(123) 555-0178</phone>� <phone>(890) 555-0133</phone>� <address>� <street1>Rue de l’Ale 8</street1>� <city>Lausanne</city>� <zip>1007</zip>� <country>CH</country>� </address>�</contact>

​

• Same in relational model

Other data models: network model

​

​

Data Wrangling

29

Working with raw data sucks

Data comes in all shapes and sizes

– CSV files, PDFs, SQL dumps, .jpg, …

Different files have different formatting

– Spaces instead of NULLs, extra rows

“Dirty” data: Unwanted anomalies, duplicates

30

Raw data without thinking ==

Recipe for disaster

31

What is data wrangling?

• a.k.a. data munging
• Goal: extract and standardize the raw data
• Combine multiple data sources
• Clean data anomalies
• Strategy: Combine automation with interactive visualizations to aid in cleaning
• Outcome: Improve efficiency and scale of data importing

​

​

32

33

Wrangling takes between 50% and 80% of your time…

​

[Source]

Types of data problems

• Missing data
• Incorrect data
• Inconsistent representations of the same data
• About 75% of data problems require human intervention (e.g., crowdsourcing, experts, etc.)
• Tradeoff between cleaning data vs. over-sanitizing data

34

link

“Dirty Data” horror stories

“Dear Idiot” letter

17,000 men are pregnant

As the crow flies

CHF 10,000 compute-cluster bill

[Source]

35

Diagnosing data problems

Visualizations and basic stats can convey issues in “raw” data

Different representations highlight different types of issues:

– Outliers often stand out in a plot

– Missing data will cause gaps or zero values

​

Becomes increasingly difficult as data gets larger�(sampling to the rescue!)

​

36

Facebook graph

37

Matrix view (1)

automatic permutation of rows and columns to highlight patterns of connectivity

38

Matrix view (2)

rows and columns sorted in the order provided by the Facebook API

Can you guess what’s going on?

[Source]

39

Viz at scale? Careful!

40

Dealing with missing data

Knowledge about domain and data collection should drive your choice!

• Set values to zero?
• Interpolate based on existing data?
• Omit missing data?

​

41

U.S. census counts of people working as ‘‘Farm Laborers’’; values from 1890 are missing due to records being burned in a fire

“My name is Willy”

42

Data preparation

43

What to do before analysis

Deal with uncertain data (can arise from measurement errors, wrong sampling strategies, etc.)

Parse/transform data (with the techniques we saw during the first hour) to obtain meaningful records

44

Desiderata

It’s always ideal if you can put your hands on the code/documentation about the dataset you are analyzing (provenance)

It’s always ideal if the provided data format is nicely parsable (otherwise you need regexes, or maybe even pay humans)

45

Highly non-parseable data

Entire NY Times archive (since 1851) digitized as of 2015

46

What’s next?

What we have seen today is definitely not an exhaustive list (when you get stuck, Google is your friend!)

E.g., when we move to machine learning, we will learn how to prepare features (i.e., attributes) with normalization, rescaling, etc.

47

Don’t be surprised when multiple iterations are required!

48

Credits

• Last year’s version of these slides

49