CODEWITHSUNDEEP
rdataframe
mikeL
mikeL

Reputation: 1114

Adding ranked column to data frame

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.

Upvotes: 8

Views: 282

Answers (4)

MichaelChirico
MichaelChirico

Reputation: 34773

Approach in data.table:

library(data.table)
setDT(dat,keep.rownames=T)[order(x1),x2:=rev(x1)]
    rn  x1  x2
1: Jul  59 170
2: Apr 100 161
3: Nov 101 151
4: Sep 104 109
5: Jun 109 104
6: May 151 101
7: Aug 161 100
8: Oct 170  59

If you want to end up with the rows in order, I think the easiest way is to use month.abb as the levels of rn as a factor:

setDT(dat,keep.rownames=T)[order(x1),x2:=rev(x1)
                           ][order(factor(rn,levels=month.abb))]
    rn  x1  x2
1: Apr 100 161
2: May 151 101
3: Jun 109 104
4: Jul  59 170
5: Aug 161 100
6: Sep 104 109
7: Oct 170  59
8: Nov 101 151

Could also use order(match(rn,month.abb)) instead, if that suits you; if you're going to be re-sorting a lot by month, it may make sense to define rn as a factor so you don't have to do the match or factor dog and pony show repeatedly: dat[,rn:=factor(rn,levels=month.abb)][order(rn)]

Upvotes: 3

Veerendra Gadekar
Veerendra Gadekar

Reputation: 4472

Making use of base R's month.abb

df = dat[order(dat$x1),, drop = FALSE]
df$x2 = sort(x1,decreasing = T)
df[match(month.abb, rownames(df), nomatch = 0),]

#     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

Using data.table simply

library(data.table)
df = setDF(setDT(dat, keep.rownames=T)[order(x1), x2 := sort(x1, decreasing = T)])
rownames(df) = df$rn; df[,1] = NULL

#     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

Upvotes: 4

Steven Beaupr&#233;
Steven Beaupr&#233;

Reputation: 21641

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

Upvotes: 4

IRTFM
IRTFM

Reputation: 263499

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.

Upvotes: 6

Related Questions