How to construct multiple columns at one time in R-CodePudding

Below is the sample data. I know how to construct a rank column for each time period but that is not the task. I have a larger data set that has monthly data from 2001 to 2022 but looking to avoid doing this manually. Is there a way to construct a rank column for a range of columns. In this case, it would would be 3 new columns. Each one would rank the values from largest to smallest.

 area <- c("Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware")
 sept2020 <- c(.120,.125,.130,.110,.095,.045,.131,.029)
 oct2020 <- c(.121,.129,.128,.119,.099,.041,.138,.028)
 nov2020 <- c(.119,.128,.129,.118,.091,.048,.139,.037)

 percent <- data.frame(area,sept2020,oct2020,nov2020)

The desired result would appear as such but with two more rank columns.. for oct2020 and nov2020

  area         sept2020    rank1
 Alabama           .120       4
 Alaska            .125       3
 Arizona           .130       2
 Arkansas          .110       5
 California        .095       6
 Colorado          .045       7
 Connecticut       .131       1
 Delaware          .029       8

CodePudding user response：

1) dplyr Use across like this:

library(dplyr)

percent %>%
  mutate(across(-1, ~ rank(desc(.)), .names = "{.col}_rank"))

giving:

         area sept2020 oct2020 nov2020 sept2020_rank oct2020_rank nov2020_rank
1     Alabama    0.120   0.121   0.119             4            4            4
2      Alaska    0.125   0.129   0.128             3            2            3
3     Arizona    0.130   0.128   0.129             2            3            2
4    Arkansas    0.110   0.119   0.118             5            5            5
5  California    0.095   0.099   0.091             6            6            6
6    Colorado    0.045   0.041   0.048             7            7            7
7 Connecticut    0.131   0.138   0.139             1            1            1
8    Delaware    0.029   0.028   0.037             8            8            8

2) Base R A base R solution would be the following. It gives similar output.

Rank <- function(nm, x) rank(-x)
cbind(percent, mapply(Rank, paste0(names(percent)[-1], "_rank"), percent[-1]))

CodePudding user response：

It sounds like you might be looking for the dense_rank function from dplyr:

percent %>%
        mutate(rank1 = dense_rank(desc(sept2020))

And then you could simply repeat that code, using oct2020 and nov2020 in the dense_rank, to create the next two ranking variables.