How to Use Calculation Grid in Displayr

Calculation Grid brings Excel-like functionality into Displayr. It is designed to work like standard built-in drag-and-drop tables, which allow you to auto-size with its contents, apply table styles, be referenced in R with square brackets, etc. However, unlike built-in tables, individual cells can be edited directly and you can even use copy and paste to populate them. Things you would normally do with table rules, like adding and removing rows and columns, can be done with a click.

Typically calculation grids are used to:

• Combine different types of information from multiple tables. This should not be used in place of combining variables into sets or using Combine Tables to combine full tables together.
• Do ad hoc calculations on the fly.
• Append outside data/calculations to tables in Displayr.

We walk through a typical example of when to use a Calculation Grid on our blog, but an example of a Calculation Grid is below:

This article covers the following features of Calculation Grids:

• Calculation Grid User Interface
• Navigating Calculation Grid
• Editing Cells
• Programming in R
• Manipulating Rows & Columns
  • Adding Rows / Columns
  • Inserting Rows / Columns
  • Deleting Rows / Columns
  • Reordering rows or columns
• Modifying the Calculation Grid Properties
  • Changing the font
  • Changing the Table Style
  • Changing the Number of Decimals
  • Changing the Number Format

Requirements

Please note this requires the Data Stories module or a Displayr license.

Calculation Grid User Interface

To create a grid on a page: 

• In the toolbar, select 
  • Calculation > Calculation Grid or
    
    
    
    

  • Table > Calculation Grid or
    
    
    
    

  • Anything > Calculation > Calculation Grid
    
    
    
    The mouse pointer will change to a cross, and from that point, you can create your grid.
    

• Click on the page, and an empty 2 x 2 grid is created (including Row and Column headings) 
  
  
• For a larger grid, click on the page and drag the pointer down and to the right until you get the desired number of rows and columns. You will get visual feedback showing the number of rows or columns as you drag the mouse, as well as a counter near your cursor.
  The results are as follows:
  
  
  

Each cell, including the row, heading, column heading, and the "statistic" cell (top-left) are all R calculations. 

Each cell has its own name, created by default.  

Demonstration: Creating a New Calculation Grid

 

Navigating Calculation Grid

The are a number of useful Keyboard Shortcuts available to help you move around in the Calculation Grid. They are as follows:

 

 

 

Editing Cells in Calculation Grid

You can edit cell values either by double-clicking, typing F2 or using the code editor in the object inspector.

For example, if I click the Row Label 1 from this grid, and then right-click on the cell, the R CODE window on the right shows the calculation, which in this case is "Row Label 1". Because Row Label 1 is a text value, it is entered between double-quotes. 

 

Numeric values don't require quotes:

 

Editing cells in Object Inspector:

• Select the table
• Click the cell you want to edit
• Edit it in the R Code window as shown above
  
  

Double-clicking a cell to edit:

• Double-click the cell
• You can directly enter your code. This includes row or column headers, including the top left cell.
  
  
  
  If you have a lot of code to write, continue typing your code and the cell with automatically expand to the right.  If you need additional lines, hold down the ALT key and keep typing the Enter key until you get the desired number of lines.
  
  
  
  

Using the F2 key to edit a cell

Instead of double-clicking, you can use the F2 key to switch to the code editor.

The steps are as follows:

• Click the cell you want to edit. 
• Type F2 on your keyboard
• Type your new code in the cell
• Type ESC to exit the cell

For example, 

Be sure you enter syntactically correct R Code. For example, to enter text, you must enter it in "double quotes".

Perform calculations on the cells on tables

Excel-like calculations, where the inputs are cells in a table, are done by point-and-click. In this example, the detractor percentage is subtracted from the promoter percentage.

To recreate the same sort of operation yourself:

• Click on Calculation in the toolbar (alternatively, select from the Calculation menu, select Custom Code)
• Click on the page where you wish to display your output and/or R code.
• Click the Promoters percentage, type a subtraction sign, and click the Detractors percentage
• Click Calculate in the Object Inspector on the right.
  
  

 

 

Use formulas with code completion

In addition to doing simple math, you can also use formulas (e.g., Sum, SumEachRow). A few tips:

• As you type the function, Displayr will present a list of its best guesses and you can click on them (see the screen recording below).
• Displayr cares about capitalization. That is, sum, Sum, and SUM are all different things (and, most of the time you will be wanting Sum).
• The names in the menus match the function names (e.g., Sum EachRow is the same function found in the toolbar under Calculation > Sum > Sum Each Row).
• In addition to performing calculations on tables, you can also use functions when creating new variables. 

