Sort data frame in r on two conditions with both increasing and decreasing columns

Question

I have data that looks like this in df_Filtered (where filtered stands for a df for a specific country):

Year Region    Percentage_of_national_sell
2012 Berlin    84%
2010 Hamburg   101%
2011 Stettin   98%
2012 Stettin   100%
2012 Hamburg   122%
2011 Berlin    111%
2010 Berlin    101%
2010 Stettin   87%
2011 Hamburg   58%

What I want to do is to sort df_Filtered so that I first get it sorted by Region and then by Year (starting with Berlin and then get 2010, 2011, 2012 in that specific order). Both Region and Year should be increasing.

I tried: df_Filtered[ order(df_Filtered$Region, (df_Filtered$Year)) , ] which works when I get the output in text in Rstudio, but it does not seem to change the order of the data frame when I try to remap it:

df_Filtered <- df_Filtered[ order(df_Filtered$Region, (df_Filtered$Year)) , ]

Is there any way to change the order of the rows in the data frame itself? I want to later export it to LaTex to make nice tables, that is why the order is important.

Obviously a duplicate... data<-data[order(data$Region,-data$Year),] data<-data[order(data$Region,data$Year),] — Vladimir Volokhonsky, Oct 16 '18 at 12:38

Saurabh Chauhan · Accepted Answer · 2018-10-16T12:36:39.730

3

Using sqldf:

Your sample data:

df=read.table(text="Year Region    Percentage_of_national_sell
          2012 Berlin    84%
          2010 Hamburg   101%
          2011 Stettin   98%
          2012 Stettin   100%
          2012 Hamburg   122%
          2011 Berlin    111%
          2010 Berlin    101%
          2010 Stettin   87%
          2011 Hamburg   58%",header=T)

Code:

library(sqldf)
sqldf("select * from df order by Region, Year")

OR (Base R solution)

df[order(df$Region,df$Year),]

Output:

Year  Region Percentage_of_national_sell
1 2010  Berlin                        101%
2 2011  Berlin                        111%
3 2012  Berlin                         84%
4 2010 Hamburg                        101%
5 2011 Hamburg                         58%
6 2012 Hamburg                        122%
7 2010 Stettin                         87%
8 2011 Stettin                         98%
9 2012 Stettin                        100%

edited Oct 16 '18 at 12:36

answered Oct 16 '18 at 12:22

Saurabh Chauhan

3,161
2
19
46

@KGB91: Does it satisfy your requirement? – Saurabh Chauhan Oct 16 '18 at 12:27
Thanks! But I want to start with `2010` in `year`. (I might have been unclear here) And it should mainly be sorted on the region, not the year. – KGB91 Oct 16 '18 at 12:28
The edited code works fine! Thanks a lot! That is SQL-code, right? – KGB91 Oct 16 '18 at 12:30
1

@KGB91: I have updated the answer. First, you mentioned Region should be increasing and Year decreasing in the question so I did as per the question. – Saurabh Chauhan Oct 16 '18 at 12:30
@KGB91: Yes this is a `sqldf` (For R) which is similar to `sql`. – Saurabh Chauhan Oct 16 '18 at 12:31
I am new to R, but that seems handy. And yeah, I made a mistake in my post. – KGB91 Oct 16 '18 at 12:32

score 1 · Answer 2 · answered Oct 16 '18 at 12:36

The dplyr package works also nice for this: (data is not complete)

library(dplyr)

Region <- c("Ham", "Ste", "Ste", "Ham", "Ber", "Ber")
Year <- c(2012, 2010, 2011, 2012, 2012, 2011)
Percentage <- c(101, 98, 100, 122, 111, 101)
df <- data.frame(Region, Year, Percentage)

df %>% arrange(Region, Year)

Output:

  Year Region Percentage
1 2011    Ber        101
2 2012    Ber        111
3 2012    Ham        101
4 2012    Ham        122
5 2010    Ste         98
6 2011    Ste        100

Sort data frame in r on two conditions with both increasing and decreasing columns

2 Answers2