15

When I try to import data into R studio from Excel, the number columns are getting imported as Logical in R.

Is there a way to import these columns as a number into R? The column in Excel has been formatted to number.

I am using read_excel to import the file.

The spreadsheet I am trying to import has 80 columns.

Zoe
  • 27,060
  • 21
  • 118
  • 148
Hisho
  • 305
  • 1
  • 3
  • 9

3 Answers3

29

I ran into the exact same problem, and due to confidentiality policies I can not provide the excel file. However, I found the solution in github provided by jennybc in here, in there she posted:

"But I'm guessing that you have lots of blank values at the top of this worksheet. Looks like this column is being guessed as logical, and anything that's neither NA nor zero is becoming TRUE. If my diagnosis is correct, you should either specify the column type you want (probably numeric in this case) or increase guess_max to something higher than the default of 1000."

As my files change a bit in format, I went for the guess_max suggestion, and this line solves the issue for me: 

temp.data <- read_xlsx(filepath, sheet = 1, guess_max = 10000)
lucia.hd
  • 621
  • 1
  • 6
  • 10
7

use col_types and explicitly specify the column types.

read_excel(path, sheet = 1, col_names = TRUE, col_types = c("text","numeric","date"), na = "", skip = 0)

https://www.rdocumentation.org/packages/readxl/versions/0.1.1/topics/read_excel

SatZ
  • 430
  • 5
  • 14
  • 1
    Is there a way to do this without specifying all 80 columns? Can I just specify the column that is getting converted as logical? – Hisho Jun 20 '18 at 13:12
  • It is better to explicitly specify the column types for all your columns, just in case you input data changes. You could use the function `rep` to make the declaration look concise. – SatZ Jun 21 '18 at 03:20
1

As you did not provide a dataset as an example, I came up with the following dataset:

df <- structure(list(`1_a` = c(1212, 1221, 32432), `2_a` = c(121, 123, 3), `3_a` = c(34, 343, 232), 
                 `4_a` = c(65, 23, 123), `5_a` = c(34, 432, 1)), row.names = c(NA, -3L), 
            class = c("tbl_df", "tbl", "data.frame"))

The dataset are all numeric, with column names starting with a number.

Using the following code, I am able to read the excel file while retaining the column names as they are (test.xlsx being an example of the above dataset):

library(readxl)
df <- read_excel("test.xlsx", sheet = 1, col_names = TRUE)
DTYK
  • 1,098
  • 1
  • 8
  • 33
  • Sorry I am not sure how to add a dataset to this, but I have columns which are text, some have dates. The column in Excel which gets converted as logical in R has blanks and values such as 0.5,1.0,11.4,9.0. These values are getting converted to TRUE/FALSE. Sorry if this does not make sense but I am not sure how to attach a file. – Hisho Jun 20 '18 at 14:22
  • @Hisho Please update your question. You did not specify the data type that you have obtained and the data type that you require. – DTYK Jun 21 '18 at 01:29