I am a R novice and was wondering how to import excel data into R using row names and column names. Specifically i require a subset of the data in a number of worksheet within one excel file. Can i use row names and column names to identify and extract certain cells of data to R ?
Worksheet 1
----------
* X Y Z
A 1 2 2
B 1 1 1
C 1 3 4
D 4 2 2
E 2 2 2
----------
Worksheet 2
----------
* X Y1 Z1
A 1 2 2
B 1 2 3
C 1 3 4
D 4 1 1
E 2 1 1
For example in the above spreadsheet how could i extract the data (2,2,2,2) using the row and column names (D,Y) (D,Z) (E,Y) (E,Z) in worksheet 1
how could i extract the data (1,1,1,1) using the row and column names (D,Y1) (D,Z1) (E,Y1) (E,Z1) in worksheet 2 ?
Thanks for any help provided
Barry
@Andrie mentionned the XLConnect package, it's a very useful package for I/O between R and Excel with the possibility to select region in Excel sheet.
I created an Excel file like yours in my Dropbox public folder, you can download the example.xls
file here.
require(XLConnect)
## A5:C5 correspond to (D,Y) (D,Z) (E,Y) (E,Z) in your example
selectworksheet1 <- readWorksheetFromFile("/home/ahmadou/Dropbox/Public/example.xls",
sheet = "Worksheet1",
region = "A5:C5", header = FALSE)
selectworksheet1
## Col0 Col1 Col2
## 1 2 2 2
## B4:C5 correspond to (D,Y1) (D,Z1) (E,Y1) (E,Z1) in the second example
selectworksheet2 <- readWorksheetFromFile("/home/ahmadou/Dropbox/Public/example.xls",
sheet = "Worksheet2",
region = "B4:C5", header = FALSE)
selectworksheet2
## Col0 Col1
## 1 1 1
## 2 1 1
unlist(selectworksheet2)
## Col01 Col02 Col11 Col12
## 1 1 1 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With