Main Content

Pivot Table

Summarize tabular data in pivoted table in the Live Editor

Since R2023b

expand all in page

Description

The Pivot Table task lets you interactively summarize tabular data in a pivoted table according to column and row groups. The task automatically generates MATLAB® code for your live script. For more information about Live Editor tasks generally, see Add Interactive Tasks to a Live Script.

A pivoted table provides a summary of tabular data. Pivoted tables are useful for analyzing and providing insights into large data sets and organizing data from another perspective, according to column and row groups. The column and row labels of a pivoted table are group names, and the data values are group counts or the result of another computation method. For more information, see Pivoting Operation or watch How to Create Pivot Tables in MATLAB (4 min, 11 sec).

.

Using this task, you can:

  • Create a pivoted table and specify grouping variables to designate pivoted table rows and columns.

  • Focus on a subset of rows by filtering the input table.

  • View sparklines and summary statistics to quickly visualize and interpret the data in each table or timetable variable.

  • Fill the values of the pivoted table by specifying the computation method and table variable.

  • Customize the format and contents of the pivoted table.

  • Visualize the pivoted table in a chart.

Related Functions

Pivot Table generates code that uses the pivot function.

Pivot Table task in the Live Editor

Open the Task

To add the Pivot Table task to a live script in the MATLAB Editor:

  • On the Live Editor tab, select Task > Pivot Table.

  • In a code block in the script, type a relevant keyword, such as pivot, group, crosstab, or heatmap. Select Pivot Table from the suggested command completions. For some keywords, the task automatically updates one or more corresponding parameters.

Examples

expand all

Open Live Script

Interactively compute the group counts for table data for groups defined by a logical grouping variable and a discretized numeric grouping variable using the Pivot Table Live Editor task.

Create a table from a file that contains information about 100 hospital patients.

T = readtable("patients.xls");

Open the Pivot Table task in the Live Editor. To understand the relationship between two variables, specify grouping variables to designate the pivoted table rows and variables. Select the Smoker variable in the Rows panel, and select the Age variable in the Columns panel.

By default, the Pivot Table task fills the data values of the pivoted table with the number of members in each group. The Values panel shows that group counts fill the pivoted table.

There are 25 unique values in the Age variable, which makes the default pivoted table difficult to interpret. Reduce the number of variables in the pivoted table by creating 5 evenly spaced bins for the Age grouping variable. Apply a binning method to Age by clicking the button and specifying the number of bins as 5.

The resulting pivoted table contains the number of elements in each age range for smoking and nonsmoking patients. Visualize the counts in a grouped bar graph using the Chart field.

Open Live Script

Interactively select a subset of tabular data and summarize the filtered data in a pivoted table using the Pivot Table Live Editor task.

Create a table from a file that contains information about 100 hospital patients.

T = readtable("patients.xls");

Open the Pivot Table task in the Live Editor. Expand the Filter rows section, and click the triangle icon in the header of the Smoker variable. Apply a filter to focus only on rows in the input table that correspond to nonsmoking patients. The resulting table is filtered from 100 to 66 rows.

Then, specify the grouping variables. Select the Location variable in the Rows panel, and select the SelfAssessedHealthStatus variable in the Columns panel.

By default, the Pivot Table task fills the data values of the pivoted table with the number of members in each group. Instead, fill the pivoted table with the average age of each group. Select the Age variable and the Mean computation method in the Values panel.

The resulting pivoted table contains the average age of nonsmoking patients reporting each health status for each hospital location. To include the average age for each health status regardless of the location, select Include totals for columns in the Select optional pivot parameters section.

Related Examples

Parameters

expand all

Select the name of the input table or timetable from the list of all the nonempty tables and timetables that are in the workspace.

Apply a filter to focus only on rows in the input table that satisfy the filtering condition. Expand the Filter rows section of the task, click the triangle icon in the header of a table variable, and use the options to interactively select a subset of the data.

For example, pivot only rows corresponding to patients who are at least 29 years old.

Filtering figure for numeric Age variable

Click the Add button. Then, select a table or timetable variable.

  • To aggregate the variable into bins, click the Apply binning method button and select a binning method.

  • To remove or add other grouping variables, click the – or + button to the right of the grouping variable. When you add another grouping variable, a new drop-down list of variable names and any applicable binning options appears below the previous grouping variable.

Select the data variable to apply the computation method to. Then, select a computation method, or select Custom to specify a function handle or write a local function. The resulting data values fill the values of the pivoted table.

Tips

  • The Pivot Table task can apply the computation method to at most one data variable. To apply multiple computation methods or specify multiple data variables, use the Compute by Group task.

Version History

Introduced in R2023b

See Also

Functions

Live Editor Tasks