for each row of a dataset of many columns that start with 'to', replace the last non na string with the string of another column in r

To achieve this in R, you can use the dplyr and tidyr packages along with some basic R operations. Here's an example of how you might approach this task. Assume you have a dataset (dataframe) named df and you want to replace the last non-NA string in each row (across columns that start with 'to') with the string from another column, let's say another_column.

main.r
library(dplyr)
library(tidyr)

# Sample dataset
df <- data.frame(
  to_col1 = c("a", NA, "c"),
  to_col2 = c(NA, "b", NA),
  to_col3 = c(NA, NA, "d"),
  another_column = c("x", "y", "z")
)

# Function to find the last non-NA value in columns starting with 'to' and replace it
replace_last_non_na <- function(row, another_column_value) {
  to_cols <- row[grepl("^to", names(row))]
  last_non_na_idx <- max(which(!is.na(to_cols)))
  
  if (!is.na(last_non_na_idx)) {
    to_cols[last_non_na_idx] <- another_column_value
  }
  
  c(to_cols, list(another_column = row$another_column))
}

# Apply the function to each row
df_result <- as.data.frame(t(apply(df, 1, function(row) {
  replace_last_non_na(row, row$another_column)
})))

# Simplify the structure if necessary
df_result <- data.frame(df_result)

print(df_result)
819 chars
33 lines

This code snippet does the following:

  1. Defines a sample dataset df with columns that start with 'to' and another column.
  2. Creates a function replace_last_non_na that identifies the last non-NA value in columns starting with 'to' for each row, and then replaces this value with the value from another_column.
  3. Applies this function to each row of the dataset using apply.
  4. Converts the result back into a dataframe for easier manipulation and viewing.

Remember, the specifics can vary based on the exact structure of your dataset and the versions of the packages you're using. Always make sure to test such operations on a small subset of your data before applying them to the entire dataset.

related categories

gistlibby LogSnag