具有特定字串的多列的字串匹配和關聯名稱中的值比較-有解無憂

我對跨一系列列進行字串檢測和值比較感興趣。如果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

資訊：

我的資料框有一百多列。我只對某些名稱以 string 結尾的列感興趣.zsd。這些列可以具有NA以下字串值ZAD、ZSD、或其中之一Not Achieved ZSD。每個帶有.zsd字串名稱的.test列都有一個關聯的列。

要求

我想要輸出中的兩個新列smallest.test和zsd.level. 要求如下：

遍歷以字串結尾的列名 .zsd
跨這些列檢測字串 ZSD
如果ZSD僅在其中一列中找到該字串，則在輸出列中zsd.level回傳該列的名稱，.test并從以結尾的列名中回傳相應的值以回傳到輸出列smallest.test。
如果沒有任何列包含字串ZSD，則在輸出列中回傳 NAzsd.level并在相應的輸出列中回傳 0.0 smallest.test。
如果不止一列包含 string ZSD，則選擇對應.test列中具有最小值的列并在輸出中回傳。
If more than one columns contain the string ZSD, and they all have the same value of the corresponding .test column, then pick the last column name for the output and the corresponding value of the .test for the output.

dput()


    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))

Partial solution

Based on the following post: String matching over multiple columns with specific string names, this code can iterate and select the .zsd columns and return the highest column name in the output. But it does not take into account the corresponding values of the .test field. Any help on this would be greatly appreciated.

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))

uj5u.com熱心網友回復：

這是一個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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/321989.html

標籤：r string comparison multiple-columns

上一篇：如何更改python字串子字串資訊

下一篇：正則運算式查找連接的文本