Reputation: 1114
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
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
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
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
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