CODEWITHSUNDEEP
r
Lira
Lira

Reputation: 53

Sorting from a column and paste it into a different row

I have a set of data as follows:

ID  Distance
0   270.4170919
0   240.0230499
0   270.5878873
0   260.3663412
0   88.81341556
1   5564.323783
1   5625.862105
1   5709.559224
1   5809.231131
1   6286.892713
2   326.8418382
2   439.2376606
2   427.4494778
2   327.625258
2   326.3938459
3   601.9958829
3   323.1702281
3   755.3196593

There are five rows with ID 0, 1..325 with their corresponding values (Distance). I want to split the column (ID) based on rows (Distance) value and paste into multiple rows as follows:

    A           B           C           D           E
0   88.81341556 240.0230499 260.3663412 270.4170919 270.5878873
1   5564.323783 5625.862105 5709.559224 5809.231131 6286.892713
2   326.3938459 326.8418382 327.625258  427.4494778 439.2376606
3   323.1702281 334.7788259 601.9958829 710.3485862 755.3196593

I have no clue where and how to start to code.

Upvotes: 1

Views: 51

Answers (2)

Jota
Jota

Reputation: 17611

You can split based on ID, then sort each split. Then transpose it to match the matrix you want as output.

t(sapply(split(d$Distance, d$ID), sort))
#        [,1]      [,2]      [,3]      [,4]      [,5]
#0   88.81342  240.0230  260.3663  270.4171  270.5879
#1 5564.32378 5625.8621 5709.5592 5809.2311 6286.8927
#2  326.39385  326.8418  327.6253  427.4495  439.2377
#3  323.17023  334.7788  601.9959  710.3486  755.3197

A few alternatives:

t(apply(matrix(d$Distance, nrow=5), 2, sort)) # rows aren't named here

do.call(rbind, lapply(split(d$Distance, d$ID), sort))

matrix(d[with(d, order(ID, Distance)), "Distance"], 
  ncol = 5, 
  byrow = TRUE, 
  dimnames = list(unique(d$ID))) # make sure IDs are in the right order

Sample data (note, the provided sample data is missing two values in ID 3, and I added them in my sample data here):

d <- read.table(text="
ID  Distance
0   270.4170919
0   240.0230499
0   270.5878873
0   260.3663412
0   88.81341556
1   5564.323783
1   5625.862105
1   5709.559224
1   5809.231131
1   6286.892713
2   326.8418382
2   439.2376606
2   427.4494778
2   327.625258
2   326.3938459
3   601.9958829
3   323.1702281
3   755.3196593
3   334.7788259
3   710.3485862", header=TRUE)

Upvotes: 4

akrun
akrun

Reputation: 887661

Here is another option using unstack

 t(unstack(d[do.call(order, d),], Distance~ID))
 #       [,1]      [,2]      [,3]      [,4]      [,5]
 #X0   88.81342  240.0230  260.3663  270.4171  270.5879
 #X1 5564.32378 5625.8621 5709.5592 5809.2311 6286.8927
 #X2  326.39385  326.8418  327.6253  427.4495  439.2377
 #X3  323.17023  334.7788  601.9959  710.3486  755.3197

Or with dcast from data.table

 library(data.table)
 dcast(setDT(d)[order(ID, Distance)][, N:= 1:.N ,ID],
                        N~ID, value.var='Distance')[, N:= NULL]

NOTE: The output we got needs to be transposed as earlier.

Upvotes: 4

Related Questions