find similar row index in 2 dataframe in R

user_9112767 0
Viewed 32

Here is my test dataset :

test
        date app count
1 21-10-18   A     2
2 21-10-19   A     3
3 21-10-23   A     5
4 21-10-19   B     2
5 21-10-24   B     3

I would like to group the data based on app column and replace the latest date by sys.Date in the date column. I could get the latest date per app by :

latest_date <- test %>% group_by(app)%>%slice(n())

latest_date 
# A tibble: 2 x 3
# Groups:   app [2]
  date       app   count
  <date>     <chr> <int>
1 21-10-23 A         5
2 21-10-24 B         3

How could I find the row indexes in test dataset which are match with the rows in latest_date ? (which (test == latest_date)) ????

what I need at the end is :

        date app count
1 21-10-18   A     2
2 21-10-19   A     3
3 21-12-13   A     5
4 21-10-19   B     2
5 21-12-13   B     3

Also how one could add the rows here with current date, after the max.date of each group, which the output looks :

           date app count
    1 21-10-18   A     2
    2 21-10-19   A     3
    3 21-10-23   A     5
    4 21-12-13   A     5
    5 21-10-19   B     2
    6 21-10-24   B     3
    7 21-12-13   B     3

the add_row command from dplyr does not work on grouped data frame !

dataframe r
1 Answers

Use replace after doing a group by 'app'

library(dplyr)
library(lubridate)
test %>% 
   mutate(date = ymd(date)) %>%
   group_by(app) %>% 
   mutate(date = replace(date, which.max(date), Sys.Date())) %>%
   ungroup

-output

# A tibble: 5 × 3
  date       app   count
  <date>     <chr> <int>
1 2021-10-18 A         2
2 2021-10-19 A         3
3 2021-12-13 A         5
4 2021-10-19 B         2
5 2021-12-13 B         3

With the updated post - use add_row with group_modify

library(tidyr)
test %>%
   mutate(date = ymd(date)) %>%
   group_by(app) %>% 
   group_modify(~ add_row(.x, date = Sys.Date())) %>%
   fill(count) %>%
   ungroup

-output

# A tibble: 7 × 3
  app   date       count
  <chr> <date>     <int>
1 A     2021-10-18     2
2 A     2021-10-19     3
3 A     2021-10-23     5
4 A     2021-12-13     5
5 B     2021-10-19     2
6 B     2021-10-24     3
7 B     2021-12-13     3

data

test <- structure(list(date = c("21-10-18", "21-10-19", "21-10-23", "21-10-19", 
"21-10-24"), app = c("A", "A", "A", "B", "B"), count = c(2L, 
3L, 5L, 2L, 3L)), class = "data.frame", row.names = c("1", "2", 
"3", "4", "5"))
user_37322710
Related