The hardware and bandwidth for this mirror is donated by METANET, the Webhosting and Full Service-Cloud Provider.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]metanet.ch.

Small Multiples

Duncan Garmonsway

2023-01-22

This vignette for the unpivotr package demonstrates unpivoting multiple similar tables from a spreadsheet via the tidyxl package. It is best read with the spreadsheet open in a spreadsheet program, e.g. Excel, LibreOffice Calc or Gnumeric.

Introduction

The spreadsheet is from the famous Enron subpoena, made available by Felienne Hermans, and has has previously been publicised by Jenny Bryan and David Robinson, in particular in Robinson’s article ‘Tidying an untidyable dataset’.

Here’s a screenshot:

knitr::include_graphics("enron-screenshot.png")

Preparation

This vignette uses several common packages.

library(unpivotr)
library(tidyxl)
library(dplyr)
library(purrr)
library(tidyr)
library(stringr)

The spreadsheet is distributed with the unpivotr package, so can be loaded as a system file.

path <- system.file("extdata/enron.xlsx", package = "unpivotr")

Main

Importing the data

Spreadsheet cells are imported with the xlsx_cells() function, which returns a data frame of all the cells in all the requested sheets. By default, every sheet is imported, but we don’t have to worry about that in this case because there is only one sheet in the file. We can also straightaway discard rows above 14 and below 56, and columns beyond 20.

cells <-
  xlsx_cells(path) %>%
  dplyr::filter(!is_blank, between(row, 14L, 56L), col <= 20) %>%
  select(row, col, data_type, numeric, character, date)

Cell formatting isn’t required for this vignette, but if it were, it would be imported via xlsx_formats(path).

formatting <- xlsx_formats(path)

Importing one of the multiples

The small multiples each have exactly one ‘Fixed Price’ header cell, so begin by filtering for those cells, and then move the selection up one row to get the title cells. The title cells are the top-left corner cell of each table.

title <-
  dplyr::filter(cells, character == "Fixed Price") %>%
  select(row, col) %>%
  mutate(row = row - 1L) %>%
  inner_join(cells, by = c("row", "col"))

Use these title cells to partition the sheet.

partitions <- partition(cells, title)

Taking one of the partitions, unpivot with behead(). The compass directions "NNW" and "N" express the direction from each data cell to its header. "NNW" means “look up and then left to find the nearest header.”

partitions$cells[[1]] %>%
  behead("NNW", "title") %>%
  behead("NNW", "price") %>%
  behead("N", "bid_offer") %>%
  print(n = Inf)
## # A tibble: 24 × 9
##      row   col data_type numeric chara…¹ date   title              price bid_o…²
##    <int> <int> <chr>       <dbl> <chr>   <dttm> <chr>              <chr> <chr>  
##  1    17    17 numeric     1.89  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
##  2    17    18 numeric     1.91  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
##  3    18    17 numeric     2.06  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
##  4    18    18 numeric     2.08  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
##  5    19    17 numeric     2.40  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
##  6    19    18 numeric     2.42  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
##  7    20    17 numeric     2.59  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
##  8    20    18 numeric     2.61  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
##  9    21    17 numeric     2.58  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
## 10    21    18 numeric     2.60  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
## 11    22    17 numeric     3.36  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
## 12    22    18 numeric     3.38  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
## 13    23    17 numeric     2.63  <NA>    NA     IF NWPL Rocky Mou… Fixe… BID    
## 14    23    18 numeric     2.65  <NA>    NA     IF NWPL Rocky Mou… Fixe… OFFER  
## 15    19    19 numeric    -0.565 <NA>    NA     IF NWPL Rocky Mou… Basis BID    
## 16    19    20 numeric    -0.545 <NA>    NA     IF NWPL Rocky Mou… Basis OFFER  
## 17    20    19 numeric    -0.494 <NA>    NA     IF NWPL Rocky Mou… Basis BID    
## 18    20    20 numeric    -0.474 <NA>    NA     IF NWPL Rocky Mou… Basis OFFER  
## 19    21    19 numeric    -0.585 <NA>    NA     IF NWPL Rocky Mou… Basis BID    
## 20    21    20 numeric    -0.565 <NA>    NA     IF NWPL Rocky Mou… Basis OFFER  
## 21    22    19 numeric    -0.295 <NA>    NA     IF NWPL Rocky Mou… Basis BID    
## 22    22    20 numeric    -0.275 <NA>    NA     IF NWPL Rocky Mou… Basis OFFER  
## 23    23    19 numeric    -0.530 <NA>    NA     IF NWPL Rocky Mou… Basis BID    
## 24    23    20 numeric    -0.510 <NA>    NA     IF NWPL Rocky Mou… Basis OFFER  
## # … with abbreviated variable names ¹​character, ²​bid_offer

The same procedure can be mapped to every small multiple.

unpivoted <-
  purrr::map_dfr(partitions$cells,
                 ~ .x %>%
                   behead("NNW", "title") %>%
                   behead("NNW", "price") %>%
                   behead("N", "bid_offer")) %>%
  select(-data_type, -character, -date)
