2

Probably the solution to this problem is really easy but I just can't see it. Here is my sample data frame:

df <- data.frame(id=c(1,1,1,2,2,2), value=rep(1:3,2), level=rep(letters[1:3],2))
df[6,2] <- NA

And here is the desired output that I would like to create:

df$new_value <- c(3,2,1,NA,2,1)

So the order of all columns is the same, and for the new_value column the value column order is reversed within each level of the id column. Any ideas? Thanks!

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
pd441
  • 2,644
  • 9
  • 30
  • 41
  • [Relevant](https://stackoverflow.com/questions/43343590/how-to-sort-putting-nas-first-in-dplyr) – Sotos Jan 31 '18 at 11:02

3 Answers3

5

As I understood your question, it's a coincidence that your data is sorted, if you just want to reverse the order without sorting:

library(dplyr)
df %>% group_by(id) %>% mutate(new_value = rev(value)) %>% ungroup

# A tibble: 6 x 4
     id value  level new_value
  <dbl> <int> <fctr>     <int>
1     1     1      a         3
2     1     2      b         2
3     1     3      c         1
4     2     1      a        NA
5     2     2      b         2
6     2    NA      c         1
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
  • I just used OP's code and mine here, using `dplyr_0.7.4` – moodymudskipper Jan 31 '18 at 10:55
  • Ok, may be I have a different version. Thanks – akrun Jan 31 '18 at 10:55
  • Thanks! This a nice solution. I realized that I also need to preserve the original order whilst shifting the position of the NA's, like: `df$new_value <- c(1,2,3,NA,1,2)` but I should probably ask that in a separate post! Thanks again... – pd441 Jan 31 '18 at 11:54
  • You could do `df %>% group_by(id) %>% mutate(new_value = c(value[is.na(value)],value[!is.na(value)])) %>% ungroup` – moodymudskipper Jan 31 '18 at 12:27
3

A slightly different approach, using the parameters in the sort function:

library(dplyr)
df %>% group_by(id) %>% 
mutate(value = sort(value, decreasing=TRUE, na.last=FALSE))

Output:

# A tibble: 6 x 3
# Groups: id [2]
     id value level 
  <dbl> <int> <fctr>
1  1.00     3 a     
2  1.00     2 b     
3  1.00     1 c     
4  2.00    NA a     
5  2.00     2 b     
6  2.00     1 c

Hope this helps!

Florian
  • 24,425
  • 4
  • 49
  • 80
2

We can use order on the missing values and on the column itself

library(dplyr)
df %>% 
     group_by(id) %>%
     mutate(new_value = value[order(!is.na(value), -value)])
# A tibble: 6 x 4
# Groups: id [2]
#     id value level  new_value
#  <dbl> <int> <fctr>     <int>
#1  1.00     1 a              3
#2  1.00     2 b              2
#3  1.00     3 c              1
#4  2.00     1 a             NA
#5  2.00     2 b              2
#6  2.00    NA c              1

Or using the arrange from dplyr

df %>% 
    arrange(id, !is.na(value), desc(value)) %>% 
    transmute(new_value = value) %>%
    bind_cols(df, .)

Or using base R and specify the na.last option as FALSE in order

with(df, ave(value, id, FUN = function(x) x[order(-x, na.last = FALSE)]))
#[1]  3  2  1 NA  2  1
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Could you please explain the logic behind this: `order(!is.na(value), -value)` ? – tushaR Jan 31 '18 at 11:03
  • @TUSHAr The logic is to keep the NA value first because FALSE comes before TRUE. You can check with `v1 <- c(1, NA, 3);order(!is.na(v1));v1[order(!is.na(v1))` – akrun Jan 31 '18 at 11:06
  • I got that part but what I am not able to figure out is the second parameter that you have passed as `-value`? I thought `order()` only takes one argument as input vector. – tushaR Jan 31 '18 at 11:11
  • 1
    @TUSHAr It takes vectors. If you check `?order`, `... a sequence of numeric, complex, character or logical vectors, all of the same length, or a classed R object`. – akrun Jan 31 '18 at 11:13
  • so does that correspond to orderby vector1 and then vector2 ? – tushaR Jan 31 '18 at 11:21
  • 1
    @TUSHAr You can check both cases i.e. `mutate(new_value = value[order(!is.na(value))])` here it make sure that NA comes first but other values are unchanged and the one in the solution – akrun Jan 31 '18 at 11:22