从一个数据框中替换另一个数据中的值

Shaxi Liver 发表于 Dev

沙溪肝

我有两个data.frames：

第一的：

   > dput(head(tbl_mz))
    structure(list(m.z = c(258.1686969, 258.168752, 587.8313625, 
    587.8425292, 523.2863282, 523.2859396), Measured.mass = c(514.3228408, 
    514.3229511, 1173.648172, 802.4706732, 1272.645144, 1044.557326
    )), .Names = c("m.z", "Measured.mass"), row.names = c(NA, 6L), class = "data.frame")

第二：

> dput(head(tbl_exl))
structure(list(V1 = c(802.4706732, 1272.649209, 1272.646875, 
1272.646599, 1272.646521, 1272.645144), V2 = c(NA, NA, NA, NA, 
NA, NA), V3 = c(NA, NA, NA, NA, NA, NA), V4 = c(NA, NA, NA, NA, 
NA, NA), V5 = c(NA, NA, NA, NA, NA, NA), V6 = structure(c(2L, 
2L, 2L, 2L, 2L, 2L), .Label = c("", "Positive"), class = "factor"), 
    V7 = c(28.7, 29.4, 29.4, 23.8, 28.6, 23.3), V8 = c(30.7, 
    31.4, 31.4, 25.8, 30.6, 25.3), X = c(NA, NA, NA, NA, NA, 
    NA), X.1 = c(NA, NA, NA, NA, NA, NA), X.2 = c(NA, NA, NA, 
    NA, NA, NA)), .Names = c("V1", "V2", "V3", "V4", "V5", "V6", 
"V7", "V8", "X", "X.1", "X.2"), row.names = c(NA, 6L), class = "data.frame")

我想用其他表中的值替换tbl_exl列中V1的某些值tbl_mz。从列中的值V1（tbl_exl）可以在列中找到Measured.mass（tbl_mz），并且它们应该由值从下一列来替换m.z在tbl_mz数据帧。

换句话说，中的值V1应替换为m.z值。

问题在于，并非所有的值V1都无法在另一个数据框中找到。可以删除的可以删除，也可以照原样保留。

我想要得到的输出：

 > dput(head(tbl_exl_modified))
    structure(list(V1 = c(587.8425292, 1272.649209, 1272.646875, 
    1272.646599, 1272.646521, 523.2863282), V2 = c(NA, NA, NA, NA, 
    NA, NA), V3 = c(NA, NA, NA, NA, NA, NA), V4 = c(NA, NA, NA, NA, 
    NA, NA), V5 = c(NA, NA, NA, NA, NA, NA), V6 = structure(c(2L, 
    2L, 2L, 2L, 2L, 2L), .Label = c("", "Positive"), class = "factor"), 
        V7 = c(28.7, 29.4, 29.4, 23.8, 28.6, 23.3), V8 = c(30.7, 
        31.4, 31.4, 25.8, 30.6, 25.3), X = c(NA, NA, NA, NA, NA, 
        NA), X.1 = c(NA, NA, NA, NA, NA, NA), X.2 = c(NA, NA, NA, 
        NA, NA, NA)), .Names = c("V1", "V2", "V3", "V4", "V5", "V6", 
    "V7", "V8", "X", "X.1", "X.2"), row.names = c(NA, 6L), class = "data.frame")

阿克伦

你可以试试看match。根据match两个数据集的列之间（“ Measured.mass”，“ V1”）创建数字索引。删除NA值（“ indx1”，“ indxN1”），然后根据这些索引将“ V1”值替换为“ mz”。

indx <- match(tbl_mz$Measured.mass, tbl_exl$V1)
indx1 <- indx[!is.na(indx)]
indxN <-  match(tbl_exl$V1, tbl_mz$Measured.mass)
indxN1 <- indxN[!is.na(indxN)]
tbl_exl$V1[indx1] <- tbl_mz$m.z[indxN1]

identical(tbl_exl, tbl_exl_modified)
#[1] TRUE

或者使用left_join从dplyr

library(dplyr)
tbl_exl1 <- left_join(tbl_exl, tbl_mz, by=c('V1'='Measured.mass')) %>%
                mutate(V1= pmax((NA^!is.na(m.z))*V1, m.z,
                                                 na.rm=TRUE)) %>% 
                select(-m.z)

 tbl_exl1
 #        V1 V2 V3 V4 V5       V6   V7   V8  X X.1 X.2
 #1  587.8425 NA NA NA NA Positive 28.7 30.7 NA  NA  NA
 #2 1272.6492 NA NA NA NA Positive 29.4 31.4 NA  NA  NA
 #3 1272.6469 NA NA NA NA Positive 29.4 31.4 NA  NA  NA
 #4 1272.6466 NA NA NA NA Positive 23.8 25.8 NA  NA  NA
 #5 1272.6465 NA NA NA NA Positive 28.6 30.6 NA  NA  NA
 #6  523.2863 NA NA NA NA Positive 23.3 25.3 NA  NA  NA

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。