How to read .Excel files into R using openxlsx package?
1
0
Entering edit mode
2.9 years ago

Good morning

I am trying read excel file into R using openxlsx package with read.xlsx function, please find links below to my file and code

Please find example file here https://www.dropbox.com/s/8arrp4lt2zhg6c6/example.xlsx?dl=0

and my code here

#install.packages("openxlsx")
library("openxlsx")

a<-read.xlsx('reddy.xlsx', sheet = 1, startRow = 1, colNames = FALSE,
  rowNames = FALSE, detectDates = FALSE, skipEmptyRows = TRUE,
  skipEmptyCols = TRUE, rows = NULL, cols = NULL, check.names = FALSE,
  namedRegion = NULL, na.strings = "NA", fillMergedCells = FALSE)
dim(a)
head(a,n=3)
f<-t(a)
dim(f)
head(f,n=3)

i know it is basic question but i am unable to solve it and i do not where i am doing mistake in this to read my file correctly. any help in this regard is highly appreciated and thanks in advance. Regards

R Excel import • 6.9k views
ADD COMMENT
0
Entering edit mode

Put the three tables in three different sheets and import them separately and remove the extra text form your excel file like "Data I have", "but I am getting like this".

ADD REPLY
0
Entering edit mode

Dear Arup thanks lot for reply. I do not want to upload all three sheets. i provided them just for understanding i want to upload data i have to data i wish to thats all please find my data as per your suggestion here https://www.dropbox.com/s/lr0q9j5gnu0otp2/example.xlsx?dl=0 Thanks in advance

ADD REPLY
0
Entering edit mode

That does not feel like a bioinformatics question to me! StackOverflow is better suited for questions like this.

ADD REPLY
1
Entering edit mode
2.9 years ago

like this?

> library(readxl)
> read_excel("example.xlsx",col_names = TRUE)
# A tibble: 9 x 6
  INDIVIDUAL IND1  IND2  IND3  IND4  IND5 
  <chr>      <chr> <chr> <chr> <chr> <chr>
1 M1         TT    TT    TT    TT    TT   
2 M2         CC    CC    GG    CC    CC   
3 M3         GG    GG    AA    AA    GG   
4 M4         GG    GG    GG    GG    GG   
5 M5         GG    GG    GG    GG    GG   
6 M6         CC    CC    TT    CC    CC   
7 M7         CC    AA    CC    CC    CC   
8 M8         TT    TT    CC    TT    TT   
9 M9         AA    AA    AA    AA    AA
ADD COMMENT
0
Entering edit mode

Dear cpad0112 Thanks for your reply, yes like that but i do not want these <chr> things and numbers from 1 to 9 rest is like expected. Please suggest me how to remove them (<chr> and numbers) Thanks in advance

ADD REPLY
0
Entering edit mode

They are part of print tibble method. They are not there, <chr>... row just shows column class. and numbers on the left 1:9 are just row names. They are not part of your data.

ADD REPLY
0
Entering edit mode

Question is about "openxlsx" package, maybe update the post?

ADD REPLY
0
Entering edit mode
> library(openxlsx)
> readWorkbook("example.xlsx")
  INDIVIDUAL IND1 IND2 IND3 IND4 IND5
1         M1   TT   TT   TT   TT   TT
2         M2   CC   CC   GG   CC   CC
3         M3   GG   GG   AA   AA   GG
4         M4   GG   GG   GG   GG   GG
5         M5   GG   GG   GG   GG   GG
6         M6   CC   CC   TT   CC   CC
7         M7   CC   AA   CC   CC   CC
8         M8   TT   TT   CC   TT   TT
9         M9   AA   AA   AA   AA   AA
ADD REPLY
0
Entering edit mode

Thanks lot zx8754 and cpad0112 for your help and now i am able to read that as i wish thanks lot again

ADD REPLY
0
Entering edit mode

Hello @blacktomato27,

If an answer was helpful, you should upvote it; if the answer resolved your question, you should mark it as accepted. You can accept more than one if they work.

Upvote|Bookmark|Accept

ADD REPLY

Login before adding your answer.

Traffic: 2524 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6