<commit_before>---
layout: page
---

# What is cleaning anyways?

What does it mean for a dataset to be dirty anyways?  With respect to what?

* integrity constraints
  * FDs are the classic constraints we discuss in intro to DB
  * Note that foreign and primary keys are special cases of how to use FDs to normalize a database
  * Classic Chase algorithm
* Application requirements

What is an example of dirtiness?  Can it happen to me?

* Luna's truth discovery paper sheds light on even pretty clean settings
* Do we think the rule-based approaches could solve Luna's problems?

How to decompose cleaning?

* Steps
  * Detect: what errors to repair
  * Repair: fix them
* What do you think is the realistic form of cleaning?
  * directly update the database?
  * send proposed errors to an expert?
  * something else?
* What are classes of errors that the DB literature deal with
  * Attribute errors: missing/incorrect values
    * value imputation, data repairing
  * Duplicates
    * dedup
  * Relevance error: data is irrelevant
    * delete it
  


# Data Source Integration

Truth finding on the deep web

What do we know?

* aggregating data from sources on the web is nice but hard.  But _how hard_?  Unclear in terms of quantifying before this paper
* What is the "deep web"?
  * HTML is not written by hand anymore, generated from data.
  * DBMS -> webapp -> HTML string -> browser
  * a webpage is a "view" over a database.
  * We see the browser, but deepweb is the dbms
* Luna looks at 2 domains that you would think would be clean: flights and stocks
  * search on google/yahoo for keywords (e.g., flights, stocks)
  * pikcs top pages, extracts their sources via crawling
    * federal aviation administration, AA website

The setup

* data model
  * object: a flight, a stock
  * attr/data item: takeoff time, duration, departure gate, etc
  * for given object's attr, single true value (debatable!  but fair assumption)
* Sources provide subset of objects and attributes
* sources can differ in three ways
  * schema: structure/attrs differ
  * instance: objects identified (pkeys) using different attrs (id vs name+company)
  * value: true values, diff value, diff format, wrong, missing, etc
* too  many issues! paper fixes schema and instance, only focus on value

### The Sources

Stock

* 55 sources
  * search "stock price quotes", "AAPL quotes" and find deep-web sources of top 200 results
  * 89 sources total, 76 use GET methods, 55 crawlable (others use JS/reject crawling)
  * 1000 stocks: 30 from dow, 100 from nasdaq, 873 random from russel 3000
  * crawl once a day during July after market close
  * object: stock on day X
* attrs
  * 3-71 attrs per source; 333 total (local attrs); 153 after manual matching (global attrs)
  * distribution follows zipf's law
  * (21) 13% provided by 1/3 of source; picked 16 of them to study.
* Performed data cleaning e.g., 6.7M = 6700000 = 6,700,000
* picked 5 popular sources, voted to pick gold standard values for 200 symbols

Flight dataset

* 38 sources, 1200 flights from AA, UA, Continental hub airports
* crawl every day in Dec
* object: flight on day X
* 43 local, 15 global, 4-15 attrs per source
* 6 (40%) attrs in 50% of sources; used those
* 3 airline websites on 100 flights as gold

### The Data

Redundancy

* Stocks have high coverage
  * all sources provide 90% of stocks
  *  50% sources have all stocks 
* Flights
  * 36% sources cover 90% flights; 50% flights in 60% sources
  * 28% sources provide 50% attrs; 29% attrs in 50% sources
* small # extremely common/rare attrs, most are found in small number of sources (fat tail)

Are values consistent?

* What are possible metrics?
  * # values
  * entropy: higher entropy --> higher inconsistency
  * dominant value D: val found in most sources
  * deviation from dominant value (std, but use dominant instead of mean. why?)
  * how dominant: % sources with dominant val D
  * are dominant values == gold?
* fundamentally what are these metrics measuring?
  * disagreement!  variety and spread of values
  * f(distribution of values)
* stocks
  * 3.7 vals/attr
  * 17% attrs have 1 value (37% if exclude a bad data source); 30% have 2 values.
  * valuues seem to be similar
    * low deviation and entropy.  
    * Suggests data is pretty similar. (one would hope so for stocks!)
  * realtime values have less inconsistency than statistics -- more semantic ambiguity with statistics (see below)
* flights
  * 1.45 vals/attr
  * 61% attrs have 1 val; 93% have <= 2 vals
  * large deviation in some attrs such as departure times (~15minutes off)

Why the inconsistency?

* Semantics (46% cases): dividend computed for year or quarter?  Market cap? Yield?
* Instance ambiguity (6%): stock symbol interpreted incorrectly (stock was terminated)
* Out of date (34%) stale data
* Data units (1 error): 76"B" instead of 76"M"  (billion instead of million!)
* Wrong data (11%)
* Stock data saw all types of inconsistency, flights saw semantic, out of date, and pure errors (56% cases)

### Does dominance work?  Can I take majority vote and call it a day?
<commit_msg>lec notes<commit_after>---
layout: page
---

# What is cleaning anyways?

What does it mean for a dataset to be dirty anyways?  With respect to what?

