R: условное форматирование в файлах Excel

Question

Я пытаюсь выделить строки файла Excel на основе совпадения из столбцов в отдельном файле Excel. Я хочу выделить строку в file1, если ячейка в этой строке совпадает с ячейкой в ​​file2.

Я видел, что R-пакет "conditionalFormatting" обладает некоторыми из этих функций, но я не могу понять, как его использовать.

я думаю, что псевдокод будет выглядеть примерно так:

file1 <- read_excel("file1")
file2 <- read_excel("file2")

conditionalFormatting(file1, sheet = 1, cols = 1:end, rows = 1:22, 
rule = "number in file1 is found in a specific column of file 2")

Пожалуйста, дайте мне знать, если это имеет смысл или мне нужно кое-что прояснить.

Спасибо!

Answer 1

conditionalFormatting() Функция встраивает активное условное форматирование в документ Excel, но, вероятно, сложнее, чем нужно для одноразового выделения. Я бы предложил загрузить каждый файл в фрейм данных, определить, какие строки содержат совпадающую ячейку, создать стиль выделения (желтый фон), загрузить файл в качестве объекта рабочей книги, задать для соответствующих строк стиль выделения и сохранить обновленную рабочую книгу. объект.

Следующая функция используется для определения соответствия строк. magrittr Пакет обеспечивает %>% трубы и data.table Пакет обеспечивает transpose() функция.

find_matched_rows <- function(df1, df2) {
  require(magrittr)
  require(data.table)

  # the dataframe object treats each column as a list making it much easier and
  # faster to search via column than row. Transpose the original file1 dataframe
  # to treat the rows as columns.
  df1_transposed <- data.table::transpose(df1)

  # assuming that the location of the match in the second file is irrelevant,
  # unlist the file2 dataframe so that each value in file1 can be searched in a
  # vector
  df2_as_vector <- unlist(df2)

  # determine which columns contain a match. If one or more matches are found,
  # attribute the row as 'TRUE' in the output vector to be used to subset the 
  # row numbers
  match_map <- lapply(df1_transposed,FUN = `%in%`, df2_as_vector) %>%
    as.data.frame(stringsAsFactors = FALSE) %>%
    sapply(function(x) sum(x) > 0)

  # make a vector of row numbers using the logical match_map vector to subset
  matched_rows <- seq(1:nrow(df1))[match_map]
  return(matched_rows)
}

Следующий код загружает данные, находит совпавшие строки, применяет выделение и сохраняет поверх исходного файла1.xlsx. Вторые tst_df1 и tst_df2 обеспечивают простой способ тестирования функции find_matched_rows(). Как и ожидалось, он обнаруживает, что 1-я и 3-я строки первого кадра данных содержат ячейку, которая соответствует ячейке во втором кадре данных.

# used to ensure that the correct rows are highlighted. the dataframe does not
# include the header as an independent row unlike excel.
file1_header_row <- 1
file2_header_row <- 1

tst_df1 <- openxlsx::read.xlsx("./file1.xlsx",
                               startRow = file1_header_row)
tst_df2 <- openxlsx::read.xlsx("./file2.xlsx",
                               startRow = file2_header_row)

#example data for testing
tst_df1 <- data.frame(fname = c("John", "Bob", "Bill"), 
                  lname = c("Smith", "Johnson", "Samson"), 
                  wage = c(10, 15.23, 137.38), 
                  stringsAsFactors = FALSE)
tst_df2 <- data.frame(a = c(10, 34, 284.2), 
                   b = c("Billy", "Bill", "Billy-Bob"), 
                   c = c("Samson", "Johansson", NA), 
                   stringsAsFactors = FALSE)

df_matched_rows <- find_matched_rows(tst_df1, tst_df2)

# any color found in colours() can be used here or hex color beginning with "#"
highlight_style <- openxlsx::createStyle(fgFill = "yellow") 

file1_wb <- openxlsx::loadWorkbook(file = "./file1.xlsx")
openxlsx::addStyle(wb = file1_wb, 
                   sheet = 1, 
                   style = highlight_style,
                   rows = file1_header_row + df_matched_rows,
                   cols = 1:ncol(tst_df1),
                   stack = TRUE,
                   gridExpand = TRUE)
openxlsx::saveWorkbook(wb = file1_wb, 
                       file = "./file1.xlsx",
                       overwrite = TRUE)