Dataframe column looping and string concatenation based on conditional in R (pref dplyr)

Question

I have a 2-column dataframe. First column contains a single entry of a class of items (in this case, vegetables). The second column is the incoming new_item, which are grocery items of different categories (meat, fruit, veg, etc).

library(tidyverse)
current <- tibble::tribble(
             ~prev_veg,   ~new_item,
             "cabbage",   "lettuce",
                    NA,     "apple",
                    NA,     "beef",
                    NA,   "spinach",
                    NA,  "broccoli",
                    NA,     "mango"
             )
current

I would like to loop through the new item column, and only add vegetables to prev_veg. Any new item that is vegetable needs to be appended onto the existing list. Importantly, I have a vector of all possible vegetables that could occur in that list. The desired dataframe is below.

target_veg <- c("cabbage","lettuce", "spinach", "broccoli"

    desired <- tibble::tribble(
      ~prev_veg,   ~new_item,
      "cabbage",   "lettuce",
      "cabbage, lettuce",     "apple",
      "cabbage, lettuce", "strawbery",
      "cabbage, lettuce",   "spinach",
      "cabbage, lettuce, spinach",  "broccoli",
      "cabbage, lettuce, spinach, broccoli",     "mango"
    )

desired

Finally, there are multiple other data columns in this dataframe that I have not included here (only relevant columns included). Ideally looking for a dplyr solution please.

score 4 · Accepted Answer · answered Feb 24 '22 at 19:43

current <- tibble::tribble(
  ~prev_veg, ~new_item,
  "cabbage", "lettuce",
  NA, "apple",
  NA, "beef",
  NA, "spinach",
  NA, "broccoli",
  NA, "mango"
)
target_veg <- c("cabbage", "lettuce", "spinach", "broccoli")

library(dplyr, warn.conflicts = FALSE)
library(purrr)

current %>%
  mutate(
    prev_veg = accumulate(
      head(new_item, -1),
      ~ if_else(.y %in% target_veg, paste(.x, .y, sep = ", "), .x),
      .init = prev_veg[1]
    )
  )
#> # A tibble: 6 × 2
#>   prev_veg                            new_item
#>   <chr>                               <chr>   
#> 1 cabbage                             lettuce 
#> 2 cabbage, lettuce                    apple   
#> 3 cabbage, lettuce                    beef    
#> 4 cabbage, lettuce                    spinach 
#> 5 cabbage, lettuce, spinach           broccoli
#> 6 cabbage, lettuce, spinach, broccoli mango

^{Created on 2022-02-24 by the reprex package (v2.0.1)}

akrun · Answer 2 · 2022-02-24T20:55:31.343

This may also be created with finding an index with match and then using rowwise to paste

library(dplyr)
library(tidyr)
current  %>%
   mutate(ind =  lag(match(new_item, target_veg))) %>% 
   fill(ind, .direction = "downup") %>%
   rowwise %>%
   mutate(ind = toString(target_veg[seq(ind)])) %>% 
   ungroup %>% 
   mutate(prev_veg = coalesce(prev_veg, ind), .keep = "unused")

-output

# A tibble: 6 × 2
  prev_veg                            new_item
  <chr>                               <chr>   
1 cabbage                             lettuce 
2 cabbage, lettuce                    apple   
3 cabbage, lettuce                    beef    
4 cabbage, lettuce                    spinach 
5 cabbage, lettuce, spinach           broccoli
6 cabbage, lettuce, spinach, broccoli mango

NOTE: rowwise could be slow compared to @IceCreamToucan's accumulate.

Dataframe column looping and string concatenation based on conditional in R (pref dplyr)

2 Answers2