UPCOMING WEBINAR: The Roadmap for Market Researchers in the Age of AI When a table has nested columns or rows, how you subset it in R changes depending on the structure of the nesting. Sometimes it's easier to combine the span name with the column name and work with the table that way. This article describes how to go from a crosstab with a nested banner...
...to an R table where the nested span labels are included in the column labels:
Requirements
A crosstab with a nested banner in the columns. If your nested columns are due to a multi- or grid variable set selected in the rows of the table, please see How to Extract Data from a Multiple Column Table with Nested Data.
Method
1. Select your table.
2. Copy the name from General > General > Name from the object inspector .
3. Go to Calculation > Custom Code from the toolbar.
4. Paste the below in the R Code editor:
#identify mytable
mytable = table.D2.Income.by.BANNER1
#create data.frame of the column spans and column names
spans = data.frame(attr(mytable,"span")["columns"])
#combine the span and name separated by -
newlabels = apply(spans,1,paste,collapse=" - ")
#make new column names for table
colnames(mytable) = newlabels
#return final result and strip off the span metadata for displaying
as.data.frame(mytable)- The above code defines the table using the reference name from step 2.
- Next, it pulls the span labels from the columns in the original table using the attr command.
- We can then use the apply function to add the span labels to the column labels using a dash as the separator.
- Finally, we return the table and add these new column labels to it.
Note, if you have spans in your table rows instead, you should reference rownames instead of colnames in your code and amend the span reference code to the below:
spans = attr(mytable,"span")["rows"]See Also
How to Extract Data from a Multiple Column Table with Nested Data
How to Extract Data from a Multiple Column Table
How to Extract Data from a Multiple Column Table with Multiple Statistics