unpivoted
## # A tibble: 240 × 6
##       row   col  numeric title                           price       bid_offer
##     <int> <int>    <dbl> <chr>                           <chr>       <chr>    
##   1    17    17  1.89    IF NWPL Rocky Mountains         Fixed Price BID      
##   2    17    18  1.91    IF NWPL Rocky Mountains         Fixed Price OFFER    
##   3    18    17  2.06    IF NWPL Rocky Mountains         Fixed Price BID      
##   4    18    18  2.08    IF NWPL Rocky Mountains         Fixed Price OFFER    
##   5    19    17  2.40    IF NWPL Rocky Mountains         Fixed Price BID      
##   6    19    18  2.42    IF NWPL Rocky Mountains         Fixed Price OFFER    
##   7    20    17  2.59    IF NWPL Rocky Mountains         Fixed Price BID      
##   8    20    18  2.61    IF NWPL Rocky Mountains         Fixed Price OFFER    
##   9    21    17  2.58    IF NWPL Rocky Mountains         Fixed Price BID      
##  10    21    18  2.60    IF NWPL Rocky Mountains         Fixed Price OFFER    
##  11    22    17  3.36    IF NWPL Rocky Mountains         Fixed Price BID      
##  12    22    18  3.38    IF NWPL Rocky Mountains         Fixed Price OFFER    
##  13    23    17  2.63    IF NWPL Rocky Mountains         Fixed Price BID      
##  14    23    18  2.65    IF NWPL Rocky Mountains         Fixed Price OFFER    
##  15    19    19 -0.565   IF NWPL Rocky Mountains         Basis       BID      
##  16    19    20 -0.545   IF NWPL Rocky Mountains         Basis       OFFER    
##  17    20    19 -0.494   IF NWPL Rocky Mountains         Basis       BID      
##  18    20    20 -0.474   IF NWPL Rocky Mountains         Basis       OFFER    
##  19    21    19 -0.585   IF NWPL Rocky Mountains         Basis       BID      
##  20    21    20 -0.565   IF NWPL Rocky Mountains         Basis       OFFER    
##  21    22    19 -0.295   IF NWPL Rocky Mountains         Basis       BID      
##  22    22    20 -0.275   IF NWPL Rocky Mountains         Basis       OFFER    
##  23    23    19 -0.530   IF NWPL Rocky Mountains         Basis       BID      
##  24    23    20 -0.510   IF NWPL Rocky Mountains         Basis       OFFER    
##  25    28     7  1.94    IF CIG Rocky Mountains          Fixed Price BID      
##  26    28     8  1.96    IF CIG Rocky Mountains          Fixed Price OFFER    
##  27    29     7  1.96    IF CIG Rocky Mountains          Fixed Price BID      
##  28    29     8  1.98    IF CIG Rocky Mountains          Fixed Price OFFER    
##  29    30     7  2.35    IF CIG Rocky Mountains          Fixed Price BID      
##  30    30     8  2.37    IF CIG Rocky Mountains          Fixed Price OFFER    
##  31    31     7  2.55    IF CIG Rocky Mountains          Fixed Price BID      
##  32    31     8  2.57    IF CIG Rocky Mountains          Fixed Price OFFER    
##  33    32     7  2.47    IF CIG Rocky Mountains          Fixed Price BID      
##  34    32     8  2.49    IF CIG Rocky Mountains          Fixed Price OFFER    
##  35    33     7  3.31    IF CIG Rocky Mountains          Fixed Price BID      
##  36    33     8  3.33    IF CIG Rocky Mountains          Fixed Price OFFER    
##  37    34     7  2.55    IF CIG Rocky Mountains          Fixed Price BID      
##  38    34     8  2.57    IF CIG Rocky Mountains          Fixed Price OFFER    
##  39    30     9 -0.615   IF CIG Rocky Mountains          Basis       BID      
##  40    30    10 -0.595   IF CIG Rocky Mountains          Basis       OFFER    
##  41    31     9 -0.54    IF CIG Rocky Mountains          Basis       BID      
##  42    31    10 -0.52    IF CIG Rocky Mountains          Basis       OFFER    
##  43    32     9 -0.695   IF CIG Rocky Mountains          Basis       BID      
##  44    32    10 -0.675   IF CIG Rocky Mountains          Basis       OFFER    
##  45    33     9 -0.34    IF CIG Rocky Mountains          Basis       BID      
##  46    33    10 -0.32    IF CIG Rocky Mountains          Basis       OFFER    
##  47    34     9 -0.614   IF CIG Rocky Mountains          Basis       BID      
##  48    34    10 -0.594   IF CIG Rocky Mountains          Basis       OFFER    
##  49    28    12  2.38    IF EL Paso Permian              Fixed Price BID      
##  50    28    13  2.40    IF EL Paso Permian              Fixed Price OFFER    
##  51    29    12  2.42    IF EL Paso Permian              Fixed Price BID      
##  52    29    13  2.44    IF EL Paso Permian              Fixed Price OFFER    
##  53    30    12  2.7     IF EL Paso Permian              Fixed Price BID      
##  54    30    13  2.72    IF EL Paso Permian              Fixed Price OFFER    
##  55    31    12  2.85    IF EL Paso Permian              Fixed Price BID      
##  56    31    13  2.87    IF EL Paso Permian              Fixed Price OFFER    
##  57    32    12  3.01    IF EL Paso Permian              Fixed Price BID      
##  58    32    13  3.03    IF EL Paso Permian              Fixed Price OFFER    
##  59    33    12  3.50    IF EL Paso Permian              Fixed Price BID      
##  60    33    13  3.52    IF EL Paso Permian              Fixed Price OFFER    
##  61    34    12  2.98    IF EL Paso Permian              Fixed Price BID      
##  62    34    13  3.00    IF EL Paso Permian              Fixed Price OFFER    
##  63    30    14 -0.26    IF EL Paso Permian              Basis       BID      
##  64    30    15 -0.24    IF EL Paso Permian              Basis       OFFER    
##  65    31    14 -0.233   IF EL Paso Permian              Basis       BID      
##  66    31    15 -0.213   IF EL Paso Permian              Basis       OFFER    
##  67    32    14 -0.158   IF EL Paso Permian              Basis       BID      
##  68    32    15 -0.138   IF EL Paso Permian              Basis       OFFER    
##  69    33    14 -0.152   IF EL Paso Permian              Basis       BID      
##  70    33    15 -0.132   IF EL Paso Permian              Basis       OFFER    
##  71    34    14 -0.182   IF EL Paso Permian              Basis       BID      
##  72    34    15 -0.162   IF EL Paso Permian              Basis       OFFER    
##  73    28    17  2.45    IF EL Paso San Juan             Fixed Price BID      
##  74    28    18  2.47    IF EL Paso San Juan             Fixed Price OFFER    
##  75    29    17  2.35    IF EL Paso San Juan             Fixed Price BID      
##  76    29    18  2.37    IF EL Paso San Juan             Fixed Price OFFER    
##  77    30    17  2.56    IF EL Paso San Juan             Fixed Price BID      
##  78    30    18  2.58    IF EL Paso San Juan             Fixed Price OFFER    
##  79    31    17  2.74    IF EL Paso San Juan             Fixed Price BID      
##  80    31    18  2.76    IF EL Paso San Juan             Fixed Price OFFER    
##  81    32    17  2.80    IF EL Paso San Juan             Fixed Price BID      
##  82    32    18  2.82    IF EL Paso San Juan             Fixed Price OFFER    
##  83    33    17  3.42    IF EL Paso San Juan             Fixed Price BID      
##  84    33    18  3.44    IF EL Paso San Juan             Fixed Price OFFER    
##  85    34    17  2.82    IF EL Paso San Juan             Fixed Price BID      
##  86    34    18  2.84    IF EL Paso San Juan             Fixed Price OFFER    
##  87    30    19 -0.4     IF EL Paso San Juan             Basis       BID      
##  88    30    20 -0.38    IF EL Paso San Juan             Basis       OFFER    
##  89    31    19 -0.345   IF EL Paso San Juan             Basis       BID      
##  90    31    20 -0.325   IF EL Paso San Juan             Basis       OFFER    
##  91    32    19 -0.365   IF EL Paso San Juan             Basis       BID      
##  92    32    20 -0.345   IF EL Paso San Juan             Basis       OFFER    
##  93    33    19 -0.23    IF EL Paso San Juan             Basis       BID      
##  94    33    20 -0.21    IF EL Paso San Juan             Basis       OFFER    
##  95    34    19 -0.347   IF EL Paso San Juan             Basis       BID      
##  96    34    20 -0.327   IF EL Paso San Juan             Basis       OFFER    
##  97    39     7  2.38    AECO / NIT                      Fixed Price BID      
##  98    39     8  2.40    AECO / NIT                      Fixed Price OFFER    
##  99    40     7  2.40    AECO / NIT                      Fixed Price BID      
## 100    40     8  2.42    AECO / NIT                      Fixed Price OFFER    
## 101    41     7  2.55    AECO / NIT                      Fixed Price BID      
## 102    41     8  2.57    AECO / NIT                      Fixed Price OFFER    
## 103    42     7  2.62    AECO / NIT                      Fixed Price BID      
## 104    42     8  2.64    AECO / NIT                      Fixed Price OFFER    
## 105    43     7  2.66    AECO / NIT                      Fixed Price BID      
## 106    43     8  2.68    AECO / NIT                      Fixed Price OFFER    
## 107    44     7  3.22    AECO / NIT                      Fixed Price BID      
## 108    44     8  3.24    AECO / NIT                      Fixed Price OFFER    
## 109    45     7  2.68    AECO / NIT                      Fixed Price BID      
## 110    45     8  2.70    AECO / NIT                      Fixed Price OFFER    
## 111    41     9 -0.408   AECO / NIT                      Basis       BID      
## 112    41    10 -0.388   AECO / NIT                      Basis       OFFER    
## 113    42     9 -0.472   AECO / NIT                      Basis       BID      
## 114    42    10 -0.452   AECO / NIT                      Basis       OFFER    
## 115    43     9 -0.505   AECO / NIT                      Basis       BID      
## 116    43    10 -0.485   AECO / NIT                      Basis       OFFER    
## 117    44     9 -0.435   AECO / NIT                      Basis       BID      
## 118    44    10 -0.415   AECO / NIT                      Basis       OFFER    
## 119    45     9 -0.488   AECO / NIT                      Basis       BID      
## 120    45    10 -0.468   AECO / NIT                      Basis       OFFER    
## 121    39    12  2.48    IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 122    39    13  2.5     IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 123    40    12  2.46    IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 124    40    13  2.48    IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 125    41    12  2.8     IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 126    41    13  2.82    IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 127    42    12  2.89    IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 128    42    13  2.91    IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 129    43    12  2.80    IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 130    43    13  2.82    IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 131    44    12  3.71    IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 132    44    13  3.73    IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 133    45    12  2.88    IF NWPL Canadian Border (Sumas) Fixed Price BID      
## 134    45    13  2.90    IF NWPL Canadian Border (Sumas) Fixed Price OFFER    
## 135    41    14 -0.16    IF NWPL Canadian Border (Sumas) Basis       BID      
## 136    41    15 -0.14    IF NWPL Canadian Border (Sumas) Basis       OFFER    
## 137    42    14 -0.196   IF NWPL Canadian Border (Sumas) Basis       BID      
## 138    42    15 -0.176   IF NWPL Canadian Border (Sumas) Basis       OFFER    
## 139    43    14 -0.37    IF NWPL Canadian Border (Sumas) Basis       BID      
## 140    43    15 -0.35    IF NWPL Canadian Border (Sumas) Basis       OFFER    
## 141    44    14  0.055   IF NWPL Canadian Border (Sumas) Basis       BID      
## 142    44    15  0.075   IF NWPL Canadian Border (Sumas) Basis       OFFER    
## 143    45    14 -0.285   IF NWPL Canadian Border (Sumas) Basis       BID      
## 144    45    15 -0.265   IF NWPL Canadian Border (Sumas) Basis       OFFER    
## 145    39    17  2.53    IF PEPL TX-OK                   Fixed Price BID      
## 146    39    18  2.55    IF PEPL TX-OK                   Fixed Price OFFER    
## 147    40    17  2.53    IF PEPL TX-OK                   Fixed Price BID      
## 148    40    18  2.55    IF PEPL TX-OK                   Fixed Price OFFER    
## 149    41    17  2.83    IF PEPL TX-OK                   Fixed Price BID      
## 150    41    18  2.85    IF PEPL TX-OK                   Fixed Price OFFER    
## 151    42    17  2.96    IF PEPL TX-OK                   Fixed Price BID      
## 152    42    18  2.98    IF PEPL TX-OK                   Fixed Price OFFER    
## 153    43    17  3.05    IF PEPL TX-OK                   Fixed Price BID      
## 154    43    18  3.07    IF PEPL TX-OK                   Fixed Price OFFER    
## 155    44    17  3.53    IF PEPL TX-OK                   Fixed Price BID      
## 156    44    18  3.55    IF PEPL TX-OK                   Fixed Price OFFER    
## 157    45    17  3.04    IF PEPL TX-OK                   Fixed Price BID      
## 158    45    18  3.06    IF PEPL TX-OK                   Fixed Price OFFER    
## 159    41    19 -0.132   IF PEPL TX-OK                   Basis       BID      
## 160    41    20 -0.112   IF PEPL TX-OK                   Basis       OFFER    
## 161    42    19 -0.13    IF PEPL TX-OK                   Basis       BID      
## 162    42    20 -0.11    IF PEPL TX-OK                   Basis       OFFER    
## 163    43    19 -0.12    IF PEPL TX-OK                   Basis       BID      
## 164    43    20 -0.1     IF PEPL TX-OK                   Basis       OFFER    
## 165    44    19 -0.12    IF PEPL TX-OK                   Basis       BID      
## 166    44    20 -0.1     IF PEPL TX-OK                   Basis       OFFER    
## 167    45    19 -0.123   IF PEPL TX-OK                   Basis       BID      
## 168    45    20 -0.103   IF PEPL TX-OK                   Basis       OFFER    
## 169    50     7  2.58    NGI Socal (South Cal Border)    Fixed Price BID      
## 170    50     8  2.6     NGI Socal (South Cal Border)    Fixed Price OFFER    
## 171    51     7  2.5     NGI Socal (South Cal Border)    Fixed Price BID      
## 172    51     8  2.52    NGI Socal (South Cal Border)    Fixed Price OFFER    
## 173    52     7  2.79    NGI Socal (South Cal Border)    Fixed Price BID      
## 174    52     8  2.81    NGI Socal (South Cal Border)    Fixed Price OFFER    
## 175    53     7  2.95    NGI Socal (South Cal Border)    Fixed Price BID      
## 176    53     8  2.97    NGI Socal (South Cal Border)    Fixed Price OFFER    
## 177    54     7  3.22    NGI Socal (South Cal Border)    Fixed Price BID      
## 178    54     8  3.24    NGI Socal (South Cal Border)    Fixed Price OFFER    
## 179    55     7  3.74    NGI Socal (South Cal Border)    Fixed Price BID      
## 180    55     8  3.76    NGI Socal (South Cal Border)    Fixed Price OFFER    
## 181    56     7  3.16    NGI Socal (South Cal Border)    Fixed Price BID      
## 182    56     8  3.18    NGI Socal (South Cal Border)    Fixed Price OFFER    
## 183    52     9 -0.17    NGI Socal (South Cal Border)    Basis       BID      
## 184    52    10 -0.15    NGI Socal (South Cal Border)    Basis       OFFER    
## 185    53     9 -0.14    NGI Socal (South Cal Border)    Basis       BID      
## 186    53    10 -0.12    NGI Socal (South Cal Border)    Basis       OFFER    
## 187    54     9  0.0586  NGI Socal (South Cal Border)    Basis       BID      
## 188    54    10  0.0786  NGI Socal (South Cal Border)    Basis       OFFER    
## 189    55     9  0.09    NGI Socal (South Cal Border)    Basis       BID      
## 190    55    10  0.11    NGI Socal (South Cal Border)    Basis       OFFER    
## 191    56     9 -0.00500 NGI Socal (South Cal Border)    Basis       BID      
## 192    56    10  0.015   NGI Socal (South Cal Border)    Basis       OFFER    
## 193    50    12  2.55    NGI Malin (North Cal Border)    Fixed Price BID      
## 194    50    13  2.57    NGI Malin (North Cal Border)    Fixed Price OFFER    
## 195    51    12  2.48    NGI Malin (North Cal Border)    Fixed Price BID      
## 196    51    13  2.5     NGI Malin (North Cal Border)    Fixed Price OFFER    
## 197    52    12  2.78    NGI Malin (North Cal Border)    Fixed Price BID      
## 198    52    13  2.80    NGI Malin (North Cal Border)    Fixed Price OFFER    
## 199    53    12  2.92    NGI Malin (North Cal Border)    Fixed Price BID      
## 200    53    13  2.94    NGI Malin (North Cal Border)    Fixed Price OFFER    
## 201    54    12  3.04    NGI Malin (North Cal Border)    Fixed Price BID      
## 202    54    13  3.06    NGI Malin (North Cal Border)    Fixed Price OFFER    
## 203    55    12  3.72    NGI Malin (North Cal Border)    Fixed Price BID      
## 204    55    13  3.74    NGI Malin (North Cal Border)    Fixed Price OFFER    
## 205    56    12  3.04    NGI Malin (North Cal Border)    Fixed Price BID      
## 206    56    13  3.06    NGI Malin (North Cal Border)    Fixed Price OFFER    
## 207    52    14 -0.175   NGI Malin (North Cal Border)    Basis       BID      
## 208    52    15 -0.155   NGI Malin (North Cal Border)    Basis       OFFER    
## 209    53    14 -0.171   NGI Malin (North Cal Border)    Basis       BID      
## 210    53    15 -0.151   NGI Malin (North Cal Border)    Basis       OFFER    
## 211    54    14 -0.125   NGI Malin (North Cal Border)    Basis       BID      
## 212    54    15 -0.105   NGI Malin (North Cal Border)    Basis       OFFER    
## 213    55    14  0.07    NGI Malin (North Cal Border)    Basis       BID      
## 214    55    15  0.09    NGI Malin (North Cal Border)    Basis       OFFER    
## 215    56    14 -0.128   NGI Malin (North Cal Border)    Basis       BID      
## 216    56    15 -0.108   NGI Malin (North Cal Border)    Basis       OFFER    
## 217    50    17  2.57    PG&E City Gate                  Fixed Price BID      
## 218    50    18  2.59    PG&E City Gate                  Fixed Price OFFER    
## 219    51    17  2.52    PG&E City Gate                  Fixed Price BID      
## 220    51    18  2.54    PG&E City Gate                  Fixed Price OFFER    
## 221    52    17  2.88    PG&E City Gate                  Fixed Price BID      
## 222    52    18  2.9     PG&E City Gate                  Fixed Price OFFER    
## 223    53    17  3.02    PG&E City Gate                  Fixed Price BID      
## 224    53    18  3.04    PG&E City Gate                  Fixed Price OFFER    
## 225    54    17  3.26    PG&E City Gate                  Fixed Price BID      
## 226    54    18  3.28    PG&E City Gate                  Fixed Price OFFER    
## 227    55    17  3.96    PG&E City Gate                  Fixed Price BID      
## 228    55    18  3.98    PG&E City Gate                  Fixed Price OFFER    
## 229    56    17  3.22    PG&E City Gate                  Fixed Price BID      
## 230    56    18  3.24    PG&E City Gate                  Fixed Price OFFER    
## 231    52    19 -0.08    PG&E City Gate                  Basis       BID      
## 232    52    20 -0.06    PG&E City Gate                  Basis       OFFER    
## 233    53    19 -0.0725  PG&E City Gate                  Basis       BID      
## 234    53    20 -0.0525  PG&E City Gate                  Basis       OFFER    
## 235    54    19  0.095   PG&E City Gate                  Basis       BID      
## 236    54    20  0.115   PG&E City Gate                  Basis       OFFER    
## 237    55    19  0.308   PG&E City Gate                  Basis       BID      
## 238    55    20  0.328   PG&E City Gate                  Basis       OFFER    
## 239    56    19  0.0512  PG&E City Gate                  Basis       BID      
## 240    56    20  0.0712  PG&E City Gate                  Basis       OFFER

