1

I have a large dataframe with in the format as seen in the bottom of my comment, but without the last 2 columns.

It is sorted by Name (chr), and Date(POSIXct). I want to add a 4. and 5. column, where the first and last occurence of each Name is listed, like this:

ID Name Date First Last
3 A 2010-10-01 2010-10-01 2010-12-31
4 A 2010-12-03 2010-10-01 2010-12-31
1 A 2010-12-31 2010-10-01 2010-12-31
2 B 2012-01-01 2012-01-01 2012-01-01

How can I do that? There are NAs and NULLs in both Name and Date column.

xsw2yaqw1
  • 21
  • 3

1 Answers1

2

We can convert the 'Date' to Date class, grouped by 'Name', get the min and max of 'Date' (if it is not already sorted or else in place of min/max use first/last)

library(dplyr)
df1 %>%
     mutate(Date = as.Date(Date)) %>%
     group_by(Name) %>%
     mutate(First = min(Date), Last = max(Date)) %>%
     ungroup
akrun
  • 874,273
  • 37
  • 540
  • 662
  • With this method, the values the First and Last columns are from the first and last row of the entire dataset, not that of the group. I tried to sort the dataset by an other column ('Type', not shown in my example above), and after this, I tried to run your code again. It seems that the group_by function doesn't sort the data, even when I left out the 'ungroup' line. I also tried to factor the Name column, but still no success. – xsw2yaqw1 Aug 13 '21 at 17:01
  • For anyone having the same problem: I loaded plyr before dplyr, and it masked the mutate function. Relevant questions: [link](https://stackoverflow.com/questions/42654219/get-first-and-last-values-in-group-dplyr-group-by-with-last-and-first) [link](https://stackoverflow.com/questions/26106146/why-does-summarize-or-mutate-not-work-with-group-by-when-i-load-plyr-after-dp/26106218#26106218) – xsw2yaqw1 Aug 13 '21 at 17:23
  • @xsw2yaqw1 if that is the case, you can do `dplyr::mutate(First = min(Date),..` to correct it – akrun Aug 13 '21 at 17:23
  • That is a common problem. I wonder why this was not corrected or changed the function name – akrun Aug 13 '21 at 17:24