R how to shift data left so that NA values / holes in the data are on the right side, and different kinds of items are grouped according to order

Question

I have a dataframe that looks like this:

structure(list(INVOICE_ID = 7367109:7367117, Edible = c("Edible", 
NA, NA, NA, NA, NA, NA, NA, "Edible"), Vape = c("Vape", NA, NA, 
NA, NA, NA, NA, NA, NA), Flower = c(NA, "Flower", "Flower", "Flower", 
"Flower", "Flower", "Flower", "Flower", "Flower"), Concentrate = c(NA, 
NA, NA, "Concentrate", NA, NA, NA, NA, NA)), row.names = c(NA, 
-9L), class = c("tbl_df", "tbl", "data.frame"))

How do I shift the items left so that there are no holes in the dataframe? I'd like the output to look like this, where different kinds of items could be stacked in the same column. The first column would always be filled out; the second column may or may not be, etc. The NA values will always be on the right.

output <- tribble(
  ~INVOICE_ID, ~Item_1, ~Item_2, ~Item_3, ~Item_4,
  "7367109", "Edible", "Vape", NA, NA,
  "7367110", "Flower", NA, NA, NA
)

Answer 1

You can sort the values rowwise, with the non-NA first:

df[-1] <- t(apply(df[-1], 1, \(x) c(x[!is.na(x)], x[is.na(x)])))
colnames(df) <- c("INVOICE_ID", paste("Item", 1:4, sep = "_"))

# A tibble: 9 × 5
  INVOICE_ID Item_1 Item_2      Item_3 Item_4
       <int> <chr>  <chr>       <chr>  <chr> 
1    7367109 Edible Vape        NA     NA    
2    7367110 Flower NA          NA     NA    
3    7367111 Flower NA          NA     NA    
4    7367112 Flower Concentrate NA     NA    
5    7367113 Flower NA          NA     NA    
6    7367114 Flower NA          NA     NA    
7    7367115 Flower NA          NA     NA    
8    7367116 Flower NA          NA     NA    
9    7367117 Edible Flower      NA     NA    

Or in one go, in the tidyverse:

library(purrr)
library(dplyr)
bind_cols(df[1],
          pmap_dfr(df[-1], ~ sort(c(...), na.last = TRUE) %>% 
           set_names(paste("Item", 1:4, sep = "_"))))