rearranging data frame by two columns

Question

How do I arrange data frame by decreasing order of values in column value where values in column "cat"=X

ex. data frame

df1<-data.frame(group=rep(c("A", "B", "C", "D"),each=2), cat=rep(c("X", "Y"),4), value=c(1,0.6,1,0.5,1,2,1,5))

ex. rearranged data frame (that I want to see):

I tried df1[with(df1, order(-value, cat)),] after reviewing the first answer on this article, but it did not work, the result is below:

As you can see the two tables look different. I tried arrange command from dplyr package too but every result looks like the one above.

Thank you

@pyll The only piece missing from the dupe is that the OP needs to reorder the levels of the factor `group`, i.e. `df1$group <- factor(df1$group,levels = c('D','C','A','B'))`. Then the solutions there work just fine. I think your answer is a bit of overkill. — joran, Jun 13 '17 at 17:59

pyll · Accepted Answer · 2017-06-14T13:02:31.777

Since value for cat'x' is always the same, we can sum the value by group and then use that to order the data.

# Create data.frame
df1<-data.frame(group=rep(c("A", "B",   "C", "D"),each=2), 
                cat=rep(c("X", "Y"),4), 
                value=c(1,0.6,1,0.5,1,2,1,5))
# Sum values by group (since x is same...)
df2 <- aggregate(df1$value,   by=list(group=df1$group), FUN=sum)

# Join tables
library(plyr)
output <- join(df1, df2, by='group', type='left', match='all')

# Sort
output <- output[with(output, order(-x, cat)),]

Or another (possibly better) way involves designating the factor levels. They are set alphabetically by default, but you can set them based on the order of their value

# Limit to only cat=Y
df2 <- df1[which(df1$cat=='Y'),]

# Order by descending
df2 <- df2[order(-df2$value),]

# Create a vector based on group column
order_vector <- df2[,'group']

# Use vector from above to designate new factor levels
df1$group <- factor(df1$group, levels = order_vector)

# Sort
output2 <- df1[with(df1, order(group, cat)), ]

rearranging data frame by two columns

1 Answers1