Chapter 4 ■ Data transformation
INSERT INTO derived_annotation_test (blood_oxygen, weight, avg_weight, volts)
VALUES (95.46, 46.49, (46.49/4.00), 69.62), (94.96, 46.18, (46.18/4.00), 70.12),
(94.62, 46.11, (46.11/4.00), 70.52);
A quick check of the data in the table confirms the rows were inserted with the correct values.mysql> SELECT * FROM derived_annotation_test;
+----+--------------+------------------------+--------+------------+-------+
| id | blood_oxygen | blood_oxygen_corrected | weight | avg_weight | volts |
+----+--------------+------------------------+--------+------------+-------+
| 5 | 94.88 | 95.665 | 46.07 | NULL | 68.42 |
| 7 | 94.88 | 95.665 | 46.07 | NULL | 68.42 |
| 8 | 95.23 | 96.015 | 46.56 | NULL | 68.52 |
| 9 | 94.97 | 95.755 | 46.42 | NULL | 68.62 |
| 10 | 95.46 | 96.245 | 46.49 | 11.6225 | 69.62 |
| 11 | 94.96 | 95.745 | 46.18 | 11.545 | 70.12 |
| 12 | 94.62 | 95.405 | 46.11 | 11.5275 | 70.52 |
+----+--------------+------------------------+--------+------------+-------+
7 rows in set (0.00 sec)
Here we see only the last three rows have the average weight column. Recall we added the new column
to a table that already contained data. Unless there was a trigger added, the values for the new column for
the existing rows will remain empty (technically NULL).
Data Interpretations
Sometimes data is generated in a form that is not usable or requires some translation or interpretation to
be usable. We have already seen an example of data interpretation. Recall the plant-monitoring example
from Chapter 1 where we had a sensor that, depending on the value generated, could indicate one of several
states. Thus, we can interpret the data values to be one of these states and therefore store the state. In this
case, we are creating a value derived from a range of values rather than calculating a new value.
Let’s see a similar example, but this time we will see data generated from a different type of sensor. In
this case, the sensor being simulated is a liquid-level sensor such as sold by Sparkfun (http://sparkfun.
com/products/10221). This is a sensor that measures resistance that can be used to determine the distance
from the top of the sensor to the surface of the liquid. More specifically, the output is inversely proportional
to the level of the liquid. The lower the liquid, the higher resistance measured. The lower the liquid, the less
resistance measured.
Now, let’s suppose we are using this sensor in a pond-monitoring solution where we want to measure
the level of the water inside the filter reservoir. Furthermore, we know from experience and observation that
depending on the level of the water, the state of the pond can change as follows:
- If the water level is low at 6" or lower than a fixed point, the filters may need cleaning.
- If it water level is between 3-6", the pond is low on water.
- If the water level is between 1-3", the pond is in a normal state.
- If the water level is above 1", there is too much water in the pond and water is flowing
over the filter elements.
Thus, we need to check the value for resistance in specific ranges and store an enumerated value for
the range. We will name the range (CLEAN, LOW, NORMAL, HIGH). Now let’s see how we can store a row of data
using a Python script. Remember, we always want to store the original value so that if the enumerations need
adjusting (the ranges change), we can alter the existing data without rendering it unusable.