CODEWITHSUNDEEP
rsortingpanel
Jj Blevins
Jj Blevins

Reputation: 395

Order values within column according to values within different column by group in R

I have the following panel data set:

group  i  f  r  d
1      4  8  3  3
1      9  4  5  1
1      2  2  2  2
2      5  5  3  2
2      3  9  3  3
2      9  1  3  1

I want to reorder column i in this data frame according to values in column d for each group. So the highest value for group 1 in column i should correspond to the highest value in column d. In the end my data.frame should look like this:

group  i  f  r  d
1      9  8  3  3
1      2  4  5  1
1      4  2  2  2
2      5  5  3  2
2      9  9  3  3
2      3  1  3  1

Upvotes: 0

Views: 90

Answers (4)

akrun
akrun

Reputation: 887118

An option with data.table

library(data.table)
setDT(df1)[, i := i[order(d)], group]
df1
#   group i f r d
#1:     1 9 8 3 3
#2:     1 2 4 5 1
#3:     1 4 2 2 2
#4:     2 9 5 3 2
#5:     2 5 9 3 3
#6:     2 3 1 3 1

If we need the second version

setDT(df1)[, i := sort(i)[d], group]

data

df1 <- structure(list(group = c(1L, 1L, 1L, 2L, 2L, 2L), i = c(4L, 9L, 
2L, 5L, 3L, 9L), f = c(8L, 4L, 2L, 5L, 9L, 1L), r = c(3L, 5L, 
2L, 3L, 3L, 3L), d = c(3L, 1L, 2L, 2L, 3L, 1L)), class = "data.frame",
row.names = c(NA, 
-6L))

Upvotes: 1

Chris
Chris

Reputation: 3986

original (wrong)

You can achieve this using dplyr and rank:

library(dplyr)

df1 %>% group_by(group) %>%
  mutate(i = i[rev(rank(d))])

Edit

This question is actually trickier than it first seems and the original answer I posted is incorrect. The correct solution orders by i before subsetting by the rank of d. This gives OP's desired output which my previous answer did not (not paying attention!)

df1 %>% group_by(group) %>%
  mutate(i = i[order(i)][rank(d)])

# A tibble: 6 x 5
# Groups:   group [2]
#  group     i     f     r     d
#  <int> <int> <int> <int> <int>
#1     1     9     8     3     3
#2     1     2     4     5     1
#3     1     4     2     2     2
#4     2     5     5     3     2
#5     2     9     9     3     3
#6     2     3     1     3     1

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 388982

There is some confusion regarding the expected output. Here I am showing a way to get both the versions of the output.

A base R using split and mapply

df$i <- c(mapply(function(x, y) sort(y)[x], 
                  split(df$d, df$group), split(df$i, df$group)))

df
#  group i f r d
#1     1 9 8 3 3
#2     1 2 4 5 1
#3     1 4 2 2 2
#4     2 5 5 3 2
#5     2 9 9 3 3
#6     2 3 1 3 1

Or another version

df$i <- c(mapply(function(x, y) y[order(x)], 
                 split(df$d, df$group), split(df$i, df$group)))

df
#  group i f r d
#1     1 9 8 3 3
#2     1 2 4 5 1
#3     1 4 2 2 2
#4     2 9 5 3 2
#5     2 5 9 3 3
#6     2 3 1 3 1

We can also use dplyr for this :

For 1st version

library(dplyr)
df %>%
  group_by(group) %>%
  mutate(i = sort(i)[d])

2nd version is already shown by @Rui using order

df %>%
  group_by(group) %>%
  mutate(i = i[order(d)])

Upvotes: 1

Rui Barradas
Rui Barradas

Reputation: 76402

Here is a dplyr solution.

First, group by group. Then get the permutation rearrangement of column d in a temporary new column, ord and use it to reorder i.

library(dplyr)

df1 %>%
  group_by(group) %>%
  mutate(ord = order(d),
         i = i[ord]) %>%
  ungroup() %>%
  select(-ord)
## A tibble: 6 x 5
#  group     i     f     r     d
#  <int> <int> <int> <int> <int>
#1     1     9     8     3     3
#2     1     2     4     5     1
#3     1     4     2     2     2
#4     2     9     5     3     2
#5     2     5     9     3     3
#6     2     3     1     3     1

Upvotes: 4

Related Questions