CODEWITHSUNDEEP
rmatrixdummy-variablemodel.matrix
sidebob
sidebob

Reputation: 13

Trouble Converting a data frame into a design matrix in r

I started with R a few days ago and really could use some help :D I currently have a data.frame with 200 observations and 12 variables (they represent clicks, so they are named c1-c12).

Background: A user clicks (up to 12 times) on fields. These fields are ordered in 6 rows and 4 columns and the the user's clicks are recorded as number.:

[,1] [,2] [,3] [,4]
[1,] "11" "21" "31" "41"
[2,] "12" "22" "32" "42"
[3,] "13" "23" "33" "43"
[4,] "14" "24" "34" "44"
[5,] "15" "25" "35" "45"
[6,] "16" "26" "36" "46"

The first digit of each value represents the row, the second one the column. Thats why they are named 11-16, 21-26 ... 41-46. The values range from c(11:16, 21:26, 31:36, 41:46) so there are 24 possible values.

I was able to read the data into R and the first 10 observations look like this:

My Data:

    c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12
1   33 43 63 23 34 32 31 41 61  21  NA  NA
2   24 23 22 21 31 61 41 NA NA  NA  NA  NA
3   61 62 63 64 31 32 33 34 41  42  43  44
4   31 32 33 34 21 22 23 24 41  NA  NA  NA
5   11 12 13 14 22 23 32 33 62  63  42  52
6   51 52 53 54 61 62 63 64 31  32  33  34
7   31 21 61 62 63 64 33 23 NA  NA  NA  NA
8   41 42 43 44 32 33 62 63 52  53  61  64
9   61 62 63 64 21 22 24 23 34  31  41  44
10  51 52 53 54 24 34 21 31 33  23  61  63

I want to convert my data into some sort of design matrix, that is based on the position table given in background, but: As an desired output I need one matrix per observation, again containing 6 rows and 4 columns and a 1 when they clicked the field (i.e. the value of the position is part of the observation) and a 0 for all other positions. The first observation would then look like this:

0   1   1   1   0   1
0   0   1   0   0   0
0   1   1   1   0   1
0   0   1   0   0   0

Could you give me tips which packages etc. I should look into and maybe have tips as a guideline or something like this ?

My idea was to create a matrix for the positions and then multiply them with the observations but I'm currently really struggling and dont know where to start.

My position matrix in R now looks like this:

#construct design matrix
alt1 <- c(paste0(1, 1:6))
alt2 <- c(paste0(2, 1:6))
alt3 <- c(paste0(3, 1:6))
alt4 <- c(paste0(4, 1:6))
positions <- matrix(c(alt1, alt2, alt3, alt4), nrow = 6)

(provided in background)

**there might be a way easier and more clever solution, as my values contain the rownumber (first digit) and columnnr (second digit), but I dont know how to write them into a matrix by position **

dput(head(data))
structure(list(c1 = c("33", "24", "61", "31", "11", "51"), c2 = c("43", 
"23", "62", "32", "12", "52"), c3 = c("63", "22", "63", "33", 
"13", "53"), c4 = c("23", "21", "64", "34", "14", "54"), c5 = c("34", 
"31", "31", "21", "22", "61"), c6 = c("32", "61", "32", "22", 
"23", "62"), c7 = c("31", "41", "33", "23", "32", "63"), c8 = c("41", 
"", "34", "24", "33", "64"), c9 = c("61", NA, "41", "41", "62", 
"31"), c10 = c("21", NA, "42", "", "63", "32"), c11 = c("", NA, 
"43", NA, "42", "33"), c12 = c(NA, NA, "44", NA, "52", "34")), .Names = c("c1", 
"c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9", "c10", "c11", 
"c12"), row.names = c(NA, 6L), class = "data.frame")

Cheers and thanks, Sidebob

Upvotes: 0

Views: 314

Answers (1)

Ott Toomet
Ott Toomet

Reputation: 1956

You can fetch the individual lines and transform those to matrices with a simple loop.

for(row in 1:nrow(data)) {
   x <- as.numeric(data[row,])
   i <- x %% 10
   j <- x %/% 10
   mat <- matrix(0, 6, 6)
   mat[cbind(i,j)] <- 1
   print(mat)
   }

This gives

     [,1] [,2] [,3] [,4] [,5] [,6]
[1,]    0    1    1    1    0    1
[2,]    0    0    1    0    0    0
[3,]    0    1    1    1    0    1
[4,]    0    0    1    0    0    0
[5,]    0    0    0    0    0    0
[6,]    0    0    0    0    0    0
     [,1] [,2] [,3] [,4] [,5] [,6]
[1,]    0    1    1    1    0    1
[2,]    0    1    0    0    0    0
[3,]    0    1    0    0    0    0
[4,]    0    1    0    0    0    0
[5,]    0    0    0    0    0    0
[6,]    0    0    0    0    0    0
    ...

I am not sure which kind of output you expect. You can just print (and output to a file with sink()), you can write() to a file, or you can write.table() in a csv-like format.

BTW, you may encounter claims that loops are slow and should be avoided in R, in a way it is true, but in this case it works fine and is simple to understand.

Upvotes: 0

Related Questions