Data Reshape

Convert data between Wide format and Long format. Use this to restructure data layouts to match the requirements of different analysis methods.

Basic Usage

Opening Reshape

Select Data > Reshape (Wide/Long)... from the menu bar to open a new Reshape tab.

The screen consists of a settings panel on the left and a preview area on the right. Switch between Wide to Long and Long to Wide using the tabs at the top of the settings panel.

Sample Data Used in This Page

The examples on this page use a grades dataset (grades.csv) containing test scores for 5 students. Each subject has its own column in Wide format.

name	math	science	english
Alice	90	85	88
Bob	75	92	80
Carol	88	78	95
Dave	92	88	82
Eve	78	95	90

Wide to Long

Collapse multiple columns into a single column (unpivot). Each row expands into multiple rows.

For example, convert the grades data above into a "one row per subject" format. The result looks like this:

name	subject	score
Alice	math	90
Alice	science	85
Alice	english	88
Bob	math	75
...	...	...

Parameters

Dataset - Select the dataset to transform.

ID Columns - Select columns to preserve through the transformation. These values are repeated in each expanded row. In the example above, name is the ID column. Optional. If omitted, the result contains only the Variable and Value columns.

Value Columns - Select columns to unpivot. In the example above, math, science, and english. At least one column is required.

Variable Column Name - Name for the new column that holds the original column names. Defaults to variable.

Value Column Name - Name for the new column that holds the values. Defaults to value.

Click Preview to see the result.

Long to Wide

Convert Long format data to Wide format (pivot). Multiple rows are collapsed into a single row.

Use this for the reverse of Wide to Long, or to arrange aggregated results into a tabular layout.

For example, convert the Wide to Long result above back to Wide format.

Input data in Long format:

name	subject	score
Alice	math	90
Alice	science	85
Alice	english	88
Bob	math	75
...	...	...

Set Variable Column to subject and Value Column to score. The unique values in subject (math, science, english) each become a new column.

Result:

name	math	science	english
Alice	90	85	88
Bob	75	92	80
...	...	...	...

Parameters

Dataset - Select the dataset to transform.

ID Columns - Select columns to preserve through the transformation. Rows with the same ID values are collapsed into a single row. In the example above, name is the ID column. Select columns that identify which group each row belongs to.

Variable Column - Select one column whose unique values become new column names. In the example above, subject is the Variable Column.

Value Column - Select one column whose values fill the new columns. In the example above, score is the Value Column.

Preview and Save

Click Preview to check the result. The preview shows up to 100 rows along with the total row and column counts.

When the result looks correct, click Save as Dataset. Enter a dataset name and save to create a new derived dataset.

Notes

• In Wide to Long, if Value Columns have different data types, the value column is converted to string type. This includes combinations of int64 and float64. When all selected columns share the same type, that type is preserved
• In Long to Wide, duplicate combinations of ID columns and variable column cause an error. To resolve duplicates, aggregate the data in SQL Editor beforehand, or add columns to ID Columns that uniquely identify each row
• In Long to Wide, missing combinations result in null values

See also

• Datasets - Differences between Primary and Derived Datasets
• Data Processing with SQL Editor - Advanced data transformation with SQL
• Data Table - Viewing and manipulating data