Danielle Lewis - Restructured Roxboro data 

 

Hi TJ, 

 

As a follow-up to our conversion on Friday, I am attaching the version of the Roxboro data that 

have been restructured. This version of the data combines the two excel 

spreadsheets from Autumn 

("RoxDataHomefor12mosummaryhalfQLversiontoConnieBrowerDataErrorCorrections_asof10-2010" 

and "RoxDataHomefor12monsummaryWholeNumberversionDataErrorCorrectionsasof10-2010") into 

one excel. The “WholeNumber” spreadsheet contains only detected values, while the 

“halfQLversion” spreadsheet has both detected and non-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 (from Autumn) that Ron forwarded to me, together 

with the Roxboro data in the ERG database. There was a period of overlap between Autumn’s data 

and the ERG database. As I was combining the overlapping 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 these updated excel spreadsheets to you on Friday. 

Below are a few comments I have after looking at and restructuring the data: 

1. The following are variables that I have created as part of restructuring the data: 

plant_eia_id, plant_name, plant_city, plant_state, sampling_location, method, and nondetected indicator. Under the sampling_location column, I used the same sampling location 

names (i.e., “influ prior set basin”, etc.) as in the 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 includes the following columns: Influ prior set 

basin, Influ after set basin, Outfall010, Outfall002, 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 the method in the Excel against the lab 

report (one day) for each analyte, I noted the following: (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 note 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 spreadsheet to be consistent with Autumn’s 

Excel; and (iii) for Chlorides and Sulfate, the Excel spreadsheet from Autumn does not 

indicate which lab methods were used, but the lab report indicated that method SM-4500 

CI/E was used for Chlorides and method 300.0 was used for Sulfate. I did not indicate 

either method in my Excel spreadsheet. Autumn's Excel 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

file://C:\Documents and Settings\DLewis\Local Settings\Temp\XPgrpwise\4FE96BEADO... 6/26/2012
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 spreadsheet for certain dates that are clearly a 

typo. Here are the analytes, original dates, and corrected 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 

"RoxDataHomefor12monsummaryWholeNumberversionDataErrorCorrectionsasof10-2010" 

should contain only "detected" values, but I noticed there was data recorded on some dates 

that should have been “non-detected” (since they were highlighted). Here are the analytes, 

dates, and locations. I made these values as non-detected. 

• 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 basin”, 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 spreadsheet, 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

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