使用每个级别列中的最高值来计算data.table中的相对值

Question

我正在为热图准备数据，我想绘制相对于最高值的更改。我想要比较的模式，而不是绝对丰度每id，并将热量图的比例限制在0到100 %。

这是我的数据：

head(kallisto_melt,14)

                id         protein_name variable     value relative_abundance
 1: BIJBGGEO_00001 hypothetical protein   tpm_A1 0.0000000                 NA
 2: BIJBGGEO_00001 hypothetical protein   tpm_A2 0.0000000                 NA
 3: BIJBGGEO_00001 hypothetical protein   tpm_A3 0.0000000                 NA
 4: BIJBGGEO_00001 hypothetical protein   tpm_A4 0.0000000                 NA
 5: BIJBGGEO_00001 hypothetical protein   tpm_A5 0.0000000                 NA
 6: BIJBGGEO_00001 hypothetical protein   tpm_A6 0.0000000                 NA
 7: BIJBGGEO_00001 hypothetical protein   tpm_A7 0.0000000                 NA
 8: BIJBGGEO_00002 hypothetical protein   tpm_A1 0.0000000                 NA
 9: BIJBGGEO_00002 hypothetical protein   tpm_A2 0.0000000                 NA
10: BIJBGGEO_00002 hypothetical protein   tpm_A3 0.0000000                 NA
11: BIJBGGEO_00002 hypothetical protein   tpm_A4 0.0703664                 NA
12: BIJBGGEO_00002 hypothetical protein   tpm_A5 0.0000000                 NA
13: BIJBGGEO_00002 hypothetical protein   tpm_A6 0.0000000                 NA
14: BIJBGGEO_00002 hypothetical protein   tpm_A7 0.0863996                 NA

我尝试添加一个相对值的列，它将每个id的最高id设置为100 %，而其他的则相应地设置。我可以想象所有的零都会导致NA (前7行)，但是对于第二个id，我期望这样的结果：

                  id         protein_name variable     value relative_abundance
 1: BIJBGGEO_00001 hypothetical protein   tpm_A1 0.0000000                 NA
 2: BIJBGGEO_00001 hypothetical protein   tpm_A2 0.0000000                 NA
 3: BIJBGGEO_00001 hypothetical protein   tpm_A3 0.0000000                 NA
 4: BIJBGGEO_00001 hypothetical protein   tpm_A4 0.0000000                 NA
 5: BIJBGGEO_00001 hypothetical protein   tpm_A5 0.0000000                 NA
 6: BIJBGGEO_00001 hypothetical protein   tpm_A6 0.0000000                 NA
 7: BIJBGGEO_00001 hypothetical protein   tpm_A7 0.0000000                 NA
 8: BIJBGGEO_00002 hypothetical protein   tpm_A1 0.0000000                 0
 9: BIJBGGEO_00002 hypothetical protein   tpm_A2 0.0000000                 0
10: BIJBGGEO_00002 hypothetical protein   tpm_A3 0.0000000                 0
11: BIJBGGEO_00002 hypothetical protein   tpm_A4 0.0703664                 "somewhere about 81"
12: BIJBGGEO_00002 hypothetical protein   tpm_A5 0.0000000                 0
13: BIJBGGEO_00002 hypothetical protein   tpm_A6 0.0000000                 0
14: BIJBGGEO_00002 hypothetical protein   tpm_A7 0.0863996                 100

我修改了我曾经在这里要求的代码，R how to calculate relative values based on a long format data.frame column?

看起来是这样的：

 kallisto_melt[,relative_abundance := value/(value[max(value)]*100), by = .(id)]

我做错什么了？

Answer 1

有了data.table，我们就可以

# setDT(kallisto_melt)
kallisto_melt[, relative_abundance := value / max(value) * 100, by = id]
kallisto_melt[is.na(relative_abundance), relative_abundance := 0]
kallisto_melt
#                id         protein_name variable     value #relative_abundance
# 1: BIJBGGEO_00001 hypothetical protein   tpm_A1 0.0000000            0.00000
# 2: BIJBGGEO_00001 hypothetical protein   tpm_A2 0.0000000            0.00000
# 3: BIJBGGEO_00001 hypothetical protein   tpm_A3 0.0000000            0.00000
# 4: BIJBGGEO_00001 hypothetical protein   tpm_A4 0.0000000            0.00000
# 5: BIJBGGEO_00001 hypothetical protein   tpm_A5 0.0000000            0.00000
# 6: BIJBGGEO_00001 hypothetical protein   tpm_A6 0.0000000            0.00000
# 7: BIJBGGEO_00001 hypothetical protein   tpm_A7 0.0000000            0.00000
# 8: BIJBGGEO_00002 hypothetical protein   tpm_A1 0.0000000            0.00000
# 9: BIJBGGEO_00002 hypothetical protein   tpm_A2 0.0000000            0.00000
#10: BIJBGGEO_00002 hypothetical protein   tpm_A3 0.0000000            0.00000
#11: BIJBGGEO_00002 hypothetical protein   tpm_A4 0.0703664           81.44297
#12: BIJBGGEO_00002 hypothetical protein   tpm_A5 0.0000000            0.00000
#13: BIJBGGEO_00002 hypothetical protein   tpm_A6 0.0000000            0.00000
#14: BIJBGGEO_00002 hypothetical protein   tpm_A7 0.0863996          100.00000

Answer 2

使用这段代码:-你会找到它的。

library(dplyr)
df1 <- df %>%
  group_by(id,protein_name) %>%
  mutate(relative_abundance = value/max(value)*100)

df1[is.na(df1)] <- 0

数据:-

df<- structure(list(id = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L), .Label = c("BIJBGGEO_00001", "BIJBGGEO_00002"
), class = "factor"), protein_name = structure(c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "hypothetical protein", class = "factor"), 
    variable = structure(c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 1L, 2L, 
    3L, 4L, 5L, 6L, 7L), .Label = c("tpm_A1", "tpm_A2", "tpm_A3", 
    "tpm_A4", "tpm_A5", "tpm_A6", "tpm_A7"), class = "factor"), 
    value = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0703664, 0, 0, 
    0.0863996), relative_abundance = c(NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 
-14L))