Adding ranked column to data frame

Question

I have some monthly data and I want to add a column to my data frame that associates the smallest value in the the first column to the largest value in the first column. The second smallest value in the first column to the second largest value in the first column, ect…

Here is some sample data

x1<-c(100,151,109,59,161,104,170,101)
dat<-data.frame(x1)
rownames(dat)<-c('Apr','May', 'Jun','Jul', 'Aug', 'Sep', 'Oct', 'Nov')

     x1
Apr 100
May 151
Jun 109
Jul  59
Aug 161
Sep 104
Oct 170
Nov 101

I m trying to get my data to look like this

     x1   x2
Apr 100  161
May 151  101
Jun 109  104
Jul  59  170
Aug 161  100
Sep 104  109
Oct 170   59
Nov 101  151

I'm going in circles with rank, sort, and order. Any help would be appreciated.

Answer 1

It's reasonably straightforward if you create a temporary object that has the ascending and descending values paired up:

> temp <- data.frame(asc =x1[order(x1)],desc=x1[rev(order(x1))])
> dat$x2 <- temp$desc[ match(dat$x1, temp$asc) ]
> dat
     x1  x2
Apr 100 161
May 151 101
Jun 109 104
Jul  59 170
Aug 161 100
Sep 104 109
Oct 170  59
Nov 101 151

The match function is designed to construct integer indexing values that are used as arguments to "[". It's the fundamental function inside merge.

Answer 2

Similar idea as @BondedDust:

library(dplyr)
dat %>% mutate(x2 = x1[match(row_number(desc(x1)), row_number(x1))])

Which gives:

#   x1  x2
#1 100 161
#2 151 101
#3 109 104
#4  59 170
#5 161 100
#6 104 109
#7 170  59
#8 101 151