Creating diagonal matrix (diag) from Excel sheet

[ziachowdhury]

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]))

Rnox.mat<-matrix(rep(0,150544),nrow=388)
diag(Rnox.mat)<-Rnox.diag

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

[Yarnabrina]

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:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

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

@ziachowdhury

as.vector(as.matrix(USEEIOv1_1_Matrices_B[265,2:389])) didn't return the value from 265

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.

[andresrcs]

This is an example using a built-in dataframe

head(iris)
#>   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
diag(iris[1,1:4])
#>      [,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

diag(USEEIOv1_1_Matrices_B[265,2:389])

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

[FJCC]

@ziachowdhury

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.

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.

[ziachowdhury]

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

[ziachowdhury]

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

[Yarnabrina]

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.

[ziachowdhury]

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!