Pivot is short for Pivot Table, and is a standard data analysis technique (see wikipedia). In general, pivoting describes a process of binning data based on categorical or numerical values. The Pivot command in DataGraph allows you to:
- bin data using categories or numerical/date ranges
- count values in a category or range
- calculate summary statistics
- dynamically sort values
The Pivot command creates a bar graph using the pivoted data.
Often, pivoting is a two step process, you create the pivot table and then you use it as a source for a graph or chart. DataGraph does this in one step. The Pivot command computes the pivot table and draws it in the same action.
From the Pivot command, you can also extract the calculated values back into in the DataTable, to use in other drawing commands. These extracted columns remain linked to the original data and are updated when the data changes. This makes the Pivot command a very powerful tool for not only creating graphs, but also analyzing and understanding data.
The input fields on the Pivot command are:
- Rows – categories for binning
- Columns – categories for binning
- Values – numeric data used for summary statistics (optional)
When you create a new Pivot command, the calculation option is ‘Count’.
When one column is selected, the Pivot command will count each unique entry.
The resulting bar graph can show the counts or percent.
The input column can be a text or a number column.
With two columns you can also calculate summary statistics.
With three columns, the data is divided a second set of categories. Add a Legend command to show them on the graph.
Highlight the data columns before adding the Pivot command.
Bin with One Category
When a category has more than one value, the data is binned and the bar graph can show a variety of summary statistics (i.e., mean, median, minimum, count, range, etc.).
In this example, we have a single column of data that we want to group by a categorical column and calculate the mean and standard error on the mean (SEM).
- Highlight two columns (category, number)
- Select the Pivot command shortcut
- Change the Calculation to ‘Mean + SEM’
Bin with Two Categories
Group the data based on two categories.
- Highlight three columns (category, category, number)
- Select the Pivot command shortcut
- Add a Legend command
Mapping and Grouping Data
The Pivot command has two drop-down boxes called Map rows and Map Columns that allow you to further group and map data. The menu options for these drop-down boxes vary depending on the type of data specified in the Rows and Columns drop-down boxes.
The default setting is ‘All labels’, meaning that all of the data in the pivoted data is included in the resulting bar graph.
Using the ‘Specified’ option you can:
- Change the order of entries
- Limit or add entries
In addition, using the ‘Columns’ option you can:
- Replace an entry with a label
- Group data based on the new label
If you change Map rows/columns to ‘Specified’ an entry bar appears where you can manually enter the data groups you want displayed. Essentially, this is working similar to a mask, where we are limiting the data in the graph.
The order can also be specified from a column. Change the Map rows to ‘Column(s)’ and select the column with the order from the data table.
The default setting is ‘Automatic’. In this setting, DataGraph will bin the numeric data based on the input. If you want to change how the data are binned several options are available. You can also specify numeric values to include using the ‘Specified’ or ‘From Column’ options, which are similar to those described in the previous section for text columns.
In this case, you can group the data over time by years, quarters, months, etc., or you can group the data by a number representing quarters, months, weeks, and days.
By default, the menu is set to ‘Automatic’ and sorts alphabetically for text data.
Change the sort option to ‘Increasing’ or ‘Decreasing’ to update the order dynamically.
The available sorting options will depend on the type of data and how it is grouped.
The values calculated in the Pivot command can be viewed and examined by expanding the view.
Here you also see the overall total, although it is not shown in the graph.
Using the Gear menu you can extract the result back into the data table, to use in other commands.
You can extract the data with either group as the column headings (pivot rows versus pivot columns).
You can extract a flattened version, where each value is in a single row. Any of the column names can be changed.
These extracted columns will remain linked to the original data. If that data is changed, the extracted values update automatically.