How to read .Excel files into R using openxlsx package?

Question

[blacktomato27]

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

[Arup Ghosh]

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".

[blacktomato27]

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

[lakhujanivijay]

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

Answer 1

[cpad0112]

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

[blacktomato27]

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

[zx8754]

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.

[zx8754]

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

[cpad0112]

> 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

[blacktomato27]

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

[zx8754]

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.