时间戳

首页 > 解决方案 > 多个列上的字符串匹配，具有特定字符串和关联名称中的值比较

r - 多个列上的字符串匹配，具有特定字符串和关联名称中的值比较

问题描述

我有兴趣在一系列列中进行字符串检测和值比较。如果在列中找到字符串（在本例中为ZSD），则需要比较它们在另一列中的对应值。

输入

我的输入如下：

a.zsd                 a.test b.zsd b.test c.zsd c.test d.zsd d.test
'ZSD'                 0.0   'ZAD'  1.0    NA    0.5   'ZAD'    1.0
'ZAD'                 1.0    NA    0.0    NA    0.5   'ZSD'    0.0
NA                    0.5    NA    0.5   'ZAD'  0.5    NA      0.5
'Not Achieved ZSD'    0.0    NA    0.5   'ZAD'  0.5    NA      0.5
'ZSD'                 1.0   'ZSD'  0.5    NA    0.5   'ZSD'    0.0
NA                    0.0    NA    0.0    NA    0.5    NA      0.0
NA                    1.0   'ZSD'  0.0   'ZSD'  0.5   'ZSD'    1.0

输出

在我的输出中，我想要两个额外的列smallest.test和zsd.level：

a.zsd                 a.test b.zsd b.test c.zsd c.test d.zsd d.test smallest.test zsd.level
'ZSD'                 0.0   'ZAD'  1.0    NA    0.5   'ZAD'    1.0  0.0           a
'ZAD'                 1.0    NA    0.0    NA    0.5   'ZSD'    0.0  0.0           d
NA                    0.5    NA    0.5   'ZAD'  0.5    NA      0.5  0.0           NA        
'Not Achieved ZSD'    0.0    NA    0.5   'ZAD'  0.5    NA      0.5  0.0           a
'ZSD'                 1.0   'ZSD'  0.5    NA    0.5   'ZSD'    0.0  0.0           d
NA                    0.0    NA    0.0    NA    0.5    NA      0.0  0.0           NA
NA                    1.0   'ZSD'  0.0   'ZSD'  0.5   'ZSD'    1.0  0.0           b

信息：

我的数据框有一百多列。我只对名称以字符串结尾的某些列感兴趣.zsd。这些列可以具有NA以下字符串值ZAD、ZSD、或其中之一Not Achieved ZSD。具有字符串名称的每一列.zsd都有一个关联的.test列。

要求

我想要输出中的两个新列smallest.test和zsd.level. 要求如下：

  1. 遍历以字符串结尾的列名.zsd

  2. 在这些列中检测字符串ZSD

  3. 如果ZSD仅在其中一列中找到该字符串，则在输出列中返回该列的名称，并从要返回到输出列zsd.level的列名称中返回对应的值。.testsmallest.test

  4. 如果没有任何列包含该字符串ZSD，则在输出列中返回 NA，zsd.level并在相应的输出列中返回 0.0 smallest.test。

  5. 如果多列包含 string ZSD，则选择对应.test列中值最小的列并在输出中返回。

  6. 如果多个列包含 string ZSD，并且它们都具有相同.test列的相应值，则选择输出的最后一列名称和输出的相应值.test。

输入（）


    dput(df)
    structure(list(a.zsd = c("ZSD", "ZAD", NA, "Not Achieved ZSD", "ZSD", NA, NA), 
                   a.test = c(0, 1, 0.5, 0, 1, 0, 1), 
                   b.zsd = c("ZAD", NA, NA, NA, "ZSD", NA, "ZSD"), 
                   b.test = c(1, 0, 0.5, 0.5, 0.5, 0, 0), 
                   c.zsd = c(NA, NA, "ZAD", "ZAD", NA, NA, "ZSD"), 
                   c.test = c(0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5), 
                   d.zsd = c("ZAD", "ZSD", NA, NA, "ZSD", NA, "ZSD"), 
                   d.test = c(1, 0, 0.5, 0.5, 0, 0, 1)), 
                   class = "data.frame", row.names = c(NA, -7L))

部分解决方案

基于以下帖子：String matching over multiple columns with specific string names，此代码可以迭代并选择.zsd列并返回输出中最高的列名。但它没有考虑到.test字段的对应值。对此的任何帮助将不胜感激。

library(dplyr)
library(tidyr)
library(stringr)

df %>%  
  mutate(across(contains("zsd"), ~case_when(str_detect(., "ZSD") ~ cur_column()), .names = 'new_{col}')) %>%
  unite(zsd_level, starts_with('new'), na.rm = TRUE, sep = ' ') %>% 
  mutate(zsd_level = str_remove_all(zsd_level, ".zsd"),
         zsd_level = str_sub(zsd_level, -1))

标签： rstringcomparisonmultiple-columns

解决方案

这是一个base涉及which.min假定相应的“.test”和“.zsd”列相邻的解决方案。

第一个代码块可用于此目的，但如果给定行与最小值有关联，它将给出第一个实例。下面是一个函数，可用于选择最小值的第一个或最后一个实例。

