如何用tidyverse获取每行最后一个非NA值对应的列名？

Hey there! Let's figure out how to extract the last answered question column name for each participant using tidyverse tools—this is super common with Qualtrics survey data where participants drop off before finishing all questions. Here's a straightforward approach:

Step 1: Load Tidyverse #

First, make sure you have the tidyverse package loaded:

library(tidyverse)

Step 2: Core Solution #

Assuming your survey data is stored in a tibble called survey_data, use this code to add a new column with the last answered question's name:

survey_data_final <- survey_data %>%
  rowwise() %>%
  mutate(last_answered_question = {
    # Target only your survey question columns (adjust the selector if needed)
    # For example, if your questions start with "Q", use starts_with("Q")
    question_cols <- c_across(starts_with("Q"))
    
    # Find positions of non-missing responses
    non_missing_positions <- which(!is.na(question_cols))
    
    # Handle cases where a participant didn't answer any questions
    if (length(non_missing_positions) == 0) {
      NA_character_
    } else {
      # Get the last non-missing position and map to column name
      last_pos <- max(non_missing_positions)
      colnames(survey_data)[which(str_detect(colnames(survey_data), "^Q"))[last_pos]]
    }
  }) %>%
  ungroup()

Breakdown of the Code: #

• rowwise(): Ensures we process each participant's row individually, so we can check responses per person.
• c_across(starts_with("Q")): Grabs only the columns that correspond to survey questions. Adjust this selector to match your actual column naming pattern—use everything() if all columns are questions, or matches("^Q\\d+") for numbered questions, for example.
• which(!is.na(question_cols)): Identifies which columns have non-missing responses for the current row.
• The if/else check: Handles edge cases where a participant didn't answer any questions, returning NA instead of throwing an error.
• max(non_missing_positions): Gets the index of the last non-missing response, then we map that index back to the original column name from your dataset.

Example with Sample Data #

Let's test this with a small simulated dataset that mimics Qualtrics dropout behavior:

# Simulate survey data
survey_data <- tibble(
  participant_id = 1:4,
  Q1 = c("Yes", "No", NA, NA),
  Q2 = c("Red", NA, NA, NA),
  Q3 = c(NA, "Disagree", "Agree", NA)
)

# Run the solution code
survey_data_final <- survey_data %>%
  rowwise() %>%
  mutate(last_answered_question = {
    question_cols <- c_across(starts_with("Q"))
    non_missing_positions <- which(!is.na(question_cols))
    if (length(non_missing_positions) == 0) {
      NA_character_
    } else {
      last_pos <- max(non_missing_positions)
      colnames(survey_data)[which(str_detect(colnames(survey_data), "^Q"))[last_pos]]
    }
  }) %>%
  ungroup()

The resulting survey_data_final will look like this:

Key Notes #

• Adjust Column Selectors: Make sure starts_with("Q") matches your actual question column names. If your questions have a different prefix (like "SURVEY_"), update the selector accordingly.
• Exclude Metadata Columns: If your dataset includes metadata columns (like participant ID, response timestamp), make sure to exclude them from c_across() so they aren't counted as "answered questions".

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