Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 31 ยท Data Preparation and Design

Preparing Data: Tidy Format and Reshaping

Prepare data for Plotly, Bokeh and Altair: wide versus long tables, pandas melt and pivot, clean column types and a plan for missing values before you chart.

Most charting problems are not charting problems. A line chart with one stray series, a legend with a column called Unnamed: 3, a bar chart that sorts months alphabetically, a gap where a number was stored as text: all of these come from the table, not from the library. Spending a few minutes shaping the data before you chart saves hours of tweaking afterwards.

This lesson describes the table shape that works best with Plotly, Bokeh and Altair (called tidy or long format), shows how to get there with pandas, and covers the two other preparation chores: column types and missing values. The pandas side is covered in more depth in the pandas reshaping lesson, and you can run every example here.

The examples on this page use real pandas in the in-browser Python runner. They prepare data; they do not draw. Plotly, Bokeh and Altair themselves cannot run on this site, so the chart calls shown in plain code blocks are read-only.

Wide versus long

A wide table puts one variable's values in several columns:

month north south
Jan 12 8
Feb 15 11

A long (tidy) table has one row per observation and one column per variable:

month region units
Jan north 12
Jan south 8
Feb north 15
Feb south 11

The three tidy-data rules are: every variable is a column, every observation is a row, and every kind of observation has its own table. In the wide table, "region" is a variable, yet its values hide in the column headings.

flowchart LR
    W["Wide<br/>month | north | south"] -- "melt" --> L["Long<br/>month | region | units"]
    L -- "pivot" --> W
    L --> A["Altair: color='region'"]
    L --> P["Plotly Express: color='region'"]
    L --> B["Bokeh: one source, filter or group"]

Long data lets a single column name drive color, facets and legends in all three libraries.

Why the libraries want long data

The reason is the grammar of charts you met in the Altair introduction: colors, facets and legends are produced by mapping a column to a channel. To get one line per region you need a column that says which region each row belongs to.

  • Altair effectively requires long data. A wide table needs transform_fold (the in-chart equivalent of melt) before it can color by series.
  • Plotly Express is friendlier: px.line(wide_df, x="month", y=["north", "south"]) works on wide data and builds one trace per column, but long data with color="region" gives you the same result and more flexibility (facets, hover columns, symbols).
  • Bokeh draws from a ColumnDataSource, a table of columns. It has no automatic color-by-column for lines, so you usually filter or group the long table and draw one glyph per group, or pivot to one column per line.

The practical rule is: keep your data long, and reshape to wide only when a particular library needs it.

melt, pivot and groupby

melt turns column headings into a column of labels. pivot does the opposite. groupby summarizes a long table, which is exactly the job of many chart types.

Python Wide to long, summarize, and back

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The sorting remark in the comments matters for charts: pivot returns the months in alphabetical order (Feb, Jan, Mar). A time axis should be sorted by real dates, not by label text, so convert month names to dates or keep an explicit order, as shown in the next section.

Notice what transform("sum") did: it kept all rows and attached the month total to each one, which made the share calculation a single line. That is the same idea as joinaggregate in Altair transforms.

Column types and order

A chart library treats a column according to its type, so a column of dates stored as text, or numbers stored as text, quietly produces the wrong axis. Check types early, then fix them.

Python Fixing types before charting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

errors="coerce" turns anything it cannot parse into a missing value (NaT for dates, NaN for numbers) instead of stopping with an error. That is convenient, but it can hide a data-entry problem, so count the rows that became missing and look at them before you move on.

An ordered categorical keeps categories like small, medium and large in a sensible order. In Altair you would say sort=["small","medium","large"] or use an ordinal type; in Plotly, category_orders={"size": [...]}; in Bokeh, the figure's x_range list. Setting the order once in pandas is simpler than repeating it for every chart, although some libraries still need it in their own options.

Missing values

Missing values show up as gaps, as zeros or as crashes, depending on the library. Decide what a missing value means before you pick a fix.

Meaning Reasonable handling
Not measured, unknown Leave it missing; a line chart shows a gap, which is honest
Measured as zero but absent from the export Fill with 0
Obvious data-entry error Correct or drop the row, and record that you did
A series that stops early Leave it missing; do not extend it

Filling gaps with the average or interpolating can be right for a smooth sensor reading and wrong for sales. Whatever you choose, say so on the chart or in the notes.

Python Counting and handling missing values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Libraries differ in how they draw a gap: some break the line, some connect across it, and some drop the point. Plotly has a connectgaps setting for lines, for example. Draw a small test chart with a known gap before you assume the result looks right.

A short preparation checklist

  1. Look first: df.head(), df.dtypes, df.describe(include="all").
  2. One row per observation: melt wide column groups; check for duplicates with df.duplicated().sum().
  3. Fix types: dates to datetime, numbers to numeric, categories with an explicit order.
  4. Handle missing values on purpose, and keep a count of what you changed.
  5. Name columns for readers: titles and legends often reuse column names, so units_sold beats col3. You can rename at chart time, but one clean source is easier.
  6. Aggregate before charting large data: send the library a summary rather than a million rows (see the large datasets lesson).

A tidy table is also a great input for tests. Checking that a DataFrame has the columns and types you expect catches most chart bugs before they reach the browser; the testing lesson shows how.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which pandas call turns the columns north, south and east into a region column and a units column?

    melt converts column headings into values of a new column (var_name) and the cell values into another (value_name), producing long data.
  2. 2. Why is long data usually better for Altair and Plotly Express?

    Both libraries map columns to visual channels. A column that names the series makes color-by-series a one-word change.
  3. 3. What does pd.to_numeric(series, errors="coerce") do with the text "n/a"?

    coerce converts anything unparseable to NaN. Count those rows afterwards so you notice real data problems.
  4. 4. A sales export omits days when nothing sold. What is the right way to treat the missing days?

    The right fix depends on the meaning. Zero for 'nothing happened' is correct here; interpolation would invent sales that did not happen.

Summary

  • Tidy (long) data has one row per observation and one column per variable; it is the shape all three libraries handle best.
  • melt goes from wide to long, pivot goes back, and groupby with transform creates the summaries that charts show.
  • Fix column types early: real datetimes, numeric columns, and ordered categoricals for sensible sorting.
  • Decide what a missing value means before filling it, count what you changed, and know how your library draws gaps.
  • Keep the source table clean and reshape per library only when needed.

Next: Time Series, Maps and Other Special Charts