arun
2013-Nov-07 22:47 UTC
[R] Sorting Data Frames in R by multiple columns with a custom order
Hi,
Not sure whether this helps:
dat1 <- as.data.frame(mat,stringsAsFactors=FALSE)
dat1$c4 <- factor(dat1$c4,levels=c("OF","ON"))
?dat1$c1 <-
factor(dat1$c1,levels=c("US","UK","WW","BO","BR","CA"))
?dat1$c2 <- factor(dat1$c2,
levels=c("P2","P3","P1"))
?dat1$c3 <- factor(dat1$c3,
levels=c("S2","S1","S3"))
?dat1[with(dat1,order(c4,c1,c2,c3)),]
A.K.
Thank you guys for the help here and my apologies if this has been answered in
the post's already somewhere which I just was not able to
find.
I am trying to sort a data frame by multiple
columns. ?Each column has different values of interest for which I am
trying to sort. ?I have been able to do this alphebetically in both
ascending and decending order using the order function. ?However, for
the document I am trying to create it is crutcial that the order is not
alphebetically. ?In fact it is by a specific ordering, on each of the
columns, which I need to specify.
I could do this via a nasty convolution of creating sort order variables for
each of the columns and then merging on to the data frame by the cols, then
ordering on the new sort order cols, then
remove them...however, I was hoping that there would be a nice and easy
automated way to handle this in case the order changes at another time.
so, here's an example
c1 = c('CA', 'CA', 'CA', 'BR', 'BR',
'UK', 'UK', 'BO', 'BO', 'BO',
'WW', 'WW', 'WW', 'US', 'US')
c2 = c('P3', 'P2', 'P1', 'P1', 'P2',
'P2', 'P1', 'P1', 'P3', 'P2',
'P1', 'P2', 'P3', 'P3', 'P2')
c3 = c('S1', 'S1', 'S2', 'S2', 'S2',
'S1', 'S2', 'S1', 'S1', 'S1',
'S1', 'S2', 'S3', 'S1', 'S1')
c4 = c('ON', 'ON', 'OF', 'ON', 'OF',
'OF', 'OF', 'ON', 'ON', 'ON',
'OF', 'ON', 'ON', 'ON', 'ON')
mat = cbind(c4, c1, c2, c3)
if we sort as usual we'd get
"OF" "BR" "P2" "S2"
"OF" "CA" "P1" "S2"
"OF" "UK" "P1" "S2"
"OF" "UK" "P2" "S1"
"OF" "WW" "P1" "S1"
"ON" "BO" "P1" "S1"
"ON" "BO" "P2" "S1"
"ON" "BO" "P3" "S1"
"ON" "BR" "P1" "S2"
"ON" "CA" "P2" "S1"
"ON" "CA" "P3" "S1"
"ON" "US" "P2" "S1"
"ON" "US" "P3" "S1"
"ON" "WW" "P2" "S2"
"ON" "WW" "P3" "S3"
however I want OF in col 1 to come first...then in the col2 i want US, UK, WW,
BO, BR, and then CA. ?then col 3 we need P2, then P3, then P1, and finally in
col 4 i need S2, then S1, and then finally S3. ?As such
"OF" "UK" "P2" "S1"
"OF" "UK" "P1" "S2"
"OF" "WW" "P1" "S1"
"OF" "BR" "P2" "S2"
"OF" "CA" "P1" "S2"
"ON" "US" "P2" "S1"
"ON" "US" "P3" "S1"
"ON" "WW" "P2" "S2"
"ON" "WW" "P3" "S3"
"ON" "BO" "P2" "S1"
"ON" "BO" "P3" "S1"
"ON" "BO" "P1" "S1"
"ON" "BR" "P1" "S2"
"ON" "CA" "P2" "S1"
"ON" "CA" "P3" "S1"
i've tried nesting orders in orders, the match
function looks like it might work if it wasn't for the fact that in my
actual data each col can have multiple records for each value and match
only looks for the first matching case....that said, the sort order is
unique.
Furthermore, these are not the real data, just an example.
anything might be able to get me further along the way than I am now.
Thanks
arun
2013-Nov-07 23:07 UTC
[R] Sorting Data Frames in R by multiple columns with a custom order
If you already have the order stored in a list or so:
For example:
dat1 <- as.data.frame(mat,stringsAsFactors=FALSE)
lst1 <- list(c("OF","ON"),
c("US","UK",
"WW","BO","BR","CA"),
c("P2","P3","P1"),c("S2","S1","S3"))
?dat1[] <- lapply(seq_along(lst1),function(i)
factor(dat1[,i],levels=lst1[[i]]))
?dat1[with(dat1,order(c4,c1,c2,c3)),]
A.K.
On Thursday, November 7, 2013 5:47 PM, arun <smartpink111 at yahoo.com>
wrote:
Hi,
Not sure whether this helps:
dat1 <- as.data.frame(mat,stringsAsFactors=FALSE)
dat1$c4 <- factor(dat1$c4,levels=c("OF","ON"))
?dat1$c1 <-
factor(dat1$c1,levels=c("US","UK","WW","BO","BR","CA"))
?dat1$c2 <- factor(dat1$c2,
levels=c("P2","P3","P1"))
?dat1$c3 <- factor(dat1$c3,
levels=c("S2","S1","S3"))
?dat1[with(dat1,order(c4,c1,c2,c3)),]
A.K.
Thank you guys for the help here and my apologies if this has been answered in
the post's already somewhere which I just was not able to
find.
I am trying to sort a data frame by multiple
columns. ?Each column has different values of interest for which I am
trying to sort. ?I have been able to do this alphebetically in both
ascending and decending order using the order function. ?However, for
the document I am trying to create it is crutcial that the order is not
alphebetically. ?In fact it is by a specific ordering, on each of the
columns, which I need to specify.
I could do this via a nasty convolution of creating sort order variables for
each of the columns and then merging on to the data frame by the cols, then
ordering on the new sort order cols, then
remove them...however, I was hoping that there would be a nice and easy
automated way to handle this in case the order changes at another time.
so, here's an example
c1 = c('CA', 'CA', 'CA', 'BR', 'BR',
'UK', 'UK', 'BO', 'BO', 'BO',
'WW', 'WW', 'WW', 'US', 'US')
c2 = c('P3', 'P2', 'P1', 'P1', 'P2',
'P2', 'P1', 'P1', 'P3', 'P2',
'P1', 'P2', 'P3', 'P3', 'P2')
c3 = c('S1', 'S1', 'S2', 'S2', 'S2',
'S1', 'S2', 'S1', 'S1', 'S1',
'S1', 'S2', 'S3', 'S1', 'S1')
c4 = c('ON', 'ON', 'OF', 'ON', 'OF',
'OF', 'OF', 'ON', 'ON', 'ON',
'OF', 'ON', 'ON', 'ON', 'ON')
mat = cbind(c4, c1, c2, c3)
if we sort as usual we'd get
"OF"??? "BR"??? "P2"??? "S2"
"OF"??? "CA"??? "P1"??? "S2"
"OF"??? "UK"??? "P1"??? "S2"
"OF"??? "UK"??? "P2"??? "S1"
"OF"??? "WW"??? "P1"??? "S1"
"ON"??? "BO"??? "P1"??? "S1"
"ON"??? "BO"??? "P2"??? "S1"
"ON"??? "BO"??? "P3"??? "S1"
"ON"??? "BR"??? "P1"??? "S2"
"ON"??? "CA"??? "P2"??? "S1"
"ON"??? "CA"??? "P3"??? "S1"
"ON"??? "US"??? "P2"??? "S1"
"ON"??? "US"??? "P3"??? "S1"
"ON"??? "WW"??? "P2"??? "S2"
"ON"??? "WW"??? "P3"??? "S3"
however I want OF in col 1 to come first...then in the col2 i want US, UK, WW,
BO, BR, and then CA. ?then col 3 we need P2, then P3, then P1, and finally in
col 4 i need S2, then S1, and then finally S3. ?As such
"OF"??? "UK"??? "P2"??? "S1"
"OF"??? "UK"??? "P1"??? "S2"
"OF"??? "WW"??? "P1"??? "S1"
"OF"??? "BR"??? "P2"??? "S2"
"OF"??? "CA"??? "P1"??? "S2"
"ON"??? "US"??? "P2"??? "S1"
"ON"??? "US"??? "P3"??? "S1"
"ON"??? "WW"??? "P2"??? "S2"
"ON"??? "WW"??? "P3"??? "S3"
"ON"??? "BO"??? "P2"??? "S1"
"ON"??? "BO"??? "P3"??? "S1"
"ON"??? "BO"??? "P1"??? "S1"
"ON"??? "BR"??? "P1"??? "S2"
"ON"??? "CA"??? "P2"??? "S1"
"ON"??? "CA"??? "P3"??? "S1"
i've tried nesting orders in orders, the match
function looks like it might work if it wasn't for the fact that in my
actual data each col can have multiple records for each value and match
only looks for the first matching case....that said, the sort order is
unique.
Furthermore, these are not the real data, just an example.
anything might be able to get me further along the way than I am now.
Thanks