在R中查找数据框（在每一行中）特定值的列名。

huangapple
go评论97阅读模式
英文:

Find the column name of a specific value on a data frame (in every row) R

问题

以下是已经翻译好的部分：

  1. 我有一个包含100列和超过10,000行的数据框。我为每行添加了两列，分别存储了最低值和次低值。现在，我想要添加两个额外的列，其中包含我找到这些值（最低值和次低值）的列的名称。
  3. 这是我的数据框的一部分：
  5.    x   y 2lowest lowest 
  6. 1 23   3      23      3    
  7. 2 41  12      41     12   
  8. 3 32  33      33     32  
  9. 4 58  38      58     38 
  11. 我希望得到类似下面这样的结果：
  13.    x   y 2lowest lowest pos(2lowest) pos(lowest)
  14. 1 23   3      23      3    x                y
  15. 2 41  12      41     12    x                y
  16. 3 32  33      33     32    y                x
  17. 4 58  38      58     38    x                y
  19. 你有任何解决这个问题的想法吗？
  21. 我已经搜索过，但没有找到类似的内容。
  23. 非常感谢！
英文:

I have a data frame with 100 columns and more than 10k rows. I add two columns with each row's lowest and second lowest values. Now, I want to add two extra columns where I have the name of the columns where I found these values (lowest and second lowest).
This is a part of my data frame so far.

  1. df['2lowest']<-apply(df, 1, function(x) sort(x, decreasing = FALSE)[2])
  2. df['lowest']<-apply(df, 1, function(x) sort(x, decreasing = FALSE)[1])
  4.    x   y 2lowest lowest 
  5. 1 23   3      23      3    
  6. 2 41  12      41     12   
  7. 3 32  33      33     32  
  8. 4 58  38      58     38

and I want something like this.

  1.    x   y 2lowest lowest pos(2lowest) pos(lowest)
  2. 1 23   3      23      3    x                y
  3. 2 41  12      41     12    x                y
  4. 3 32  33      33     32    y                x
  5. 4 58  38      58     38    x                y

Do you have any idea how can I solve this?

I have searched but I could not find any similar to this.

Thank you very much!

答案1

得分: 2

