Data for Decision Making Part 2

Replace values

fix misspellings or missing codes

Remove Duplicates

after append/merge to keep unique rows

Remove blank rows

after imports from Excel

Split column by delimiter

“Last, First” → two columns (pick comma)

Trim

kill leading/trailing spaces

Concatenate

glue first & last name into “Full Name”

Capitalization

standardize to “Title Case”

Conditional column

flag valid e-mail format

Extract text after delimiter

pull domain after “@”

Cleaning strategies

• Remove duplicates

• Replace values

• Remove blank rows

• Split column by delimiter

• Trim

• Concatenate

• Capitalization

• Conditional column

• Extract text after the delimiter

Single-table aggregation

• Sum

• Min

• Max

• Median

• Average

• Count

• Count Distinct.

Sum

• Adds up all values in a numeric column

• Total sales, total quantities, total revenue

Min

• Finds the smallest value in a column

• Finding earliest date, lowest price, smallest quantity

Max

• Finds the largest value in a column

• Finding latest date, highest price, largest quantity

Median

• Finds the middle value when data is sorted

• When you want the "typical" value that's not skewed by outliers

Average

• Calculates the mean (sum ÷ count)

•  Finding typical values, mean scores, average prices

Count

Rows

Count Distinct

Unique Values (e.g., count unique customers)

Ascending

(A→Z, small→large) vs. descending

Text filters

“equals” vs. “contains” (wildcard)

Multiple criteria

AND (both true) vs. OR (either true)

Sorting & Filtering

• Ascending

• Text Filters

• Multiple Criteria

Append

• add new rows (same columns)

• think “union”

Use when stacking yearly files

Merge

• add new columns (join on key)

• think “lookup”

• when adding lookup tables

Multi-Table Operations

• Append

• Merge