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