RUser_Jenny RUser_Jenny - 2 months ago 9
R Question

Transforming Row Based Migration Data to a Migration Matrix

I have migration data that is row based.

param <- c("A", "B", "C")
df <- data.frame(Case1 = c("A", "A", "B", "B"),
Case2 = c("A", "C", "A", "B"),
Val = c(0.5,0.4,0.3,0.7))


So this data frame looks like

Case1 Case2 Val
1 A A 0.5
2 A C 0.4
3 B A 0.3
4 B B 0.7

This row based data frame should be transformend in an kind of "migration matrix".

dd <- data.frame(cA = c(0.5, 0.3, 0),
cB = c(0, 0.7, 0),
cC = c(0.4,0,0))
rownames(dd) <- paste0("Case1","_", param)
colnames(dd) <- paste0("Case2","_", param)


So the migration matrix looks like


Case2_A Case2_B Case2_C
Case1_A 0.5 0.0 0.4
Case1_B 0.3 0.7 0.0
Case1_C 0.0 0.0 0.0


Does anyone know a good way to do this in R? Thank you very much!

Answer

With base R:

df

  Case1 Case2 Val
1     A     A 0.5
2     A     C 0.4
3     B     A 0.3
4     B     B 0.7

library(reshape2)
levels(df$Case1) <- c(levels(df$Case1), 'C')
df <- dcast(df, Case1~Case2, value.var='Val', drop=FALSE)
rownames(df) <- paste('Case1', df[,1], sep='_')
df <- df[-1]
names(df) <- paste('Case2', names(df), sep='_')
df[is.na(df)] <- 0.0
df

     Case2_A Case2_B Case2_C
Case1_A     0.5     0.0     0.4
Case1_B     0.3     0.7     0.0
Case1_C     0.0     0.0     0.0