So far, only the column headers have been joined, but there are also row headers on the left-hand side of the spreadsheet. The following code incorporates these into the final dataset.

row_headers <-
  cells %>%
  dplyr::filter(between(row, 17, 56), between(col, 2, 4)) %>%
  # Concatenate rows like "Dec-01", "to", "Mar-02"
  mutate(character = ifelse(!is.na(character),
                            character,
                            format(date, origin="1899-12-30", "%b-%y"))) %>%
  select(row, col, character) %>%
  nest(-row) %>%
  mutate(row_header = map(data,
                          ~ str_trim(paste(.x$character, collapse = " ")))) %>%
  unnest(row_header) %>%
  mutate(col = 2L) %>%
  select(row, row_header)
## Warning: All elements of `...` must be named.
## Did you want `data = -row`?
unpivoted <- left_join(unpivoted, row_headers, by = "row")
unpivoted
## # A tibble: 240 × 7
##       row   col  numeric title                           price   bid_o…¹ row_h…²
##     <int> <int>    <dbl> <chr>                           <chr>   <chr>   <chr>  
##   1    17    17  1.89    IF NWPL Rocky Mountains         Fixed … BID     Cash   
##   2    17    18  1.91    IF NWPL Rocky Mountains         Fixed … OFFER   Cash   
##   3    18    17  2.06    IF NWPL Rocky Mountains         Fixed … BID     ROM    
##   4    18    18  2.08    IF NWPL Rocky Mountains         Fixed … OFFER   ROM    
##   5    19    17  2.40    IF NWPL Rocky Mountains         Fixed … BID     Dec-01 
##   6    19    18  2.42    IF NWPL Rocky Mountains         Fixed … OFFER   Dec-01 
##   7    20    17  2.59    IF NWPL Rocky Mountains         Fixed … BID     Dec-01…
##   8    20    18  2.61    IF NWPL Rocky Mountains         Fixed … OFFER   Dec-01…
##   9    21    17  2.58    IF NWPL Rocky Mountains         Fixed … BID     Apr-02…
##  10    21    18  2.60    IF NWPL Rocky Mountains         Fixed … OFFER   Apr-02…
##  11    22    17  3.36    IF NWPL Rocky Mountains         Fixed … BID     Nov-02…
##  12    22    18  3.38    IF NWPL Rocky Mountains         Fixed … OFFER   Nov-02…
##  13    23    17  2.63    IF NWPL Rocky Mountains         Fixed … BID     One Ye…
##  14    23    18  2.65    IF NWPL Rocky Mountains         Fixed … OFFER   One Ye…
##  15    19    19 -0.565   IF NWPL Rocky Mountains         Basis   BID     Dec-01 
##  16    19    20 -0.545   IF NWPL Rocky Mountains         Basis   OFFER   Dec-01 
##  17    20    19 -0.494   IF NWPL Rocky Mountains         Basis   BID     Dec-01…
##  18    20    20 -0.474   IF NWPL Rocky Mountains         Basis   OFFER   Dec-01…
##  19    21    19 -0.585   IF NWPL Rocky Mountains         Basis   BID     Apr-02…
##  20    21    20 -0.565   IF NWPL Rocky Mountains         Basis   OFFER   Apr-02…
##  21    22    19 -0.295   IF NWPL Rocky Mountains         Basis   BID     Nov-02…
##  22    22    20 -0.275   IF NWPL Rocky Mountains         Basis   OFFER   Nov-02…
##  23    23    19 -0.530   IF NWPL Rocky Mountains         Basis   BID     One Ye…
##  24    23    20 -0.510   IF NWPL Rocky Mountains         Basis   OFFER   One Ye…
##  25    28     7  1.94    IF CIG Rocky Mountains          Fixed … BID     Cash   
##  26    28     8  1.96    IF CIG Rocky Mountains          Fixed … OFFER   Cash   
##  27    29     7  1.96    IF CIG Rocky Mountains          Fixed … BID     ROM    
##  28    29     8  1.98    IF CIG Rocky Mountains          Fixed … OFFER   ROM    
##  29    30     7  2.35    IF CIG Rocky Mountains          Fixed … BID     Dec-01 
##  30    30     8  2.37    IF CIG Rocky Mountains          Fixed … OFFER   Dec-01 
##  31    31     7  2.55    IF CIG Rocky Mountains          Fixed … BID     Dec-01…
##  32    31     8  2.57    IF CIG Rocky Mountains          Fixed … OFFER   Dec-01…
##  33    32     7  2.47    IF CIG Rocky Mountains          Fixed … BID     Apr-02…
##  34    32     8  2.49    IF CIG Rocky Mountains          Fixed … OFFER   Apr-02…
##  35    33     7  3.31    IF CIG Rocky Mountains          Fixed … BID     Nov-02…
##  36    33     8  3.33    IF CIG Rocky Mountains          Fixed … OFFER   Nov-02…
##  37    34     7  2.55    IF CIG Rocky Mountains          Fixed … BID     One Ye…
##  38    34     8  2.57    IF CIG Rocky Mountains          Fixed … OFFER   One Ye…
##  39    30     9 -0.615   IF CIG Rocky Mountains          Basis   BID     Dec-01 
##  40    30    10 -0.595   IF CIG Rocky Mountains          Basis   OFFER   Dec-01 
##  41    31     9 -0.54    IF CIG Rocky Mountains          Basis   BID     Dec-01…
##  42    31    10 -0.52    IF CIG Rocky Mountains          Basis   OFFER   Dec-01…
##  43    32     9 -0.695   IF CIG Rocky Mountains          Basis   BID     Apr-02…
##  44    32    10 -0.675   IF CIG Rocky Mountains          Basis   OFFER   Apr-02…
##  45    33     9 -0.34    IF CIG Rocky Mountains          Basis   BID     Nov-02…
##  46    33    10 -0.32    IF CIG Rocky Mountains          Basis   OFFER   Nov-02…
##  47    34     9 -0.614   IF CIG Rocky Mountains          Basis   BID     One Ye…
##  48    34    10 -0.594   IF CIG Rocky Mountains          Basis   OFFER   One Ye…
##  49    28    12  2.38    IF EL Paso Permian              Fixed … BID     Cash   
##  50    28    13  2.40    IF EL Paso Permian              Fixed … OFFER   Cash   
##  51    29    12  2.42    IF EL Paso Permian              Fixed … BID     ROM    
##  52    29    13  2.44    IF EL Paso Permian              Fixed … OFFER   ROM    
##  53    30    12  2.7     IF EL Paso Permian              Fixed … BID     Dec-01 
##  54    30    13  2.72    IF EL Paso Permian              Fixed … OFFER   Dec-01 
##  55    31    12  2.85    IF EL Paso Permian              Fixed … BID     Dec-01…
##  56    31    13  2.87    IF EL Paso Permian              Fixed … OFFER   Dec-01…
##  57    32    12  3.01    IF EL Paso Permian              Fixed … BID     Apr-02…
##  58    32    13  3.03    IF EL Paso Permian              Fixed … OFFER   Apr-02…
##  59    33    12  3.50    IF EL Paso Permian              Fixed … BID     Nov-02…
##  60    33    13  3.52    IF EL Paso Permian              Fixed … OFFER   Nov-02…
##  61    34    12  2.98    IF EL Paso Permian              Fixed … BID     One Ye…
##  62    34    13  3.00    IF EL Paso Permian              Fixed … OFFER   One Ye…
##  63    30    14 -0.26    IF EL Paso Permian              Basis   BID     Dec-01 
##  64    30    15 -0.24    IF EL Paso Permian              Basis   OFFER   Dec-01 
##  65    31    14 -0.233   IF EL Paso Permian              Basis   BID     Dec-01…
##  66    31    15 -0.213   IF EL Paso Permian              Basis   OFFER   Dec-01…
##  67    32    14 -0.158   IF EL Paso Permian              Basis   BID     Apr-02…
##  68    32    15 -0.138   IF EL Paso Permian              Basis   OFFER   Apr-02…
##  69    33    14 -0.152   IF EL Paso Permian              Basis   BID     Nov-02…
##  70    33    15 -0.132   IF EL Paso Permian              Basis   OFFER   Nov-02…
##  71    34    14 -0.182   IF EL Paso Permian              Basis   BID     One Ye…
##  72    34    15 -0.162   IF EL Paso Permian              Basis   OFFER   One Ye…
##  73    28    17  2.45    IF EL Paso San Juan             Fixed … BID     Cash   
##  74    28    18  2.47    IF EL Paso San Juan             Fixed … OFFER   Cash   
##  75    29    17  2.35    IF EL Paso San Juan             Fixed … BID     ROM    
##  76    29    18  2.37    IF EL Paso San Juan             Fixed … OFFER   ROM    
##  77    30    17  2.56    IF EL Paso San Juan             Fixed … BID     Dec-01 
##  78    30    18  2.58    IF EL Paso San Juan             Fixed … OFFER   Dec-01 
##  79    31    17  2.74    IF EL Paso San Juan             Fixed … BID     Dec-01…
##  80    31    18  2.76    IF EL Paso San Juan             Fixed … OFFER   Dec-01…
##  81    32    17  2.80    IF EL Paso San Juan             Fixed … BID     Apr-02…
##  82    32    18  2.82    IF EL Paso San Juan             Fixed … OFFER   Apr-02…
##  83    33    17  3.42    IF EL Paso San Juan             Fixed … BID     Nov-02…
##  84    33    18  3.44    IF EL Paso San Juan             Fixed … OFFER   Nov-02…
##  85    34    17  2.82    IF EL Paso San Juan             Fixed … BID     One Ye…
##  86    34    18  2.84    IF EL Paso San Juan             Fixed … OFFER   One Ye…
##  87    30    19 -0.4     IF EL Paso San Juan             Basis   BID     Dec-01 
##  88    30    20 -0.38    IF EL Paso San Juan             Basis   OFFER   Dec-01 
##  89    31    19 -0.345   IF EL Paso San Juan             Basis   BID     Dec-01…
##  90    31    20 -0.325   IF EL Paso San Juan             Basis   OFFER   Dec-01…
##  91    32    19 -0.365   IF EL Paso San Juan             Basis   BID     Apr-02…
##  92    32    20 -0.345   IF EL Paso San Juan             Basis   OFFER   Apr-02…
##  93    33    19 -0.23    IF EL Paso San Juan             Basis   BID     Nov-02…
##  94    33    20 -0.21    IF EL Paso San Juan             Basis   OFFER   Nov-02…
##  95    34    19 -0.347   IF EL Paso San Juan             Basis   BID     One Ye…
##  96    34    20 -0.327   IF EL Paso San Juan             Basis   OFFER   One Ye…
##  97    39     7  2.38    AECO / NIT                      Fixed … BID     Cash   
##  98    39     8  2.40    AECO / NIT                      Fixed … OFFER   Cash   
##  99    40     7  2.40    AECO / NIT                      Fixed … BID     ROM    
## 100    40     8  2.42    AECO / NIT                      Fixed … OFFER   ROM    
## 101    41     7  2.55    AECO / NIT                      Fixed … BID     Dec-01 
## 102    41     8  2.57    AECO / NIT                      Fixed … OFFER   Dec-01 
## 103    42     7  2.62    AECO / NIT                      Fixed … BID     Dec-01…
## 104    42     8  2.64    AECO / NIT                      Fixed … OFFER   Dec-01…
## 105    43     7  2.66    AECO / NIT                      Fixed … BID     Apr-02…
## 106    43     8  2.68    AECO / NIT                      Fixed … OFFER   Apr-02…
## 107    44     7  3.22    AECO / NIT                      Fixed … BID     Nov-02…
## 108    44     8  3.24    AECO / NIT                      Fixed … OFFER   Nov-02…
## 109    45     7  2.68    AECO / NIT                      Fixed … BID     One Ye…
## 110    45     8  2.70    AECO / NIT                      Fixed … OFFER   One Ye…
## 111    41     9 -0.408   AECO / NIT                      Basis   BID     Dec-01 
## 112    41    10 -0.388   AECO / NIT                      Basis   OFFER   Dec-01 
## 113    42     9 -0.472   AECO / NIT                      Basis   BID     Dec-01…
## 114    42    10 -0.452   AECO / NIT                      Basis   OFFER   Dec-01…
## 115    43     9 -0.505   AECO / NIT                      Basis   BID     Apr-02…
## 116    43    10 -0.485   AECO / NIT                      Basis   OFFER   Apr-02…
## 117    44     9 -0.435   AECO / NIT                      Basis   BID     Nov-02…
## 118    44    10 -0.415   AECO / NIT                      Basis   OFFER   Nov-02…
## 119    45     9 -0.488   AECO / NIT                      Basis   BID     One Ye…
## 120    45    10 -0.468   AECO / NIT                      Basis   OFFER   One Ye…
## 121    39    12  2.48    IF NWPL Canadian Border (Sumas) Fixed … BID     Cash   
## 122    39    13  2.5     IF NWPL Canadian Border (Sumas) Fixed … OFFER   Cash   
## 123    40    12  2.46    IF NWPL Canadian Border (Sumas) Fixed … BID     ROM    
## 124    40    13  2.48    IF NWPL Canadian Border (Sumas) Fixed … OFFER   ROM    
## 125    41    12  2.8     IF NWPL Canadian Border (Sumas) Fixed … BID     Dec-01 
## 126    41    13  2.82    IF NWPL Canadian Border (Sumas) Fixed … OFFER   Dec-01 
## 127    42    12  2.89    IF NWPL Canadian Border (Sumas) Fixed … BID     Dec-01…
## 128    42    13  2.91    IF NWPL Canadian Border (Sumas) Fixed … OFFER   Dec-01…
## 129    43    12  2.80    IF NWPL Canadian Border (Sumas) Fixed … BID     Apr-02…
## 130    43    13  2.82    IF NWPL Canadian Border (Sumas) Fixed … OFFER   Apr-02…
## 131    44    12  3.71    IF NWPL Canadian Border (Sumas) Fixed … BID     Nov-02…
## 132    44    13  3.73    IF NWPL Canadian Border (Sumas) Fixed … OFFER   Nov-02…
## 133    45    12  2.88    IF NWPL Canadian Border (Sumas) Fixed … BID     One Ye…
## 134    45    13  2.90    IF NWPL Canadian Border (Sumas) Fixed … OFFER   One Ye…
## 135    41    14 -0.16    IF NWPL Canadian Border (Sumas) Basis   BID     Dec-01 
## 136    41    15 -0.14    IF NWPL Canadian Border (Sumas) Basis   OFFER   Dec-01 
## 137    42    14 -0.196   IF NWPL Canadian Border (Sumas) Basis   BID     Dec-01…
## 138    42    15 -0.176   IF NWPL Canadian Border (Sumas) Basis   OFFER   Dec-01…
## 139    43    14 -0.37    IF NWPL Canadian Border (Sumas) Basis   BID     Apr-02…
## 140    43    15 -0.35    IF NWPL Canadian Border (Sumas) Basis   OFFER   Apr-02…
## 141    44    14  0.055   IF NWPL Canadian Border (Sumas) Basis   BID     Nov-02…
## 142    44    15  0.075   IF NWPL Canadian Border (Sumas) Basis   OFFER   Nov-02…
## 143    45    14 -0.285   IF NWPL Canadian Border (Sumas) Basis   BID     One Ye…
## 144    45    15 -0.265   IF NWPL Canadian Border (Sumas) Basis   OFFER   One Ye…
## 145    39    17  2.53    IF PEPL TX-OK                   Fixed … BID     Cash   
## 146    39    18  2.55    IF PEPL TX-OK                   Fixed … OFFER   Cash   
## 147    40    17  2.53    IF PEPL TX-OK                   Fixed … BID     ROM    
## 148    40    18  2.55    IF PEPL TX-OK                   Fixed … OFFER   ROM    
## 149    41    17  2.83    IF PEPL TX-OK                   Fixed … BID     Dec-01 
## 150    41    18  2.85    IF PEPL TX-OK                   Fixed … OFFER   Dec-01 
## 151    42    17  2.96    IF PEPL TX-OK                   Fixed … BID     Dec-01…
## 152    42    18  2.98    IF PEPL TX-OK                   Fixed … OFFER   Dec-01…
## 153    43    17  3.05    IF PEPL TX-OK                   Fixed … BID     Apr-02…
## 154    43    18  3.07    IF PEPL TX-OK                   Fixed … OFFER   Apr-02…
## 155    44    17  3.53    IF PEPL TX-OK                   Fixed … BID     Nov-02…
## 156    44    18  3.55    IF PEPL TX-OK                   Fixed … OFFER   Nov-02…
## 157    45    17  3.04    IF PEPL TX-OK                   Fixed … BID     One Ye…
## 158    45    18  3.06    IF PEPL TX-OK                   Fixed … OFFER   One Ye…
## 159    41    19 -0.132   IF PEPL TX-OK                   Basis   BID     Dec-01 
## 160    41    20 -0.112   IF PEPL TX-OK                   Basis   OFFER   Dec-01 
## 161    42    19 -0.13    IF PEPL TX-OK                   Basis   BID     Dec-01…
## 162    42    20 -0.11    IF PEPL TX-OK                   Basis   OFFER   Dec-01…
## 163    43    19 -0.12    IF PEPL TX-OK                   Basis   BID     Apr-02…
## 164    43    20 -0.1     IF PEPL TX-OK                   Basis   OFFER   Apr-02…
## 165    44    19 -0.12    IF PEPL TX-OK                   Basis   BID     Nov-02…
## 166    44    20 -0.1     IF PEPL TX-OK                   Basis   OFFER   Nov-02…
## 167    45    19 -0.123   IF PEPL TX-OK                   Basis   BID     One Ye…
## 168    45    20 -0.103   IF PEPL TX-OK                   Basis   OFFER   One Ye…
## 169    50     7  2.58    NGI Socal (South Cal Border)    Fixed … BID     Cash   
## 170    50     8  2.6     NGI Socal (South Cal Border)    Fixed … OFFER   Cash   
## 171    51     7  2.5     NGI Socal (South Cal Border)    Fixed … BID     ROM    
## 172    51     8  2.52    NGI Socal (South Cal Border)    Fixed … OFFER   ROM    
## 173    52     7  2.79    NGI Socal (South Cal Border)    Fixed … BID     Dec-01 
## 174    52     8  2.81    NGI Socal (South Cal Border)    Fixed … OFFER   Dec-01 
## 175    53     7  2.95    NGI Socal (South Cal Border)    Fixed … BID     Dec-01…
## 176    53     8  2.97    NGI Socal (South Cal Border)    Fixed … OFFER   Dec-01…
## 177    54     7  3.22    NGI Socal (South Cal Border)    Fixed … BID     Apr-02…
## 178    54     8  3.24    NGI Socal (South Cal Border)    Fixed … OFFER   Apr-02…
## 179    55     7  3.74    NGI Socal (South Cal Border)    Fixed … BID     Nov-02…
## 180    55     8  3.76    NGI Socal (South Cal Border)    Fixed … OFFER   Nov-02…
## 181    56     7  3.16    NGI Socal (South Cal Border)    Fixed … BID     One Ye…
## 182    56     8  3.18    NGI Socal (South Cal Border)    Fixed … OFFER   One Ye…
## 183    52     9 -0.17    NGI Socal (South Cal Border)    Basis   BID     Dec-01 
## 184    52    10 -0.15    NGI Socal (South Cal Border)    Basis   OFFER   Dec-01 
## 185    53     9 -0.14    NGI Socal (South Cal Border)    Basis   BID     Dec-01…
## 186    53    10 -0.12    NGI Socal (South Cal Border)    Basis   OFFER   Dec-01…
## 187    54     9  0.0586  NGI Socal (South Cal Border)    Basis   BID     Apr-02…
## 188    54    10  0.0786  NGI Socal (South Cal Border)    Basis   OFFER   Apr-02…
## 189    55     9  0.09    NGI Socal (South Cal Border)    Basis   BID     Nov-02…
## 190    55    10  0.11    NGI Socal (South Cal Border)    Basis   OFFER   Nov-02…
## 191    56     9 -0.00500 NGI Socal (South Cal Border)    Basis   BID     One Ye…
## 192    56    10  0.015   NGI Socal (South Cal Border)    Basis   OFFER   One Ye…
## 193    50    12  2.55    NGI Malin (North Cal Border)    Fixed … BID     Cash   
## 194    50    13  2.57    NGI Malin (North Cal Border)    Fixed … OFFER   Cash   
## 195    51    12  2.48    NGI Malin (North Cal Border)    Fixed … BID     ROM    
## 196    51    13  2.5     NGI Malin (North Cal Border)    Fixed … OFFER   ROM    
## 197    52    12  2.78    NGI Malin (North Cal Border)    Fixed … BID     Dec-01 
## 198    52    13  2.80    NGI Malin (North Cal Border)    Fixed … OFFER   Dec-01 
## 199    53    12  2.92    NGI Malin (North Cal Border)    Fixed … BID     Dec-01…
## 200    53    13  2.94    NGI Malin (North Cal Border)    Fixed … OFFER   Dec-01…
## 201    54    12  3.04    NGI Malin (North Cal Border)    Fixed … BID     Apr-02…
## 202    54    13  3.06    NGI Malin (North Cal Border)    Fixed … OFFER   Apr-02…
## 203    55    12  3.72    NGI Malin (North Cal Border)    Fixed … BID     Nov-02…
## 204    55    13  3.74    NGI Malin (North Cal Border)    Fixed … OFFER   Nov-02…
## 205    56    12  3.04    NGI Malin (North Cal Border)    Fixed … BID     One Ye…
## 206    56    13  3.06    NGI Malin (North Cal Border)    Fixed … OFFER   One Ye…
## 207    52    14 -0.175   NGI Malin (North Cal Border)    Basis   BID     Dec-01 
## 208    52    15 -0.155   NGI Malin (North Cal Border)    Basis   OFFER   Dec-01 
## 209    53    14 -0.171   NGI Malin (North Cal Border)    Basis   BID     Dec-01…
## 210    53    15 -0.151   NGI Malin (North Cal Border)    Basis   OFFER   Dec-01…
## 211    54    14 -0.125   NGI Malin (North Cal Border)    Basis   BID     Apr-02…
## 212    54    15 -0.105   NGI Malin (North Cal Border)    Basis   OFFER   Apr-02…
## 213    55    14  0.07    NGI Malin (North Cal Border)    Basis   BID     Nov-02…
## 214    55    15  0.09    NGI Malin (North Cal Border)    Basis   OFFER   Nov-02…
## 215    56    14 -0.128   NGI Malin (North Cal Border)    Basis   BID     One Ye…
## 216    56    15 -0.108   NGI Malin (North Cal Border)    Basis   OFFER   One Ye…
## 217    50    17  2.57    PG&E City Gate                  Fixed … BID     Cash   
## 218    50    18  2.59    PG&E City Gate                  Fixed … OFFER   Cash   
## 219    51    17  2.52    PG&E City Gate                  Fixed … BID     ROM    
## 220    51    18  2.54    PG&E City Gate                  Fixed … OFFER   ROM    
## 221    52    17  2.88    PG&E City Gate                  Fixed … BID     Dec-01 
## 222    52    18  2.9     PG&E City Gate                  Fixed … OFFER   Dec-01 
## 223    53    17  3.02    PG&E City Gate                  Fixed … BID     Dec-01…
## 224    53    18  3.04    PG&E City Gate                  Fixed … OFFER   Dec-01…
## 225    54    17  3.26    PG&E City Gate                  Fixed … BID     Apr-02…
## 226    54    18  3.28    PG&E City Gate                  Fixed … OFFER   Apr-02…
## 227    55    17  3.96    PG&E City Gate                  Fixed … BID     Nov-02…
## 228    55    18  3.98    PG&E City Gate                  Fixed … OFFER   Nov-02…
## 229    56    17  3.22    PG&E City Gate                  Fixed … BID     One Ye…
## 230    56    18  3.24    PG&E City Gate                  Fixed … OFFER   One Ye…
## 231    52    19 -0.08    PG&E City Gate                  Basis   BID     Dec-01 
## 232    52    20 -0.06    PG&E City Gate                  Basis   OFFER   Dec-01 
## 233    53    19 -0.0725  PG&E City Gate                  Basis   BID     Dec-01…
## 234    53    20 -0.0525  PG&E City Gate                  Basis   OFFER   Dec-01…
## 235    54    19  0.095   PG&E City Gate                  Basis   BID     Apr-02…
## 236    54    20  0.115   PG&E City Gate                  Basis   OFFER   Apr-02…
## 237    55    19  0.308   PG&E City Gate                  Basis   BID     Nov-02…
## 238    55    20  0.328   PG&E City Gate                  Basis   OFFER   Nov-02…
## 239    56    19  0.0512  PG&E City Gate                  Basis   BID     One Ye…
## 240    56    20  0.0712  PG&E City Gate                  Basis   OFFER   One Ye…
## # … with abbreviated variable names ¹​bid_offer, ²​row_header

