PivotTables Example: Reducing Multiple Records for One Site

Click to enlarge: Example dataset and completed PivotTable

There is a dataset that has unique records for various pollutants for a number of locations across the state.  Thus, there are multiple instances of the same station for each of the four pollutants in this abbreviated example: CO, NO_x, PB, and PM (etc.).  I gave each of these records imitation data in the Value_ppm field to illustrate how the data are moved after being manipulated by the PivotTable process.  The values for the first station group are 1.x, the second group are 2.x, etc.

Process:

• Open a dataset in Excel (2007) - though this is largely available in previous versions of Excel.
• From the Insert tab choose Pivot Table

 

• From the Create PivotTable dialog, select all of your important data (including field headings), and choose to place the new table in a New Worksheet
  
• From the PivotTable wizard that will likely appear docked to the right side of the Excel window, click the menu button at the top and choose "Fields Selection and Areas Stacked" option

 

• Select the fields you wish to be included in your final dataset.  I picked all three from my dummy dataset

 

• Select the menu button again at the top of the PivotTable wizard and choose “Areas Section Only (1 by 4)”
  
• Now drag the fields into the following order:
  - Row Lables: Station (or station name/id)
  - Column Labels: Pollutant
  - Values: Sum of Value_PPM

 

• Remove the "Grand Total" fields by right clicking on the new table and choosing PivotTable Options
• From the Totals & Filters tab, uncheck the first two boxes under Grand Totals and click OK

Final Table

•  Finally, Copy and Paste the raw field headings and data to a new, final worksheet.  From here, if you are dealing with spatial data, I suggest joining Lat/Long/Datum information to the Station in Access or ArcMap/Catalog

Additional Grouping Fields:
Your dataset will be a bit more difficult if you need to attach additional information included as a grouping parameter, such as an observation date.  You can start by adding the Date column (switch back to the “Fields Selection and Areas Stacked” view) to the Row Labels area of the Pivot Table wizard.  Make sure Date is first, followed by Station.

Further grouping by Date

• If this last table is useful, you may want to remove the group subtotals before you copy the raw data into a new worksheet.  Simply right click on a Date cell inside the newly created table and uncheck the Subtotal "Date" option

Running Line in a Cave

Here's my first shot at an illustration of running line from open water into an overhead environment such as a cavern or cave.

[Click to enlarge]

I suggest practicing in open water or even on land so you won't have to worry about holding a flash light on your knot (though it's much easier to run line in water; the line won't fall to the ground as quickly because it will be suspended better in the water column.).  Run a course around a spring basin where there will usually be plenty of large rocks and tree branches to use as tie-off points.

Here's a video example.  Watch at 0:40 and 1:10 for this technique.  The only difference is that the diver does not wrap the line back around a couple times - but that is not always necessary if the spot is secure.




Things to consider:

• Another technique is to wrap first, then pull the tension from the incoming line last.  This will help keep tension on your line between tie-offs, however I like to pull the tension first to shore up previous segments.  Practice on your own and use whatever works best for you and  the situations you regularly face.
• Running the reel close to the floor of a cave/cavern can easily stir up the bottom, or even cause a silt-out.  Try making a loop with the line and fixing the loop around the rock or spur without moving the reel through the sediment.  Watch the video above from 3:38 to 3:56 for an example of how to do this.  Simply make a loop, attach the loop to a good spot, and pull tight.  The diver above makes two loops before pulling to secure the loops.
• Not all tie-off locations can actually be tied.  Often turning a corner around a large rock or other firm piece of debris will be a sufficient way to secure line as long as there is enough tension on the line.  This can serve as an intermediate spot between two tie-offs.  Further, if there are only loose rocks (football-basketball sized) available, don't bother wrapping.  Just lay your line and put a rock on top of it to hold it down.  It's easy for a line to become unwrapped, thus losing tension, and this will also conserve some line as well.