Reshaping

The previous lesson gathered three tables into one dataset, and what remained was 3,199 rows, each carrying one subscriber’s reading for one period. This shape answers some questions directly: “what is the total usage” is the sum of a single column. It does not answer others. The question “how much did a subscriber’s usage change between January and March” requires finding two separate rows of the same subscriber and placing them side by side, and that cannot be done directly on a row-based dataset.

This lesson changes the shape of the dataset. A change of shape is not a calculation; no value is summed, divided, or filtered out. Even so, the row count changes, and in some cases values disappear too. The measure of the lesson is this: is the conversion lossless, that is, when the data is converted to wide format and back, do we arrive at the original dataset again?

• TF6. Long format makes every measurement a row: key columns (subscriber and period), a variable name, and a value. Wide format moves part of the key into the column header: one row is one subscriber, and three columns are three periods. The dataset is the same dataset; the row count is not.
• TF7. In wide format the row count equals the number of unique keys, and the cell count equals the product of rows and columns. Every cell of the product that has no counterpart in long format stays empty.
• TF8. Wide format carries a single cell for the same key pair. If a subscriber and period pair appears twice in long format, the second value overwrites the first and no warning is produced.
• TF9. On the way back, the empty-cell decision is as decisive as the conversion itself: empty cells can be skipped, filled with a value, or, through a wrong test, can take genuine values down with them.
• TF10. Losslessness is something that can be measured: the dataset that comes back is compared with the original in row count, key set, and total. If all three are the same, the conversion is lossless.

The Same Dataset, Two Shapes

// shape.mjs — MODEL: the long-format dataset produced by the previous lesson and the wide
// format of the same dataset. The dataset is fictional; no interface is imitated.
export const SEED = 20260218, RAW = 1400, PERIOD = ["2026-01", "2026-02", "2026-03"];
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const pick = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
const REGION = [["north", 0.28, 21], ["south", 0.22, 17], ["east", 0.18, 26],
  ["west", 0.14, 14], ["central", 0.18, 23]];
const SUBSCRIBER = [], DROPPED = [];
for (let i = 0; i < RAW; i++) {
  const r = rng(SEED + i), b = REGION[pick(r(), REGION.map((x) => x[1]))];
  const k = { subscriber_id: 10001 + i, region: b[0], base: b[2],
    household_size: pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05]),
    satisfaction: pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1 };
  (r() < 0.046 ? DROPPED : SUBSCRIBER).push(k);
}
const keys = new Set(SUBSCRIBER.map((a) => a.subscriber_id));
// Rows that passed the previous lesson's inner join: the 169 unmatched readings are gone,
// the rows that carry the same subscriber+period pair a second time are still inside.
export const DUPLICATED = [];
for (const k of [...SUBSCRIBER, ...DROPPED]) {
  const r = rng(SEED + 7000 + k.subscriber_id), n = pick(r(), [0.05, 0.12, 0.21, 0.62]);
  for (let d = 0; d < n; d++) {
    const t = r() < 0.038 ? 0 : Math.round(k.base * Math.exp((r() + r() + r() - 1.5) * 0.62)
      * (1 - d * 0.05) * 100) / 100;
    if (!keys.has(k.subscriber_id)) { r(); continue; }
    DUPLICATED.push({ subscriber_id: k.subscriber_id, region: k.region, period: PERIOD[d], usage_m3: t });
    if (r() < 0.028) DUPLICATED.push({ subscriber_id: k.subscriber_id, region: k.region, period: PERIOD[d],
      usage_m3: Math.round(t * 1.02 * 100) / 100 });
  }
}
// Closing dataset of the previous lesson: one subscriber+period pair is one row.
const first = new Map();
for (const o of DUPLICATED) { const a = `${o.subscriber_id}|${o.period}`;
  if (!first.has(a)) first.set(a, o); }
export const LONG = [...first.values()];

// Widening: part of the key moves into the column header. A value written to the same
// cell a second time overwrites the first; the operation produces no error.
export const widen = (long) => {
  const rows = new Map();
  let written = 0, overwritten = 0;
  for (const o of long) {
    if (!rows.has(o.subscriber_id)) rows.set(o.subscriber_id,
      { subscriber_id: o.subscriber_id, region: o.region, ...Object.fromEntries(PERIOD.map((d) => [d, null])) });
    const s = rows.get(o.subscriber_id);
    if (s[o.period] !== null) overwritten++; else written++;
    s[o.period] = o.usage_m3;
  }
  return { wide: [...rows.values()], written, overwritten };
};
export const gen = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");

