2

I would like to keep from an external list:

list <- c("Google", "Yahoo", "Amazon")

The values in the dataframe which have record in the first timestamp (the most old timestamp) in data like this:

dframe <- structure(list(id = c(1L, 1L, 1L, 1L, 2L, 2L, 2L), name = c("Google", 
    "Google", "Yahoo", "Amazon", "Amazon", "Google", "Amazon"), date = c("2008-11-01", 
    "2008-11-02", "2008-11-01", "2008-11-04", "2008-11-01", "2008-11-02", 
    "2008-11-03")), class = "data.frame", row.names = c(NA, -7L))

The expected output is this:

id   name       date
1 Google 2008-11-01
1  Yahoo 2008-11-01
1 Amazon 2008-11-04
2 Amazon 2008-11-01
2 Google 2008-11-02

How is it possible to make it?

Using this it keep only the first record for every id and not for every single value from the list which recorded first time in time

library(data.table)
setDT(dframe)
date_list_first = dframe[order(date)][!duplicated(id)]
slava-kohut
  • 4,203
  • 1
  • 7
  • 24
Nathalie
  • 1,228
  • 7
  • 20

3 Answers3

5

Using data.table:

dframe = data.table(dframe)
dframe[, date := as.Date(date)]

dt = dframe[, .(date = min(date)), .(id, name)]

> dt
   id   name       date
1:  1 Google 2008-11-01
2:  1  Yahoo 2008-11-01
3:  1 Amazon 2008-11-04
4:  2 Amazon 2008-11-01
5:  2 Google 2008-11-02
JDG
  • 1,342
  • 8
  • 18
2

An option using base R

dframe$date <- as.Date(dframe$date)
aggregate(date~ ., dframe, min)
#  id   name       date
#1  1 Amazon 2008-11-04
#2  2 Amazon 2008-11-01
#3  1 Google 2008-11-01
#4  2 Google 2008-11-02
#5  1  Yahoo 2008-11-01
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Any idea for this please https://stackoverflow.com/questions/58141008/keep-before-and-after-date-of-an-external-list ? – Nathalie Sep 27 '19 at 21:42
1

This is how you can do it in dplyr:

dframe %>% mutate(date = as.Date(date)) %>%
group_by(id, name) %>% summarise(date = min(date)) %>%
ungroup()

Nothing fancy really, just group and summarize.

Output

# A tibble: 5 x 3
     id name   date      
  <int> <chr>  <date>    
1     1 Amazon 2008-11-04
2     1 Google 2008-11-01
3     1 Yahoo  2008-11-01
4     2 Amazon 2008-11-01
5     2 Google 2008-11-02
slava-kohut
  • 4,203
  • 1
  • 7
  • 24