Programming in R

Working in the Calculation Grid often involves writing R code.  If you are not an expert in R but still want to write R code, you still can.

For example, suppose you have this Calculation Grid and want to subtract the percentage of people who say they love Coke Zero from the percentage of people who say they love Coca-Cola.   

These are the steps:

• Click on Calculation in the toolbar (alternatively, select from the Calculation menu, select Custom Code)
• Click on the page where you wish to display your output and/or R code.
• Click the Coca-Cola Love percentage, type a subtraction sign, and click the Coke Zero Love percentage.
• Click Calculate in the Object Inspector on the right.

 

Manipulating Rows & Columns

Adding Rows / Columns

You can add columns and rows with the inserter and drag to reorder them.

Additional Rows and/or Columns can be added by using Grid > Properties on the right. In this example, we are requesting a Calculation Grid with 7 Rows and 5 Columns.

To add columns and/or rows:

• Select the Calculation Grid table to view Grid > Properties
• Use the Rows and Columns pull-down menus to specify the desired number of rows and columns.

Inserting Rows / Columns

You can add columns and rows with the inserter and drag to reorder them.

• Select the Calculation Grid
• Hover over the location when you want to insert a row or column until you see the insert tool symbol: .  
• Click the symbol
  
• A blank space will appear between the two labels.
  
• Double-click the blank space and type a label (be sure the label is between double quotes)
  
  

Deleting Rows / Columns

To delete a row or column from the Calculation Grid:

• Right-click the header of the row or column you want to delete
• From the menu, select Delete Row (Column)
  
  For example:
  
  
  

Reordering rows or columns

You can move rows and columns from a menu by 

• Right-clicking the header of the row or column you want to move 
• From the menu, for rows select Move Up or Move Down, and for columns select Move Left or Move Right for columns.  For example, 
  
  
  
  
  

You can also drag to reorder rows and columns.  

• Click the header of the row or column you want to move
  
  
• Drag the box up or down to where you want to move the row.  For columns, drag the box to the left or right.   
• Release the mouse button at the desired location.  
  
  For example, to move Column 2 to the left of Column 1:
  
  

Copy/Paste Values in a Calculation Grid

The following copy/paste operations are supported by Calculation Grid:

• Copy and paste within the same grid
• Copy from one grid to another
• Copy from Excel and paste into the grid
• Copy out of the grid as text values to paste into Excel

To copy/paste calculation grid cells:

• Select the cell(s) you want to copy.
• Right-click the highlighted cell(s) and select Copy
• Click the target cell and select one of the Paste options.
• If you are copying an entire row or column, select all of the cells in the row or column, click the first cell in the row or column you pasting to, and then select one of the Paste options.  
  
  
  
  Paste Code pastes the Value in the target cell and the R Code in the R Code window
  
  Note: This option will only be enabled if the item you have copied contains R Code, and using it will put the copied item's code inside the selected cell(s).

Paste Link pastes the source Cell Location (Link) in the R Code window and the Value in the target cell. This pastes a live connection to a number from another output in Displayr. This Value will update as the original output changes. For example:



Paste Values pastes the Value in both the target cell and in the R Code window. This just pastes a Value that will not update as other data changes. For example:

 

Demonstration: Copy/Paste Cells in the Same Grid

 

Demonstration: Copy/Paste From Excel

 

 

Modifying the Calculation Grid Properties

The properties window on the right allows you to modify your Calculation Grid.

The following properties can be modified:

• Font
• Table Style
• Decimals
• Number format (either % or $)

 

Changing the font

• Select the table to enable Font editing
  
  
  
  
• In the Calculation Grid Properties window on the right:
  • Select Appearance > Fonts
• Select the font you want.
  
  

Changing the Table Style

• Select the table
• From the Appearance > Table Style menu, select the style you want
  
  
  
  The results are as follows:
  
  

Changing the Number of Decimals

• Select the table
• From to the right of Appearance > Decimals, select
  • for more decimal places
  • for fewer decimal places

In this example, the number of decimal places is changed from two places to one place:

 

Changing the Number Format

You can change the number format to either or or to just a number by using either of these symbols to the right of Appearance > Numeric Format on the right:

For example, let's say I start with this table and would like to change the format to :

• Select the table
• Click the sign to the right of Appearance > Number format. Notice that the symbol is now highlighted which indicates the current number format for the table
  
  

The results are as follows:

Next

How to Export to Excel

How to Use R in Displayr