When working with drag and drop built-in tables, you are able to right click and Hide or Delete columns. However, if you're using an R table, you will need to perform this action within the code. This article describes how to remove rows or columns from a table using R code in a Calculation. For example, go from a 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 > General > General > Name or go to Tools > Copy > Copy Name.
Method 1 - Specifying the rows/columns to remove by index
- Select your Calculation.
- Go to the object inspector > Data > R Code > Edit 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 > Data > R Code > Edit 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 > Data > R Code > Edit 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:
Next
How to Work with R in Displayr
How to Create Dynamic Links Using R Code