-1

I am trying to sort date column which is in the format M/Y. I tried using several ways given in other posts but none helped. Please suggest. For 2012 it shows correctly with all the months given with 2012 but then it changes the pattern. Thank you.

Here is how it looks like currently

Sanket Saxena
  • 31
  • 2
  • 2
    Welcome to SO. Please take a look at these tips on how to produce a [minimum, complete, and verifiable example](http://stackoverflow.com/help/mcve), as well as this post on [creating a great example in R](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). You are not working with a date variable. It is probably a factor variable, but might be a character variable. You will have to convert it to character, use `strsplit` and `paste` or `gsub` to get the format as Y/M. Include 0s in front of single digit months (01 for January). Then it will sort properly. – lmo Jun 08 '17 at 13:15

3 Answers3

1

That should work:

#let's add a column to your data.frame with a proper date
df$date_of_first_day <- as.Date(paste0("1/",monyear),format = "%d/%m/%y")
# then sort the data.frame
df <- df[order(df$date_of_first_day,decreasing = FALSE),]
# then get rid of this column
df$date_of_first_day <- NULL
moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
  • That worked like a charm. I was doing the same thing but as a hit and try as I am yet not good with the syntax in R. Your comments before every command helped to understand the flow. Thank you very much. – Sanket Saxena Jun 08 '17 at 13:47
  • you're welcome, I just corrected the last line though where there was a little error :). consider validating the answer if it suits you as it is. – moodymudskipper Jun 08 '17 at 13:50
  • Thank you for the note. I did not know that I can edit it though I did correct it in my code. – Sanket Saxena Jun 08 '17 at 14:13
1

1) This converts DF (see Note) to a zoo time series whose first column is of class yearmon and which is sorted by that.

library(zoo)

z <- read.zoo(DF, FUN = as.yearmon, format = "%m/%y")

You may wish to keep it as a zoo object as above or to convert it back to a data frame:

fortify.zoo(z)

2) This is an alternative to read.zoo. It also uses class yearmon.

library(zoo)

DF2 <- transform(DF, monyear. = as.yearmon(monyear., "%m/%y"))
DF2[order(DF2$monyear.), ]

Note: The above uses DF defined as:

library(tesseract)
u <- "https://i.stack.imgur.com/Szlhw.png"
DF <- read.table(text = ocr(u), header = TRUE, as.is = TRUE)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
1

Using lubridate and dplyr:

library(dplyr)
library(lubridate)
df <- data.frame( monyear = c( "1/12", "10/13", "1/13", "2/10" ), 
                  amount = 1:4, 
                  prices = 1:4 )

Original Data:

  monyear amount prices
1    1/12      1      1
2   10/13      2      2
3    1/13      3      3
4    2/10      4      4

To sort by date:

df <- df %>%
      arrange( parse_date_time(monyear,"my") )

The function parse_date_time will read the format as "my" or "month year"

Output:

  monyear amount prices
1    2/10      4      4
2    1/12      1      1
3    1/13      3      3
4   10/13      2      2
CPak
  • 13,260
  • 3
  • 30
  • 48