# The Wide View

> Long format is easy. Wide format is useful.

Canonical URL: <https://datadriven.io/problems/the_wide_view>

Domain: Python · Difficulty: hard · Seniority: L4

## Problem

A reporting export arrives in long format: one record per cell, each carrying a `row` label, a `col` label, and a numeric `val`. Reshape it into a nested lookup keyed by row then column, so `result[row][col]` returns that cell's value. Rows are sparse, holding only the columns that actually appear for them, and when the same row and column pair shows up more than once the later record wins.

## Worked solution and explanation

### What this really is

This is a sparse two-level dict build wearing a spreadsheet costume. The skill being probed: can you grow a nested dict whose inner dict may not exist yet, without clobbering rows that share an outer key? Everyone writes the loop. The tell is whether you create `result[row]` exactly once and keep adding columns to it as more records for that same row arrive. Get that wrong and Alice's `sci` entry wipes out her `math` entry.

---

### Break down the requirements

#### Step 1: The row label is the outer key

Each record's `row` field is the outer key. Different records can share the same `row`, so you cannot treat each record as a fresh row.

#### Step 2: Columns map to values inside each row

Within a row, set `result[row][col] = val`. Because rows are sparse, you only ever write the columns that show up; there is no dense matrix to fill.

#### Step 3: Initialize the inner dict once

Create the inner dict the first time a row is seen, and never again. This is the one line that separates a correct answer from one that silently drops columns.

---

### The solution

**Two-level dict construction from flat records**

```python
def pivot_table(records):
    result = {}
    for record in records:
        row = record['row']
        col = record['col']
        val = record['val']
        if row not in result:
            result[row] = {}
        result[row][col] = val
    return result
```

*One pass, inner dict created only on first sight of a row.*

> **Time and Space Complexity**
>
> **Time:** O(n) for n records, each touched once.
> 
> **Space:** O(n) for the nested result, one leaf per distinct row/col pair.

> **Common Pitfall**
>
> Writing `result[row] = {col: val}` instead of `result[row][col] = val`. The first form rebuilds the inner dict on every record, so each new column for a row erases the columns before it; Bob survives but Alice loses a subject. Guard the initialization behind an `if row not in result` (or use `setdefault(row, {})`).

> **Trick to solving**
>
> `result.setdefault(row, {})[col] = val` collapses the guard and the assignment into one line and is the idiom a reviewer expects here. The explicit `if` is equally fine and easier to read aloud in an interview; pick whichever you can explain cleanly.

---

## Common follow-up questions

- What if you need the reverse operation, turning the nested dict back into long-format records? _(Tests iterating the nested dict and producing flat records with row/col/val keys.)_
- What if duplicate row/col pairs should be summed instead of overwritten? _(Tests defining a conflict policy: last-write-wins, sum, or raise.)_
- How would you turn this into a pandas DataFrame, and where do sparse rows bite you? _(Tests pd.DataFrame.from_dict(result, orient='index') and the pivot vs pivot_table distinction.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_wide_view)
- [Python Interview Questions](https://datadriven.io/python-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.