UPCOMING WEBINAR: Finding the story in your survey data This article describes how to perform basic mathematical functions on tables in Displayr using the Calculation menu. These functions include Divide, Multiply, Subtract, Average, Sum, Maximum, Minimum, Any of, None of, Count, Standard Deviation, and Variance.
For example, we can go from a table like below...
...to one that has been divided by 2...
...or a table that calculates the average of each column:
For details of each mathematical calculation and options see Mathematical Calculations on Tables and Variables.
Requirements
- A Displayr document with a data set.
- One or two tables or tabular outputs.
Method - Divide, Multiply, Subtract
1. Select the table(s) you wish to perform the calculation on. Note, the order you select these tables determines where they are placed in the calculation.
When working with multiple tables, the calculation will be performed across the tables for each cell provided they have common row and column labels and the same dimensions. In this example, we will select a single table only.
2. Go to Calculation > Divide, Multiply, or Subtract in the toolbar.
3. OPTIONAL: In the object inspector, you can adjust which Rows/Columns to exclude by name. By default, all rows and columns apart from NET and SUM will be selected.
4. OPTIONAL: You can change the selected outputs by using the relevant drop-down.
5. OPTIONAL: If you only select a single table, it will default to a Single numeric value of 1 for the second reference. You can change this value as required to update your calculation. Here, we have changed this to 2.
6. OPTIONAL: You can additionally switch either reference between an Output and a Single numeric value if required.
7. OPTIONAL: Update Appearance > Appearance > Decimals and Number format as required.
Method - Average, Sum, Maximum, Minimum, Any of, None of, Count, Standard Deviation, Variance
1. Select the one or more tables you wish to perform the calculation on. Note, the order you select these tables determines where they are placed in the calculation.
2. Go to Calculation > Average/Sum/Maximum/Minimum/Any of/None of/Count/Standard Deviation/Variance in the toolbar. You can then choose from any of the below options:
- Average
- Average Each Row/Column
- Sum
- Sum Each Row/Column
- Maximum
- Maximum Each Row/Column
- Minimum
- Minimum Each Row/Column
- Any of
- Any of Each Row/Column
- None of
- None of Each Row/Column
- Count
- Count Each Row/Column
- Standard Deviation
- Standard Deviation Each Row/Column
- Variance
- Variance Each Row/Column
Note, on a single table, the options without Each Row or Each Column will return a single value from all cells. When working with multiple tables, the calculation will instead provide averaged or summed values across the tables for each cell provided they have common row and column labels and the same dimensions and statistics. The row and column-specific functions, on the other hand, can only be performed on a single table. In this example, we will perform a column average on one table.
3. OPTIONAL: In the object inspector, you can adjust which Rows/Columns to exclude by name. By default, all rows and columns apart from NET and SUM will be selected.
4. OPTIONAL: You can change the input table in the drop-down as required. If using Average, Sum, Maximum, Minimum, Any of, None of, Count, Standard Deviation, or Variance, you can also add or remove inputs.
5. OPTIONAL: If you select more than one input table, you will have the additional option to Automatically match elements.
You can choose between the following options:
- Yes - hide unmatched - keeps only the matching rows/columns. For a full description of the matching algorithm, see the Technical Details.
- Yes - show unmatched - shows both the matching rows/columns and the unmatched rows/columns by appending the latter like a merged table.
- No - does not match on labels.
- Custom - lets you apply different settings for columns and rows.
6. OPTIONAL: Match rows is only shown if Automatically match elements is set to "Custom". Specifies the matching behavior when comparing row labels of the inputs. "Yes - show unmatched" and "Yes - hide unmatched" look for exact matches in the row labels in the inputs. "Fuzzy - show unmatched" and "Fuzzy - hide unmatched" perform fuzzy matching so that labels that differ only by a single character are considered to be a match.
7. OPTIONAL: Match columns is only shown if Automatically match elements is set to "Custom". The options are the same as Match rows, but control the matching between columns.
7. OPTIONAL: Untick Calculate for inputs with incomplete data if you wish to return missing for any record that has missing data in any of the selected variables. If this option is checked, then any missing values in any of the inputs will be ignored in the calculation.
8. OPTIONAL: For all versions of the Any of, None of, and Count options where you are working with numeric data, you can additionally adjust Values to count. This takes the format of individual specified values or a range of values, e.g. 0-10 or 1,2,3 or >1. When working with categorical data, you instead specify the Categorical labels to include.
9. OPTIONAL: For all versions of the Variance options, the Variance formula / Standard Deviation formula option allows you to choose whether the Population or Sample formula is used to compute the Variance or Standard Deviation (see Technical Details below).
10. OPTIONAL: Update Appearance > Appearance > Decimals and Number format as required.
Technical details
For all versions of Variance, the default option is to compute the variance or standard deviation using the sample variance formula rather than the population variance formula. You have the option to choose between these two formulas so that you can apply whichever is relevant to your calculation. The standard deviation is the square root of the variance, and the two variance formulas are:
Sample variance
\[\begin{align} \sigma_{sample}^2=\frac{\sum^n_{i=1}(x_i -\frac{\sum^n_{i=1}x_i }{n})^2}{n-1}\end{align}\]
Population variance
\[\begin{align} \sigma_{population}^2=\frac{\sum^n_{i=1}(x_i -\frac{\sum^n_{i=1}x_i }{n})^2}{n}\end{align}\]
When there are multiple inputs, inputs that contain only a single row (column) may be recycled to a matrix/table with the same number of rows (columns) as the other inputs. For example, if the supplied inputs are a table with three rows and two columns and another table with two rows and a single column, the single column will be expanded by rows into a table with three rows and two columns with each row identical to the original column.
When Automatically match elements is set to Yes - show unmatched or Yes - hide unmatched, both exact matches and fuzzy matches (as described above) are considered, and the order of elements may be permuted so that the names match. It also may transpose an input if, for example, the column names of one input match the row names of another input.
Next
How to Perform Mathematical Calculations on Variables