How to Relabel Rows and Columns of Tables using R in Q
Q enables you to flexibly mix data from different tables. The mixing process creates a new table as an R Output.
Consider the example table below (an R Output created with the feature Create> Tables > Merge Two or More Tables). You may want to relabel the column headers.
The thing with R Outputs is that they cannot be manipulated via the context menu (accessible via right-click). Those menu items are designed for use with tables made with the questions in the blue and brown drop-down menus. Outputs made with R code can only be modified with R code. The good news is that modifying R Output is very easy and only requires an additional line (or two) of code.
The purpose of this blog article is to show you how you can easily modify the labels of your tables within R Outputs. I’ll show you how you can do this in two ways:
- Respecifying all the labels at once (manual)
- Renaming individual single row/column labels (manual)
I’ll work through these cases in the examples below. Key to this are the functions
colnames() respectively, which I explain via the worked example. There are other more advanced and automatic ways to do relabeling as well, which I allude to at the end of this post.
Setting the labels of all the rows and columns
Manually respecifying all the rows and column labels can be done easily with the template line of code below:
(table) = c(“label1”, “label2”, “label3”)
table is the name of the table you wish to change. The labels, in order, are shown in green. They are being combined using the combine function
c(). When doing it this way (manually setting them all at once) you will need to specify the exact number of labels, else the output will throw an error. So you may have any number of labels.
We can use this code to quickly modify the column labels in the example R Output shown earlier. The headers look a bit messy because they are actually the names of the three source tables that were merged. You may wish to tidy theses to be: “Awareness”, “Affinity”, “Main”. You can do this easily by adding the following line of code within the R Output:
(merged) = c(“Awareness”, “Affinity”, “Main”)
In this case, I’ve edited an existing R output that had been set up (from Create > Tables > Merge Two or More Tables). Lines 1 to 3 were already set up within the R Output (which you can access via Object Inspector > Properties > R CODE). On line 3, the code is storing the new table as an object called ‘merged’. So using that name, the code I’ve added in line 5 sets the column names of merged to be the new (tidy) names.
Note: you need line 6 to return the final (modified) table. Line 5 on its own simply does the relabeling to the table (but doesn’t produce the table in the R Output). Line 6 makes the R Output show the final table (with the relabeling all done by line 5).
Renaming individual single row/column labels (manual)
You can also just change one of the row or column headers, without having to respecify the whole lot. This makes it handy for tweaking a table (e.g. for correcting a spelling error). The code is a little more convoluted, but it is again just a single line that you can easily adapt from the below.
(table)[rownames(table) == "old label"] = “new label”
It’s essentially the same line of code in the first example, but it looks more complicated because of the bit in the square brackets. If you can recall from Simple Table Manipulations with R in Q, the square brackets subset the table. In other words, the subsetting is to specify which label is to be relabeled. All you need to do is borrow the above and swap the red, purple and green text! I’ve done that by extending the previous example, adding lines 8 and 9 to the code:
(merged)[rownames(merged) == “Coca-Cola”] = “Coke”
Further automatic ways to relabel your tables
Here is a preview of some other ways you can go about automatically replacing labels in R Outputs. I won’t explain these in detail here, as I’ll save this content for another blog post.
In the first example in this post, you had to specify all the new row and column headers at once. And you had to specify the exact number. There is a simple and neat way you can get the row/column names to refer to another table. This means you don’t have to write out all the new headers and it automatically updates:
colnames(table) = colnames(reference_table)
Have you ever used the Find/Replace feature in Excel? There are equivalent ways to do this in R as well. You can get the code to scan through the labels (or values) of a table made within an R Output to find and replace text. This can be useful to clean up messy text. Examples include the
gsub() functions. Again, I won’t go into detail here how they work; I just wanted to point out it’s possible! Stay tuned for further posts where we show this via example.
Try it yourself
The worked example above you can find in this QPack.