### Danielle Lewis

- Restructured Roxboro data

Hi TJ,

As a follow-up to our conversion on Friday, I am at taching the version of the Roxboro data that have been restructured. This version of the data c ombines the two excel spreadsheets from Autumn ("RoxDataHomefor12mosummaryhalfQLversiontoConnieBro werDataErrorCorrections_asof10-2010" and "RoxDataHomefor12monsummaryWholeNumberversionDa taErrorCorrectionsasof10- 2010") into one excel. The “WholeNumber” spreadsheet contains only detected values, while the “halfQLversion” spreadsheet has both detected and n on-detected values, with the non-detected values highlighted. I have restructured the data to a format that is similar to ERG database that contains the Roxboro data.

I was originally working with the Roxboro data (fro m Autumn) that Ron forwarded to me, together with the Roxboro data in the ERG database. There w as a period of overlap between Autumn’ s data and the ERG database. As I was combining the overl apping data, I realized that there were some inconsistencies between the two data sets. Autumn and I went through these data and she made corrections to her data based on the original recorded data. She also added the analyte TSS to this excel spreadsheet. I forwarded both of the se updated excel spreadsheets to you on Friday. Below are a few comments I have after looking at an d restructuring the data:

1. The following are variables that I have created a s part of restructuring the data: plant_eia_id, plant_name, plant_city, plant_state, sampling_location, method, and nondetected indicator. Under the sampling_location co lumn, I used the same sampling location names (i.e., “influ prior set basin”, etc.) as in t he Autum’s Excel spreadsheet. 2. This Excel spreadsheet does not include the data from the Excel spreadsheets named "FLOW Daily" and "FLOW WeeklyMoAVG". 3. For each analyte, this Excel spreadsheet only inc ludes the following columns: Influ prior set basin, Influ after set basin, Outfall010, Outfall00 2, Outfall_003. This Excel spreadsheet does not include the last 4 columns from Autumn’s Excel: Outfall003, Outfall002, Ash pond prior 010, Lake Intake, or Water Quality (WS) since these were not included in all spreadsheets (and had little or no data when present).
4. I created a column called "Method”. This column has the method as identified at the end of each of the Autumn’s Excel sheet. After checking t he method in the Excel against the lab report (one day) for each analyte, I noted the foll owing: (i) for most of the analytes, the method recorded in the excel sheet matched with the lab report; (ii) for Aluminum, the Excel indicated that the method used was 200.8 (with a no te stating that there was method switching), while one lab report indicated that the method used was 200.7. I recorded the method for this pollutant as 200.8 in my Excel spre adsheet to be consistent with Autumn’s Excel; and (iii) for Chlorides and Sulfate, the Exc el spreadsheet from Autumn does not indicate which lab methods were used, but the lab r eport indicated that method SM-4500 CI/E was used for Chlorides and method 300.0 was u sed for Sulfate. I did not indicate either method in my Excel spreadsheet. Autumn's Exc el indicated that the lab methods used for Fluoride and TSS were 200.8. However, the lab report indicated that method 340.2 was 

### From: <Schroeder.Cuc@epamail.epa.gov> To: Thomas.Finseth@erg.com Date: 6/26/2011 9:56 AM Subject: Restructured Roxboro data CC: Jordan.Ronald@epamail.epa.gov Attachments: Roxboro_6_11_CS.xls Page 1 of 2

6/26/2012 file://C:\Documents and Settings\DLewis\Local Settings\Temp\XP grpwise\4FE96BEADO

...used for Fluoride and method 160.2 was used for TSS . I used the method as indicated in

Autumn's Excel spreadsheet (200.8). 5. I have multiplied concentrations by a factor of 2 for all values that are Non-detected (<). 6. I made some corrections to Autumn’s Excel spreads heet for certain dates that are clearly a typo. Here are the analytes, original dates, and co rrected dates:

• Aluminum, changing the recorded date from 7/7/09 to 7/7/08.

• Arsenic, changing recorded dates from (9/8/09, 9/29 /09, 10/6/09) to (9/8/08, 9/29/08, 10/6/08), respectively.

• For Antinomy, Iron, Copper, Lead, and Zinc changing the recorded date from 9/8/09 to 9/8/08.

7. Also, Excel sheet named "RoxDataHomefor12monsummaryWholeNumberversionDataEr rorCorrectionsasof10-2010" should contain only "detected" values, but I notice d there was data recorded on some dates that should have been “non-detected” (since they we re highlighted). Here are the analytes, dates, and locations. I made these values as non-d etected.

• Copper, dates (02/24/09,04/06/09), Influ prior set basin

• Cobalt ug/L, 12/08/08, Outfall003

• Molybdenum, dates (06/03/08, 06/09/08, 06/16/08,06/ 23/08), Outfall003

• Vanadium, dates (9/15/08, 09/22/08, 09/29/08, 10/6/ 08, 10/13/08, 10/20/08, 10/27/08, 11/3/08, 11/10/08, 11/17/08, 11/24/08, 12 /1/08, 12/8/08), Outfall002.

8. There are some non-detected values in the dataset that I am not sure about. For example, (i) for Chromium on 9/8/08, a non-detected value of 0.5 was recorded for “influ after set basin”, while most non-detected values were 5; and (ii) for cobalt on 9/8/08, a non-detected value of 0.55 was recorded for “influ after set bas in”, while most non-detected values were 5.5. There are many other similar cases that I am also unsure about. So far, I have used the values as recorded.

9. Please note that in the “WholeNumber” Excel sprea dsheet, the column usually named “Analyte” was named “Parameter” for Fluoride.

Let me know if you have any questions.

Cuc Schroeder, Statistician Technology and Statistics Branch Engineering & Analysis Division 202-566-2397 

### Page 2 of 2

6/26/2012 file://C:\Documents and Settings\DLewis\Local Settings\Temp\XP grpwise\4FE96BEADO

...