How do you clean sensor CSV data?

Separators, decimals and units

Sensor exports vary with the software and regional settings that produced them. A file may use semicolons as separators and commas as decimal marks, which a default import can read wrongly. Check the delimiter and decimal separator before anything else. A value written as '4,20' can be read as 420 if the parser takes the comma as a thousands separator. Then confirm the encoding, the header row, the timestamp format and time zone, and that each column has one unit, stated in the header or a data dictionary.

Before any change, keep an untouched copy of the file. Every later step should be a logged rule applied to that copy, so the result can be reproduced and questioned.

A hypothetical pressure log

Hypothetical pressure log with semicolon separators and comma decimals. Three rows: 08:00:00;4,20, 08:00:01;-999, 08:00:02;4,25.

• Parse with separator ';' and decimal ',' so 4,20 becomes 4.20 bar. A misread decimal mark would turn the same value into 420.
• In this example, -999 is a sensor error code. Mark it missing: do not convert it to zero, and do not keep it as a reading.
• Mean of the valid readings: (4.20 + 4.25) / 2 = 4.225 bar.

If -999 is left in, the mean becomes (4.20 − 999 + 4.25) / 3 ≈ −330.18 bar, which is physically meaningless for this sensor. The raw file still holds all three rows, so the step can be checked later.

Cleaning mistakes

The first mistake is confusing missing with zero. If a gap was filled with 0, the data now claims the sensor read exactly zero. The second is deleting outliers without looking at them, when a spike may be a real event such as a fast pressure surge. The third is treating every row with a repeated timestamp as junk. If the values differ, find the cause first. The last is overwriting the raw file, which removes the ability to undo or audit a cleaning step.

Follow-up questions