Hey there! Let's tackle this problem step by step. We'll use dplyr for data manipulation (plus a touch of tidyr for flexibility) to get exactly the output you're looking for.

First, let's start with your original data and define the abbreviation mapping for your oth values:

library(dplyr)
library(tidyr)

# Define abbreviation key for oth values (add more entries here if needed)
oth_abbr <- c("red" = "r", "yellow" = "y")

# Your original dataset
oth<- c("red", NA, NA, "yellow") 
b_g<- c(1, 2, 3, 2) 
s_g<- c(2, 3, 1, 4) 
b_p<- c(1, 2, 3, 2) 
s_p<- c(2, 3, 1, 4) 
b_o<- c(3, 0, 0, 1) 
s_o<- c(2, 0, 0, 4) 

df<- data.frame(oth, b_g, s_g, b_p, s_p, b_o, s_o) 

Basic Approach (for a small number of oth values) #

If you only have a few possible values in oth, this straightforward method works perfectly:

df_final <- df %>%
  # Create new columns for each oth abbreviation, starting at 0
  mutate(
    b_r = 0,
    s_r = 0,
    b_y = 0,
    s_y = 0
  ) %>%
  # Process each row individually to copy values from b_o/s_o to the right columns
  rowwise() %>%
  mutate(
    # If oth isn't NA, grab the corresponding abbreviation and copy the o-column values
    across(ends_with(oth_abbr[oth]), ~ if(!is.na(oth)) get(str_replace(cur_column(), oth_abbr[oth], "o")) else .x)
  ) %>%
  ungroup() %>%
  # Set b_o/s_o to 0 where oth is NA (matches your desired output)
  mutate(
    across(c(b_o, s_o), ~ if(is.na(oth)) 0 else .x)
  ) %>%
  # Reorder columns to match your expected layout
  select(oth, b_g, s_g, b_p, s_p, b_o, s_o, b_r, s_r, b_y, s_y)

# Check the result
df_final

Breakdown of the basic approach: #

• We first create all the new abbreviation columns and initialize them to 0.
• Using rowwise(), we check each row's oth value. If it's not NA, we find the matching abbreviation columns and copy the values from b_o and s_o into them.
• For rows where oth is NA, we set b_o and s_o to 0 as shown in your sample output.
• Finally, we reorder the columns to match your expected structure.

Dynamic Approach (for many oth values) #

If you anticipate adding more values to oth later, this dynamic method avoids manually creating each new column:

# Get unique non-NA values from oth and generate their column names
unique_oth_vals <- na.omit(unique(df$oth))
new_col_names <- expand.grid(
  prefix = c("b_", "s_"),
  abbr = oth_abbr[unique_oth_vals]
) %>%
  mutate(col_name = paste0(prefix, abbr)) %>%
  pull(col_name)

# Process the data dynamically
df_dynamic_final <- df %>%
  # Add all new columns at once, initialized to 0
  mutate(!!!set_names(rep(0, length(new_col_names)), new_col_names)) %>%
  rowwise() %>%
  mutate(
    across(ends_with(oth_abbr[oth]), ~ if(!is.na(oth)) get(str_replace(cur_column(), oth_abbr[oth], "o")) else .x)
  ) %>%
  ungroup() %>%
  mutate(across(c(b_o, s_o), ~ if(is.na(oth)) 0 else .x)) %>%
  # Sort columns to keep original ones first, then new ones in order
  select(oth, starts_with(c("b_", "s_")) %>% sort())

# Check the dynamic result
df_dynamic_final

Why this works: #

• We automatically generate all necessary column names based on the unique values in oth and your abbreviation mapping.
• The !!!set_names() trick lets us add all new columns in one go without typing each one manually.
• The rest of the logic matches the basic approach, so you get the same output but with more flexibility for future changes.

Both methods will produce exactly the output you shared. Just update the oth_abbr mapping if you add new values to the oth column later!

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