Fill the matrix using a file data

Question

I have a file example.csv contains three columns

A, A, 0
A, B, 0.05
A, C, 0.04
B, A, 0.001
B, B, 0.000
B, C, 0.05
C, A, 0.04
C, B, 0.05
C, C, 0.00

I have defined a 3 x 3 matrix and read the file

mat <- matrix(NA, nrow=3, ncol=3)

df = read.csv(file="~/Desktop/example.csv", head = FALSE, sep=",")

Read the table as matrix

inp.mtx <- as.matrix(df)

inp.mtx

> inp.mtx

        V1  V2  V3     
  [1,] "A" "A" "0.000"
  [2,] "A" "B" "0.050"
  [3,] "A" "C" "0.040"
  [4,] "B" "A" "0.001"
  [5,] "B" "B" "0.000"
  [6,] "B" "C" "0.050"
  [7,] "C" "A" "0.040"
  [8,] "C" "B" "0.050"
  [9,] "C" "C" "0.000"

mat shows as

     [,1] [,2] [,3]
[1,]   NA   NA   NA
[2,]   NA   NA   NA
[3,]   NA   NA   NA

How can I fill first two columns as index and fill the corresponding values? So that the matrix looks like below.

    A       B      C
A   0.000   0.05   0.04
B   0.001   0.000  0.05
C   0.04    0.05   0.00

When I am trying fill I mess up the columns and rows. Sometimes the error as

Error in mat[inp.mtx[, 1:2]] <- inp.mtx[, 3] : subscript out of bounds

Thank you for your inputs!

EDIT: inp.mtx data display

Stephen Henderson · Answer 1 · 2018-03-08T16:55:05.603

0

This will give the right answer. Note that t() is transposing the data to rotate the matrix the way you want above.

 inp.df<- read.table(text =
    "V1  V2  V3
    1  A   A   0.000
    2  A   B   0.050
    3  A   C   0.040
    4  B   A   0.001
    5  B   B   0.000
    6  B   C   0.050
    7  C   A   0.040
    8  C   B   0.050
    9  C   C   0.000", header =TRUE)

t(matrix(inp.df$V3, nrow=3, ncol =3, dimnames= list(c("A", "B", "C"),c("A", "B", "C") )))

      A    B    C
A 0.000 0.05 0.04
B 0.001 0.00 0.05
C 0.040 0.05 0.00

edited Mar 08 '18 at 16:55

answered Mar 08 '18 at 15:25

Stephen Henderson

6,340
3
27
33

How can I add "V1" and "V2" as index/label names in the matrix? Like [,1] as A, [,2] as B and [,3] as C. – catuf Mar 08 '18 at 15:42
When I add similar one using the file data, the error pop's up "inp.mtx$V3 Error in inp.mtx$V3 : $ operator is invalid for atomic vectors" – catuf Mar 08 '18 at 15:49
1. In your code you don't need to make the DF you import into a matrix before extracting the third column. Do it the way I show above. 2. I added how to specify matrix dimnames. – Stephen Henderson Mar 08 '18 at 16:53
Is it possible to give dimnames as a dataframe i.e. in case if I have list of 100 names using a separate file. thank you – catuf Mar 08 '18 at 17:50

Onyambu · Answer 2 · 2018-03-08T16:29:42.767

0

You can use xtabs in base R to create a table that has V1 as the rownames and V2 as the column names. You fill it withV3, ie V3~V1+V2. This creates a an object of class table. since it is not easy to subset or even call columns from a table, we transform it to a dataframe by the as.data.frame.matrix function. In the end we can transform the result to a matrix if we want to.

as.data.frame.matrix(xtabs(V3~V1+V2,df))
      A    B    C
A 0.000 0.05 0.04
B 0.001 0.00 0.05
C 0.040 0.05 0.00

edited Mar 08 '18 at 16:29

answered Mar 08 '18 at 15:39

Onyambu

67,392
3
24
53

Can you please explain? – catuf Mar 08 '18 at 16:05
> as.data.frame(xtabs(df$V3~df$V1+df$V2),mat) – catuf Mar 08 '18 at 16:23
NO NO NO.. why are you doing `df$V3`?? Please check my code.. – Onyambu Mar 08 '18 at 16:24
Where can I provide the data? (i.e df or inp.mtx?) – catuf Mar 08 '18 at 16:27
`xtabs(V3~V1+V2,df)` – Onyambu Mar 08 '18 at 16:28
You provide the dataframe – Onyambu Mar 08 '18 at 16:29
earlier it was mat. Now its working. Thank you so much!!! – catuf Mar 08 '18 at 16:37
If this is what you were looking for please accept the answer by clicking on the tick beside the solution – Onyambu Mar 08 '18 at 16:42

Fill the matrix using a file data

2 Answers2