Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 39 ยท Data Preprocessing

Data Integration and Reduction

Combine data from several sources, detect redundant attributes, then shrink data with sampling, feature selection and PCA. Runnable pandas and scikit-learn examples.

Mining projects rarely start with one tidy table. Customer facts sit in a CRM, purchases in a sales database, clicks in log files. Data integration combines them. Afterward the combined table is often too big or too wide, and data reduction shrinks it while keeping its meaning. The two steps come together because integration tends to create redundancy, and reduction is how you remove it.

Data integration

Merging sources raises several problems:

  • Entity identification: does cust_id in one system match customer_no in another? Sometimes you must match on name, email or address.
  • Schema integration: different names and units for the same attribute (income in dollars versus salary in thousands).
  • Redundancy: an attribute may be derivable from another (age and birth_year), or two columns may be almost the same.
  • Conflicting values: the same customer has different addresses in two systems. Decide a rule (most recent wins, trusted source wins).
Python Merge two sources and spot conflicts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The how argument is a decision: an inner join drops unmatched rows silently, an outer join keeps them for inspection. Always check row counts before and after a merge.

Detecting redundancy

Two numeric attributes that move together carry the same information. The correlation coefficient measures this. Values near +1 or -1 suggest one attribute can be dropped. For two nominal attributes, the chi-square test of independence plays the same role (see Hypothesis Testing for Data Miners).

Python Find a redundant column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Data reduction

Why reduce? Smaller data means faster algorithms, less storage and often better models, because irrelevant or noisy attributes can mislead them. There are three families: reduce the number of rows (sampling), the number of columns (feature selection, dimensionality reduction) or the number of distinct values (discretization, see Data Transformation).

flowchart LR
    A["Large data"] --> B["Fewer rows<br/>sampling"]
    A --> C["Fewer columns<br/>selection / PCA"]
    A --> D["Fewer values<br/>discretization"]
    A --> E["Compression<br/>aggregation"]

Sampling

A sample is a subset used instead of the full data. Types:

  • Simple random sampling without or with replacement.
  • Stratified sampling: sample each group in proportion, so rare groups are not lost.
  • Systematic sampling: every k-th row.
  • Cluster sampling: pick whole groups.
Python Random versus stratified sampling

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A random sample of 200 may contain almost no rare cases; stratifying keeps them. Use train_test_split(..., stratify=y) for the same reason.

Feature selection

Pick a useful subset of the existing columns. Three styles:

  • Filter: score each feature alone (correlation, variance, mutual information) and keep the best.
  • Wrapper: try subsets and score a model on each (slow but accurate).
  • Embedded: the model selects while training (decision-tree importances, Lasso).
Python Filter selection with mutual information

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dimensionality reduction with PCA

Principal component analysis (PCA) builds new columns (components) that are weighted mixes of the old ones, ordered by how much variance they carry. Keeping the first few components compresses the data with little loss.

Python PCA keeps most of the variance in 2 columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Scale before PCA, otherwise columns with big units dominate. Components are harder to interpret than the original columns, which is the price of compression.

Aggregation and numerosity reduction

Replace detailed records by summaries (daily instead of per-second sensor readings), or replace the data by a model: a histogram, cluster centers or a regression line.

Python Aggregation: from seconds to minutes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Two columns, height in cm and height in inches, have correlation 0.99. This is:

    They carry the same information in different units.
  2. 2. Why use stratified sampling for fraud data with 1 percent fraud?

    A small simple random sample may contain almost no fraud rows.
  3. 3. Which feature selection style scores a model on many feature subsets?

    Wrappers search subsets with a model in the loop; they are accurate but slow.
  4. 4. Why should you standardize columns before PCA?

    PCA follows variance, so large-scale columns would swamp the others.
  5. 5. An outer join, unlike an inner join, will:

    Outer joins keep rows without a match, which helps you detect entity-matching problems.

Summary

  • Integration must solve entity matching, schema differences, redundancy and conflicts.
  • Correlation (numeric) and chi-square (nominal) detect redundant attributes.
  • Reduce rows by sampling (use stratified for rare classes), columns by feature selection or PCA, and detail by aggregation.
  • Scale before PCA.

Next: Data Transformation