Sort by Month and Year in Dataframe

Question

I'm having trouble sorting by month/year format in R. I have data in a %m/%Y format but in trying to use

df_prod<-df_prod[order(as.Date(df_prod$date,format="%m/%Y")),]

The data frame is not sorting. I'm getting a text-like ordering (01/2000,01/2001,01/2002)

Additional details: I'm performing an dplyr aggregation on a dataframe that was nicely sorted in %m/%Y format:

df_prod<-df %>%
  group_by(date,comp_id) %>%
  summarise(a_prod=prod(1+TRT)-1)

Thank you

Most likely as.Date(df_prod$date,format="%m/%Y") is producing an invalid date and thus the order function is sorting based on a string value. — Dave2e, Mar 02 '18 at 21:52
As dave2e mentions, "%m/%Y" is not a date. You need to include a day in order to have a valid date. The easiest solution is to paste "/1" at the beginning and use "%d/%d/%Y". This has been asked before. — lmo, Mar 02 '18 at 22:02

score 1 · Accepted Answer · answered Mar 02 '18 at 22:06

1

As @lmo has already mentioned that using just %m/%Y will not create a valid date. One solution is to use as.yearmon from zoo package.

library(zoo)    
df_prod<-df_prod[order(as.yearmon(df_prod$date,format="%m/%Y")),]

answered Mar 02 '18 at 22:06

MKR

score 0 · Answer 2 · answered Mar 02 '18 at 22:01

%m/%Y is a tricky starting format for a date, if that's what you ultimately need:

grab date components from the string:

aa <- format(Sys.Date(), "%m/%Y")
m <- as.numeric(substr(aa, 1, 2))
y <- as.numeric(substr(aa, 4, 8))

Then you can paste them together into a date format and sort:

as.Date(paste(y, "-", m, "-01", sep = ''))

2 Answers2