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_offerThe 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 OFFERSo 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