Skip to content
Fondazione ITS INCOM

Examples · Internet of Things

Cleaning sensor data with pandas

Two sensors log at different times, with duplicate rows and one of them in local time. A model writes the pandas code that puts both on a 5-minute grid in UTC; you run it, and a checker compares the result with values you worked out by hand.

Intermediate · 35 min

The idea

In a lab at school, two sensors log the same room:

Their messages travel over MQTT with QoS 1, as in MQTT topics for a smart classroom, which means “at least once”: now and then a reading arrives twice. The Pi also lost its Wi-Fi for about ten minutes, and when it came back it sent its last reading again.

To compare the two you need one table: one row every 5 minutes, in UTC, with the mean of each sensor in that slot. A model can write that pandas code in seconds. The code runs, prints a nice table, and can still be wrong by a few hundredths. So:

  1. the model writes merge_sensors.py from a written request;
  2. you read it, then run it;
  3. a checker compares the result with numbers you worked out by hand.

What you need

A key and the openai library, as in Your first API call in Python, and pandas: pip install pandas. We used pandas 3.0.6, and the version matters, as you will see.

The two files

Save them as sensor_a.csv and sensor_b.csv:

time,temperature
2026-10-06T08:00:37Z,20.8
2026-10-06T08:03:36Z,20.9
2026-10-06T08:06:00Z,20.9
2026-10-06T08:09:05Z,21.1
2026-10-06T08:11:28Z,21.0
2026-10-06T08:14:06Z,21.1
2026-10-06T08:17:14Z,21.2
2026-10-06T08:19:19Z,21.2
2026-10-06T08:22:09Z,21.3
2026-10-06T08:24:29Z,21.3
2026-10-06T08:27:36Z,21.4
2026-10-06T08:29:40Z,21.3
2026-10-06T08:32:10Z,21.4
2026-10-06T08:35:09Z,21.5
2026-10-06T08:37:34Z,21.5
2026-10-06T08:40:11Z,21.4
2026-10-06T08:42:21Z,21.6
2026-10-06T08:45:30Z,21.4
2026-10-06T08:47:58Z,21.6
2026-10-06T08:47:58Z,21.6
2026-10-06T08:50:16Z,21.7
2026-10-06T08:52:56Z,21.7
2026-10-06T08:56:01Z,21.8
2026-10-06T08:58:09Z,21.9

time,humidity
06/10/2026 10:01:52,46.0
06/10/2026 10:05:49,45.9
06/10/2026 10:09:11,46.5
06/10/2026 10:09:11,46.5
06/10/2026 10:12:31,46.2
06/10/2026 10:15:57,46.4
06/10/2026 10:20:07,46.6
06/10/2026 10:24:39,46.5
06/10/2026 10:28:33,46.8
06/10/2026 10:30:41,47.0
06/10/2026 10:34:02,47.3
06/10/2026 10:46:15,47.9
06/10/2026 10:34:02,47.3
06/10/2026 10:49:30,48.1
06/10/2026 10:53:08,48.0
06/10/2026 10:56:44,47.6
06/10/2026 10:59:20,47.4

On 6 October Italy is on summer time, UTC+2: the Pi's 10:01:52 is 08:01:52 UTC. Both files cover the same hour, 08:00 to 09:00 UTC.

1. The request

Save it as request.txt. Every trap of the two files is written down: the model knows only what you tell it.

