Skip to content
NTPU AI Learn

DS 102 · Module 1

Wrangling: From Raw Tables to Analysis-Ready Data

The first step in turning readable data into trustworthy, reproducible and traceable analysis data.

Start Section 1
  • Duration180 min
  • LevelFoundation
  • PrerequisiteDS 101 recommended but not required
0 / 5 completed
Sync my progressJoin a session to keep progress across devices and share it with your instructor

Info

After you join, your completed lesson numbers are sent to your instructor together with your learner code, so they can see your progress. Use the codes your instructor gave you, and do not enter your name, company secrets or other personal data. Progress is kept with the rest of that session's data.

Learning objectives

  • Define dataset grain; distinguish formatting from semantic quality; establish raw/staging/clean zones
  • Convert types safely; normalize strings; parse dates; preserve failed conversions for review
  • Differentiate missing from zero; detect duplicate rows versus duplicate entities; define candidate keys
  • Use melt/pivot concepts; understand tidy structure; avoid accidental aggregation
  • Apply typed cleaning steps and produce a reproducible staging-to-clean transformation

Grain and cleaning zones

25 min

Wrangling Is Not Cosmetic Cleanup

Learning objective: define dataset grain; distinguish formatting from semantic quality; establish raw/staging/clean zones.

Many people think of data wrangling as tidying column names or fixing colors. What actually decides whether an analysis can be trusted is two more basic questions: what is this dataset's grain, and can every transformation be re-run to get the same result?

Cosmetic cleanup only

Column names look consistent, numbers get thousands separators, blanks get filled so the table looks complete, but nobody has confirmed what each row actually represents.

Checking semantic quality too

Grain
Confirm what one row represents first: one company per year, or one site per month?
Zones
Keep the raw file untouched, do transformations in staging, and only move validated results into clean.
Reproducibility
Every transformation is code or a logged step; running it again should give the same result.
What raw, staging and clean each do
ZoneContentRule
RawThe file exactly as it arrived, never modifiedAlways keep one copy as the starting point for traceability
StagingIntermediate results of type conversion, cleaning and reshapingReproducible; never treated as the final conclusion
CleanAnalysis-ready data that has passed validation rulesOnly this zone is used directly for metric calculation
  • A column name contains "total" or "avg" but nobody states which dimension it was aggregated across
  • The same company_id has a different number of rows in the raw file each time, with no explanation
  • The row count changes after merging two sources, but nobody checks it against the expected count
This course's data (load first, change nothing yet)
import pandas as pd

# raw: never modify it; look at it first
raw = pd.read_csv("DS102_Company_Year_Practice.csv")
raw.shape
raw.head()
A common grain mistake (simulated scenario)

An analyst sums a company's four quarterly electricity readings into one annual number, then compares it against another "annual" dataset. Both tables look like "one number per year," but one is a sum of quarters and the other is a direct annual measurement; the calculation methods differ, so comparing them directly produces a wrong conclusion, and the code never raises an error.

Warning

Data safety reminder: every exercise in this course uses the synthetic files provided. Unless authorized, never paste confidential institutional or company data, customer data, or personal data into an AI tool or a practice environment.

Info

Human, AI, Human: a person decides the grain and validation rules, AI may help write transformation code, and a person checks whether every step is reproducible and the result is trustworthy.
Check yourself

Each row of a dataset represents "one company in one year." Which approach best protects that grain?

Each row of a dataset represents "one company in one year." Which approach best protects that grain?

Key takeaway: The first step in wrangling is not making data look nice; it is fixing the grain and the zones so every step can be redone.

Types, strings and dates

35 min

Data Types, Strings, and Dates

Learning objective: convert types safely; normalize strings; parse dates; preserve failed conversions for review.

Raw data often stores numbers as text (like "18,900" with a thousands comma), adds a stray space to a name, or mixes date formats. Ignoring these problems will not always throw an error; the code just quietly produces a wrong number.

Three common problems and the correct handling
ProblemWrong handlingCorrect handling
A number stored as text (e.g. "18,900")Compare it as a string, or ignore the errorStrip formatting characters like thousands commas first, then convert to a numeric type
Leading/trailing whitespace (e.g. "Riverbank Foods Co. ")Use it directly for grouping or matching, silently splitting it into two groupsApply .strip() before using the value
Inconsistent or unparseable date formatGuess a date to fill it in, or delete the whole rowKeep the original value and flag it as an error for human review
Safe type conversion: look at the problem first, then handle it
# A thousands comma makes pandas read the whole column as text (dtype = object)
raw["revenue_m_ntd"].dtype

