Home > OS >  How do I sort a dataframe by date. But the date column is in Quarters
How do I sort a dataframe by date. But the date column is in Quarters

Time:10-09

enter image description here

Date <- c("Q1 2005", "Q1 2006","Q1 2007","Q1 2008","Q2 2005", "Q2 2006","Q2 2007","Q2 2008","Q3 2005", "Q3 2006","Q3 2007","Q3 2008","Q4 2005", "Q4 2006","Q4 2007","Q4 2008")

I need it sorted in chronological order.

CodePudding user response:

Here is an alternative approach:

library(dplyr)
libray(tidyr)
df %>% 
  separate(Date, c("Quarter", "Year"), remove = FALSE) %>% 
  arrange(Year, Quarter) %>% 
  select(-Quarter, -Year)
      Date
1  Q1 2005
2  Q2 2005
3  Q3 2005
4  Q4 2005
5  Q1 2006
6  Q2 2006
7  Q3 2006
8  Q4 2006
9  Q1 2007
10 Q2 2007
11 Q3 2007
12 Q4 2007
13 Q1 2008
14 Q2 2008
15 Q3 2008
16 Q4 2008

data:

df <- data.frame(Date = c("Q1 2005", "Q1 2006","Q1 2007","Q1 2008","Q2 2005", "Q2 2006","Q2 2007","Q2 2008","Q3 2005", "Q3 2006","Q3 2007","Q3 2008","Q4 2005", "Q4 2006","Q4 2007","Q4 2008"))

CodePudding user response:

Use as.yearqtr from zoo to convert it and then do sort

library(zoo)
df1[with(df1, order(as.yearqtr(Date, format = 'Q%q %Y'))),, drop = FALSE]

-output

  Date
1  Q1 2005
5  Q2 2005
9  Q3 2005
13 Q4 2005
2  Q1 2006
6  Q2 2006
10 Q3 2006
14 Q4 2006
3  Q1 2007
7  Q2 2007
11 Q3 2007
15 Q4 2007
4  Q1 2008
8  Q2 2008
12 Q3 2008
16 Q4 2008

Or another option is parse_date from parsedate

library(parsedate)
library(dplyr)
df1 %>% 
   arrange(parse_date(Date))
      Date
1  Q1 2005
2  Q2 2005
3  Q3 2005
4  Q4 2005
5  Q1 2006
6  Q2 2006
7  Q3 2006
8  Q4 2006
9  Q1 2007
10 Q2 2007
11 Q3 2007
12 Q4 2007
13 Q1 2008
14 Q2 2008
15 Q3 2008
16 Q4 2008

data

df1 <- structure(list(Date = c("Q1 2005", "Q1 2006", "Q1 2007", "Q1 2008", 
"Q2 2005", "Q2 2006", "Q2 2007", "Q2 2008", "Q3 2005", "Q3 2006", 
"Q3 2007", "Q3 2008", "Q4 2005", "Q4 2006", "Q4 2007", "Q4 2008"
)), class = "data.frame", row.names = c(NA, -16L))

CodePudding user response:

in base R, you could do:

df[order(sub("(\\S ) (\\S )", "\\2\\1", df$Date)),, drop = F]
      Date
1  Q1 2005
5  Q2 2005
9  Q3 2005
13 Q4 2005
2  Q1 2006
6  Q2 2006
10 Q3 2006
14 Q4 2006
3  Q1 2007
7  Q2 2007
11 Q3 2007
15 Q4 2007
4  Q1 2008
8  Q2 2008
12 Q3 2008
16 Q4 2008

CodePudding user response:

Another solution:

library(tidyverse)

Date <- data.frame(
  V1=c("Q1 2005", "Q1 2006","Q1 2007","Q1 2008",
       "Q2 2005", "Q2 2006","Q2 2007","Q2 2008",
       "Q3 2005", "Q3 2006","Q3 2007","Q3 2008",
       "Q4 2005", "Q4 2006","Q4 2007","Q4 2008"))

separate(Date, V1, into=c("Quarter", "Year"),sep=" ") %>% 
  arrange(Year, Quarter) %>% 
  {str_c(.$Quarter,.$Year, sep= " ")}

# Alternatively and by Onyambu's suggestion:
separate(Date, V1, into=c("Quarter", "Year"),sep=" ") %>% 
  arrange(Year, Quarter) %>% 
  unite("z",sep = " ") %>% .$z
  •  Tags:  
  • r
  • Related