Reputation: 41
I have an excel file which contains 250 sheets( 1st sheet contains 250 rows and 11 columns,2nd sheet contains 249 rows and 11 columns, 3rd sheet contains 248 rows and 11 columns and so on). I need only the values of 11th column from all sheets for creating diagonal matrix. I think , First I have to import 11th column values from excel file to R . Based on these values I have to create a diagonal matrix . Please help me
Upvotes: 2
Views: 43059
Reputation: 1808
For some strange reason google query for "r diagonal matrix" points me to this page, thus i think it is reasonable to answer this question here.
The most basic way to do this in R is to use function diag:
diag(1, 4, 4)
gives:
[,1] [,2] [,3] [,4]
[1,] 1 0 0 0
[2,] 0 1 0 0
[3,] 0 0 1 0
[4,] 0 0 0 1
Upvotes: 22
Reputation: 263499
From what you are describing, you really want a triangular matrix, and I will show how to create an upper triangular matrix:
Step 1: create a matrix to receive results:
mat <- matrix(NA, ncol=250, nrow=250)
Step 2: get a function to read n-th sheet's the n rows in the 11-th column
require(gdata) # to get read.xls or use an equivalent that works with your unstated OS
# you do need a Perl interpreter and a proper .pm file
Step 3: loop over the 250 sheets and put in the matrix rows
for(idx in 1:250 ) {
intemp <- read.xls(xlsfilename, sheet = idx )
mat[ idx, (251-idx):250] <- intemp[1:idx, 11]
}
There are lots of way this could fail. The obvious one is indexing past the end of the range of data that comes in from the sheets
Upvotes: 4
Reputation: 70098
You can use the Diagonal function from the Matrix library; just pass in the dimension and value (which seem to be what is in column 11 of your spreadsheet):
> library(Matrix)
> v = Diagonal(n=5, x=5.5)
> v
5 x 5 diagonal matrix of class "ddiMatrix"
[,1] [,2] [,3] [,4] [,5]
[1,] 5.5 . . . .
[2,] . 5.5 . . .
[3,] . . 5.5 . .
[4,] . . . 5.5 .
[5,] . . . . 5.5
(Note: Matrix needs to be explicitly imported via call to Library, though not separately installed because it seems to be included in the the R install)
Upvotes: 6