Group by condition with no aggregate R-CodePudding

I have a dataframe, that I'd like to group by ID and DATE, but without any aggregation function. Since my dataframe is like this

ID	DATE	VAL 1	VAL 2	VAL 3	VAL 4
ID1	2018-02-08 01:00:00	10	NA	NA	NA
ID1	2018-02-08 01:00:00	NA	30	NA	NA
ID1	2018-02-08 01:00:00	NA	NA	90	NA
ID1	2018-02-08 01:00:00	NA	NA	NA	60

What I'd like to do is shifting a the values of successive columns up, in order to have the values of the same DATE and of the same ID in a single row.

RESULT EXPECTED

ID	DATE	VAL 1	VAL 2	VAL 3	VAL 4
ID1	2018-02-08 01:00:00	10	30	90	60

CodePudding user response：

library(tidyverse)

d <-structure(list(ID = c("ID1", "ID1", "ID1", "ID1"), DATE = c("2018-02-08 01:00:00", 
                                                                "2018-02-08 01:00:00", "2018-02-08 01:00:00", "2018-02-08 01:00:00"
), VAL.1 = c(10L, NA, NA, NA), VAL.2 = c(NA, 30L, NA, NA), VAL.3 = c(NA, 
                                                                     NA, 90L, NA), VAL.4 = c(NA, NA, NA, 60L)), class = "data.frame", row.names = c(NA, 
                                                                                                                                                    -4L))

d %>% pivot_longer(-c("ID", "DATE")) %>% 
filter(!is.na(value)) %>% 
  pivot_wider(names_from = "name", values_from = "value")
#> # A tibble: 1 × 6
#>   ID    DATE                VAL.1 VAL.2 VAL.3 VAL.4
#>   <chr> <chr>               <int> <int> <int> <int>
#> 1 ID1   2018-02-08 01:00:00    10    30    90    60

^{Created on 2022-12-06 with reprex v2.0.2}

CodePudding user response：

Use fill and slice:

library(dplyr)
library(tidyr)

data |>
  group_by(ID, DATE) |>
  fill(everything(), .direction = "downup") |>
  slice(1) |>
  ungroup()

Output:

# A tibble: 1 × 6
  ID    DATE       VAL1    VAL2  VAL3  VAL4
  <chr> <date>     <time> <dbl> <dbl> <dbl>
1 ID1   2018-02-08 01:00     10    30    90

Data:

library(readr)

data <- read_table("ID  DATE    VAL1    VAL2    VAL3    VAL4
ID1 2018-02-08 01:00:00 10  NA  NA  NA
ID1 2018-02-08 01:00:00 NA  30  NA  NA
ID1 2018-02-08 01:00:00 NA  NA  90  NA
ID1 2018-02-08 01:00:00 NA  NA  NA  60")
``