Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Data Cleanup  ›  Capstone Project

Data Transformation

Data Transformation 14 minTransformation reshapes values into a consistent, comparable form, by fixing formats and units, deriving new columns, and rescaling numbers.

Data Transformation

Kettle & Crumb opens a second branch, and its till exports data its own way: dates like 03/06/2025 stored as text, prices in paise, and a column called Qty Sold. You cannot stack the two tables, compare the branches, or chart them together until they speak the same language. Transformation is the step that makes values consistent and comparable.

The rule

Same meaning, same form: standardise, derive, then rescale.

1. Standardise so that equal things look equal:

  • Types: text that looks like a date or number becomes a real date or number.
  • Formats: one date format (2025-06-03), one spelling for categories (Cold coffee, not cold coffee and Cold Coffee).
  • Units: one currency and scale. 12,000 paise ÷ 100 = ₹120.
  • Names: matching column names, such as qty in both tables.

2. Derive new columns from existing ones:

  • weekday from order_date, to compare weekends with weekdays.
  • revenue = qty × price.
  • Bins: turning a number into bands, like order size Small / Medium / Large.
  • branch, added before stacking two tables, so each row keeps its origin.

3. Rescale when numbers on different scales must be compared:

  • Min-max scaling squeezes a column into 0 to 1: (x − min) ÷ (max − min).
  • z-scores say how many standard deviations a value is from the mean: (x − mean) ÷ standard deviation. For the week's sales (mean 48, standard deviation 21), a day of 69 cups has z = 1.
  • Aggregation, such as daily rows summed to weekly totals, is also a transformation: it changes the level of detail.

The shortcut

Before combining any two sources, check type, format, unit and name for every column you will use. A mismatch in any one of them breaks a join, a sort or a sum.

Stacking branch 2 under branch 1Step 1 of 4

  1. Type and format

    03/06/2025 is text in day-month-year order. Convert it to the date 2025-06-03.

The trap is ambiguous dates. 03/06/2025 is 3 June in India and the UK but 6 March in the US. Confirm the source's convention before converting, or every month's figures shift.

Drills

Answer fast — trust the rule.

A day sells 69 cups. With the week's mean of 48 and standard deviation of 21, what is its z-score?

What's the answer?
(69−48)÷21= ?(69 - 48) \div 21 = \,?
quick drill

Using min-max scaling on the week (min 36, max 95), what does Saturday's 95 become?

quick drill

Branch 2 records an amount of 22,000 paise. What is it in rupees?

quick drill

You want to compare weekend and weekday sales. Which transformation helps most?

quick drill

A US supplier's file has 04/07/2025. Read with the US convention, which date is it?

Remember

  • Same meaning, same form: standardise, derive, then rescale.
  • Shortcut: check type, format, unit and name before combining sources.
  • Trap: ambiguous dates; confirm day-first or month-first before converting.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainCombine two exports

A second branch sends sales with dates as 03/06/2025 text, prices in paise, and the column name 'Qty Sold'. Describe the transformations needed before stacking it under the main orders sheet, and one derived column you would add.