Creating diagonal matrix (diag) from Excel sheet

I want to create a diagonal matrix from the following excel sheet (all values of row 265 of the sheet B)

I used the following codes. Earlier, I read the excel file using library(readxl) and it worked. However, as.vector(as.matrix(USEEIOv1_1_Matrices_B[265,2:389])) didn't return the value from 265. Rather, it returned to different values.

Rnox from sheet B

Rnox <-as.vector(as.matrix(USEEIOv1_1_Matrices_B[265,2:389]))


Now, all I want is to have the values from 265 rows as a vector and then make a diagonal matrix form that vector (like identity matrix where all diagonal values are 1). I was wondering if anyone could help me in this regard. Thanks in advance

It's hard to tell why you're failing from the provided information. Can you please turn this into a reproducible example? If you don't know how, here's a helpful post:

I'm not sure I understand exactly what do you mean by this:

Do you mean you're not getting the elements of columns 2-389 in row 265 using this? Do you fail only for the 265^{th} row, or also for some other rows?

And, what is USEEIOv1_1_Matrices_B? Is it the complete data? And, what is Rnox.diag? You haven't defined it yet.

This is an example using a built-in dataframe

#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa
#>      [,1] [,2] [,3] [,4]
#> [1,]  5.1  0.0  0.0  0.0
#> [2,]  0.0  3.5  0.0  0.0
#> [3,]  0.0  0.0  1.4  0.0
#> [4,]  0.0  0.0  0.0  0.2

Created on 2019-03-04 by the reprex package (v0.2.1)

Assuming that you have imported your data correctly this should work


In order for us been able to give you better help, please follow @Yarnabrina advice and make a reproducible example.

To address this part of the problem, I suspect you have blank rows somewhere and they are being skipped. Here is a quote from the documentation of the read_excel() function of the readxl package with some emphasis added:

skip - Minimum number of rows to skip before reading anything, be it column names or data. Leading empty rows are automatically skipped, so this is a lower bound. Ignored if range is given.

Hey Yarnabrina
Thanks for your reply. Yes, I am not getting the elements of columns 2-389 in row 265. the See below what it returns

Also, look at the excerpt from the code. You'll find what is USEEIOv1_1_Matrices_B

Well, this is not a reproducible example. It is a screenshot from your code. Please go through the link I shared to understand how to make one. It's a really really helpful post.

That being said, let me try to suggest some steps.

  1. You mentioned that USEEI0v1_1_Matrices_B is read correctly. Confirm it before proceeding.

  2. In that case, most probably USEEI0v1_1_Matrices_B[265, 2:389] will also be correct, but check that. Also, is it a vector?

  3. Now, you want a 388 by 388 diagonal matrix with diagonal entries being the entries of USEEI0v1_1_Matrices_B[265, 2:389], right? So, try with Rnox.diag <- diag(x = USEEI0v1_1_Matrices_B[265, 2:389]).

Let us know your findings.

Just to note, you can create a k-dimensional identity matrix by diag(x = k), where k \in \mathbb{N}. You don't need to create a k by k zero matrix and change diagonals to unity afterwards.

1 Like

Hey Yarnabrina

I am so glad and it really worked. Great community! Actually I was wrong with the row number. It should be 264. Also, I easily created the diagonal matrix. Thank you so much!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.