我有一个具有如下结构的字符矩阵:
dog 1 cow 9 mouse 7
bird 10 tiger 1 gnu 2
tiger 3 deer 7 deer 27
skunk 2 rat 50 NA NA
mouse 8 snake 3 NA NA
cow 7 NA NA NA NA
sheep 21 NA NA NA NA
gnu 5 NA NA NA NA
想象一下,这是一个由区域组成的动物矩阵,每个区域的数据都由连续的列对定义。某些动物在地区之间可能很常见,但地区可能也有独特的动物。最终,我想对该数据进行热图绘制,因此需要重新排序此矩阵,以使其具有一种结构,其中所有类型的动物都有一列,而与每个区域设置中的数字相对应的连续列:
dog 1 NA NA
tiger 3 1 NA
skunk 2 NA NA
mouse 8 NA NA
cow 7 9 NA
sheep 21 NA NA
gnu 5 NA 2
deer NA 7 27
rat NA 50 NA
snake NA 3 NA
mouse NA NA 7
bird 10 NA NA
换句话说,我有
A1 <- c("dog", "bird", "tiger", "skunk", "mouse", "cow", "sheep", "gnu")
B1 <- as.character(c(1, 10, 3, 2, 8, 7, 21, 5))
A2 <- c("cow", "tiger", "deer", "rat", "snake", NA, NA, NA)
B2 <- as.character(c(9, 1, 7, 50, 3, NA, NA, NA))
A3 <- c("mouse", "gnu", "deer", NA, NA, NA, NA, NA)
B3 <- as.character(c(7, 2, 27, NA, NA, NA, NA, NA))
TheMatrix <- cbind(A1, B1, A2, B2, A3, B3)
想要
a1 <- c("dog", "tiger", "skunk", "mouse", "cow", "sheep", "gnu", "deer", "rat", "snake", "mouse", "bird")
b1 <- as.character(c(1, 3, 2, 8, 7, 21, 5, NA, NA, NA, NA, 10))
b2 <- as.character(c(NA, 1, NA, NA, 9, NA, NA, 7, 50, 3, NA, NA))
b3 <- as.character(c(NA, NA, NA, NA, NA, NA, 2, 27, NA, NA, 7, NA))
DesiredResult <- cbind(a1, b1, b2, b3)
关于如何实现这种重组的想法?它可以通过循环和记帐来完成,但是肯定有我遗漏的更优雅的方法。
最佳答案
library(reshape2)
ncols = ncol(TheMatrix)
nrows = nrow(TheMatrix)
dcast(as.data.frame(na.omit(cbind(c(TheMatrix[,seq(1,ncols,2)]),
c(TheMatrix[,seq(2,ncols,2)]),
rep(colnames(TheMatrix)[seq(2,ncols,2)],
each = nrows)))),
V1 ~ V3, value.var = 'V2')
# V1 B1 B2 B3
#1 bird 10 <NA> <NA>
#2 cow 7 9 <NA>
#3 deer <NA> 7 27
#4 dog 1 <NA> <NA>
#5 gnu 5 <NA> 2
#6 mouse 8 <NA> 7
#7 rat <NA> 50 <NA>
#8 sheep 21 <NA> <NA>
#9 skunk 2 <NA> <NA>
#10 snake <NA> 3 <NA>
#11 tiger 3 1 <NA>
有很多事情(每个事情都很简单)在这里发生并且要理解,只需自己运行一点(从内部开始然后出去)。