if (import.meta.url === `file://${process.argv[1]}`) {
  const G = widen(LONG), Y = widen(DUPLICATED);
  const cells = G.wide.length * PERIOD.length;
  console.log(`model (seed ${SEED}): long format ${LONG.length} rows, ` +
    `${G.wide.length} unique subscribers, ${PERIOD.length} periods`);
  const y = gen([14, 10, 12, 12, 12]);
  console.log("\nwide format (first five rows)");
  console.log(y(["subscriber_id", "region", ...PERIOD]));
  for (const s of G.wide.slice(0, 5))
    console.log(y([s.subscriber_id, s.region, ...PERIOD.map((d) => s[d] === null ? "-" : s[d])]));
  console.log(`\n${G.wide.length} rows x ${PERIOD.length} columns = ${cells} cells; ` +
    `${G.written} filled, ${cells - G.written} empty`);
  console.log(`the shape of the input changed, row count ${LONG.length} -> ${G.wide.length}`);
  console.log(`\nif the duplicated dataset is widened: in ${DUPLICATED.length} rows, ` +
    `out ${Y.wide.length} rows, filled cells ${Y.written}`);
  console.log(`${Y.overwritten} rows were written to the same cell a second time and overwrote the ` +
    `first; no warning was produced`);
}

model (seed 20260218): long format 3199 rows, 1260 unique subscribers, 3 periods

wide format (first five rows)
subscriber_id     region     2026-01     2026-02     2026-03
10001            central       30.09       24.65           -
10002              north       20.21       22.14       21.57
10003              south       13.42       14.12       18.75
10004              north       20.01       20.29       17.48
10005              south       18.29           -           -

1260 rows x 3 columns = 3780 cells; 3199 filled, 581 empty
the shape of the input changed, row count 3199 -> 1260

if the duplicated dataset is widened: in 3302 rows, out 1260 rows, filled cells 3199
103 rows were written to the same cell a second time and overwrote the first; no warning was produced

Wide format and long format carry the same 3,199 measurements. The difference is where a measurement is read from: in long format the period is a value, in wide format it is a column name. The question “the change between January and March” is the difference of two columns in a single row in wide format; in long format it requires matching two rows.

The price of the shape is 581 empty cells. These are not a deficiency but combinations that never existed in long format: not all of the 1,260 subscribers were read in all three periods. Wide format makes a missing row visible by turning it into an empty cell; in long format that combination did not exist at all and could not be counted. This is a real gain of wide format: a gap becomes countable only once the set of expected combinations has been built.

Neither of the two formats is the correct one; the question decides which is suitable. Wide format has a limit, and it has nothing to do with the row count: the number of columns equals the number of unique values of the key that moved into the header. Three periods make three columns, thirty-six periods make thirty-six columns, and if the number of periods is not known in advance, the schema of the table changes together with the data. In long format the same growth produces not new columns but new rows, and the schema stays fixed. For this reason the output of preparation is mostly stored in long format, and wide format is produced only for a specific question, temporarily.

The last two lines show the silent loss of the lesson. If the duplicates that the previous lesson removed had not been removed, there would be 3,302 rows, and when that dataset was converted to the same wide format it would still produce 1,260 rows and 3,199 filled cells. 103 rows were written to the same cell a second time and overwrote the first. The operation completed successfully, no error was produced, and the drop in row count was a drop that was expected anyway. Converting to wide format can therefore be used as a deduplication tool; when it is used that way, nobody has chosen which value stays.

Is the Way Back Lossless

// round-trip.mjs — three decisions for converting from wide format back to long format and
// the difference of each from the original dataset. Losslessness is measured by rows, keys, and total.
import { LONG, PERIOD, widen, gen } from "./shape.mjs";

const { wide } = widen(LONG);
// Y1 skips empty cells. Y2 fills empty cells with zero. Y3 tests a cell for "truthiness";
// this test counts zero as empty too.
const PATH = [
  ["Y1 empty cells are skipped", (v) => v !== null],
  ["Y2 empty cells are filled with zero", () => true],
  ["Y3 cell passes the truthiness test", (v) => Boolean(v)],
];
const lengthen = (keep) => {
  const out = [];
  for (const s of wide) for (const d of PERIOD) {
    if (!keep(s[d])) continue;
    out.push({ subscriber_id: s.subscriber_id, period: d, usage_m3: s[d] ?? 0 });
  }
  return out;
};
const T = (a) => a.reduce((x, o) => x + o.usage_m3, 0);
const originalT = T(LONG), zeros = LONG.filter((o) => o.usage_m3 === 0).length;
console.log(`original long format: ${LONG.length} rows, ${originalT.toFixed(1)} m3, ` +
  `containing ${zeros} genuine zero readings`);

