Small Multiples

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 character date   title                   price 
##    <int> <int> <chr>       <dbl> <chr>     <dttm> <chr>                   <chr> 
##  1    17    17 numeric     1.89  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  2    17    18 numeric     1.91  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  3    18    17 numeric     2.06  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  4    18    18 numeric     2.08  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  5    19    17 numeric     2.40  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  6    19    18 numeric     2.42  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  7    20    17 numeric     2.59  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  8    20    18 numeric     2.61  <NA>      NA     IF NWPL Rocky Mountains Fixed…
##  9    21    17 numeric     2.58  <NA>      NA     IF NWPL Rocky Mountains Fixed…
## 10    21    18 numeric     2.60  <NA>      NA     IF NWPL Rocky Mountains Fixed…
## 11    22    17 numeric     3.36  <NA>      NA     IF NWPL Rocky Mountains Fixed…
## 12    22    18 numeric     3.38  <NA>      NA     IF NWPL Rocky Mountains Fixed…
## 13    23    17 numeric     2.63  <NA>      NA     IF NWPL Rocky Mountains Fixed…
## 14    23    18 numeric     2.65  <NA>      NA     IF NWPL Rocky Mountains Fixed…
## 15    19    19 numeric    -0.565 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 16    19    20 numeric    -0.545 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 17    20    19 numeric    -0.494 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 18    20    20 numeric    -0.474 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 19    21    19 numeric    -0.585 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 20    21    20 numeric    -0.565 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 21    22    19 numeric    -0.295 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 22    22    20 numeric    -0.275 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 23    23    19 numeric    -0.530 <NA>      NA     IF NWPL Rocky Mountains Basis 
## 24    23    20 numeric    -0.510 <NA>      NA     IF NWPL Rocky Mountains Basis 
## # ℹ 1 more variable: bid_offer <chr>

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: Supplying `...` without names was deprecated in tidyr 1.0.0.
## ℹ Please specify a name for each selection.
## ℹ Did you want `data = -row`?
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.
unpivoted <- left_join(unpivoted, row_headers, by = "row")
unpivoted
## # A tibble: 240 × 7
##       row   col  numeric title                        price bid_offer row_header
##     <int> <int>    <dbl> <chr>                        <chr> <chr>     <chr>     
##   1    17    17  1.89    IF NWPL Rocky Mountains      Fixe… BID       Cash      
##   2    17    18  1.91    IF NWPL Rocky Mountains      Fixe… OFFER     Cash      
##   3    18    17  2.06    IF NWPL Rocky Mountains      Fixe… BID       ROM       
##   4    18    18  2.08    IF NWPL Rocky Mountains      Fixe… OFFER     ROM       
##   5    19    17  2.40    IF NWPL Rocky Mountains      Fixe… BID       Dec-01    
##   6    19    18  2.42    IF NWPL Rocky Mountains      Fixe… OFFER     Dec-01    
##   7    20    17  2.59    IF NWPL Rocky Mountains      Fixe… BID       Dec-01 to…
##   8    20    18  2.61    IF NWPL Rocky Mountains      Fixe… OFFER     Dec-01 to…
##   9    21    17  2.58    IF NWPL Rocky Mountains      Fixe… BID       Apr-02 to…
##  10    21    18  2.60    IF NWPL Rocky Mountains      Fixe… OFFER     Apr-02 to…
##  11    22    17  3.36    IF NWPL Rocky Mountains      Fixe… BID       Nov-02 to…
##  12    22    18  3.38    IF NWPL Rocky Mountains      Fixe… OFFER     Nov-02 to…
##  13    23    17  2.63    IF NWPL Rocky Mountains      Fixe… BID       One Year …
##  14    23    18  2.65    IF NWPL Rocky Mountains      Fixe… OFFER     One Year …
##  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 to…
##  18    20    20 -0.474   IF NWPL Rocky Mountains      Basis OFFER     Dec-01 to…
##  19    21    19 -0.585   IF NWPL Rocky Mountains      Basis BID       Apr-02 to…
##  20    21    20 -0.565   IF NWPL Rocky Mountains      Basis OFFER     Apr-02 to…
##  21    22    19 -0.295   IF NWPL Rocky Mountains      Basis BID       Nov-02 to…
##  22    22    20 -0.275   IF NWPL Rocky Mountains      Basis OFFER     Nov-02 to…
##  23    23    19 -0.530   IF NWPL Rocky Mountains      Basis BID       One Year …
##  24    23    20 -0.510   IF NWPL Rocky Mountains      Basis OFFER     One Year …
##  25    28     7  1.94    IF CIG Rocky Mountains       Fixe… BID       Cash      
##  26    28     8  1.96    IF CIG Rocky Mountains       Fixe… OFFER     Cash      
##  27    29     7  1.96    IF CIG Rocky Mountains       Fixe… BID       ROM       
##  28    29     8  1.98    IF CIG Rocky Mountains       Fixe… OFFER     ROM       
##  29    30     7  2.35    IF CIG Rocky Mountains       Fixe… BID       Dec-01    
##  30    30     8  2.37    IF CIG Rocky Mountains       Fixe… OFFER     Dec-01    
##  31    31     7  2.55    IF CIG Rocky Mountains       Fixe… BID       Dec-01 to…
##  32    31     8  2.57    IF CIG Rocky Mountains       Fixe… OFFER     Dec-01 to…
##  33    32     7  2.47    IF CIG Rocky Mountains       Fixe… BID       Apr-02 to…
##  34    32     8  2.49    IF CIG Rocky Mountains       Fixe… OFFER     Apr-02 to…
##  35    33     7  3.31    IF CIG Rocky Mountains       Fixe… BID       Nov-02 to…
##  36    33     8  3.33    IF CIG Rocky Mountains       Fixe… OFFER     Nov-02 to…
##  37    34     7  2.55    IF CIG Rocky Mountains       Fixe… BID       One Year …
##  38    34     8  2.57    IF CIG Rocky Mountains       Fixe… OFFER     One Year …
##  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 to…
##  42    31    10 -0.52    IF CIG Rocky Mountains       Basis OFFER     Dec-01 to…
##  43    32     9 -0.695   IF CIG Rocky Mountains       Basis BID       Apr-02 to…
##  44    32    10 -0.675   IF CIG Rocky Mountains       Basis OFFER     Apr-02 to…
##  45    33     9 -0.34    IF CIG Rocky Mountains       Basis BID       Nov-02 to…
##  46    33    10 -0.32    IF CIG Rocky Mountains       Basis OFFER     Nov-02 to…
##  47    34     9 -0.614   IF CIG Rocky Mountains       Basis BID       One Year …
##  48    34    10 -0.594   IF CIG Rocky Mountains       Basis OFFER     One Year …
##  49    28    12  2.38    IF EL Paso Permian           Fixe… BID       Cash      
##  50    28    13  2.40    IF EL Paso Permian           Fixe… OFFER     Cash      
##  51    29    12  2.42    IF EL Paso Permian           Fixe… BID       ROM       
##  52    29    13  2.44    IF EL Paso Permian           Fixe… OFFER     ROM       
##  53    30    12  2.7     IF EL Paso Permian           Fixe… BID       Dec-01    
##  54    30    13  2.72    IF EL Paso Permian           Fixe… OFFER     Dec-01    
##  55    31    12  2.85    IF EL Paso Permian           Fixe… BID       Dec-01 to…
##  56    31    13  2.87    IF EL Paso Permian           Fixe… OFFER     Dec-01 to…
##  57    32    12  3.01    IF EL Paso Permian           Fixe… BID       Apr-02 to…
##  58    32    13  3.03    IF EL Paso Permian           Fixe… OFFER     Apr-02 to…
##  59    33    12  3.50    IF EL Paso Permian           Fixe… BID       Nov-02 to…
##  60    33    13  3.52    IF EL Paso Permian           Fixe… OFFER     Nov-02 to…
##  61    34    12  2.98    IF EL Paso Permian           Fixe… BID       One Year …
##  62    34    13  3.00    IF EL Paso Permian           Fixe… OFFER     One Year …
##  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 to…
##  66    31    15 -0.213   IF EL Paso Permian           Basis OFFER     Dec-01 to…
##  67    32    14 -0.158   IF EL Paso Permian           Basis BID       Apr-02 to…
##  68    32    15 -0.138   IF EL Paso Permian           Basis OFFER     Apr-02 to…
##  69    33    14 -0.152   IF EL Paso Permian           Basis BID       Nov-02 to…
##  70    33    15 -0.132   IF EL Paso Permian           Basis OFFER     Nov-02 to…
##  71    34    14 -0.182   IF EL Paso Permian           Basis BID       One Year …
##  72    34    15 -0.162   IF EL Paso Permian           Basis OFFER     One Year …
##  73    28    17  2.45    IF EL Paso San Juan          Fixe… BID       Cash      
##  74    28    18  2.47    IF EL Paso San Juan          Fixe… OFFER     Cash      
##  75    29    17  2.35    IF EL Paso San Juan          Fixe… BID       ROM       
##  76    29    18  2.37    IF EL Paso San Juan          Fixe… OFFER     ROM       
##  77    30    17  2.56    IF EL Paso San Juan          Fixe… BID       Dec-01    
##  78    30    18  2.58    IF EL Paso San Juan          Fixe… OFFER     Dec-01    
##  79    31    17  2.74    IF EL Paso San Juan          Fixe… BID       Dec-01 to…
##  80    31    18  2.76    IF EL Paso San Juan          Fixe… OFFER     Dec-01 to…
##  81    32    17  2.80    IF EL Paso San Juan          Fixe… BID       Apr-02 to…
##  82    32    18  2.82    IF EL Paso San Juan          Fixe… OFFER     Apr-02 to…
##  83    33    17  3.42    IF EL Paso San Juan          Fixe… BID       Nov-02 to…
##  84    33    18  3.44    IF EL Paso San Juan          Fixe… OFFER     Nov-02 to…
##  85    34    17  2.82    IF EL Paso San Juan          Fixe… BID       One Year …
##  86    34    18  2.84    IF EL Paso San Juan          Fixe… OFFER     One Year …
##  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 to…
##  90    31    20 -0.325   IF EL Paso San Juan          Basis OFFER     Dec-01 to…
##  91    32    19 -0.365   IF EL Paso San Juan          Basis BID       Apr-02 to…
##  92    32    20 -0.345   IF EL Paso San Juan          Basis OFFER     Apr-02 to…
##  93    33    19 -0.23    IF EL Paso San Juan          Basis BID       Nov-02 to…
##  94    33    20 -0.21    IF EL Paso San Juan          Basis OFFER     Nov-02 to…
##  95    34    19 -0.347   IF EL Paso San Juan          Basis BID       One Year …
##  96    34    20 -0.327   IF EL Paso San Juan          Basis OFFER     One Year …
##  97    39     7  2.38    AECO / NIT                   Fixe… BID       Cash      
##  98    39     8  2.40    AECO / NIT                   Fixe… OFFER     Cash      
##  99    40     7  2.40    AECO / NIT                   Fixe… BID       ROM       
## 100    40     8  2.42    AECO / NIT                   Fixe… OFFER     ROM       
## 101    41     7  2.55    AECO / NIT                   Fixe… BID       Dec-01    
## 102    41     8  2.57    AECO / NIT                   Fixe… OFFER     Dec-01    
## 103    42     7  2.62    AECO / NIT                   Fixe… BID       Dec-01 to…
## 104    42     8  2.64    AECO / NIT                   Fixe… OFFER     Dec-01 to…
## 105    43     7  2.66    AECO / NIT                   Fixe… BID       Apr-02 to…
## 106    43     8  2.68    AECO / NIT                   Fixe… OFFER     Apr-02 to…
## 107    44     7  3.22    AECO / NIT                   Fixe… BID       Nov-02 to…
## 108    44     8  3.24    AECO / NIT                   Fixe… OFFER     Nov-02 to…
## 109    45     7  2.68    AECO / NIT                   Fixe… BID       One Year …
## 110    45     8  2.70    AECO / NIT                   Fixe… OFFER     One Year …
## 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 to…
## 114    42    10 -0.452   AECO / NIT                   Basis OFFER     Dec-01 to…
## 115    43     9 -0.505   AECO / NIT                   Basis BID       Apr-02 to…
## 116    43    10 -0.485   AECO / NIT                   Basis OFFER     Apr-02 to…
## 117    44     9 -0.435   AECO / NIT                   Basis BID       Nov-02 to…
## 118    44    10 -0.415   AECO / NIT                   Basis OFFER     Nov-02 to…
## 119    45     9 -0.488   AECO / NIT                   Basis BID       One Year …
## 120    45    10 -0.468   AECO / NIT                   Basis OFFER     One Year …
## 121    39    12  2.48    IF NWPL Canadian Border (Su… Fixe… BID       Cash      
## 122    39    13  2.5     IF NWPL Canadian Border (Su… Fixe… OFFER     Cash      
## 123    40    12  2.46    IF NWPL Canadian Border (Su… Fixe… BID       ROM       
## 124    40    13  2.48    IF NWPL Canadian Border (Su… Fixe… OFFER     ROM       
## 125    41    12  2.8     IF NWPL Canadian Border (Su… Fixe… BID       Dec-01    
## 126    41    13  2.82    IF NWPL Canadian Border (Su… Fixe… OFFER     Dec-01    
## 127    42    12  2.89    IF NWPL Canadian Border (Su… Fixe… BID       Dec-01 to…
## 128    42    13  2.91    IF NWPL Canadian Border (Su… Fixe… OFFER     Dec-01 to…
## 129    43    12  2.80    IF NWPL Canadian Border (Su… Fixe… BID       Apr-02 to…
## 130    43    13  2.82    IF NWPL Canadian Border (Su… Fixe… OFFER     Apr-02 to…
## 131    44    12  3.71    IF NWPL Canadian Border (Su… Fixe… BID       Nov-02 to…
## 132    44    13  3.73    IF NWPL Canadian Border (Su… Fixe… OFFER     Nov-02 to…
## 133    45    12  2.88    IF NWPL Canadian Border (Su… Fixe… BID       One Year …
## 134    45    13  2.90    IF NWPL Canadian Border (Su… Fixe… OFFER     One Year …
## 135    41    14 -0.16    IF NWPL Canadian Border (Su… Basis BID       Dec-01    
## 136    41    15 -0.14    IF NWPL Canadian Border (Su… Basis OFFER     Dec-01    
## 137    42    14 -0.196   IF NWPL Canadian Border (Su… Basis BID       Dec-01 to…
## 138    42    15 -0.176   IF NWPL Canadian Border (Su… Basis OFFER     Dec-01 to…
## 139    43    14 -0.37    IF NWPL Canadian Border (Su… Basis BID       Apr-02 to…
## 140    43    15 -0.35    IF NWPL Canadian Border (Su… Basis OFFER     Apr-02 to…
## 141    44    14  0.055   IF NWPL Canadian Border (Su… Basis BID       Nov-02 to…
## 142    44    15  0.075   IF NWPL Canadian Border (Su… Basis OFFER     Nov-02 to…
## 143    45    14 -0.285   IF NWPL Canadian Border (Su… Basis BID       One Year …
## 144    45    15 -0.265   IF NWPL Canadian Border (Su… Basis OFFER     One Year …
## 145    39    17  2.53    IF PEPL TX-OK                Fixe… BID       Cash      
## 146    39    18  2.55    IF PEPL TX-OK                Fixe… OFFER     Cash      
## 147    40    17  2.53    IF PEPL TX-OK                Fixe… BID       ROM       
## 148    40    18  2.55    IF PEPL TX-OK                Fixe… OFFER     ROM       
## 149    41    17  2.83    IF PEPL TX-OK                Fixe… BID       Dec-01    
## 150    41    18  2.85    IF PEPL TX-OK                Fixe… OFFER     Dec-01    
## 151    42    17  2.96    IF PEPL TX-OK                Fixe… BID       Dec-01 to…
## 152    42    18  2.98    IF PEPL TX-OK                Fixe… OFFER     Dec-01 to…
## 153    43    17  3.05    IF PEPL TX-OK                Fixe… BID       Apr-02 to…
## 154    43    18  3.07    IF PEPL TX-OK                Fixe… OFFER     Apr-02 to…
## 155    44    17  3.53    IF PEPL TX-OK                Fixe… BID       Nov-02 to…
## 156    44    18  3.55    IF PEPL TX-OK                Fixe… OFFER     Nov-02 to…
## 157    45    17  3.04    IF PEPL TX-OK                Fixe… BID       One Year …
## 158    45    18  3.06    IF PEPL TX-OK                Fixe… OFFER     One Year …
## 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 to…
## 162    42    20 -0.11    IF PEPL TX-OK                Basis OFFER     Dec-01 to…
## 163    43    19 -0.12    IF PEPL TX-OK                Basis BID       Apr-02 to…
## 164    43    20 -0.1     IF PEPL TX-OK                Basis OFFER     Apr-02 to…
## 165    44    19 -0.12    IF PEPL TX-OK                Basis BID       Nov-02 to…
## 166    44    20 -0.1     IF PEPL TX-OK                Basis OFFER     Nov-02 to…
## 167    45    19 -0.123   IF PEPL TX-OK                Basis BID       One Year …
## 168    45    20 -0.103   IF PEPL TX-OK                Basis OFFER     One Year …
## 169    50     7  2.58    NGI Socal (South Cal Border) Fixe… BID       Cash      
## 170    50     8  2.6     NGI Socal (South Cal Border) Fixe… OFFER     Cash      
## 171    51     7  2.5     NGI Socal (South Cal Border) Fixe… BID       ROM       
## 172    51     8  2.52    NGI Socal (South Cal Border) Fixe… OFFER     ROM       
## 173    52     7  2.79    NGI Socal (South Cal Border) Fixe… BID       Dec-01    
## 174    52     8  2.81    NGI Socal (South Cal Border) Fixe… OFFER     Dec-01    
## 175    53     7  2.95    NGI Socal (South Cal Border) Fixe… BID       Dec-01 to…
## 176    53     8  2.97    NGI Socal (South Cal Border) Fixe… OFFER     Dec-01 to…
## 177    54     7  3.22    NGI Socal (South Cal Border) Fixe… BID       Apr-02 to…
## 178    54     8  3.24    NGI Socal (South Cal Border) Fixe… OFFER     Apr-02 to…
## 179    55     7  3.74    NGI Socal (South Cal Border) Fixe… BID       Nov-02 to…
## 180    55     8  3.76    NGI Socal (South Cal Border) Fixe… OFFER     Nov-02 to…
## 181    56     7  3.16    NGI Socal (South Cal Border) Fixe… BID       One Year …
## 182    56     8  3.18    NGI Socal (South Cal Border) Fixe… OFFER     One Year …
## 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 to…
## 186    53    10 -0.12    NGI Socal (South Cal Border) Basis OFFER     Dec-01 to…
## 187    54     9  0.0586  NGI Socal (South Cal Border) Basis BID       Apr-02 to…
## 188    54    10  0.0786  NGI Socal (South Cal Border) Basis OFFER     Apr-02 to…
## 189    55     9  0.09    NGI Socal (South Cal Border) Basis BID       Nov-02 to…
## 190    55    10  0.11    NGI Socal (South Cal Border) Basis OFFER     Nov-02 to…
## 191    56     9 -0.00500 NGI Socal (South Cal Border) Basis BID       One Year …
## 192    56    10  0.015   NGI Socal (South Cal Border) Basis OFFER     One Year …
## 193    50    12  2.55    NGI Malin (North Cal Border) Fixe… BID       Cash      
## 194    50    13  2.57    NGI Malin (North Cal Border) Fixe… OFFER     Cash      
## 195    51    12  2.48    NGI Malin (North Cal Border) Fixe… BID       ROM       
## 196    51    13  2.5     NGI Malin (North Cal Border) Fixe… OFFER     ROM       
## 197    52    12  2.78    NGI Malin (North Cal Border) Fixe… BID       Dec-01    
## 198    52    13  2.80    NGI Malin (North Cal Border) Fixe… OFFER     Dec-01    
## 199    53    12  2.92    NGI Malin (North Cal Border) Fixe… BID       Dec-01 to…
## 200    53    13  2.94    NGI Malin (North Cal Border) Fixe… OFFER     Dec-01 to…
## 201    54    12  3.04    NGI Malin (North Cal Border) Fixe… BID       Apr-02 to…
## 202    54    13  3.06    NGI Malin (North Cal Border) Fixe… OFFER     Apr-02 to…
## 203    55    12  3.72    NGI Malin (North Cal Border) Fixe… BID       Nov-02 to…
## 204    55    13  3.74    NGI Malin (North Cal Border) Fixe… OFFER     Nov-02 to…
## 205    56    12  3.04    NGI Malin (North Cal Border) Fixe… BID       One Year …
## 206    56    13  3.06    NGI Malin (North Cal Border) Fixe… OFFER     One Year …
## 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 to…
## 210    53    15 -0.151   NGI Malin (North Cal Border) Basis OFFER     Dec-01 to…
## 211    54    14 -0.125   NGI Malin (North Cal Border) Basis BID       Apr-02 to…
## 212    54    15 -0.105   NGI Malin (North Cal Border) Basis OFFER     Apr-02 to…
## 213    55    14  0.07    NGI Malin (North Cal Border) Basis BID       Nov-02 to…
## 214    55    15  0.09    NGI Malin (North Cal Border) Basis OFFER     Nov-02 to…
## 215    56    14 -0.128   NGI Malin (North Cal Border) Basis BID       One Year …
## 216    56    15 -0.108   NGI Malin (North Cal Border) Basis OFFER     One Year …
## 217    50    17  2.57    PG&E City Gate               Fixe… BID       Cash      
## 218    50    18  2.59    PG&E City Gate               Fixe… OFFER     Cash      
## 219    51    17  2.52    PG&E City Gate               Fixe… BID       ROM       
## 220    51    18  2.54    PG&E City Gate               Fixe… OFFER     ROM       
## 221    52    17  2.88    PG&E City Gate               Fixe… BID       Dec-01    
## 222    52    18  2.9     PG&E City Gate               Fixe… OFFER     Dec-01    
## 223    53    17  3.02    PG&E City Gate               Fixe… BID       Dec-01 to…
## 224    53    18  3.04    PG&E City Gate               Fixe… OFFER     Dec-01 to…
## 225    54    17  3.26    PG&E City Gate               Fixe… BID       Apr-02 to…
## 226    54    18  3.28    PG&E City Gate               Fixe… OFFER     Apr-02 to…
## 227    55    17  3.96    PG&E City Gate               Fixe… BID       Nov-02 to…
## 228    55    18  3.98    PG&E City Gate               Fixe… OFFER     Nov-02 to…
## 229    56    17  3.22    PG&E City Gate               Fixe… BID       One Year …
## 230    56    18  3.24    PG&E City Gate               Fixe… OFFER     One Year …
## 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 to…
## 234    53    20 -0.0525  PG&E City Gate               Basis OFFER     Dec-01 to…
## 235    54    19  0.095   PG&E City Gate               Basis BID       Apr-02 to…
## 236    54    20  0.115   PG&E City Gate               Basis OFFER     Apr-02 to…
## 237    55    19  0.308   PG&E City Gate               Basis BID       Nov-02 to…
## 238    55    20  0.328   PG&E City Gate               Basis OFFER     Nov-02 to…
## 239    56    19  0.0512  PG&E City Gate               Basis BID       One Year …
## 240    56    20  0.0712  PG&E City Gate               Basis OFFER     One Year …

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")