CODEWITHSUNDEEP
rsortingdataframe
MIH
MIH

Reputation: 1113

Sorting values in dataframe by order of values in another dataframe R

I would like to sort values in columns of the xy1 dataframe, based on the increasing order of values in columns of the xy dataframe.

x <- c(3,1,7,45,22,2)
y <- c(23,65,1,23,2,11)
xy <- data.frame(x,y)

x1 <- c(0.34,0.3,0.7,0.22,0.67,0.87)
y1 <- c(0.4,0.13,0.17,0.72,0.61,0.7)
xy1 <- data.frame(x1,y1)

    > xy
       x  y
    1  3 23
    2  1 65
    3  7  1
    4 45 23
    5 22  2
    6  2 11
    > xy1
        x1   y1
    1 0.34 0.40
    2 0.30 0.13
    3 0.70 0.17
    4 0.22 0.72
    5 0.67 0.61
    6 0.87 0.70

The following is a new data.frame result that I desire - note it deals with repeated observations (two the same values in y). x1 and y1 are now sorted according to the order of values in each column of xy dataframe.

    x1   y1
1 0.30 0.17
2 0.87 0.61
3 0.34 0.70
4 0.70 0.40
5 0.67 0.72
6 0.22 0.13

Upvotes: 1

Views: 1395

Answers (3)

akrun
akrun

Reputation: 886948

As this is based on ordering corresponding columns on both datasets, Map can be used

xy1[] <- Map(function(x,y) x[order(y)], xy1, xy)
xy1
#    x1   y1
#1 0.30 0.17
#2 0.87 0.61
#3 0.34 0.70
#4 0.70 0.40
#5 0.67 0.72
#6 0.22 0.13

Or another option is to order based on the col of 'xy', 'xy' 

xy1[] <- as.matrix(xy1)[order(col(xy), xy)]
xy1
#    x1   y1
#1 0.30 0.17
#2 0.87 0.61
#3 0.34 0.70
#4 0.70 0.40
#5 0.67 0.72
#6 0.22 0.13

Upvotes: 2

tyluRp
tyluRp

Reputation: 4768

You could try this:

library(tidyverse)

df_1 <- xy %>% 
  bind_cols(xy1) %>% 
  arrange(x) %>% 
  select(x1)

df_2 <- xy %>% 
  bind_cols(xy1) %>% 
  arrange(y) %>% 
  select(y1)

df <- bind_cols(df_1, df_2)

Which returns:

# A tibble: 6 x 2
     x1    y1
  <dbl> <dbl>
1  0.30  0.17
2  0.87  0.61
3  0.34  0.70
4  0.70  0.40
5  0.67  0.72
6  0.22  0.13

Basically just arrange x1 and y1 by x and y separately, then combine x1 and y1.

Upvotes: 1

ekstroem
ekstroem

Reputation: 6151

You can use the order function to get the sorting order of a vector.

x <- c(3,1,7,45,22,2)
y <- c(23,65,1,23,2,11)
xy <- data.frame(x,y)

x1 <- c(0.34,0.3,0.7,0.22,0.67,0.87)
y1 <- c(0.4,0.13,0.17,0.72,0.61,0.7)
xy1 <- data.frame(x1,y1)

result <- data.frame(x1[order(x)], y1[order(y)])
result

This produces

  x1.order.x.. y1.order.y..
1         0.30         0.17
2         0.87         0.61
3         0.34         0.70
4         0.70         0.40
5         0.67         0.72
6         0.22         0.13

You can beautify the output by setting the column names in the result:

data.frame(x1=x1[order(x)], y1=y1[order(y)])

Now if you don't want to manually type in everything but have two data frames with the same dimensions that you can use this one-liner

 sapply(1:ncol(xy1), function(i) {xy1[order(xy[,i]), i]})

which produces

     [,1] [,2]
[1,] 0.30 0.17
[2,] 0.87 0.61
[3,] 0.34 0.70
[4,] 0.70 0.40
[5,] 0.67 0.72
[6,] 0.22 0.13

Upvotes: 2

Related Questions