CODEWITHSUNDEEP
r
MJ2410
MJ2410

Reputation: 518

Get the first row and first column of an xlsx sheet without reading the entire data in R

For a project requirement I would like to first do a check on the first row (Column names) and first column (row names) of an xlsx file with 2 sheets. The first sheet consist of the Data set of which i need the colnames and the second sheet consist of another data set of which i need the row names.

I checked the option of Colnames and rownames but it requires to read the data. I would like to just get the names of the colnames and rownames without reading the data for a preliminary check. 

Data1 <- read.xlsx(file, sheetName=data, colNames = TRUE,
                           endRow = 2)
print(colnames(Data1))

Data2 <- read.xlsx(file, sheetName=data, rowNames= TRUE,
                           endRow = 2)
print(rownames(Data2 ))

Since the above method I used is not a direct way, is there any better way to achieve the results?

Upvotes: 5

Views: 8680

Answers (1)

R. Schifini
R. Schifini

Reputation: 9313

If you use package openxlsx the way to read the column names is:

columnsNames <- read.xlsx(file, sheetName='sheet name you want', colNames = F, rows = 1)

This will give you a data frame with one row, containing the column names of your Excel sheet. By setting colNames = F you'll avoid reading the first data row.

If the first column contains the row names then you can read them in a similar way:

rowsNames <- read.xlsx(file, sheetName='sheet name you want', colNames = F, cols = 1)

In this case set colNames to TRUE is this column has a name, if not, leave it as F.

Warning: cols=1 and rows=1 read the first column and row of the sheet, not the first non-empty column/row. If column 'A' is empty and your table starts at column 'B' then set cols=2.

Example:

I have an Excel with this data with the top left cell at B2 (column A empty and 1st row empty):

---------------------------
|    |  Year|  Age| Weight|
|row1|  2019|   11|     87|
|row2|  2000|   22|     76|
|row3|  2015|   33|     65|
---------------------------

> columnsNames <- read.xlsx("Book3.xlsx", colNames = F, rows = 2)
> columnsNames
    X1  X2     X3
1 Year Age Weight

> rowsNames <- read.xlsx("Book3.xlsx", colNames = F, cols = 2)
> rowsNames
    X1
1 row1
2 row2
3 row3

Upvotes: 4

Related Questions