# sample data
df <- structure(list(a.zsd = c("ZSD", "ZAD", NA, "Not Achieved ZSD", "ZSD", NA, NA), 
               a.test = c(0, 1, 0.5, 0, 1, 0, 1), 
               b.zsd = c("ZAD", NA, NA, NA, "ZSD", NA, "ZSD"), 
               b.test = c(1, 0, 0.5, 0.5, 0.5, 0, 0), 
               c.zsd = c(NA, NA, "ZAD", "ZAD", NA, NA, "ZSD"), 
               c.test = c(0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5), 
               d.zsd = c("ZAD", "ZSD", NA, NA, "ZSD", NA, "ZSD"), 
               d.test = c(1, 0, 0.5, 0.5, 0, 0, 1)), 
          class = "data.frame", row.names = c(NA, -7L))


# select .zsd columns
zsd_cols <- grep(".zsd", names(df), value = TRUE)
zsd_df <- df[, zsd_cols]

# select .test columns
test_cols <- gsub("zsd", "test",zsd_cols)
test_df <- df[, test_cols]

# convert "Not Achieved ZSD" to "ZSD"
zsd_df[zsd_df == "Not Achieved ZSD" ] <- "ZSD"

# assign NA to non "ZSD" cells
zsd_df[zsd_df != "ZSD"] <- NA

# assign 999 test_df values whose corresponding zsd_df is NA
test_df[is.na(zsd_df)] <- 999

# return cols which hold minimum
nams <- names(test_df)[apply(test_df, 1 ,which.min)]

# scrub .test suffix
nams <- gsub(".test", "", nams)

# return mins
mins <- apply(test_df, 1 ,min)

# assign values less than 999 as smallest test, or zero
df$smallest.test <- ifelse(mins < 999, mins, 0)

# assign name if corresponding min less than 999 or NA
df$zsd_level <- ifelse(mins < 999, nams, NA)

> df
             a.zsd a.test b.zsd b.test c.zsd c.test d.zsd d.test smallest.test zsd_level
1              ZSD    0.0   ZAD    1.0  <NA>    0.5   ZAD    1.0             0         a
2              ZAD    1.0  <NA>    0.0  <NA>    0.5   ZSD    0.0             0         d
3             <NA>    0.5  <NA>    0.5   ZAD    0.5  <NA>    0.5             0      <NA>
4 Not Achieved ZSD    0.0  <NA>    0.5   ZAD    0.5  <NA>    0.5             0         a
5              ZSD    1.0   ZSD    0.5  <NA>    0.5   ZSD    0.0             0         d
6             <NA>    0.0  <NA>    0.0  <NA>    0.5  <NA>    0.0             0      <NA>
7             <NA>    1.0   ZSD    0.0   ZSD    0.5   ZSD    1.0             0         b

编辑

相同的想法，但是当领带连续出现时可以获得第一个或最后一个最小值的函数：

# adjusted sample data
df <- data.frame(a.zsd = c("ZSD", "ZAD", NA, 
                        "Not Achieved ZSD", "ZSD", NA, NA), 
                 a.test = c(0, 1, 0.5, 0, 1, 0, 1), 
                 b.zsd = c("ZAD", NA, NA, NA, "ZSD", NA, "ZSD"), 
                 b.test = c(1, 0, 0.5, 0.5, 0.0, 0, 0), # adjusted 
                 c.zsd = c(NA, NA, "ZAD", "ZAD", NA, NA, "ZSD"), 
                 c.test = c(0.5, 0.5, 0.5, 0.5, 0.5, 0.5, 0.5), 
                 d.zsd = c("ZAD", "ZSD", NA, NA, "ZSD", NA, "ZSD"), 
                 d.test = c(1, 0, 0.5, 0.5, 0, 0, 1))


appendMin <- function(df, last_min = TRUE){
  
  # select .zsd columns
  zsd_cols <- grep(".zsd", names(df), value = TRUE)
  zsd_df <- df[, zsd_cols]
  if(last_min) { zsd_df <- rev(zsd_df) } # for last min
  
  # select .test columns
  test_cols <- gsub("zsd", "test",zsd_cols)
  test_df <- df[, test_cols]
  if(last_min) { test_df <- rev(test_df) } # for last min
  
  # convert "Not Achieved ZSD" to "ZSD"
  zsd_df[zsd_df == "Not Achieved ZSD" ] <- "ZSD"
  
  # assign NA to non "ZSD" cells
  zsd_df[zsd_df != "ZSD" ] <- NA
  
  # assign 999 test_df values whose corresponding zsd_df is NA
  test_df[is.na(zsd_df)] <- 999
  
  # return cols which hold the first minimum 
  nams <- names(test_df)[apply(test_df, 1 ,which.min)]
  
  # scrub .test suffix
  nams <- gsub(".test", "", nams)
  # return mins
  mins <- apply(test_df, 1 ,min)
  
  # assign values less than 999 as smallest test, or zero
  df$smallest.test <- ifelse(mins < 999, mins, 0)
  
  # assign name if corresponding min less than 999 or NA
  df$zsd_level <- ifelse(mins < 999, nams, NA)
  
  return(df)
  
}


ties_first <- appendMin(df, last_min = FALSE)
ties_last <- appendMin(df, last_min = TRUE)

推荐阅读