This article describes how to go from a crosstab or summary grid table...
...to a table that displays the rank based on the cell percentages. In this example, we are ranking the brand attributes for each cola:
Method
To convert the crosstab to a table of ranks:
- Right-click on your Report tree and select Add R Output.
-
Paste the following code in the Properties > R CODE box and change the second line to the table you’d like to convert to rankings. If you are applying these steps to your own data step, assign x to the reference name of the crosstab you want to convert. This can be done by right-clicking the table in the Report tree and selecting Reference name…
# specify the table with the data to use for the rankings
# replace table.q5 with your table name
# (you can click on the table to automatically insert its name)
x = table.q5
# make data negative (the rank function ranks smallest number as 1)
x = x*-1
#remove NETs if present, code only keeps row/col names that aren't NET
x = x[rownames(x) != "NET", colnames(x) != "NET"]
# calculate the ranking within each row across the columns and use
# the "average" rank for ties (google the rank function to see other options)
# change 1 to 2 if you'd like to calculate the ranking within each column instead
ranks = apply(x,1,rank, ties.method="average")
# transpose the result back to the original format
table.rank = t(ranks)If you’d like to create the ranks within each column instead of each row, change line 15 to the following: ranks = apply(x,2, rank, ”average”).
-
[Optional]: If using a ranking table with only one column, it's interpreted as only a series of numbers in R can require different code. Use the following code after the x=x*-1 line:
#remove NETs if present, code only keeps names that aren't NET
x = x[names(x) != "NET"]
# calculate the ranking and use the "average" rank for ties
# (google the rank function to see other options)
ranks = rank(x, ties.method="average")
# transpose the result back to the original format
table.rank = t(ranks) - Check Automatic to allow the R table to automatically update with your data.
The end result is an R output table with the brand attributes ranked for each type of cola.