const y = gen([38, 9, 8, 11, 10]);
console.log("\n" + y(["convert-back decision", "rows", "diff", "total m3", "mean"]));
for (const [name, keep] of PATH) {
  const g = lengthen(keep), f = g.length - LONG.length;
  console.log(y([name, g.length, f > 0 ? `+${f}` : f, T(g).toFixed(1),
    (T(g) / g.length).toFixed(3)]));
}
// Losslessness test: are row count, key set, and total the same? Because the total is
// floating point, a threshold is used instead of equality; 0.05 m3 was chosen
// because readings are recorded to 0.01 m3 precision and the total has 3199 terms.
const LIMIT_M3 = 0.05;
const keySet = (a) => new Set(a.map((o) => `${o.subscriber_id}|${o.period}`));
const originalK = keySet(LONG);
console.log("\n" + gen([38, 9, 10, 10, 10])(["lossless test", "rows",
  "keys", "total", "lossless"]));
for (const [name, keep] of PATH) {
  const g = lengthen(keep), a = keySet(g);
  const s1 = g.length === LONG.length;
  const s2 = a.size === originalK.size && [...originalK].every((k) => a.has(k));
  const s3 = Math.abs(T(g) - originalT) < LIMIT_M3;
  console.log(gen([38, 9, 10, 10, 10])([name, s1 ? "same" : "different",
    s2 ? "same" : "different", s3 ? "same" : "different",
    s1 && s2 && s3 ? "yes" : "no"]));
}
console.log(`\nin all three paths the total stayed at ${originalT.toFixed(1)} m3; ` +
  `the mean moved between 16.683 and 20.474 m3`);

original long format: 3199 rows, 63060.6 m3, containing 119 genuine zero readings

convert-back decision                      rows    diff   total m3      mean
Y1 empty cells are skipped                 3199       0    63060.6    19.713
Y2 empty cells are filled with zero        3780    +581    63060.6    16.683
Y3 cell passes the truthiness test         3080    -119    63060.6    20.474

lossless test                              rows      keys     total  lossless
Y1 empty cells are skipped                 same      same      same       yes
Y2 empty cells are filled with zero   different different      same        no
Y3 cell passes the truthiness test    different different      same        no

in all three paths the total stayed at 63060.6 m3; the mean moved between 16.683 and 20.474 m3

Only Y1 is lossless: its row count, key set, and total are all the same as the original dataset’s. The other two paths also run without error, and both give the same total.

Y2 fills the empty cell with zero and invents 581 rows. These rows are not wrong; the assumption is wrong: a period that was not read is not a period in which zero was consumed. The key set contains all of the original but has 581 extra, so it fails the test. Because the total does not change, the error does not show in the total; it shows in the mean, which falls from 19.713 m³ to 16.683 m³. The figure has moved by more than 15%, and not a single reading has changed.

Y3 is the most insidious decision in the lesson, and it comes from a coding habit: whether a cell is filled has been asked by checking whether its value passes a truthiness test. Zero does not pass this test. The result is that 119 genuine zero readings are deleted together with the empty cells. The key set breaks here in the opposite direction: the set that comes back is missing 119 keys of the original. The total again does not change, because the deleted readings have a value of zero; the mean, however, moves the other way, up to 20.474 m³.

The three paths have the same total and different means. This shows why losslessness cannot be tested with the total: adding or deleting rows whose value is zero does not move the total at all. A losslessness test therefore requires three separate comparisons: row count, key set, and total. Because the total comparison is floating point, it is made not with equality but with a threshold of 0.05 m³; the threshold was chosen from the 0.01 m³ precision of the readings and from the fact that the total has more than three thousand terms.

Summary

• Long format makes every measurement a row, and wide format moves part of the key into the column header: the same dataset moves between 3,199 rows and 1,260 rows without a single value changing.
• In wide format, 1,260 rows and three columns produce 3,780 cells; 3,199 are filled and 581 stay empty, and these empty cells make a gap countable for the first time.
• A dataset of 3,302 rows that carries repeated subscriber and period pairs does not fit the same wide format: 103 rows are overwritten without any warning, and nobody has chosen which value stays.
• The three decisions on the way back give three row counts: 3,199, 3,780, and 3,080. Only the first is lossless.
• Filling empty cells with zero lowers the mean from 19.713 to 16.683 m³; the truthiness test deletes 119 genuine zero readings and raises the mean to 20.474 m³. In all three paths the total is 63,060.6 m³.

Next Step

Reshaping did not sum a single measurement; when 3,199 rows fell to 1,260 rows, every value was still in its own cell. The number that goes into a report, however, is rarely a cell. The question “average usage per region” asks to reduce thousands of rows to five rows, and that reduction cannot be undone: someone who looks at five rows cannot see the distribution behind them, only the number the summary left behind. The next lesson counts the rows that grouping produces and measures how much of the variability within each group disappears together with the summary.