* integrity constraints
  * FDs are the classic constraints we discuss in intro to DB
  * Note that foreign and primary keys are special cases of how to use FDs to normalize a database
  * Classic Chase algorithm
* Application requirements

What is an example of dirtiness?  Can it happen to me?

* Luna's truth discovery paper sheds light on even pretty clean settings
* Do we think the rule-based approaches could solve Luna's problems?

How to decompose cleaning?

* Steps
  * Detect: what errors to repair
  * Repair: fix them
* What do you think is the realistic form of cleaning?
  * directly update the database?
  * send proposed errors to an expert?
  * something else?
* What are classes of errors that the DB literature deal with
  * Attribute errors: missing/incorrect values
    * value imputation, data repairing
  * Duplicates
    * dedup
  * Relevance error: data is irrelevant
    * delete it
  


# Data Source Integration

Truth finding on the deep web

What do we know?

* aggregating data from sources on the web is nice but hard.  But _how hard_?  Unclear in terms of quantifying before this paper
* What is the "deep web"?
  * HTML is not written by hand anymore, generated from data.
  * DBMS -> webapp -> HTML string -> browser
  * a webpage is a "view" over a database.
  * We see the browser, but deepweb is the dbms
* Luna looks at 2 domains that you would think would be clean: flights and stocks
  * search on google/yahoo for keywords (e.g., flights, stocks)
  * pikcs top pages, extracts their sources via crawling
    * federal aviation administration, AA website

The setup

* data model
  * object: a flight, a stock
  * attr/data item: takeoff time, duration, departure gate, etc
  * for given object's attr, single true value (debatable!  but fair assumption)
* Sources provide subset of objects and attributes
* sources can differ in three ways
  * schema: structure/attrs differ
  * instance: objects identified (pkeys) using different attrs (id vs name+company)
  * value: true values, diff value, diff format, wrong, missing, etc
* too  many issues! paper fixes schema and instance, only focus on value

### The Sources

Stock

* 55 sources
  * search "stock price quotes", "AAPL quotes" and find deep-web sources of top 200 results
  * 89 sources total, 76 use GET methods, 55 crawlable (others use JS/reject crawling)
  * 1000 stocks: 30 from dow, 100 from nasdaq, 873 random from russel 3000
  * crawl once a day during July after market close
  * object: stock on day X
* attrs
  * 3-71 attrs per source; 333 total (local attrs); 153 after manual matching (global attrs)
  * distribution follows zipf's law
  * (21) 13% provided by 1/3 of source; picked 16 of them to study.
* Performed data cleaning e.g., 6.7M = 6700000 = 6,700,000
* picked 5 popular sources, voted to pick gold standard values for 200 symbols

Flight dataset

* 38 sources, 1200 flights from AA, UA, Continental hub airports
* crawl every day in Dec
* object: flight on day X
* 43 local, 15 global, 4-15 attrs per source
* 6 (40%) attrs in 50% of sources; used those
* 3 airline websites on 100 flights as gold

### The Data

Redundancy

* Stocks have high coverage
  * all sources provide 90% of stocks
  *  50% sources have all stocks 
* Flights
  * 36% sources cover 90% flights; 50% flights in 60% sources
  * 28% sources provide 50% attrs; 29% attrs in 50% sources
* small # extremely common/rare attrs, most are found in small number of sources (fat tail)

Are values consistent?

* What are possible metrics?
  * \# values
  * entropy: higher entropy --> higher inconsistency
  * dominant value D: val found in most sources
  * deviation from dominant value (std, but use dominant instead of mean. why?)
  * how dominant: % sources with dominant val D
  * are dominant values == gold?
* fundamentally what are these metrics measuring?
  * disagreement!  variety and spread of values
  * f(distribution of values)
* stocks
  * 3.7 vals/attr
  * 17% attrs have 1 value (37% if exclude a bad data source); 30% have 2 values.
  * valuues seem to be similar
    * low deviation and entropy.  
    * Suggests data is pretty similar. (one would hope so for stocks!)
  * realtime values have less inconsistency than statistics -- more semantic ambiguity with statistics (see below)
* flights
  * 1.45 vals/attr
  * 61% attrs have 1 val; 93% have <= 2 vals
  * large deviation in some attrs such as departure times (~15minutes off)

Why the inconsistency?

* Semantics (46% cases): dividend computed for year or quarter?  Market cap? Yield?
* Instance ambiguity (6%): stock symbol interpreted incorrectly (stock was terminated)
* Out of date (34%) stale data
* Data units (1 error): 76"B" instead of 76"M"  (billion instead of million!)
* Wrong data (11%)
* Stock data saw all types of inconsistency, flights saw semantic, out of date, and pure errors (56% cases)

### Does dominance work?  Can I take majority vote and call it a day?

{'fraction_non_alphanumeric': 0.06773845701401823, 'fraction_numerical': 0.027840407803156553, 'mean_word_length': 3.716597318539066, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6728544', 'n_tokens_mistral': 3106, 'n_tokens_neox': 2772, 'n_words': 1521}