3

I'm a student and working on a project and I fail to do a basic thing in R and its very frustrating. 

data <- read.xlsx("dataset.xlsx",1, header=1)
#Creating a matrix with headers.
matrix <- as.matrix(data, row.names=1, col.names=1)

Sum <- rowSums(matrix, na.rm = FALSE, dims = 1)

The data looks something like this:

    Point1  Point2   Point3
SP1  0      1        1
SP1  0      1        0
SP1  1      1        0
SP1  1      1        1
SP1  0      1        0

This data is located and imported in R using the xlsx package. After that I want to calculate the row sums and import it in the matrix to create something like this:

    Point1  Point2   Point3  Sums
SP1  0      1        1       2
SP1  0      1        0       1
SP1  1      1        0       2
SP1  1      1        1       3
SP1  0      1        0       1

However, I can't get this to work. The code I used keeps returning non-numeric value. Obviously it probably tries to calculate with the first row and column, which are headers. For future purposes, Id like R to exclude the first row and column always and treat them as headers and treat the 0 and 1s as numeric.

Could you guys help me out here?

Cheers Jasper

Colonel Beauvel
  • 30,423
  • 11
  • 47
  • 87
Jasperovic
  • 107
  • 6
  • Welcome to SO. With R it's helpful if you can provide a [minimal reproducible example] (http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example), in your case this would mean providing a minimal data set. You can assign column names with [`colnames`](http://stat.ethz.ch/R-manual/R-devel/library/base/html/colnames.html) – Phil May 18 '15 at 09:40
  • By the way, rowSums will not take into account the headers...make sure you convert your matrix as numeric, seems like you have characters in your matrix. – Colonel Beauvel May 18 '15 at 09:55

1 Answers1

2

Your matrix contains characters and you try to sum them. So you first need to convert the submatrix you want to sum to numeric (it is better to use class here rather than as.numeric since it keeps the object type). To add a new column to a matrix, you can use cbind. Then you can update the colnames.

Here is a one-liner:

`colnames<-`(cbind(matrix, rowSums(`class<-`(matrix[,-1], 'numeric'))),
             c(colnames(matrix), 'Sums'))

#           col1 col2 col3 Sums
#[1,] "Sp1" "1"  "0"  "0"  "1" 
#[2,] "Sp2" "1"  "0"  "0"  "1" 
#[3,] "Sp3" "1"  "1"  "0"  "2" 
#[4,] "Sp4" "0"  "1"  "1"  "2"

Data

matrix = structure(c('Sp1', 'Sp2', 'Sp3', 'Sp4', 1, 1, 1, 0, 0, 0, 1, 1, 0, 
0, 0, 1), .Dim = c(4L, 4L), .Dimnames = list(NULL, c("", "col1", "col2", "col3")))
Colonel Beauvel
  • 30,423
  • 11
  • 47
  • 87
  • This is somewhat useful, but the imported matrix already contains the headers and is more something like, so how do I make R get the headers from the file?: matrix = structure(c('Sp1', 'Sp2', 'Sp3', 'Sp4', 1, 1, 1, 0, 0, 0, 1, 1, 0, 0, 0, 1), .Dim = c(4L, 4L), .Dimnames = list(NULL, c("", "col1", "col2", "col3"))) – Jasperovic May 18 '15 at 09:56
  • 1
    @Jasperovic I don't know about `read.xlsx` but with `read.table` you can specify that the row.names are in the first column with `row.names=1` so maybe you can do that with `read.xlsx`? – Cath May 18 '15 at 10:04
  • Thanks a lot, it seems to work. However, one last question and I'll be off your back. How do I permanently add the row sums to my matrix? It prints the right matrix now, but when I recall matrix later its gone. Sorry for my ultra rookie-ness – Jasperovic May 18 '15 at 10:07
  • 1
    Store my code in a new matrix `newMatrix = `colnames<-`(cbind...)` or in the matrix variable `matrix = `colnames<-`(cbind...)`. Modifying by reference is clearly not into R paradigm. – Colonel Beauvel May 18 '15 at 10:09
  • Actually, it is in two lines :-) – akrun May 18 '15 at 10:54
  • ;) I would have shorten the name of the matrix with `m` ! – Colonel Beauvel May 18 '15 at 11:35