How to sort column based on specific column-CodePudding

i am trying to sort m_year column which starts from mar-2021 (highest year) to dec-2020 (lowest year)

emp.data <- data.frame(
  emp_id = c (1:12), 
  emp_name = c("Rick","Dan","Michelle","Ryan","Gary","aa","bb","cc","dd","ee","qq","ww"),
  m_year =c("Dec-2020", "Nov-2020", "Oct-2020", "Sep-2020", "Aug-2020", 
                "Jul-2020", "Jun-2020", "May-2020", "Apr-2020", "Mar-2021", "Feb-2021", 
                "Jan-2021")
)

with the help of this code i achieved the sorting of m_year but dont know why the other columns are not sorted based on m_year

emp.data$m_year = emp.data$m_year[order(as.yearmon(emp.data$m_year, "%b-%Y"), decreasing = TRUE)]

CodePudding user response：

You just sorted the m_year column. For sorting the whole dataframe, try this:

emp.data[order(as.yearmon(emp.data$m_year, "%b-%Y"), decreasing = TRUE), ]

CodePudding user response：

You only sorted the m_year column and assigned that back to the m_year column so the rest of your data shouldn't change. However with a small change to your code you can use the same logic to sort the order of the rows in the whole dataset by this column.

emp.data <- data.frame(
  emp_id = c (1:12), 
  emp_name = c("Rick","Dan","Michelle","Ryan","Gary","aa","bb","cc","dd","ee","qq","ww"),
  m_year =c("Dec-2020", "Nov-2020", "Oct-2020", "Sep-2020", "Aug-2020", 
            "Jul-2020", "Jun-2020", "May-2020", "Apr-2020", "Mar-2021", "Feb-2021", 
            "Jan-2021")
)


emp.data[order(zoo::as.yearmon(emp.data$m_year, "%b-%Y"), decreasing = TRUE),]
#>    emp_id emp_name   m_year
#> 10     10       ee Mar-2021
#> 11     11       qq Feb-2021
#> 12     12       ww Jan-2021
#> 1       1     Rick Dec-2020
#> 2       2      Dan Nov-2020
#> 3       3 Michelle Oct-2020
#> 4       4     Ryan Sep-2020
#> 5       5     Gary Aug-2020
#> 6       6       aa Jul-2020
#> 7       7       bb Jun-2020
#> 8       8       cc May-2020
#> 9       9       dd Apr-2020

^{Created on 2022-01-06 by the reprex package (v2.0.1)}

CodePudding user response：

Using lubridate package which works wonders(!) in date-time manipulation:

library(tidyverse)
library(lubridate)

emp.data <- data.frame(
  emp_id = c (1:12), 
  emp_name = c("Rick","Dan","Michelle","Ryan","Gary",
               "aa","bb","cc","dd","ee","qq","ww"),
  m_year = c("Dec-2020", "Nov-2020", "Oct-2020", "Sep-2020", "Aug-2020", 
            "Jul-2020", "Jun-2020", "May-2020", "Apr-2020", "Mar-2021", 
            "Feb-2021", "Jan-2021")
)

emp.data <- emp.data %>% arrange(desc(my(m_year)))

Extremely succinct methods to do what you want to do with dates and times.

The my() function in lubridate reads in strings as dates having month and year specified and outputs as full-form date strings (YYYY-MM-DD). It can read a bunch of different formats and automatically picks up the right one in most cases. Although not done here, you could mutate a new column from m_year using my(), and later if required even extract the year, month, etc. without having to mess with strings too much.