Reading a sensor CSV: outliers, drift and flatlines before analytics

Start with the file, not the chart

A trend chart is persuasive, which is exactly why it should come second. Before plotting anything, check the file itself. Most bad analytics starts with a file that was never inspected.

• Columns and units. Which column is the measurement? What unit is it in, and is the unit written anywhere? A header such as value says nothing.
• Timestamp format and time zone. Is it ISO 8601 with an offset or UTC marker, a Unix epoch (seconds or milliseconds?) or a local time without any zone? Local time without an offset becomes ambiguous twice a year when clocks change.
• Row count and span. Does the number of rows match the sampling interval multiplied by the time span? A shortfall means gaps.
• Plausible range. Compare the minimum and maximum with what the sensor can physically measure.

Missing, duplicated and irregular samples

Compute the difference between consecutive timestamps and look at the distribution, not only the average. A clean one-second signal has an interval of 1 s almost everywhere. What you may find instead:

• Gaps, where the interval jumps to minutes or hours: a communication outage or a stopped logger.
• Duplicates, where an interval is zero: a buffer replayed after reconnection.
• Time jumps, including backwards steps: a clock correction or a daylight-saving change.

Decide how each case is handled (fill, interpolate, mark as missing) before any calculation that assumes a regular grid.

Outliers: the z-score and its limits

The z-score measures how many standard deviations a value lies from the mean: z = (x − mean) ÷ standard deviation. A common rule flags values beyond ±3. In the sensor data analyzer you set the sigma threshold yourself, see each value’s z-score and status in the filtered table and export the result.

The method has a known weakness: the mean and standard deviation are themselves distorted by the outliers you are trying to find, and a large spike can inflate the standard deviation enough to hide itself. Robust alternatives use the median and the interquartile range (flag values below Q1 − 1.5 × IQR or above Q3 + 1.5 × IQR). Use more than one method when the result matters.

An outlier is only a statement about the data, not about the process. A pressure spike may be a failing transmitter or a real water-hammer event. Check the neighbouring signals and the maintenance log before deciding which one it is.

Drift: when the baseline moves slowly

Drift is a slow, steady change that individual values do not reveal. It shows up when you compare windows: the mean of the first hour against the mean of the last, or a rolling mean over a long window. Typical causes are sensor ageing, fouling, temperature effects on the electronics and a calibration that has lapsed. If you have a second, independent measurement of the same quantity, plot the difference between them: a steadily widening gap is the clearest sign of drift in one of the two.

Flatlines and frozen values

A signal that stops changing is as suspicious as one that spikes. Real process variables rarely return the identical value for long stretches, so a run of repeated values where noise is normal often means:

• the communication link dropped and the last value is being held;
• the sensor has failed or is stuck at a limit;
• a scaling or mapping fault is feeding a constant.

Detect it by measuring the length of runs of identical values, or by testing for near-zero variance in a sliding window. Be careful with signals that are constant by design, such as a setpoint or a digital state, and decide per signal what “too flat” means.

Decide before you clean

• Flag, do not delete. Add a quality column rather than removing rows, so the decision can be reviewed.
• Keep the raw file. Work on a copy and write down the filter parameters you used.
• Make the rule explicit. “Remove everything beyond 3σ” may be right for one signal and wrong for another.

Try it on synthetic data first

If you want to practise without real plant data, create a multi-channel file with noise and trends in the sensor data generator, then load it into the analyzer and see which of the problems above you can find. For the wider picture of why this matters, read sensor data quality before analytics and data quality for manufacturing time series.