2

sort a dataframe rowwise in R by ascending order

I have two dataframes 

x <- data.frame("Age1"=c(21, 15, 16), "Age2"=c(14, 9, 11), "Age3"=c(5, 10, 8))
y <- data.frame("Age1"=c(10, 20, 9), "Age"=c(14, 10, 11), "Age3"=c(7, 14, 10))

x
  Age1 Age2 Age3
1   21   14    5
2   15    9   10
3   16   11    8

y
  Age1 Age Age3
1   10  14    7
2   20  10   14
3    9  11   10

I need to sort the data x row wise in ascending order and apply the same ordering mechanism for y 

for (i in 1:3){
  order.pop[i,] <- order(x[i,])
  x[i,] <- x[order(x[i,]),]
  y[i,] <- y[order(x[i,]),]
}

this gives error message 

In `[<-.data.frame`(`*tmp*`, i, , value = list(Age1 = c(16, 15,  :
  replacement element 3 has 3 rows to replace 1 rows

I have the ordering

order.pop
  Age1 Age2 Age3
1    3    2    1
2    3    1    2
3    3    2    1

And in the end I need to get

x
  Age1 Age2 Age3
1   5   14    21
2   9   10    15
3   8   11   16

y 
  Age1 Age2 Age3
1   7   14    10
2   10   14  20 
3   10   11   9

Can anybody help?

Darren Tsai
  • 32,117
  • 5
  • 21
  • 51
Friederike
  • 31
  • 2
  • this might be relevant: https://stackoverflow.com/questions/9506442/fastest-way-to-sort-each-row-of-a-large-matrix-in-r – chinsoon12 Nov 04 '19 at 03:12

2 Answers2

3

I use the rank of each row to solve it :

x.rank <- apply(x, 1, rank)
x[] <- tapply(t(x), x.rank, c)
y[] <- tapply(t(y), x.rank, c)

x
#   Age1 Age2 Age3
# 1    5   14   21
# 2    9   10   15
# 3    8   11   16

y
#   Age1 Age2 Age3
# 1    7   14   10
# 2   10   14   20
# 3   10   11    9

The second row of y is different from your expected output. Maybe you have a typo or I misunderstand your question.

Darren Tsai
  • 32,117
  • 5
  • 21
  • 51
1

I wrote a small function based on this answer:

orderby <- function(x, by) {
  out <- as.data.frame(t(sapply(1:NROW(by), function(i) x[i,][order(by[i,])])))
  colnames(out) <- colnames(x)
  out
}

orderby(x, by = x)
#>   Age1 Age2 Age3
#> 1    5   14   21
#> 2    9   10   15
#> 3    8   11   16
orderby(y, by = x)
#>   Age1 Age Age3
#> 1    7  14   10
#> 2   10  14   20
#> 3   10  11    9
JBGruber
  • 11,727
  • 1
  • 23
  • 45
  • Thanks! That works perfectly. The example I wrote down was actually a simplified version of my actual data: in reality I have 406 observations of 48 variables in one data.frame, I do not have values for everything. Somtimes there is a NA in the data. Do you know how I can adjust the function so that the missing values are put last? (I am new to R) – Friederike Oct 31 '19 at 22:53
  • btw if you notice that an answer works but you found another problem it is usually best to open a new question about that other problem including data that shows/reproduces the issue. – JBGruber Oct 31 '19 at 23:15
  • Sorry, but how is the last row in 'y' ranked in ascending order? – ASH Dec 28 '19 at 05:05
  • It is based on the ordering of `x`. In the last row the order is column 3, 2, 1. – JBGruber Dec 28 '19 at 14:26