34-line code listing

library(unpivotr)
library(tidyxl)
library(dplyr)
library(purrr)
library(tidyr)
library(stringr)

cells <-
  xlsx_cells(system.file("extdata/enron.xlsx", package = "unpivotr")) %>%
  dplyr::filter(!is_blank, between(row, 14L, 56L), col <= 20) %>%
  select(row, col, data_type, numeric, character, date)

row_headers <-
  dplyr::filter(cells, between(row, 17, 56), between(col, 2, 4)) %>%
  mutate(character = ifelse(!is.na(character),
                            character,
                            format(date, origin="1899-12-30", "%b-%y"))) %>%
  select(row, col, character) %>%
  nest(-row) %>%
  mutate(row_header = map(data,
                          ~ str_trim(paste(.x$character, collapse = " ")))) %>%
  unnest(row_header) %>%
  mutate(col = 2L) %>%
  select(row, row_header)

titles <-
  dplyr::filter(cells, character == "Fixed Price") %>%
  select(row, col) %>%
  mutate(row = row - 1L) %>%
  inner_join(cells, by = c("row", "col"))

partition(cells, titles)$cells %>%
  purrr::map_dfr(~ .x %>%
                 behead("NNW", "title") %>%
                 behead("NNW", "price") %>%
                 behead("N", "bid_offer")) %>%
  select(-data_type, -character, -date) %>%
  left_join(row_headers, by = "row")

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.