readxl error on iteration for importing many sheets into .csv files

mjfrigaard · November 6, 2018, 7:01pm

Hi #rstats and #tidyverse folks!

I was hoping someone could help me with the error I am seeing when trying to get the functions from the excellent examples on readxl?

I have multiple sheets in an .xlsx file I want to read into data frames and write (a few) of them as .csv files.

Unfortunately I keep seeing this error:

    library(tidyverse)
    library(readxl)
    library(magrittr)
    #> 
    #> Attaching package: 'magrittr'
    #> The following object is masked from 'package:purrr':
    #> 
    #>     set_names
    #> The following object is masked from 'package:tidyr':
    #> 
    #>     extract
    read_then_csv <- function(sheet, path) {
      pathbase <- path %>%
        basename() %>%
        tools::file_path_sans_ext()
      path %>%
        read_excel(sheet = sheet) %>% 
        write_csv(paste0(pathbase, "-", sheet, ".csv"))
    }
    path <- readxl_example("datasets.xlsx")
    path %>%
      excel_sheets() %>%
      set_names() %>% 
      map(read_then_csv, path = path)
    #> Error in set_names(.): 1 argument passed to 'names<-' which requires 2

<sup>Created on 2018-11-06 by the [reprex package](https://reprex.tidyverse.org) (v0.2.1)</sup>

    devtools::session_info()
    #> - Session info ----------------------------------------------------------
    #>  setting  value                       
    #>  version  R version 3.5.1 (2018-07-02)
    #>  os       Windows Server 2012 R2 x64  
    #>  system   x86_64, mingw32             
    #>  ui       RTerm                       
    #>  language (EN)                        
    #>  collate  English_United States.1252  
    #>  ctype    English_United States.1252  
    #>  tz       America/Los_Angeles         
    #>  date     2018-11-06                  
    #> 
    #> - Packages --------------------------------------------------------------
    #>  package     * version date       lib source        
    #>  assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.1)
    #>  backports     1.1.2   2017-12-13 [1] CRAN (R 3.5.0)
    #>  base64enc     0.1-3   2015-07-28 [1] CRAN (R 3.5.0)
    #>  bindr         0.1.1   2018-03-13 [1] CRAN (R 3.5.1)
    #>  bindrcpp      0.2.2   2018-03-29 [1] CRAN (R 3.5.1)
    #>  broom         0.5.0   2018-07-17 [1] CRAN (R 3.5.1)
    #>  callr         3.0.0   2018-08-24 [1] CRAN (R 3.5.1)
    #>  cellranger    1.1.0   2016-07-27 [1] CRAN (R 3.5.1)
    #>  cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.1)
    #>  colorspace    1.3-2   2016-12-14 [1] CRAN (R 3.5.1)
    #>  crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.1)
    #>  desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.1)
    #>  devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.1)
    #>  digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.1)
    #>  dplyr       * 0.7.7   2018-10-16 [1] CRAN (R 3.5.1)
    #>  evaluate      0.12    2018-10-09 [1] CRAN (R 3.5.1)
    #>  forcats     * 0.3.0   2018-02-19 [1] CRAN (R 3.5.1)
    #>  fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.1)
    #>  ggplot2     * 3.1.0   2018-10-25 [1] CRAN (R 3.5.1)
    #>  glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.1)
    #>  gtable        0.2.0   2016-02-26 [1] CRAN (R 3.5.1)
    #>  haven         1.1.2   2018-06-27 [1] CRAN (R 3.5.1)
    #>  hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.1)
    #>  htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.5.1)
    #>  httr          1.3.1   2017-08-20 [1] CRAN (R 3.5.1)
    #>  jsonlite      1.5     2017-06-01 [1] CRAN (R 3.5.1)
    #>  knitr         1.20    2018-02-20 [1] CRAN (R 3.5.1)
    #>  lattice       0.20-35 2017-03-25 [2] CRAN (R 3.5.1)
    #>  lazyeval      0.2.1   2017-10-29 [1] CRAN (R 3.5.1)
    #>  lubridate     1.7.4   2018-04-11 [1] CRAN (R 3.5.1)
    #>  magrittr    * 1.5     2014-11-22 [1] CRAN (R 3.5.1)
    #>  memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.1)
    #>  modelr        0.1.2   2018-05-11 [1] CRAN (R 3.5.1)
    #>  munsell       0.5.0   2018-06-12 [1] CRAN (R 3.5.1)
    #>  nlme          3.1-137 2018-04-07 [2] CRAN (R 3.5.1)
    #>  pillar        1.3.0   2018-07-14 [1] CRAN (R 3.5.1)
    #>  pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.1)
    #>  pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.1)
    #>  pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.1)
    #>  plyr          1.8.4   2016-06-08 [1] CRAN (R 3.5.1)
    #>  prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.1)
    #>  processx      3.2.0   2018-08-16 [1] CRAN (R 3.5.1)
    #>  ps            1.2.0   2018-10-16 [1] CRAN (R 3.5.1)
    #>  purrr       * 0.2.5   2018-05-29 [1] CRAN (R 3.5.1)
    #>  R6            2.3.0   2018-10-04 [1] CRAN (R 3.5.1)
    #>  Rcpp          0.12.19 2018-10-01 [1] CRAN (R 3.5.1)
    #>  readr       * 1.1.1   2017-05-16 [1] CRAN (R 3.5.1)
    #>  readxl      * 1.1.0   2018-04-20 [1] CRAN (R 3.5.1)
    #>  remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.1)
    #>  rlang         0.3.0.1 2018-10-25 [1] CRAN (R 3.5.1)
    #>  rmarkdown     1.10    2018-06-11 [1] CRAN (R 3.5.1)
    #>  rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.1)
    #>  rvest         0.3.2   2016-06-17 [1] CRAN (R 3.5.1)
    #>  scales        1.0.0   2018-08-09 [1] CRAN (R 3.5.1)
    #>  sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.1)
    #>  stringi       1.2.4   2018-07-20 [1] CRAN (R 3.5.1)
    #>  stringr     * 1.3.1   2018-05-10 [1] CRAN (R 3.5.1)
    #>  tibble      * 1.4.2   2018-01-22 [1] CRAN (R 3.5.1)
    #>  tidyr       * 0.8.2   2018-10-28 [1] CRAN (R 3.5.1)
    #>  tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.1)
    #>  tidyverse   * 1.2.1   2017-11-14 [1] CRAN (R 3.5.1)
    #>  usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.1)
    #>  withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.1)
    #>  xml2          1.2.0   2018-01-24 [1] CRAN (R 3.5.1)
    #>  yaml          2.2.0   2018-07-25 [1] CRAN (R 3.5.1)
    #> 
    #> [1] C:/Users/MFrigaard/Documents/R/win-library/3.5
    #> [2] C:/Program Files/R/R-3.5.1/library

Thank you!

aosmith · November 6, 2018, 7:24pm

Looks like magrittr masked set_names() from package purrr, which led to the error.

The set_names() function from magrittr needs you to supply the names, where you set_names() from purrr will name the vector with itself if the names are missing.

You can refer directly to function from the desired package with ::, like purrr::set_names.

path %>%
     excel_sheets() %>%
     purrr::set_names() %>% 
     map(read_then_csv, path = path)

jcblum · November 6, 2018, 7:33pm

One more point: you don't need to load magrittr if you only want the pipe (%>%) — tidyverse supplies that for you (ultimately pulled from magrittr, but without pulling all the other magrittr functions along with it). So your code can just start with:

library(tidyverse)
library(readxl)

and then you won't have to worry about other functions in magrittr whose names overlap with functions in some tidyverse packages.

mjfrigaard · November 6, 2018, 7:36pm

I love when the answer is something simple! Thank you!

mjfrigaard · November 6, 2018, 7:37pm

@jcblum Thank you! I tend to use a few other magrittr functions, but I will keep this in mind (and call explicitly when loading conflicts).

jcblum · November 6, 2018, 7:49pm

In that case, it might make sense to see if the name masking works out more in line with your typical use if you load magrittr first — then tidyverse functions will mask magrittr functions (and not for nothing, tidyverse has a prettier display of what's getting masked — too bad the colors don't show up here!)

library(tidyverse)
#> ── Attaching packages ────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
#> ✔ ggplot2 3.1.0     ✔ purrr   0.2.5
#> ✔ tibble  1.4.2     ✔ dplyr   0.7.7
#> ✔ tidyr   0.8.2     ✔ stringr 1.3.1
#> ✔ readr   1.1.1     ✔ forcats 0.3.0
#> ── Conflicts ───────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
#> ✖ dplyr::filter() masks stats::filter()
#> ✖ dplyr::lag()    masks stats::lag()
library(magrittr)
#> 
#> Attaching package: 'magrittr'
#> The following object is masked from 'package:purrr':
#> 
#>     set_names
#> The following object is masked from 'package:tidyr':
#> 
#>     extract

^{Created on 2018-11-06 by the reprex package (v0.2.1)}

library(magrittr)
library(tidyverse)
#> ── Attaching packages ────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
#> ✔ ggplot2 3.1.0     ✔ purrr   0.2.5
#> ✔ tibble  1.4.2     ✔ dplyr   0.7.7
#> ✔ tidyr   0.8.2     ✔ stringr 1.3.1
#> ✔ readr   1.1.1     ✔ forcats 0.3.0
#> ── Conflicts ───────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
#> ✖ tidyr::extract()   masks magrittr::extract()
#> ✖ dplyr::filter()    masks stats::filter()
#> ✖ dplyr::lag()       masks stats::lag()
#> ✖ purrr::set_names() masks magrittr::set_names()

^{Created on 2018-11-06 by the reprex package (v0.2.1)}

mjfrigaard · November 6, 2018, 8:00pm

@jcblum oh the colors are definitely not for nothing! Prettier code is more fun to read

Thank you!