2

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

Improve this question
2
  • 3
    Some very rudimentary Google searches turn up read.xls in the gdata package and diag for creating diagonal matrices. Commented Aug 18, 2011 at 3:55
  • Edited question to better reflect the problem statements. Commented Jan 15, 2023 at 0:37

3 Answers 3

Reset to default
22

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This general formula is useful for putting whatever you want in the diagonal, but if you just want a simple 4-by-4 identity matrix, you can simply write diag(4).
as does diag(1,4), (which allows a digit other than 1 along the diagonal, but saves two whole characters!)
6

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)

Improve this answer

3 Comments

Thank you very much for your answer. I tried your code . Using your code how to get lower and upper triangle values i.e 11th column values from 250 sheets. These values are different in each sheet? Could you please explain?
from reading your Question, i am not certain how you wish to map your spreadsheet values to matrices in R. Perhaps the easiest way to proceed is to create the 'complete' matrix, then call tril() (lower triangular) or triu() on those to extract the triangular matrices.
@manjitha I do not think that you read the text offered by the questioner. You only read the term “diagonal” and that term was not an accurate description of the problem.
4

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.