Integration, transformation and reduction — many tables into one, values into useful shapes, and big into thinkable.
Knowledge Discovery in Data · University of the Philippines Cebu
Stack, join and de-duplicate — knowledge scattered across tables becomes one frame.
Fix types, harmonize labels, derive the columns your question actually needs.
Fewer columns, fewer rows — keep what matters, on purpose.
Last week fixed what was wrong inside one table. This week's problems appear the moment you have more than one — which is every real project.
Same discipline as week 5: every merge, rename and drop is a decision someone should be able to audit.
Sales in one file, city metadata in another, last month's records in a third. Analysis needs them in one frame, at one grain (what one row stands for), in comparable units. Getting there is today.
Different systems, different owners, different months. Nobody designed your analysis table — you build it.
Joins that silently drop rows, duplicates that double your totals, labels that almost match. All quiet, all deadly.
Join tables end to end: the rows of one go under the rows of the other.
e.g. pd.concat([jan, feb])
The column two tables share, used to decide which rows belong together.
e.g. city, or a project’s contract_id
Put two tables side by side by matching their key values, like Excel’s VLOOKUP.
e.g. pd.merge(sales, meta, on="city")
Which rows survive a merge: only matches; every row of the left table; everything.
e.g. how="left"
A new column computed from existing ones.
e.g. duration = completion date − start date
Rescaling columns so that big-number columns do not drown small-number ones.
e.g. pesos in millions next to percent
The min–max kind of scaling: smallest value → 0, largest → 1.
e.g. (x - x.min()) / (x.max() - x.min())
Turning a text category into numbers: one 0/1 column per category.
e.g. status_Completed = 1 or 0
Sorting numbers into ranges (bins) so you can count them as categories.
e.g. duration → “<3mo”, “3-12mo”…
A summary that turns many values into one: sum, count, mean.
e.g. one total per region
What one row stands for: a project, a region, a region-year.
e.g. 34,079 project rows vs 18 region rows
Long: one measurement per row. Wide: a grid with one category across the columns.
e.g. region-year rows vs a years-across table
Keeping fewer columns while losing as little information as possible.
e.g. dropping a column that is 0 on every row
Taking a random subset of rows to look at.
e.g. df.sample(500, random_state=42)
A fixed starting number for random picks, so the same “random” rows come back every time.
e.g. random_state=42
Two moves: stack rows, or join columns on a key.
Last week you cleaned one table. This part combines several: putting tables under each other, or side by side by matching a shared column.
Two months of records with identical columns belong on top of each other —
that's pd.concat. To concatenate is to join end to end:
here, the February rows go under the January rows.
ignore_index=TrueWithout it, both frames keep their own 0, 1 labels — and a frame with
duplicate index labels ambushes you later (.loc[0] returns two rows).
jan, febtwo small tables with the same two columns, city and sales (Jan: Cebu, Davao; Feb: Cebu, Iloilo)[jan, feb]a list of the tables to stack, in order: January firstignore_index=Truenumber the rows afresh, 0 to 3 (the row labels are the index)4 rows, 2 columns2 + 2 rows; the columns did not changeSales knows numbers; metadata (data about the cities) knows regions. The city column
is the key: the column both tables share, used to match rows. A
merge (databases say join) puts the matching rows side by side.
Like Excel’s VLOOKUP: for each sales row, look up its city in the other table and copy the region across.
sales, metasales: Cebu, Davao, Iloilo and their sales; meta: a lookup table of regions for Cebu and Iloilo onlypd.merge(sales, meta, …)the left table first, then the right tableon="city"the key: match rows whose city is the samehow="left"keep every row of the left table, matched or notNaNDavao simply wasn't in the metadata table. After every merge, that's the first thing to look for — unmatched keys.
A key should name each row once (a primary key). A join silently multiplies rows when the key repeats. Two minutes of checking here saves an afternoon of wondering why your totals doubled.
34,079 ids for 34,079 rows. That is what a real primary key looks like — and you should confirm it, not assume it.
.nunique()how many different values the column holds(34079, 34079)as many different ids as rows: no id repeatsduplicated(keep=False)mark every copy of a repeated id, not just the second one.shape → (0, 15)0 rows (and the usual 15 columns): no id repeats| Left rows | Right rows | Key repeats? | Result rows |
|---|---|---|---|
| 34,079 | 18 | no (1 per region) | 34,079 — correct |
| 34,079 | 36 | yes (2 per region) | 68,158 — every project doubled |
| 34,079 | 18 | right missing 3 regions | 34,079, but 3 regions now NaN |
Always print len(df) before and after a join. If it grew and you did not expect growth, stop — your budget totals are now double-counted and every percentage downstream is wrong.
The choice is not stylistic. It decides which rows survive, and therefore what population your conclusion is about.
It keeps your main table intact and makes unmatched keys visible as NaN, instead of silently deleting them.
regionsa small lookup table, one row per region (shown here as an idea)indicator=Trueadds a column _merge saying where each row matched: both or left_onlym[m._merge == "left_only"]the rows that found no partnerhow= Decision
left keeps all your main rows and marks gaps with
NaN; inner silently deletes the non-matches. Choose by asking:
can I afford to lose rows?
Two class lists, one per subject. Inner: students in both. Left: everyone on the first list, blank where the second has no mark. Outer: everyone on either list.
how= | Keeps | Davao's fate |
|---|---|---|
left | every left row | kept, region = NaN |
inner | only matches | gone — silently |
outer | everything from both | kept, plus right-only rows |
Print len() before and after every merge. A changed row count you can't
explain is a bug in your data's story.
The same record arrives from two files, and suddenly Cebu's sales count
double. drop_duplicates() keeps one copy of each fully identical row. (A
duplicate, from week 5: a row identical to an earlier one.)
subset= trapdrop_duplicates(subset=["city"]) would merge 2024 and 2025 Cebu into one
row — deleting a real year of data. Match on the columns that define "the same
record," not fewer.
You left-join 100 sales rows to a city table — and the result has 130 rows. What happened?
ignore_index was forgottenB — duplicate keys multiply rows.
A left join keeps every left row at least once — but a key that matches three right-table rows comes back three times. Sum that frame and your totals are silently inflated. This is why the row-count habit exists.
Merges fail loudly only in tutorials. In the wild they fail by giving you a plausible, wrong table.
Before joining, know whether your key is unique in each table — one line:
df["city"].is_unique.
Then: making the values themselves usable.
The table is assembled. Now shape the values to fit the question.
You have one table. This part changes its values: the right data types, consistent labels, new columns computed from old ones, numbers on a common scale, and categories turned into numbers.
Scraped and merged data loves to arrive as strings — "31",
"₱1,204", "2024-01-05". Fix types before any
arithmetic touches them.
pd.to_numeric(…, errors="coerce")text → numbers; anything unreadable becomes NaN instead of an errorpd.to_datetime(…)text like "2024-01-05" → real dates you can subtract and sortOne source logs millimeters, another meters — both parse fine, and the merged column is nonsense. Unit mismatches are the outliers you met last week, wearing a disguise.
Read the file without help (Python’s built-in csv reader, not pandas) and every column is a string (text), including the ones that look like money.
Sorting text puts ₱9M above ₱80M, because "9" > "8". Your "top projects" list would be nonsense and nothing would raise an error.
rowsthe file read by csv.DictReader: a list with one dictionary (column name → value) per linerows[0]["budget"]the first row’s budget, exactly as stored in the file'279839557.31'the quote marks mean text (a str), not a numbersorted([…])puts values in order; text is ordered character by character, like words in a dictionaryForcing a type on dirty data either raises, or quietly produces nonsense. coerce gives you a third option: fail visibly, then count the failures.
If isna().sum() jumped after a conversion, those rows had values that were not numbers. Go look at them.
df["budget"] = …replace the column with its converted version.isna().sum()count the NaNs after converting: new ones are values that refusedduration = completion_date − start_date
A derived column is one you compute from other columns. "How long does a flood control project take?" is not a column. You have to make it — and the making is one line once the types are right.
The rest are missing one of the two dates — which, from last week, you already know means they are unfinished.
completion_date - start_datesubtracting two dates gives a length of time.dt.days.dt reaches date parts; .days turns each length into a whole number of dayscount 27,664only rows with both dates get a duration; the rest are NaNThe median is a sensible 202 days. The maximum, 2,688 days, is a long project but a possible one. The minimum is −17, and that is not possible at all.
You would never have found it by eye. The derived column found it for you the moment you called .describe().
df.loc[df.duration < 0, cols]rows where the test is True, and only the columns named in cols.iloc[0]the first (here the only) such row, printed down the page, one column per line00:00:00 / -17.0dates carry a time of day (midnight); the duration is a decimal because the column also holds NaN2016-05-23 is a real date, correctly formatted, inside the dataset’s range. Nothing to flag.
2016-05-06 is equally valid. Any per-column check passes it.
The error exists only between the columns. It is invisible until you compute something that uses both.
This is the argument for deriving early: a derived column is a consistency test you get for free. Check its range the moment you create it — min, max, and how many are impossible.
Write the check in the same cell as the calculation, while you still remember what "impossible" means for that quantity.
Can it be negative? Is there a physical maximum? How many rows violate either?
assert bad == 0, "…"assert checks a claim; if it is False, the program stops with your messageAssertionError: 1 negative durationsread the last line of the error: the type (a failed assert) and your own messageTo you, one place. To merge, three unrelated keys — and three
rows of NaN. Harmonize labels before integrating, not after the
damage.
.str.strip()remove spaces at both ends of each text value.str.lower()make every letter small.replace({"cebu city": "cebu"})swap one spelling for another, using a dict: old → newdf["city"].unique() on each source, eyes on, before every join. Thirty
seconds that saves the whole afternoon.
A groupby treats "Cebu" and "cebu " as two different places. The flood data happens to be clean — but you only know that because you checked.
No case variants, no stray whitespace. Run this on any column you are about to group on; it costs one line.
Raw columns record what was measured. Derived columns express what you're asking — a rate, a ratio, a flag (a True/False column), an age from a date.
Raw totals mostly measure city size. Dividing by population turns "Cebu is biggest" into a fair comparison — often reversing the ranking entirely.
Pesos in the millions next to percentages in single digits: any distance or average across them is dominated by the big-number column. Scaling (rescaling each column to a common range) puts them on equal footing.
Comparing a height in centimetres with a weight in tonnes: the bigger numbers win every comparison unless you first convert both to “how big for its kind”.
Normalisation squeezes each column to 0–1: (x − min) / (max − min). Simple, bounded,
sensitive to outliers.
Center at 0, spread of 1: (x − mean) / std. "How unusual is this value for
its own column?"
Any time columns are combined or compared across units — clustering and modeling later in your DS path depend on it.
Many methods only accept numbers. Encoding turns a text category into numbers. One-hot encoding makes one new column per category, holding 1 where the row is that category and 0 elsewhere.
A checklist with one box per option: exactly one box is ticked on each row.
Then “On-Going” (2) would count as twice “Completed” (1): an order and a distance that the categories do not have.
pd.get_dummies(…)pandas’ one-hot encoder (“dummy” columns is another name)columns=["status"]encode this column; city is left alonedtype=intwrite 1 and 0 instead of True and False(34079, 5)every project gets five yes/no columns, one per statusNot every column — or row — earns its place.
Your table now has the right values. This part makes it smaller on purpose: fewer columns, fewer rows, or fewer distinct values.
Internal IDs, empty notes, audit fields — dead weight for an analysis of sales by region. A column list is the simplest reduction there is.
Dropping from the working frame is reversible precisely because week 5's rule holds: the raw file still has everything. Reduce copies, never sources.
The column you dropped is the confounder you'll need in week 9. When unsure, keep it — columns are cheap; re-collection isn't.
Each column is one dimension of the data. Dimensionality reduction means keeping fewer columns while losing as little information as possible. The simplest case: a column with one value on every row.
amount_paid
has a single value, 0, on all 34,079 rows. It cannot tell one project from another, so it can go
from the working copy.
Methods such as PCA combine many related columns into a few new ones. Same idea, more maths: your ML courses.
df.nunique()for every column, how many different values it holds.sort_values().head(4)the four columns with the fewest different valuesamount_paid 1one value everywhere: no informationdf.drop(columns=[…])a new table without that column; the raw file keeps itBoth "combine two tables". They do opposite things to the shape, and the row count tells you which one you actually did.
After a concat, rows should be the sum of the inputs. After a merge, columns should be the sum minus the shared key.
Binning sorts numbers into ranges (bins). Sometimes the useful question is not "how many days" but "short, normal, or dragging". pd.cut takes the edges you choose.
bins = [-9999, 0, 90, …]the edges: below 0, 0–89, 90–364 days…right=Falseeach bin includes its left edge, not its right: 90 days counts as 3–12 months.value_counts().sort_index()count per bin, in bin order rather than biggest firstcut uses edges you pick — good when the boundaries mean something. qcut makes equal-sized groups — good when you just want quartiles.
| Bucket | Projects | Reading |
|---|---|---|
| impossible (< 0 days) | 1 | A data error — contract 16DC0070 |
| under 3 months | 3,049 | Small works; 11% of finished projects |
| 3–12 months | 21,101 | The norm — 76% land here |
| 1–2 years | 3,080 | Large works |
| over 2 years | 433 | Worth a second look — 1.6% |
Binning did two jobs at once: it made the distribution readable, and it put the impossible row in a bucket of its own where it cannot hide.
Budget is in pesos, duration in days, progress in percent. Anything that compares them — distance, clustering, a combined score — needs them rescaled first.
Min-max when you need a fixed 0–1 range. Z-score when outliers matter and you want distance from typical. On skewed money, z-score is safer.
xthe budget column, df.budget(x - x.min()) / (x.max() - x.min())normalisation: smallest → 0, largest → 1(x - x.mean()) / x.std()standardising (z-scores, week 5): the largest budget is 28.8 SDs above the mean37.7M → 0.026the median project sits near 0 on the 0–1 scale: skew squashes itLong is one row per observation — what most plotting and grouping wants. Wide is one row per subject — what a reader wants in a table.
Do the analysis long, pivot to wide only for presentation. Wide tables are hard to filter and easy to break.
.reset_index()turn the group labels back into ordinary columnslong.pivot(index=…, columns=…, values=…)regions become rows, years become columns, budgets fill the cells/ 1e9 … round(2)show billions of pesos with two decimalswide.melt(…)the reverse: every cell of the grid becomes its own row againAggregation turns many values into one summary (a sum, a count, a mean). Group by region and sum, and many city-rows become one region-row. Smaller, clearer — and the city detail is gone from that frame.
.groupby("region")sort the rows into piles, one per region["sales"].sum()add up the sales in each pile.reset_index()make region an ordinary column again"One row per city" and "one row per region" answer different questions. Know which grain each frame is at — and don't mix them in one table.
| Group by | Rows out | The question it answers |
|---|---|---|
| nothing | 34,079 | What did each individual project cost? |
| region | 18 | Which parts of the country get the money? |
| year | 11 | How has spending moved over time? |
| contractor | 4,841 | Who is doing the work? |
Aggregation is not "making the data smaller" — it is changing what one row means. After a groupby, a row is no longer a project; saying "the average project" about it would be wrong.
Give the output an explicit name. Half of all aggregation bugs are someone treating a summary table as if it were still the original.
.agg(projects=("contract_id", "count"), …)several summaries at once; each is new name = (column, summary)len(by_region) → 18one row per region now: the grain changedA region with a huge total and 12 projects tells a different story from one with the same total across 900.
Sampling means taking a random subset of rows. Sampling is for looking, not for concluding. The one thing that makes it defensible is the seed.
Tasting a spoonful of soup to judge the pot. Stir first (random), and note which spoon you used (the seed) so someone else can taste the same spoonful.
You would report a number nobody — including future you — can obtain again.
random_state=42the seed: a fixed starting number for the random picks, so the same rows come back.groupby("region").sample(n=50, …)take 50 random rows from each region’s pile (the smallest region has 180 projects, so all can give 50)Load the flood CSV. Convert budget with pd.to_numeric and both date columns with pd.to_datetime, all with errors="coerce".
How many became NaN in each conversion? Does the number match what week 5 told you about missing dates?
Make duration. Run .describe(). Find the negative one yourself.
Try cost_per_day = budget / duration. What goes wrong, and which rows cause it?
Step 4 has a trap worth hitting: dividing by a duration of 0 gives inf (infinity), not an error. Count them.
You join 34,079 projects to a region table and get 68,158 rows back. What almost certainly happened?
regions.region.duplicated().any() before joining, and compare len(df) before and after.Why did computing duration reveal an error that checking start_date and completion_date separately did not?
A million rows won't fit in your head. A random sample shows the texture —
and random_state makes it the same sample every run.
Your classmate reruns the notebook and sees your exact rows; your claims about them can be checked. Unseeded randomness makes findings that vanish on rerun.
Techniques like PCA reduce dimensions mathematically — a story for your ML courses. The idea is the same: keep the signal, shed the bulk.
You aggregated to one row per region and deleted the city-level frame to save space. Your professor asks: "Which city drove the Visayas total?" Can you answer?
C — you summed away the answer.
A total of 33 can't tell you it was 24+9. Aggregation destroys within-group detail — which is fine, if the detailed frame still exists. Reduce the working copy; keep the fine-grained one.
Same lesson as dropping columns: reduction is powerful because it discards — so only discard from copies.
Name frames by their grain: sales_by_city, sales_by_region.
The name warns you.
Scrape or call an API — weeks 3–4.
Gaps and outliers, decided on purpose — week 5.
Merge, harmonize, derive — today.
The frame that fits the question — today.
That's CRISP-DM's (week 2) Data Preparation phase, complete. What comes out the far end is the analysis-ready table — one grain, honest values, columns that earn their place.
Exploration: for the next two weeks, that table finally starts answering questions.
pd.concatignore_index renumbers thempd.cutrandom_state=42Two months of sales to stack, a metadata table to join (watch Davao's
NaN), duplicates to drop without losing 2025, and a frame to slim down.
~45 minutes, in the portal’s lab page: fill each ____, press Run, then Check.
pd.concat with ignore_index, pd.merge with
how=, drop_duplicates and its subset= trap,
and column selection.
Aggregate cities into regions with groupby, and take a seeded
sample — then say why the seed matters.
concat for same-shaped rows; merge on a key for new
columns — and read every NaN it produces.
Grown or shrunk counts you can't explain are silent data corruption.
Right types, harmonized labels, derived rates — the table should speak your question's language.
Aggregation and column-dropping are one-way inside a frame — fine, when the detailed frame survives.
One sentence: build one honest table at the right grain — and be able to show how it was built.
Data Preparation done. Exploration begins next week.
pandas User Guide — "Merge, join, concatenate and compare": the diagrams make
how= visual. pandas.pydata.org
Hadley Wickham's "Tidy Data" paper, sections 1–3 — the clearest argument ever written for one-observation-per-row tables.
Both are linked on the course page beside this deck and the lab.
The merge diagrams land harder once you've met Davao's NaN yourself.
Descriptive statistics and univariate EDA — your analysis-ready table starts answering questions, one variable at a time.
DS 227 · Knowledge Discovery in Data