Difference between revisions of "ISSS608 2017-18 T3 Assignment LIU YUQING Data Preparation"
Yqliu.2017 (talk | contribs) |
Yqliu.2017 (talk | contribs) |
||
(2 intermediate revisions by the same user not shown) | |||
Line 27: | Line 27: | ||
=Dataset description= | =Dataset description= | ||
Mini-Challenge 2 provides two datasets - [Boonsong Lekagul waterways readings.csv] and [chemical units of measure.csv] and one map image about rivers and streams of some specific locations. | Mini-Challenge 2 provides two datasets - [Boonsong Lekagul waterways readings.csv] and [chemical units of measure.csv] and one map image about rivers and streams of some specific locations. | ||
− | [Boonsong Lekagul waterways readings.csv] includes 5 variables: ID, measure, value of | + | |
+ | [Boonsong Lekagul waterways readings.csv] includes 5 variables: ID, measure, value of measure, location and measuring date. There are 10 locations, 106 measures and measuring date span is from 1998 to 2016. | ||
+ | |||
[chemical units of measure.csv] includes measures and unit of each measure. | [chemical units of measure.csv] includes measures and unit of each measure. | ||
Line 42: | Line 44: | ||
After the overview of whole data, we observed value variance among measures are quite big and measuring time span of each measure also varies greatly. Units of measures are mainly separated into ug/l and mg/l, it’s hard for us to estimate whether the value of this chemical is toxic for water or not. In this case, I found a file from the California Water Board as a reference. | After the overview of whole data, we observed value variance among measures are quite big and measuring time span of each measure also varies greatly. Units of measures are mainly separated into ug/l and mg/l, it’s hard for us to estimate whether the value of this chemical is toxic for water or not. In this case, I found a file from the California Water Board as a reference. | ||
− | [[Image:image1-3.jpg|800px | + | [[Image:image1-3.jpg|800px]] |
+ | |||
This file provides MCL(California’s maximum contaminant levels) and DLR( Detection limits for purpose of reporting) of about 120 measures for regulated drinking water. Maximum level of contaminants in drinking water should be lower than raw water, so any value of chemical less than its corresponding level could be considered insignificant. | This file provides MCL(California’s maximum contaminant levels) and DLR( Detection limits for purpose of reporting) of about 120 measures for regulated drinking water. Maximum level of contaminants in drinking water should be lower than raw water, so any value of chemical less than its corresponding level could be considered insignificant. | ||
<br> | <br> | ||
Line 58: | Line 61: | ||
3. Join [Measures with MCL] table to [Boonsong Lekagul waterways readings] table in JMP using left outer join so that measures not contained in CWS can also be reserved. We saved the joined data as excel file. | 3. Join [Measures with MCL] table to [Boonsong Lekagul waterways readings] table in JMP using left outer join so that measures not contained in CWS can also be reserved. We saved the joined data as excel file. | ||
− | [[Image:image1-4.jpg| | + | '''Join table''' |
+ | <table cellspacing="10" border="1"> | ||
+ | <tr> | ||
+ | <td> | ||
+ | '''Join table with MCL level''' | ||
+ | <td>[[Image:image1-4.jpg|700px|right]]</td> | ||
+ | </tr> | ||
+ | </table> | ||
− | ''' | + | '''Compare with MCL level''' |
+ | <table cellspacing="10" border="1"> | ||
+ | <tr> | ||
+ | <td> | ||
+ | Compare the value of these measures in excel, for those lower than the MCL level, delete the data points, others all stay same. | ||
+ | <td> | ||
+ | [[Image:image1-5.jpg|700px|right]]</td> | ||
+ | </tr> | ||
+ | </table> | ||
+ | |||
+ | '''Select value < MCL level''' | ||
+ | <table cellspacing="10" border="1"> | ||
+ | <tr> | ||
+ | <td> | ||
+ | Using if function: =IF(B:B<G:G,"null",B:B), column B is measured value, column G is MCL level, after calculation, here is the data file: | ||
+ | <td> | ||
+ | [[Image:image1-6.jpg|700px|right]]</td> | ||
+ | </tr> | ||
+ | </table> | ||
+ | |||
+ | '''Data cleaning''' | ||
+ | <table cellspacing="10" border="1"> | ||
+ | <tr> | ||
+ | <td> | ||
+ | We deleted all records with “null” value. After data cleaning, 13 measures all cleaned, which means their value are all lower than MCL level over the date, they are: | ||
+ | |||
+ | 1,2,4-Trichlorobenzene 2008 – 2009, Aldrin, Alachlor, Aluminium, Barium, Berilium, Endrin, Hexachlorobenzene, Mercury, Methoxychlor, Pentachlorobenzene, Selenium,Simazine. | ||
+ | <td> | ||
+ | [[Image:image1-7.jpg|500px|right]]</td> | ||
+ | </tr> | ||
+ | </table> | ||
+ | |||
+ | There are 106 chemicals in total. | ||
− | + | 25 of them have standard MCL value. | |
− | + | 12 of chemicals less than MCL over the years. | |
− | [ | + | Now data records are 110,581 and we save the remaining data as [Measures exclude nonimpact] file. |
Latest revision as of 02:23, 8 July 2018
|
|
|
|
Dataset description
Mini-Challenge 2 provides two datasets - [Boonsong Lekagul waterways readings.csv] and [chemical units of measure.csv] and one map image about rivers and streams of some specific locations.
[Boonsong Lekagul waterways readings.csv] includes 5 variables: ID, measure, value of measure, location and measuring date. There are 10 locations, 106 measures and measuring date span is from 1998 to 2016.
[chemical units of measure.csv] includes measures and unit of each measure.
Map view
According to the map image, we can observe the geographical distribution of locations, in order to get specific coordinates of each location, we add the image into tableau as background and set basic range of coordinates of X axis and Y axis given by Mini-Challenge 2.
Here we added pints as annotate on the top of each arrow of each location, so X&Y coordinates of each location will show on the map. We do same for all other locations and we get the [Location.xlsx] file about coordinates of each location.
Data augmentation and data pruning
After the overview of whole data, we observed value variance among measures are quite big and measuring time span of each measure also varies greatly. Units of measures are mainly separated into ug/l and mg/l, it’s hard for us to estimate whether the value of this chemical is toxic for water or not. In this case, I found a file from the California Water Board as a reference.
This file provides MCL(California’s maximum contaminant levels) and DLR( Detection limits for purpose of reporting) of about 120 measures for regulated drinking water. Maximum level of contaminants in drinking water should be lower than raw water, so any value of chemical less than its corresponding level could be considered insignificant.
Notice: all units are mg/l in this file.
Data cleaning
Steps:
1. Compare measures of Boonsong Lekagul waterways readings with measures given in California Water Board. Check measures one by one of both dataset, selected common measures of both. There are 25 measures appeared in CWB in total. Saved the 25 measures in [Measures with MCL] file
2. Unify units of MCL value to a standard unit of these 25 measures, for measures with unit ug/l, changed the maximum value in CWB as ug/l for comparison.
3. Join [Measures with MCL] table to [Boonsong Lekagul waterways readings] table in JMP using left outer join so that measures not contained in CWS can also be reserved. We saved the joined data as excel file.
Join table
Join table with MCL level |
Compare with MCL level
Compare the value of these measures in excel, for those lower than the MCL level, delete the data points, others all stay same. |
Select value < MCL level
Using if function: =IF(B:B<G:G,"null",B:B), column B is measured value, column G is MCL level, after calculation, here is the data file: |
Data cleaning
We deleted all records with “null” value. After data cleaning, 13 measures all cleaned, which means their value are all lower than MCL level over the date, they are: 1,2,4-Trichlorobenzene 2008 – 2009, Aldrin, Alachlor, Aluminium, Barium, Berilium, Endrin, Hexachlorobenzene, Mercury, Methoxychlor, Pentachlorobenzene, Selenium,Simazine. |
There are 106 chemicals in total.
25 of them have standard MCL value.
12 of chemicals less than MCL over the years.
Now data records are 110,581 and we save the remaining data as [Measures exclude nonimpact] file.