CODEWITHSUNDEEP
rexcelalgorithmmatrix
PAX2GARO
PAX2GARO

Reputation: 23

Import Excel Data into R

I'm working on an excel-file consisting of a 261 x 10 matrix. The matrix consists of the weekly returns of 10 stocks from 2010 to 2015. So, I have 10 variables (stocks) and 261 observations (weekly returns) for each variable. For my master thesis I have to apply a "rearrangement algorithm" developed by Rüschendorf and Puccetti (2012) on my matrix. I'm not going into further details on the theorical side of that concept. The thing is that I downloaded a package capable of performing the rearrangement algorithm in R. I tested it out and it works perfectly.

Actually the only thing I need to know is how to import my excel-matrix into R in order to be capable of performing the rearrangement algorithm on it. I can rewrite my matrix into R (manually) just by encoding every element of the matrix by using the matrix programming formula in R:

A = matrix( c(), nrow= , ncol= , byrow=TRUE)

The problem is that doing so for such a big matrix (261 x 10) would be very time consuming. Is their any way to import my excel-matrix in R and that R recognizes it as matrix consisting of numerical values ready for calculations (similar to the case of doing it manually) ? In such a way that I just have to run the "rearrangement algorithm" function provided in R.

Thanks in advance.

Upvotes: 1

Views: 780

Answers (2)

IRTFM
IRTFM

Reputation: 263352

I make a selection within an opened Excel sheet and copied to the clipboard. This then worked on a Mac:

> con=pipe("pbpaste")
> dat <- data.matrix( read.table(con) )
> dat
      V1 V2 V3
 [1,]  1  1  1
 [2,]  2  2  2
 [3,]  3  3  3
 [4,]  4  4  4
 [5,]  5  5  5
 [6,]  6  6  6
 [7,]  7  7  7
 [8,]  8  8  8
 [9,]  9  9  9
[10,] 10 10 10
[11,] 11 11 11
[12,] 12 12 12
[13,] 13 13 13
[14,] 14 14 14

The method is somewhat different on Windows devices but the help page for ?connections should have your OS-specific techniques.

Upvotes: 1

Anders Ellern Bilgrau
Anders Ellern Bilgrau

Reputation: 10223

You didn't provide a minimal reproducible example, so the answers are probably gonna of lesser quality. Anyway, you should be able to load the the excel file with something like:

require(XLConnect)
wrkbk <- loadWorkbook("path/to/your/data.xlsx")
df <- readWorksheet(wrkbk, sheet = 1, header = TRUE)

And then convert the data.frame to a matrix via

ans <- as.matrix(df)

Otherwise, you need to save your file as a .txt or .csv plain-text file and use read.table or read.csv and the like. Consult their respective help pages.

Upvotes: 0

Related Questions