how rank the column values for each group separately in R-CodePudding

I have such data

Timework=structure(list(SAP = c("S643", "S985"), X07.hour = c(NA, NA), 
    X08.hour = c(NA, NA), X09.hour = c(3.6, NA), X10.hour = c(51685.42769, 
    60284.83147), X11.hour = c(85539.56302, 117911.4336), X12.hour = c(128681.5323, 
    177794.5036), X13.hour = c(207335.5267, 233137.2999), X14.hour = c(145656.5062, 
    320948.6083), X15.hour = c(143449.3214, 244140.2991), X16.hour = c(147891.4464, 
    184859.2897), X17.hour = c(136605.3676, 212647.2968), X18.hour = c(125787.0896, 
    155052.8752), X19.hour = c(96901.46277, 163971.1803), X20.hour = c(NA, 
    262414.2152), X21.hour = c(NA, 107269.9123), X22.hour = c(NA, 
    -1204.704396), X23.hour = c(NA, NA)), class = "data.frame", row.names = c(NA, 
-2L))

How do I rank the values in the hours columns for each SAP in ascending order for each SAP separately? For example to be more clear. For sap=s643 there are values from 9 hours to 19 hours. So i can rank something like this

hour           value         rank
 09 hour    3,6               1
 10 hour    51685,42769       2
 11 hour    85539,56302       3
 19 hour    96901,46277       4
 18 hour    125787,0896       5
 12 hour    128681,5323       6
 17 hour    136605,3676       7
 15 hour    143449,3214       8
 14 hour    145656,5062       9
 16 hour    147891,4464      10
 13 hour    207335,5267      11

and for sap=s985 we can rank it so(here value from 10hour to 22 hour)

Hours      Value        rank
 22 hour    -1204,704396    1
 10 hour    60284,83147     2
 21 hour    107269,9123     3
 11 hour    117911,4336     4
 18 hour    155052,8752     5
 19 hour    163971,1803     6
 12 hour    177794,5036     7
 16 hour    184859,2897     8
 17 hour    212647,2968     9
 13 hour    233137,2999     10
 15 hour    244140,2991     11
 20 hour    262414,2152     12
 14 hour    320948,6083     13

In other words, how do I rank the column values for each sap separately? Desired output

SAP hour           value          rank
s643     09 hour    3,6         1
s643     10 hour    51685,42769 2
s643     11 hour    85539,56302 3
s643     19 hour    96901,46277 4
s643     18 hour    125787,0896 5
s643     12 hour    128681,5323 6
s643     17 hour    136605,3676 7
s643     15 hour    143449,3214 8
s643     14 hour    145656,5062 9
s643     16 hour    147891,4464 10
s643     13 hour    207335,5267 11
s985     22 hour    -1204,704396    1
s985     10 hour    60284,83147 2
s985     21 hour    107269,9123 3
s985     11 hour    117911,4336 4
s985     18 hour    155052,8752 5
s985     19 hour    163971,1803 6
s985     12 hour    177794,5036 7
s985     16 hour    184859,2897 8
s985     17 hour    212647,2968 9
s985     13 hour    233137,2999 10
s985     15 hour    244140,2991 11
s985     20 hour    262414,2152 12
s985     14 hour    320948,6083 13

thank you for you help.

CodePudding user response：

Here's a tidyverse solution.

library(tidyverse)

Timework %>% 
  # Transform the data to long format
  pivot_longer(-SAP) %>% 
  # Extract the hour value from the column names
  mutate(hour=as.numeric(str_sub(name, 2, 3))) %>% 
  select(-name) %>% 
  filter(!is.na(value)) %>% 
  group_by(SAP) %>% 
  # Sort into order requested by OP (can be omitted)
  arrange(SAP, value) %>% 
  # Calculate the rank
  mutate(rank=row_number())
# A tibble: 24 × 4
# Groups:   SAP [2]
   SAP      value  hour  rank
   <chr>    <dbl> <dbl> <int>
 1 S643       3.6     9     1
 2 S643   51685.     10     2
 3 S643   85540.     11     3
 4 S643   96901.     19     4
 5 S643  125787.     18     5
 6 S643  128682.     12     6
 7 S643  136605.     17     7
 8 S643  143449.     15     8
 9 S643  145657.     14     9
10 S643  147891.     16    10
# … with 14 more rows

CodePudding user response：

Is this what you have in mind? I haven't cleaned the names of hours.

res <- Timework %>% pivot_longer(!SAP, names_to = "hours", values_to = "value") %>% group_by(SAP) %>% drop_na() %>% mutate(rank = rank(value)) %>% arrange(SAP, rank)

CodePudding user response：

Maybe you could use something like that:

Timework[order(Timework[,1]),]

(Ordered according to column '1'.)