This article describes how to remove rows or columns from a table using R code in a Calculation. The post contains the steps to go from the below table containing all brands and a NET column...
...to a table showing only Coca-Cola and Coke brand in rows and removing the NET column:
Requirements
- A table consisting of at least two rows and columns.
- A Calculation output on the page.
For the purpose of all of the below examples, the name of the table we're referring to is "table". To find the name of your table you can select it and go to its object inspector > Properties > GENERAL > Name or go to Copy > Copy Name.
Method 1 - Specifying the rows/columns to remove by index
- Select your Calculation.
- Go to the object inspector > Properties > R Code.
- Add the following code below any existing code.
To keep only the first three rows use:
table[1:3,]
Alternatively, you can use a minus sign and specify the rows you don't wish to keep:
table[-(4:6),]
The above two examples will both return a table showing only the first three rows of the table:
However, if a table changes and contains row seven or eight, the second approach using a minus sign would return the first three rows as well as rows seven and eight.
To exclude a specific number of rows from the bottom of the table:
# NROW function returns the number of rows; e.g. 8
n = NROW(table)
# Removes rows 7:8
table[-((n-1):n),]
The above code first counts the number of rows, storing the number as an n. The second line of code is saying to not return (minus sign) the second last to the last row (ie remove the last 2 rows).
Method 2 - Specifying the rows/columns to remove by name
- Select your Calculation.
- Go to the object inspector > Properties > R Code.
- Add the following code below any existing code.
To remove the NET Column:
#rownames function returns the row labels, e.g. Coca-Cola, Diet Coke, etc.
x = rownames(table)
#colnames function retutns the column labels, e.g. Hate, Dislike, etc.
# != "NET" removes the "NET" label
y = colnames(table) != "NET"
table[x,y]
The above code stores the row names in x and column names excluding 'NET' in y. The last line brings both values together returning the below table.
The above code could be combined into one single line of code by replacing x and y in the last line with the code used to define these values in lines one and two, as per the next example.
To remove Pepsi from rows and the NET Column:
table[rownames(table) !="Pepsi",colnames(table) !="NET"]
Resulting table:
Method 3 - Specifying the rows/columns to remove by index and name
- Select your Calculation.
- Go to the object inspector > Properties > R Code.
- Add the following code below any existing code.
To show only the first three brands/rows and remove the NET Column:
x = 1:3
y = colnames(table) !="NET"
table[x,y]
or just:
table[1:3, colnames(table) != "NET"]
Resulting table:
See Also
How to Work with R in Displayr
How to Create Dynamic Links Using R Code
How to Troubleshoot R Code in Displayr