我会一次性在一个单独的apply中完成所有操作。我添加了一个列到输入数据，以使其更有趣，并展示对于相等值的处理。

  2. df = read.table(text = '   x   y z
  3. 1 23   3      4
  4. 2 41  12      11
  5. 3 32  33      32
  6. 4 58  38 58')
  8. result = apply(df, 1, \(x) {
  9.   sx = sort(x)[1:2]
  10.   c(as.list(sx), as.list(names(sx))) |>
  11.     setNames(c("lowest", "2lowest", "pos(lowest)", "pos(2lowest)"))
  12. })
  14. cbind(df, do.call(rbind, result))
  15. #    x  y  z lowest 2lowest pos(lowest) pos(2lowest)
  16. # 1 23  3  4      3       4           y            z
  17. # 2 41 12 11     11      12           z            y
  18. # 3 32 33 32     32      32           x            z
  19. # 4 58 38 58     38      58           y            x
英文:

I'd do it all at once in a single apply. I added a column to the input to make it a little more interesting and illustrate behavior with ties.

  2. df = read.table(text = '   x   y z
  3. 1 23   3      4
  4. 2 41  12      11
  5. 3 32  33      32
  6. 4 58  38 58')
  8. result = apply(df, 1, \(x) {
  9.   sx = sort(x)[1:2]
  10.   c(as.list(sx), as.list(names(sx))) |>
  11.     setNames(c("lowest", "2lowest", "pos(lowest)", "pos(2lowest)"))
  12. })
  14. cbind(df, do.call(rbind, result))
  15. #    x  y  z lowest 2lowest pos(lowest) pos(2lowest)
  16. # 1 23  3  4      3       4           y            z
  17. # 2 41 12 11     11      12           z            y
  18. # 3 32 33 32     32      32           x            z
  19. # 4 58 38 58     38      58           y            x

答案2

得分: 0

我会将数据重塑成长格式，以检查相应的变量名称，然后再将其重塑回宽格式：

  1. library(dplyr)
  2. library(data.table)
  4. df <- tribble(
  5.   ~x, ~y, ~`2lowest`, ~lowest, 
  6.   23,   3,        23,       3,    
  7.   41,  12,        41,      12,   
  8.   32,  33,        33,      32,  
  9.   58,  38,        58,      38
  10. )
  12. as.data.table(df) %>%
  13.   .[, ID := 1:.N] %>%
  14.   melt(id.vars = c("ID", "2lowest", "lowest")) %>%
  15.   .[, ":=" (`pos(lowest)` = variable[lowest == value],
  16.             `pos(2lowest)` = variable[`2lowest` == value]), by = ID] %>%
  17.   dcast(ID + ... ~ variable, value.var = "value")
  1.    ID 2lowest lowest pos(lowest) pos(2lowest)  x  y
  2. 1:  1      23      3           y            x 23  3
  3. 2:  2      41     12           y            x 41 12
  4. 3:  3      33     32           x            y 32 33
  5. 4:  4      58     38           y            x 58 38

希望这个翻译对你有帮助。

英文:

I would reshape the data to long format to check for the corresponding variable name and then reshape it back to the wide format:

  2. library(dplyr)
  3. library(data.table)
  5. df &lt;- tribble(
  6.   ~x, ~y, ~`2lowest`, ~lowest, 
  7.   23,   3,        23,       3,    
  8.   41,  12,        41,      12,   
  9.   32,  33,        33,      32,  
  10.   58,  38,        58,      38
  11. )
  13. as.data.table(df) %&gt;%
  14.   .[, ID := 1:.N] %&gt;%
  15.   melt(id.vars = c(&quot;ID&quot;, &quot;2lowest&quot;, &quot;lowest&quot;)) %&gt;%
  16.   .[, &quot;:=&quot; (`pos(lowest)` = variable[lowest == value],
  17.             `pos(2lowest)` = variable[`2lowest` == value]), by = ID] %&gt;%
  18.   dcast(ID + ... ~ variable, value.var = &quot;value&quot;)
  1.    ID 2lowest lowest pos(lowest) pos(2lowest)  x  y
  2. 1:  1      23      3           y            x 23  3
  3. 2:  2      41     12           y            x 41 12
  4. 3:  3      33     32           x            y 32 33
  5. 4:  4      58     38           y            x 58 38

答案3

得分: 0

我们可以在base R中使用矢量化操作

  1. j1 <- max.col(-df, "first")
  2. i1 <- seq_len(nrow(df))
  3. m1 <- cbind(i1, j1)
  4. j2 <- max.col(-replace(df, m1, Inf), 'first')
  5. m2 <- cbind(i1, j2)
  6. df[c("lowest", "2lowest", "pos(lowest)", "pos(2lowest)")] <- list(df[m1], 
  7.      df[m2], names(df)[j1], names(df)[j2])

-输出

  1. > df
  2.    x  y  z lowest 2lowest pos(lowest) pos(2lowest)
  3. 1 23  3  4      3       4           y            z
  4. 2 41 12 11     11      12           z            y
  5. 3 32 33 32     32      32           x            z
  6. 4 58 38 58     38      58           y            x

数据

  1. df <- structure(list(x = c(23L, 41L, 32L, 58L), y = c(3L, 12L, 33L, 
  2. 38L), z = c(4L, 11L, 32L, 58L)), class = "data.frame", row.names = c("1", 
  3. "2", "3", "4"))
英文:

We may use a vectorized operation in base R

  1. j1 &lt;- max.col(-df, &quot;first&quot;)
  2. i1 &lt;- seq_len(nrow(df))
  3. m1 &lt;- cbind(i1, j1)
  4. j2 &lt;- max.col(-replace(df, m1, Inf), &#39;first&#39;)
  5. m2 &lt;- cbind(i1, j2)
  6.  df[c(&quot;lowest&quot;, &quot;2lowest&quot;, &quot;pos(lowest)&quot;, &quot;pos(2lowest)&quot;)] &lt;- list(df[m1], 
  7.      df[m2], names(df)[j1], names(df)[j2])

-output

  1. &gt; df
  2.    x  y  z lowest 2lowest pos(lowest) pos(2lowest)
  3. 1 23  3  4      3       4           y            z
  4. 2 41 12 11     11      12           z            y
  5. 3 32 33 32     32      32           x            z
  6. 4 58 38 58     38      58           y            x

data

  1. df &lt;- structure(list(x = c(23L, 41L, 32L, 58L), y = c(3L, 12L, 33L, 
  2. 38L), z = c(4L, 11L, 32L, 58L)), class = &quot;data.frame&quot;, row.names = c(&quot;1&quot;, 
  3. &quot;2&quot;, &quot;3&quot;, &quot;4&quot;))

huangapple
  • 本文由 发表于 2023年2月24日 04:28:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/75549999.html
  • dataframe
  • r
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定