Pick out column name of cells that match-CodePudding

I've got a table such as this:

structure(list(Suggested.Symbol = c("CCT4", "DHRS2", "PMS2", 
"FARSB", "RPL31", "ASNS"), p_dc14 = c(0.3975, 0.3707, 6.117e-17, 
0.2975, 0.4443, 0.7661), p_tfc6 = c(0.2078, 0.896, 7.388e-19, 
0.5896, 0.3043, 0.6696), p_tms30 = c(0.5724, 0.3409, 4.594e-13, 
0.2403, 0.1357, 0.3422), p.min = c(0.2078, 0.3409, 7.388e-19, 
0.09781, 0.1357, 0.3422)), row.names = c(NA, 6L), class = "data.frame")

I'd like to create a new column called 'significant'. In this column, on a row-wise basis, I'd like to look up the value of "p.min" in the "p_dc14", "p_tfc6" or "p_tms30" columns, and return the name of the column that matches "p.min".

Can anyone help?

CodePudding user response：

Vectorised Base R option using max.col -

cols <- grep('p_', names(df), value = TRUE)
df$significant <- cols[max.col(round(df$p.min, 3) == round(df[cols], 3))]
df

#  Suggested.Symbol    p_dc14    p_tfc6   p_tms30     p.min significant
#1             CCT4 3.975e-01 2.078e-01 5.724e-01 2.078e-01      p_tfc6
#2            DHRS2 3.707e-01 8.960e-01 3.409e-01 3.409e-01     p_tms30
#3             PMS2 6.117e-17 7.388e-19 4.594e-13 7.388e-19      p_tfc6
#4            FARSB 2.975e-01 5.896e-01 2.403e-01 9.781e-02      p_dc14
#5            RPL31 4.443e-01 3.043e-01 1.357e-01 1.357e-01     p_tms30
#6             ASNS 7.661e-01 6.696e-01 3.422e-01 3.422e-01     p_tms30

Decimal comparisons are not accurate (Read Why are these numbers not equal?) I have rounded the values to 3 decimal places.

CodePudding user response：

A dplyr solution with across() cur_column():

df %>%
  as_tibble() %>% 
  mutate(significant = do.call(coalesce,
      across(starts_with("p_"), ~ ifelse(abs(.x - p.min) < 1e-5, cur_column(), NA))
    )
  )

# # A tibble: 6 × 6
#   Suggested.Symbol   p_dc14   p_tfc6  p_tms30    p.min significant
#   <chr>               <dbl>    <dbl>    <dbl>    <dbl> <chr>      
# 1 CCT4             3.98e- 1 2.08e- 1 5.72e- 1 2.08e- 1 p_tfc6
# 2 DHRS2            3.71e- 1 8.96e- 1 3.41e- 1 3.41e- 1 p_tms30
# 3 PMS2             6.12e-17 7.39e-19 4.59e-13 7.39e-19 p_dc14
# 4 FARSB            2.97e- 1 5.90e- 1 2.40e- 1 9.78e- 2 NA
# 5 RPL31            4.44e- 1 3.04e- 1 1.36e- 1 1.36e- 1 p_tms30
# 6 ASNS             7.66e- 1 6.70e- 1 3.42e- 1 3.42e- 1 p_tms30