# The Problem How would you import into R an Excel file like the one below? ![Snapshot of the table](https://dev-to-uploads.s3.amazonaws.com/i/y8trlgbf1rcgl4rucrsc.png) The table has no text data in its cells but the fill colors have meanings. The `readxl` package would not be of much help for a few reasons: 1. The data is not [“tidy”](https://r4ds.had.co.nz/tidy-data.html). Merged cells to the left convey categorical information; 2. The formatting of the cells is where the information really is. # The Solution *Enter* [`tidyxl`](https://nacnudus.github.io/tidyxl/) and [`unpivotr`](https://nacnudus.github.io/unpivotr/) packages. The `tidyxl` package imports each cell on the excel file as a row in a data frame, with columns describing its position, content and formatting. The `unpivotr` package makes use of the data frame generated by the tidyxl package and makes possible to tidy it up. First, I converted the [original PDF file](http://www.ceagesp.gov.br/wp-content/uploads/2015/05/produtos_epoca.pdf) to an Excel using a popular online service. The converted Excel file can be downloaded [here](https://www.scribd.com/document/477048238/Sazonalidade-dos-produtos-comercializados-no-CEAGESP). With the aforementioned packages, reading the table above become as simple as: ``` r library(dplyr) library(purrr) library(tidyxl) library(unpivotr) library(here) ``` ``` r filename <- here("raw-data/produtos_epoca-converted.xlsx") # The workbook contains several sheets. We first import all tables to a # list tables_names <- c("Table 1", "Table 3", "Table 4", "Table 5", "Table 6", "Table 7") tables_to_read <- map(tables_names, xlsx_cells, path = filename) # We create a function to read each sheet import_table <- function(df) { # Each fill color represent a different information. First, we create a # pallette of the fill colors in the sheet that can be indexed by the # `local_format_id` of a given cell to get the fill color of that cell fill_color_palette <- xlsx_formats(filename, "Table 1")$local$fill$patternFill$fgColor$rgb # Since the table has different headings, we have to filter out these # headings in order to have only the cells with data. Then, we create a # new column for the fill colors by looking up the `local_format_id` of # each cell in the pallette. Following, we create another column where # we codify this information. availability <- df %>% filter(row >= 2, col >= 3) %>% # filter out headers mutate(fill_color = fill_color_palette[local_format_id]) %>% mutate( availability = case_when( fill_color == "FFFF7F7F" ~ "Low", fill_color == "FFFFFFCC" ~ "Medium", fill_color == "FFCCFFCC" ~ "High" ) ) %>% select(availability) # We now transform all the headings so we can have a tidy data df %>% behead("left-up", category) %>% behead("left", produce) %>% behead("up", month) %>% bind_cols(availability) %>% select(category, produce, month, availability) } # Let's apply the function to our list of sheets availability_ceagesp <- map_dfr(tables_to_read, import_table) ``` Done\! # Result So, we imported and Excel file that contained un-tidy data and with formatting that conveys meaning. Now the data is already prepared for further processing and to be explored as needed: ``` r availability_ceagesp %>% filter(month == "Set", availability == "High") ``` ## # A tibble: 89 x 4 ## category produce month availability ## ## 1 Frutas Abacate Breda/Margarida Set High ## 2 Frutas Abiu Set High ## 3 Frutas Acerola Set High ## 4 Frutas Banana Maçã Set High ## 5 Frutas Banana Prata Set High ## 6 Frutas Caju Set High ## 7 Frutas Graviola Set High ## 8 Frutas Jabuticaba Set High ## 9 Frutas Kiwi Estrangeiro Set High ## 10 Frutas Laranja Lima Set High ## # ... with 79 more rows

leonardoshibata

Reading complex Excel files

Join Our Newsletter. No Spam, Only the good stuff.