How to convert a dataframe into a matrix using variables as the coordinate of the matrix?

Question

I have a data frame with 3 columns:

df<-data.frame(x=c(1,1,1,2,2,2,2,3,3), y=c(1,2,3,1,2,3,4,1,2), percentage=c(50,25,25,15,35,25,25,55,45))

Looks like:

  x y percentage
1 1 1         50
2 1 2         25
3 1 3         25
4 2 1         15
5 2 2         35
6 2 3         25
7 2 4         25
8 3 1         55
9 3 2         45

The third column represents the percentage of the area of y-ID objects (2 col) that are within the x-ID objects (1 col).

I would like to get a matrix (or smthg related) with the x and y that define the coordinates/subscript and the "percentage", the elements of the matrix.

Basically, I would like to get a matrix like that:

  1  2  3  4
1 50 25 25 0
2 15 35 25 25
3 55 45 0  0

Is there an easy way to achieve this ?

Answer 1

xtabs(percentage ~ x + y, data = df)

Answer 2

If the elements in x and y are consecutive natural numbers, try:

df<-data.frame(x=c(1,1,1,2,2,2,2,3,3), y=c(1,2,3,1,2,3,4,1,2), percentage=c(50,25,25,15,35,25,25,55,45))
out <- matrix(0, nrow=length(unique(df$x)), ncol=length(unique(df$y)))
out[cbind(df$x, df$y)] <- df$percentage
out

##      [,1] [,2] [,3] [,4]
## [1,]   50   25   25    0
## [2,]   15   35   25   25
## [3,]   55   45    0    0

Answer 3

A solution using data.table:

# Load package
library(data.table)

# Set up data
dt <- data.table(x=c(1,1,1,2,2,2,2,3,3), y=c(1,2,3,1,2,3,4,1,2), percentage=c(50,25,25,15,35,25,25,55,45))

# Transform data  
m <- as.matrix(dcast.data.table(data=dt, x ~ y, value.var="percentage", fill=0)[,-1, with=FALSE])

# > m
#       1  2  3  4
# [1,] 50 25 25  0
# [2,] 15 35 25 25
# [3,] 55 45  0  0