Creating Isolated Responses from Multiple Columns Using Word Search in R

Matching Phrases in Multiple Columns Using Word Search

In this article, we’ll explore how to create isolated responses from multiple columns based on specific words or phrases using R. This technique can be applied to various datasets where there are categorical variables that need to be matched against specific values.

Introduction

The problem presented is a common one in data analysis: when working with multiple selections from a Google form or other categorical variables, you may want to create isolated responses for further analysis. In this case, we’re dealing with feedback from teachers and multiple selections from a Google form that produce a single column. The goal is to match phrases (words) in different columns.

We’ll take an example from the given Stack Overflow question and apply it to the broader concept of word search in R.

Preparation

To begin working on this problem, you should have some basic knowledge of R programming language. Familiarize yourself with the following concepts:

Vectors: A sequence of values.
Data frames: A table containing rows and columns, similar to an Excel spreadsheet.
Factors and character vectors: Used to store categorical data.

For our example, we’ll need the stringr package for string manipulation functions. You can install it via CRAN:

# Install required packages
install.packages("stringr")

# Load necessary libraries
library(stringr)

Step 1: Prepare Your Data

First, let’s prepare our example data in a data frame format similar to the one provided in your question. The example includes several responses and their corresponding words:

# Sample data
words_example <- c("Difficult", "Easy", "Fair", "Challenging", "Necessary", "Useful")
eg_responses <- c("Difficult, Challenging, Fair", "Necessary, Useful", "Cruel", "Easy, Challenging", "School's shouldn't have to do that")

# Create a data frame
df <- data.frame(response = eg_responses, words = words_example)

Step 2: Convert Words into a List

Since we are dealing with phrases (words) across different responses, it might be helpful to convert these lists into a more manageable format. We’ll use the strsplit() function from the base R package to achieve this:

# Split words by comma and remove empty strings
df$words <- lapply(df$words, function(x) strsplit(x, ",")[[1]])

# Remove empty strings if any
df$words <- lapply(df$words, function(x) x[x != "", .])

Step 3: Create a Function to Match Words Across Responses

Next, we’ll create a function that can match each word across all responses and mark its presence or absence in the corresponding column. For simplicity, we’ll treat “NA” as the absence of a specific word.

# Function to find words in each row of the data frame
find_words_in_row <- function(row) {
  # Initialize an empty vector for result
  result <- rep("NA", nrow(df))

  # Loop through each response and its corresponding list of words
  for (i in seq_along(row)) {
    # For each word, check if it's present in the row's words list
    for (word in row[i]) {
      if (word %in% df$words[i]) {
        result[i] <- word
        break
      }
    }
  }

  return(result)
}

# Example usage
example_row <- df[1, ]  # Use any row to test the function

result <- find_words_in_row(example_row)

print(paste("Row:", example_row))
print(paste("Result:", result))

Step 4: Create Data Frame Columns for Matches and Non-Matches

Using our find_words_in_row() function, we can create new columns in the data frame to mark where words are found or not. For each word that is present across all responses, we’ll assign its corresponding response column.

# Function to add match columns for each unique word
add_match_columns <- function(df) {
  # Get unique words across rows (not needed here since words are already split)
  
  # Initialize an empty data frame with the original structure but added new columns
  result_df <- df
  
  # Loop through each unique word and its corresponding response column
  for (i in seq_along(words_example)) {
    word <- words_example[i]
    
    # Apply function to find match/miss match across all rows
    matches <- sapply(df$words, function(x) any(x %in% df$words[i]))
    
    # Create new column based on the matches found
    result_df[, paste0("match_", i)] <- ifelse(matches == 1 & !(matches == FALSE), word, "NA")
  }
  
  return(result_df)
}

# Apply function to our data frame
result_df <- add_match_columns(df)

print(head(result_df))

Step 5: Strip Away Unnecessary Responses and Handle Remaining Words/Phrases

Finally, we need to strip away the responses that have been already accounted for by our match columns. This leaves us with only those responses where no word has matched across all rows.

Afterwards, we can handle any remaining words or phrases that are left at the end of this process.

# Function to filter out used responses and add last response as "Other"
filter_responses <- function(df) {
  # Find unique words across all columns in our data frame (not necessary here since each word is already matched)
  
  # Get the rows where no word has been found across all columns
  df_new <- df[!apply(df$words, 1, any), ]
  
  # Add an "Other" response for those remaining words/phrases not caught by our previous steps
  for (i in seq_along(words_example)) {
    if (!(any(words_example[i] %in% unlist(df$words)))) {
      df_new$match_i <- words_example[i]
    }
  }
  
  return(df_new)
}

# Apply function to our data frame
final_df <- filter_responses(result_df)

print(head(final_df))

Conclusion

This example demonstrates how you can extend the concept of word search across different responses in a structured manner. It requires pre-processing and application of various R functions tailored to your specific needs, including string manipulation for handling phrases (words) across multiple rows.

While this solution has covered the basic steps involved, real-world applications may require adjustments based on your dataset’s specifics or additional requirements not addressed here.

Last modified on 2024-01-28