ranking date in R

Question

I have a dataframe of two columns TSH values and dates. I want to give a rank number for each day (1 starting from the oldest date) as in the picture.

Answer 1

It is preferable to copy data using dput and not use images/screen captures.

Using your example data.frame, we'll call df, you can do the following.

Your first step can be to change your DATE to a date object. The code you provided in your comment can work, but if you use pipes such as %>% below, you won't need to make intermediate variables or objects. Instead, it will feed the result of one line of code into the next line. See this for more information.

Next, using dplyr, you can sort by Date (oldest first) and then use dense_rank.

To write your result to an Excel file, you can use write.xlsx from the xlsx package. There are other options available for this as well.

Here is a complete example:

library(dplyr)
library(xlsx)

df %>%
  mutate(DATE = as.Date(DATE, format = "%d.%m.%y")) %>%
  arrange(DATE) %>%
  mutate(ID = dense_rank(DATE)) %>%
  write.xlsx("filename.xlsx")

An alternative would be cur_group_id after grouping by Date:

df %>%
  mutate(DATE = as.Date(DATE, format = "%d.%m.%y")) %>%
  arrange(DATE) %>%
  group_by(DATE) %>%
  mutate(ID = cur_group_id()) %>%
  ungroup %>%
  write.xlsx("filename.xlsx")
  

Output

  TSH       DATE ID
1 1.3 2015-01-05  1
2 0.9 2015-01-05  1
3 1.4 2015-01-05  1
4 1.6 2015-01-05  1
5 3.1 2015-02-24  2
6 1.6 2015-02-24  2