Go to TogaWare.com Home Page. Data Science Desktop Survival Guide
by Graham Williams
Duck Duck Go

Excel Data Read

20200104 Microsoft Excel spreadsheets are supported by readxl which provides readxl::read_excel(). A common requirement is to skip= the first few lines of the spreadsheet which might be taken up with logos and file meta data. A specific sheet can be chosen with sheet=2 to select the second sheet or sheet="expenses" to select a named sheet. A specific range within a sheet is selected using range=. The package also provides readxl::excel_format() and readxl::excel_sheets().

Below we read data from the Sydney tab of the weatherAUS.xlsx spreadsheet created in Section 6.5.

library(magrittr)     # Data pipelines: %>% %<>% %T>% equals().
library(glue)         # Format strings: glue().
library(readxl)       # Read Excel spreadsheets: read_excel().

dsname <- "weatherAUS"
dstype <- "xlsx"
fsep   <- .Platform$file.sep

getwd() %>%
  glue("{fsep}{dsname}.{dstype}") %T>%
  print() ->
dspath
## /home/kayon/git/bitbucket/kayontoga/onepager/weatherAUS.xlsx

dspath %>% excel_format()
## [1] "xlsx"

dspath %>% excel_sheets()
##  [1] "Adelaide"         "Albany"           "Albury"          
##  [4] "AliceSprings"     "BadgerysCreek"    "Ballarat"        
##  [7] "Bendigo"          "Brisbane"         "Cairns"          
## [10] "Canberra"         "Cobar"            "CoffsHarbour"    
## [13] "Dartmoor"         "Darwin"           "GoldCoast"       
## [16] "Hobart"           "Katherine"        "Launceston"      
....

dspath %>% read_excel(sheet="Sydney")
## # A tibble: 3,984 x 24
##    Date                Location MinTemp MaxTemp Rainfall Evaporation Sun...
##    <dttm>              <chr>      <dbl>   <dbl>    <dbl>       <dbl>    ...
##  1 2008-02-01 00:00:00 Sydney      19.5    22.4     15.6         6.2    ...
##  2 2008-02-02 00:00:00 Sydney      19.5    25.6      6           3.4    ...
##  3 2008-02-03 00:00:00 Sydney      21.6    24.5      6.6         2.4    ...
....


Support further development by purchasing the PDF version of the book.
Other online resources include the GNU/Linux Desktop Survival Guide.
Books available on Amazon include Data Mining with Rattle and Essentials of Data Science.
Popular open source software includes rattle and wajig.
Hosted by Togaware, a pioneer of free and open source software since 1984.
Copyright © 2000-2020 Togaware Pty Ltd. . Creative Commons ShareAlike V4.