Wide and long form

The same numbers can be laid out two ways, and the choice matters more than it looks.

# WIDE — a column per day
  city    mon  tue  wed
  Delhi   120   95  110
  Mumbai   80   60   90

# LONG — a row per observation
  city    day  cups
  Delhi   mon   120
  Mumbai  mon    80
  Delhi   tue    95
  ...

Wide is compact and reads well — it's how a spreadsheet would hold it. Long has one row per observation, with the day as a value rather than a column name.

Why long form wins for computing

Try to answer "what's the average across days" in wide form. The days aren't data — they're column names — so there is nothing to group by:

wide.groupby("day")     # KeyError — there is no "day" column
long.groupby("day")["cups"].mean()   # works

That's the whole argument. Every operation you've learned — groupby, filtering, pivot_table, merging, plotting — assumes long form. Data in wide form has to be reshaped before most of pandas will touch it.

melt: wide to long

wide.melt(
    id_vars="city",        # columns to KEEP as they are
    var_name="day",        # what to call the old column names
    value_name="cups",     # what to call their values
)

Everything not listed in id_vars gets folded down. Name value_vars too if you only want some of them melted.

Without var_name and value_name you get columns called variable and value, which nobody wants in a report — so pass them.

pivot: long to wide

long.pivot(index="city", columns="day", values="cups")

The inverse. It only reshapes — if a (city, day) pair appears twice it raises ValueError: Index contains duplicate entries, because it has no instruction for combining them. That's when you want pivot_table, which aggregates.

How to spot a wide table

The tell is column names that are data:

• 2021, 2022, 2023 — a year is data
• q1, q2, q3, q4 — so is a quarter
• delhi, mumbai, pune — and a city
• one column per survey question

The test: would you ever want to filter or group by what a column is called? If yes, that name belongs in a column.

The working shape

Melt on the way in. Do the work. Pivot at the very end, once, for the table a human reads. Most of the time someone finds pandas awkward, they're fighting a table that's in the wrong shape rather than the API.

This is what "tidy data" means: one variable per column, one observation per row. Long form is tidy form.