Merge Row into one with condition and replace value in one row with value in the other R

user_13440033 0
Viewed 107

I have a dataset that look like this in R:

A <- c("X", "Y", "Z", "W", "U")
B <- c("apple", "pear", "apple", "pear", "pear")
C <- c("december", "december" ,"June", "june", "march")
D <- c("Winter", "Summer" ,"Winter", "Summer", "Summer")
df <- data.frame(A,B,C,D);df

  A     B        C      D
1 X apple december Winter
2 Y  pear december Summer
3 Z apple     June Winter
4 W  pear     june Summer
5 U  pear    march Summer

I would like to combine row by column C (to mix row 1 with row 2 and row 3 with row 4) but I also want to replace the value in row B, taking into account column D. Basically, when 2 values are indentical in C ("december" for example), that the value in B when D is "Summer" ("pear") is always replace by the value in B when D is "Winter" (apple) I would like to have at the end a dataframe like this :

  A     B        C             D
1 X apple december Winter,Summer
2 Z apple     june Winter,Summer
3 U  pear    march        Summer

I really want to keep a the 2 values in column D when 2 rows were combined.

Does someone have an idea?

row conditional-statements r
2 Answers

A data.table option

setDT(df)[
  ,
  c(
    lapply(
      setNames(.(A, B), c("A", "B")),
      function(x) if ("Winter" %in% D) replace(x, D == "Summer", x[D == "Winter"]) else x
    ),
    .(D = D)
  ),
  C
][
  ,
  lapply(.SD, function(x) toString(unique(x))),
  C
][,
  .SD,
  .SDcols = names(df)
]

gives

   A     B        C              D
1: X apple december Winter, Summer
2: Z apple     june Winter, Summer
3: U  pear    march         Summer

Data

> dput(df)
structure(list(A = c("X", "Y", "Z", "W", "U"), B = c("apple",
"pear", "apple", "pear", "pear"), C = c("december", "december",
"june", "june", "march"), D = c("Winter", "Summer", "Winter",
"Summer", "Summer")), class = "data.frame", row.names = c(NA,
-5L))
user_121587570

An option with dplyr

library(dplyr)
library(tidyr)
df %>% 
    group_by(C = tolower(C)) %>% 
    mutate(across(c(A, B), ~ if(n_distinct(D) > 1) replace(., D %in% 'Summer', NA) else
         .)) %>%
    fill(c(A, B)) %>% 
    summarise(across(c(A, B), first), D = toString(D), .groups = 'drop')
# A tibble: 3 x 4
#  C        A     B     D             
#* <chr>    <chr> <chr> <chr>         
#1 december X     apple Winter, Summer
#2 june     Z     apple Winter, Summer
#3 march    U     pear  Summer
user_37322710
Related