When I use read_excel to import data from Excel to R, some numeric columns are automatically converted to dates.
# e.g. 5600 to 1915-05-01 Is there a way to turn of this feature? Other than using "col_types" argument in read_excel.
- 3col_types is it mateDom– Dom2018-04-13 07:23:35 +00:00Commented Apr 13, 2018 at 7:23
- 3what's your aversion to using an argument explicitly designed for the purpose you're after? I must be missing somethingMichaelChirico– MichaelChirico2018-04-13 07:35:13 +00:00Commented Apr 13, 2018 at 7:35
- 3The question is, what is in your data that makes read_excel think it's a date? If it were only numbers, this probably wouldn't happentalat– talat2018-04-13 07:47:47 +00:00Commented Apr 13, 2018 at 7:47
- @MichaelChirico I'm uploading multiple files (30+ excels) using a for loop, and they can have different structures (e.g. column names, number of columns). Therefore, defining col_types one-by-one is possible but not quite efficient.LeGeniusII– LeGeniusII2018-04-15 23:15:54 +00:00Commented Apr 15, 2018 at 23:15
- @docendodiscimus That's a really good point. I've checked the data on Excel before posting this question, and the column only contains 1 to 5 digits whole numbers.LeGeniusII– LeGeniusII2018-04-15 23:26:38 +00:00Commented Apr 15, 2018 at 23:26
| Show 3 more comments
1 Answer
The readxl package, like readr for raw data files, has a type guesser to determine how to read columns in an Excel spreadsheet. As noted in the package vignette, the guessing process is not perfect, especially as it relates to date formats because they are stored as a special type of number.
As stated in the package documentation (as well as the comments on the OP), the way to avoid inaccurate guesses from the column type guesser is to explicitly specify the column types with the col_types argument on read_excel().
1 Comment
MichaelChirico
if the type guesser is failing, probably the algorithm can be improved. I encourage OP to file an issue on GitHub and share their data if possible