# Strip the comma before converting to a numeric type
clean_revenue = raw["revenue_m_ntd"].astype(str).str.replace(",", "", regex=False).astype(float)
Keep a clue when date parsing fails, instead of guessing
parsed = pd.to_datetime(raw["reported_at"], format="%Y-%m-%d", errors="coerce")
raw["reported_at_error"] = parsed.isna() & raw["reported_at"].notna()
raw[raw["reported_at_error"]][["company_id", "year", "reported_at"]]
  • Check df.dtypes first to see what type each column actually is before deciding to convert it
  • Before converting numbers, confirm the one known formatting character (like a thousands comma); do not force a conversion that "mostly works"
  • Always .strip() text columns before grouping, filtering, or using them as a join key
  • An unparseable date must keep its original value and an error flag; never guess a date to fill it in
A hidden split caused by whitespace (simulated scenario)

The same company shows up in different monthly files as "Riverbank Foods Co." and "Riverbank Foods Co. " (with a trailing space). Grouping directly by company name treats these as two different companies, silently splitting the annual total into two smaller numbers, with no error message at all.

Warning

Code that runs is not proof the data is right. errors="coerce" quietly turns an unparseable date into a missing value; without a separate check, you might never notice a row was silently emptied out.
Check yourself

One row's reported_at value cannot be parsed as a date at all. Which approach is most correct?

One row's reported_at value cannot be parsed as a date at all. Which approach is most correct?

Key takeaway: Look closely at the problem before converting a type, and leave a trace when a conversion fails instead of letting it disappear quietly.

Missing, duplicates and keys

40 min

Missing Data, Duplicates, and Keys

Learning objective: differentiate missing from zero; detect duplicate rows versus duplicate entities; define candidate keys.

"Blank" and "0" carry completely different information: 0 means "confirmed, the value is zero," while blank means "unknown," which could be not disclosed, not collected, or never recorded by the system at all. Treating them the same distorts every average and sum computed afterward.

Missing

  • Represents unknown or not collected
  • Cannot be treated as 0 directly
  • Confirm the mechanism first, then decide whether to fill it

Zero (actually zero)

  • A confirmed real value
  • Can be used directly in calculations
  • Mistaking it for missing would understate the true situation
The real difference between 0 and blank (simulated scenario)

One site's water_usage_m3 for 2023 is 0, meaning the site confirmed no recorded water use that year. Another site's same field is blank, meaning it has not reported yet. Treating the blank as 0 when computing an average mistakes "unknown" for "confirmed zero" and understates the true average.

Find missing values and confirm the candidate key is unique
raw.isna().sum()

raw["company_year_key"] = raw["company_id"].astype(str) + "-" + raw["year"].astype(str)
raw["company_year_key"].duplicated().sum()  # greater than 0 means the candidate key repeats
Duplicate rows vs. duplicate entities
SituationMeaningWhat to do
Two exactly identical rowsVery likely the same record entered or imported twiceConfirm the grain rule first; only delete if it is truly a duplicate, and record the reason
Same candidate key, different other valuesThe same entity may have been recorded in two different versions (e.g. before and after a correction)Check the source and timestamps to judge which is correct; never delete by row order alone

Info

A candidate key is the smallest set of columns that uniquely identifies a row. company_id plus year uniquely identifies a row here; company_id alone does not, because one company has several years.

Warning

"Delete the duplicate rows" is not a safe default action. First confirm whether the grain actually allows multiple rows (for example, the same monthly file refreshed daily), then judge whether it is a true duplicate.
Check yourself

One row in the water_usage_m3 column is blank. Which approach is most correct?

One row in the water_usage_m3 column is blank. Which approach is most correct?

Key takeaway: Missing is not zero; duplicate rows must not be deleted without checking; a candidate key must uniquely identify every row.

Reshape

35 min

Wide/Long Data and Reshaping

Learning objective: use melt/pivot concepts; understand tidy structure; avoid accidental aggregation.

The same data can be stored wide or long. Wide format puts each month in its own column, which reads intuitively; long (tidy) format turns "month" itself into a column, which is usually easier for code, grouping, and plotting to handle.

