Sorting dataframe by one column based on other column

Question

I am trying to sort the Sex column. When I try to sort, I get all Male and all Female together. But I want to sort the Sex column between the countries. Also, please note the IND should be at the bottom of the country.

Suppose, the dataset is

Country<- c("France","France", "Germnay", "Germnay", "UK", "UK", "IND", "IND")
Sex <-c("Female", "Male", "Female", "Male", "Female", "Male", "Female", "Male")
A<- c(10, 20, 30 ,40 ,50 ,60 ,70 ,80)
B <- c(20, 30, 40, 50, 56, 84, 75, 85)
dt <- data.frame(Country, Sex, A, B)

When I try sorting with order(), I get the below output (screenshot is an example not with the same dataset)

dt <-dt[with(dt, order(dt$sex, decreasing = TRUE)),]

The desired output is

Country<- c("France","France", "Germnay", "Germnay", "UK", "UK", "IND", "IND")
Sex <-c("Male", "Female", "Male", "Female", "Male", "Female", "Male", "Female)
A<- c(10, 20, 30 ,40 ,50 ,60 ,70 ,80)
B <- c(20, 30, 40, 50, 56, 84, 75, 85)
output <- data.frame(Country, Sex, A, B)

Do you want `dt[with(dt, order(dt$Country, decreasing = TRUE)),] ` ? — Allan Cameron, Dec 08 '22 at 13:44
I want the country to be in the same way as the desired output; Ind should come at the end; I figured out the Ind to be at the end but want the sex to be sorted based on country; For ex: Sex to be sorted within France and then Sex to be sorted within Germany .... — Learner, Dec 08 '22 at 13:49
This is not sorting by Sex. This is sorting first by Country, then by Sex. — Gregor Thomas, Dec 08 '22 at 13:59
(1) You said in a comment that you really wanted `Ind` to sort last, that needs to be in the original question as it is not obvious. (2) The sample data you have is not exactly (spelling typo) the same as what you show in the image. Thank you for providing sample data as something we can directly use! Please make sure that the two agree. — r2evans, Dec 08 '22 at 14:08
dplyr: `dt %>% arrange(Country == "IND", Country, desc(Sex))`; base R: `dt[with(dt, order(Country == "IND", Country, -xtfrm(Sex))),]` — r2evans, Dec 08 '22 at 14:10

Flap · Accepted Answer · 2022-12-08T14:14:40.217

You have to sort both on country and sex. However, if you don't want the country variable to be sorted alfabatically, you can convert it to a factor variable first and define its levels in the order you want.

#Sample data
Country<- c("France","France", "Germnay", "Germnay", "UK", "UK", "IND", "IND")
Sex <-c("Female", "Male", "Female", "Male", "Female", "Male", "Female", "Male")
A<- c(10, 20, 30 ,40 ,50 ,60 ,70 ,80)
B <- c(20, 30, 40, 50, 56, 84, 75, 85)
dt <- data.frame(Country, Sex, A, B)

#Convert country to factor variables and define its levels in desired order
dt$Country <- factor(Country, levels = c("France", "Germnay", "UK", "IND"))

Using the data.table:

library(data.table)
dt[with(dt, order(Country, desc(Sex))),] 


  Country    Sex  A  B
2  France   Male 20 30
1  France Female 10 20
4 Germnay   Male 40 50
3 Germnay Female 30 40
6      UK   Male 60 84
5      UK Female 50 56
8     IND   Male 80 85
7     IND Female 70 75

Using the dplyr package:

library(dplyr)
dt %>% arrange(Country, Sex)


Output: 
 Country Sex        A     B
  <fct>   <chr>  <dbl> <dbl>
1 France  Male      20    30
2 France  Female    10    20
3 Germnay Male      40    50
4 Germnay Female    30    40
5 UK      Male      60    84
6 UK      Female    50    56
7 IND     Male      80    85
8 IND     Female    70    75

Oh sorry, I want the IND to be at the end of the row (at 7 and 8th row) — Learner, Dec 08 '22 at 13:47
In that case, you should convert the Country variable to a factor first, and define the levels in the desired order. I edited my code. — Flap, Dec 08 '22 at 14:18

Sorting dataframe by one column based on other column

1 Answers1