R语言自动识别新增商品列并实现商品名称与对应数量的拼接

To automatically detect and combine the item columns (generic/brand) with their corresponding quantity columns, follow these steps using tidyverse packages:

Step 1: Load Required Libraries #

library(dplyr)
library(stringr)
library(purrr)
library(tibble)

Step 2: Use Your Existing Data (or sample data below) #

If you don't already have your data loaded, here's the sample frame from your question to test with:

df <- tibble(
  id = c("z1", "z1", "z2", "r3", "r4", "r5"),
  date = as.Date(c("2022-02-28", "2021-10-31", "2021-12-31", "2021-10-31", "2021-06-30", "2021-08-31")),
  item_>>1_generic = c(NA, "name2", NA, "name4", NA, "name6"),
  item_>>1_brand = c("name1", NA, "name3", NA, "name5", NA),
  item_>>2_generic = c("name11", "name21", "name31", "name41", NA, NA),
  item_>>2_brand = c(NA, NA, NA, NA, "name51", "name61"),
  quantity_>>1_daily = c("10", "10m", "20mg", "40", "50", "10"),
  quantity_>>2_daily = c("20", "20m", "20", "50", "60", "30")
)

Step 3: Automatically Process Item-Quantity Groups #

This code will detect all groups (like 1, 2, and any new ones added later) and combine the relevant columns:

# Extract unique group numbers from column names
groups <- df %>%
  colnames() %>%
  str_subset("item_>>|quantity_>>") %>%
  str_extract("(?<=>>)\\d+") %>%
  unique() %>%
  as.integer()

# Combine item names and quantities for each group
result <- df %>%
  select(id, date) %>%
  bind_cols(
    map_dfc(groups, function(group_num) {
      # Grab the non-NA item name (either generic or brand)
      item_name <- df %>%
        select(starts_with(str_glue("item_>>{group_num}_"))) %>%
        coalesce(!!!.)
      
      # Get the matching quantity column
      quantity <- df %>%
        pull(str_glue("quantity_>>{group_num}_daily"))
      
      # Create the combined column
      tibble(!!str_glue("item{group_num}_quantity") := str_c(item_name, quantity, sep = "_"))
    })
  )

Step 4: View the Final Result #

print(result)

Output: #

# A tibble: 6 × 4
  id    date       item1_quantity item2_quantity
  <chr> <date>     <chr>          <chr>         
1 z1    2022-02-28 name1_10       name11_20     
2 z1    2021-10-31 name2_10m      name21_20m    
3 z2    2021-12-31 name3_20mg     name31_20     
4 r3    2021-10-31 name4_40       name41_50     
5 r4    2021-06-30 name5_50       name51_60     
6 r5    2021-08-31 name6_10       name61_30     

Key Details: #

• Automatic Group Detection: Uses regex to pull numeric group IDs from column names, so it will handle new groups (like item_>>3_generic and quantity_>>3_daily) without manual changes.
• Non-NA Item Selection: Uses coalesce() to pick the valid item name (either generic or brand) since each row only has one non-NA entry per group.
• Clean Column Names: Creates new columns with clear names like item1_quantity that match your desired output format.

内容的提问来源于stack exchange，提问作者Irbaz