Write a Python function merge(a_path, b_path) with pandas.
- sensor_a.csv has the columns time,temperature. time is UTC in ISO 8601, like 2026-10-06T08:00:37Z.
- sensor_b.csv has the columns time,humidity. time is the local time in Busto Arsizio (Italy), written dd/mm/yyyy hh:mm:ss, with no offset.
- Both files may contain the same row twice: count it once. The rows of sensor_b.csv are not always in time order.
Return one DataFrame on a 5-minute grid in UTC:
- the index is a timezone-aware DatetimeIndex in UTC, named time, from the first to the last 5-minute slot that has a reading;
- each slot holds the mean of the readings from its start (included) to 5 minutes later (excluded);
- the columns are temperature and humidity; a slot without readings stays NaN.
Only the function and its imports, in one ```python block.

2. The model writes the code

Save it as write_merge.py:

import os
import re
import sys
from pathlib import Path

from openai import OpenAI

client = OpenAI(
    base_url="https://api.ai.itsincom.org/v1",
    api_key=os.environ["API_KEY"],
)
model = sys.argv[1] if len(sys.argv) > 1 else "qwen3-coder-30b"

answer = client.chat.completions.create(
    model=model,
    temperature=0,
    messages=[{"role": "user", "content": Path("request.txt").read_text()}],
)
if answer.choices[0].finish_reason != "stop":
    sys.exit(f"The answer was cut off ({answer.choices[0].finish_reason}): run it again.")
text = answer.choices[0].message.content
code = re.search(r"```(?:python)?\n(.*?)```", text, re.S)
Path("merge_sensors.py").write_text(code.group(1) if code else text)
print(f"merge_sensors.py, by {model}: read it before you run it.\n")
print(Path("merge_sensors.py").read_text())

3. The checker

The numbers in KNOWN come from the two files, with a pencil: for example, the 08:05 slot of sensor B holds 45.9 and 46.5, so the mean is 46.2. If the second copy of 46.5 is counted too, it becomes 46.3. Save it as check_merge.py:

import math
import sys

import pandas as pd

from merge_sensors import merge

# Worked out by hand from the two files: the mean of each 5-minute slot, in UTC.
KNOWN = {
    "08:05": (21.0, 46.2),       # sensor B sent 10:09:11 twice: with both copies, 46.3
    "08:30": (21.4, 47.15),      # 10:34:02 came back after the gap: with both copies, 47.2
    "08:40": (21.5, math.nan),   # sensor B was offline: no humidity, and none made up
    "08:45": (21.5, 48.0),       # sensor A sent 08:47:58 twice: with both copies, 21.53
    "08:55": (21.85, 47.5),      # the last slot
}

try:
    df = merge("sensor_a.csv", "sensor_b.csv")
except Exception as error:  # a crash is a failed check too
    sys.exit(f"FAIL merge() stopped with {type(error).__name__}: {error}")
print(df, "\n")


def same(got, expected):
    return math.isnan(got) if math.isnan(expected) else abs(got - expected) < 0.01


def slot_is(hhmm, temperature, humidity):
    try:
        row = df.loc[pd.Timestamp(f"2026-10-06 {hhmm}", tz="UTC")]
        return same(float(row["temperature"]), temperature) and same(float(row["humidity"]), humidity)
    except (KeyError, TypeError, ValueError):  # the slot is missing, twice, or in another time zone
        return False


checks = {
    "12 rows, 08:00 to 08:55": len(df) == 12,
    "no time twice": df.index.is_unique,
    "index in UTC": str(getattr(df.index, "tz", None)) == "UTC",
    "columns temperature, humidity": list(df.columns) == ["temperature", "humidity"],
}
for hhmm, (temperature, humidity) in KNOWN.items():
    label = f"{hhmm}: {temperature} °C, " + ("no humidity" if math.isnan(humidity) else f"{humidity} %")
    checks[label] = slot_is(hhmm, temperature, humidity)
for name, ok in checks.items():
    print("ok  " if ok else "FAIL", name)
print(f"{sum(checks.values())} of {len(checks)} checks passed")

check_merge.py imports and runs code that a model wrote. Read merge_sensors.py before you run it, as you would read code from a stranger.

What you should see

First the model for code, qwen3-coder-30b:

python3 write_merge.py
python3 check_merge.py

merge_sensors.py, by qwen3-coder-30b: read it before you run it.

import pandas as pd
…
    df_combined['time_bin'] = df_combined['time'].dt.floor('5T')
…

FAIL merge() stopped with ValueError: Invalid frequency: 5T. Failed to parse with error message: ValueError("Invalid frequency: T. Failed to parse with error message: KeyError('T'). Did you mean min?")

Then qwen3.8-27b:

python3 write_merge.py qwen3.8-27b
python3 check_merge.py

                           temperature  humidity
time                                            
2026-10-06 08:00:00+00:00        20.85     46.00
2026-10-06 08:05:00+00:00        21.00     46.20
…
2026-10-06 08:30:00+00:00        21.40     47.15
2026-10-06 08:35:00+00:00        21.50       NaN
2026-10-06 08:40:00+00:00        21.50       NaN
2026-10-06 08:45:00+00:00        21.50     48.00
2026-10-06 08:50:00+00:00        21.70     48.00
2026-10-06 08:55:00+00:00        21.85     47.50 

ok   12 rows, 08:00 to 08:55
ok   no time twice
ok   index in UTC
ok   columns temperature, humidity
ok   08:05: 21.0 °C, 46.2 %
ok   08:30: 21.4 °C, 47.15 %
ok   08:40: 21.5 °C, no humidity
ok   08:45: 21.5 °C, 48.0 %
ok   08:55: 21.85 °C, 47.5 %
9 of 9 checks passed

Its code reads sensor B with format="%d/%m/%Y %H:%M:%S", then .dt.tz_localize("Europe/Rome").dt.tz_convert("UTC"); it removes duplicates in each file, puts each reading in its slot with .dt.floor("5min"), and averages each slot.

What the generated code got wrong

qwen3-coder-30b never passed. With this request, its three runs all stopped with an error: twice on "5T", once because it put times with and without a time zone in the same column. "5T" is an old name for “5 minutes”: older pandas accepted it, pandas 3 does not. The model used it in every one of its seven runs, with this request and an earlier one. It also tried, in different runs, to subtract a fixed two hours from Italian time (“assuming summer”), and to read the Italian times as if they were UTC. Twice we sent its error back to it, with “I use pandas 3.0”: both new versions stopped again, one of them with the same error.

qwen3.8-27b passed three runs out of three with this request. Our first request said only “Both files may contain the same row twice”, without “count it once”. Six runs, and every one of them ran without errors and printed a table that looks right. In three of the six, the checker said 6 of 9: the model had kept both copies. One of them even wrote it in a comment:

    # Mean per slot (duplicates are naturally included in the mean)

All three tables had 46.3 at 08:05, 47.2 at 08:30 and 21.533 at 08:45: wrong by a few hundredths, exactly where the comments of KNOWN say. Nobody would see that by looking at the table. A known value at a known time does.

A crash is the easy case: you see it. The dangerous one is code that runs and gives numbers that are almost right.

Good to know

Exercise

All examples