Wide vs. long format (illustrative, not the actual course values)
FormatStructureGood for
WideEach month gets its own column: Jan, Feb, Mar, ...Human reading, report layout
Long / tidyOne month column, one value column, one row per monthFiltering, grouping, plotting and time-series analysis in code
melt: collapse wide month columns into two columns
site_wide = pd.read_csv("DS102_Site_Monthly_Wide.csv")
month_cols = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

site_long = site_wide.melt(
    id_vars=["company_id", "site_id", "year", "metric_name"],
    value_vars=month_cols,
    var_name="month",
    value_name="value"
)
site_long.shape  # 4 rows x 12 months = 48 rows
What happens when site_id is dropped during melt (simulated scenario)

If a melt only keeps company_id and drops site_id, two sites' monthly data end up flattened under the same company_id, looking like one site appearing twice. A later groupby("company_id", "month") sum then adds the two sites' electricity usage together, with no error message warning that the data got mixed.

Warning

The most common reshape mistake is accidental aggregation: if melt or pivot leaves out an identifying column (for example dropping site_id and keeping only company_id), data from different sites is silently mixed together, and any later sum or average becomes wrong with no error message at all.
  • Before melting, list every identifying column that must be kept (id_vars); do not rely on memory alone
  • After melting, check whether the row count matches expectations (original rows x collapsed columns)
  • pivot goes the other way: it spreads one long-format value column into several wide columns, typically for reports or cross-tabs
  • If the row count is much lower than expected, suspect a missing identifying column first, rather than jumping to "the data shrank"
Check yourself

A dataset stores Jan through Dec as twelve separate columns. Turning it into month and value columns is closest to which operation?

A dataset stores Jan through Dec as twelve separate columns. Turning it into month and value columns is closest to which operation?

Key takeaway: Reshaping changes the shape of data, not its content; dropping an identifying column can mix data together without you noticing.

Guided Lab

45 min

Guided Lab: Wrangle a Messy Sustainability Dataset

Learning objective: with instructor guidance, apply a reproducible staging-to-clean transformation to a messy dataset.

Info

Lab question: how should site-level monthly electricity data and company-level yearly water, waste and revenue data be put together, and what needs special care before merging them directly?
  1. Load all three files
    Load the company master, company-year data and site monthly data, and check each one's shape and columns.
  2. Build and check a candidate key
    Build a company_id + year candidate key for the company-year data and confirm there are no duplicates.
  3. melt the site monthly data
    Turn the twelve Jan..Dec columns into month and value columns, keeping company_id and site_id.
  4. Find the join trap
    Check whether site_id repeats across different companies; if it does, joining on site_id alone is risky.
  5. Build the correct join key
    Build a unique site key from company_id + site_id, then do a 1:N join with the company master.
  6. Check the row count
    Confirm the joined row count matches expectations, no more and no less.
  7. Record three observations
    Write three observations from the output in your own words.
  8. Label the evidence type
    Label each observation as Fact from data, Interpretation, or Needs verification.
  9. Write one limitation
    State one thing this analysis cannot do yet.
Label each observation by evidence type (sentence patterns, fill in your own output)
LabelMeaningSentence pattern
Fact from dataA number read directly from the output, reproducible by someone elseIn this synthetic dataset, [site]'s electricity usage in [month] was [value] kWh.
InterpretationYour view based on the facts, with its basis and uncertainty stated[Company]'s electricity usage is higher in the second half of the year, possibly related to a peak season or cooling load, but the data alone cannot confirm the reason.
Needs verificationSomething the data alone cannot confirm, needing another source or the data providerBoth companies have a site called S01; merging without the company code would mix electricity data from different companies, so the naming convention needs to be confirmed with the data provider.
  • Confirm the final merged row count matches the value you worked out by hand beforehand
  • Confirm site_id has been combined with company_id into a unique site code
  • Confirm every observation labeled Needs verification states who to check with and what to ask

Warning

This data can only describe what happened, not explain why on its own. Avoid causal statements, and do not treat site-level electricity usage as a company-level metric unless it has been correctly aggregated and validated first.

Done

If AI helps write the melt or join code, check every item yourself: are the identifying columns all listed, is the join key unique, does the row count match expectations. AI can speed up writing the code; verification and the conclusion remain a human responsibility.

Key takeaway: A trustworthy integration is not just merged files; it can also explain why that merge is correct and why the row count is what it is.

Save as PDF

What next?