Merge rows into one depending on another column with NA-CodePudding

I don't know how to write a title that properly explains my question.

Assume I have a df like so:

original <- structure(list(ID = c("a", NA, "b", NA, NA, "c"), x = c("this", NA, "is", "some", NA, "data"), y = c("lorem", NA, NA, NA, "ipsu", "dolor")), .Names = c("ID", "x", "y"), row.names = c(NA, -6L), class = "data.frame")

And now I'd like to merge de columns x and y (and maybe others even) into one depending on wheter or not the ID column has a value, meaning, if there's no value in the column ID, we assume that all the other rows should be merged together, in this simple example:

target <- structure(list(ID = c("a", "b", "c"), x = c("this lorem","is some ipsum", "data dolor")), .Names = c("ID", "x"), row.names = c(NA, -3L), class = "data.frame")

So the change would look something like this:

CodePudding user response：

This can be done with unite and fill

library(dplyr)
library(tidyr)

unite(original, col = x, x, y, sep = " ", na.rm = T) |> 
  fill(ID, .direction = "down") |> 
  group_by(ID) |> 
  summarise(x = paste(x, collapse = " "), .groups = "drop")

  ID    x             
  <chr> <chr>         
1 a     "this lorem " 
2 b     "is some ipsu"
3 c     "data dolor"

CodePudding user response：

Does this work:

library(dplyr)
original %>% fill(ID, .direction = 'down') %>% group_by(ID) %>% 
       summarise(across(x:y, ~ paste(.[!is.na(.)], collapse = ' ')))
# A tibble: 3 × 3
  ID    x       y    
  <chr> <chr>   <chr>
1 a     this    lorem
2 b     is some ipsu 
3 c     data    dolor