Clean and aggregate factory event data in Pandas

You are given three Pandas DataFrames for a factory: (1) events[event_id, machine_id, ts_utc (datetime64[ns, UTC]), event_type in {'start','stop','fault'}, batch_id], (2) telemetry[machine_id, ts_local (datetime64[ns]), temperature_C, rpm, power_kW, timezone (IANA string like 'US/Pacific')], (3) calendar[date (YYYY-MM-DD), is_holiday (bool), shift in {'A','B','C'}]. Data issues: late-arriving events up to 48 hours late, duplicate events (same event_id with ts_utc differences up to ±2 seconds), daylight saving transitions, and missing telemetry rows. Memory budget is 1 GB, total rows ≈50M. Tasks: a) Normalize all time to a single axis; deduplicate events with a deterministic rule (state your rule) while preserving correct event order. b) For the last 7 calendar days up to and including today=2025-09-01 in each machine’s local time, compute per-machine hourly features: throughput (count of completed start→stop cycles), 95th percentile temperature, and a rolling 24-hour z-score of power_kW. Handle missing hours and DST gaps/overlaps correctly. c) Join features into a tidy machine-hour panel indexed by [machine_id, hour_start_utc); impute missing values robustly; flag anomalies where |z|>3. Provide Pandas code snippets and explain performance tactics (chunked IO, dtypes, categoricals, Parquet, vectorized ops) and how you would test correctness on edge cases.

Deduplicate factory events while preserving time order

You are given factory event data with possible duplicate events for the same logical event_id. Duplicates are defined as rows that share the same event_id but may have slightly different ts_utc values (up to a couple of seconds apart). The goal is to deduplicate these events in a deterministic way while preserving chronological ordering.

Using the events table below:

• Treat ts_utc as the canonical timestamp (already in UTC).
• If multiple rows have the same event_id, keep only the row with the latest ts_utc.
• Return one row per event_id with the columns: event_id, machine_id, ts_utc, event_type, batch_id. Format ts_utc as YYYY-MM-DDTHH24:MI:SSZ.
• Order the result by machine_id ascending, then ts_utc ascending.

Write a SQL query to produce this deduplicated, time-ordered list of events.

Table events

column	type	constraints
event_row_id	INT	PRIMARY KEY
event_id	INT	NOT NULL
machine_id	INT	NOT NULL
ts_utc	TIMESTAMP WITH TIME ZONE	NOT NULL
event_type	VARCHAR(10)	NOT NULL
batch_id	INT

Sample data:

batch_id	event_id	event_row_id	event_type	machine_id	ts_utc
101	1	1	start	1	2025-05-25T23:58:00Z
101	2	2	stop	1	2025-05-26T00:10:00Z
102	3	3	start	1	2025-05-26T01:05:00Z
102	4	4	stop	1	2025-05-26T01:45:00Z
102	4	5	stop	1	2025-05-26T01:45:01Z
201	5	6	start	2	2025-05-26T02:00:00Z
201	6	7	stop	2	2025-05-26T02:20:00Z
NULL	7	8	fault	2	2025-05-27T03:00:00Z

Table telemetry

column	type	constraints
id	INT	PRIMARY KEY
machine_id	INT	NOT NULL
ts_local	TIMESTAMP WITHOUT TIME ZONE	NOT NULL
temperature_c	DECIMAL(5,2)	NOT NULL
rpm	INT	NOT NULL
power_kw	DECIMAL(6,2)	NOT NULL
timezone	VARCHAR(50)	NOT NULL

Sample data:

id	machine_id	power_kw	rpm	temperature_c	timezone	ts_local
1	1	5	1000	70	UTC	2025-05-26T00:05:00
2	1	5.5	1100	72	UTC	2025-05-26T00:30:00
3	1	6	1150	75	UTC	2025-05-26T01:15:00
4	2	4	900	68	US/Pacific	2025-05-25T19:10:00
5	2	4.5	950	69	US/Pacific	2025-05-25T19:40:00
6	2	4.8	1000	71	US/Pacific	2025-05-26T20:05:00

Table calendar

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
is_holiday	BOOLEAN	NOT NULL
shift	CHAR(1)	NOT NULL

Sample data:

date	is_holiday	shift
2025-05-26	false	A
2025-05-27	false	A
2025-05-28	false	B
2025-05-29	false	B
2025-05-30	false	C
2025-05-31	false	C
2025-06-01	true	A

Expected output

One row per logical event_id, keeping the row with the latest ts_utc for each event_id, ordered by machine_id and ts_utc.

batch_id	event_id	event_type	machine_id	ts_utc
101	1	start	1	2025-05-25T23:58:00Z
101	2	stop	1	2025-05-26T00:10:00Z
102	3	start	1	2025-05-26T01:05:00Z
102	4	stop	1	2025-05-26T01:45:01Z
201	5	start	2	2025-05-26T02:00:00Z
201	6	stop	2	2025-05-26T02:20:00Z
NULL	7	fault	2	2025-05-27T03:00:00Z

Hints

1. Use ROW_NUMBER partitioned by event_id and ordered by ts_utc DESC to keep the latest duplicate.
2. Format the UTC timestamp with a PostgreSQL TO_CHAR template containing literal T and Z characters.
3. Order by machine_id, then the kept timestamp to preserve chronological machine order.

Compute machine-hour throughput, temperature percentiles, and rolling power z-scores

You are building per-machine, per-hour features for a 4-hour window on a UTC time axis, combining deduplicated production events with machine telemetry.

There are three tables (events, telemetry, calendar). For this question only events and telemetry are needed.

Assume the window is the 4 hourly buckets from 2025-05-26 00:00:00 UTC through 2025-05-26 03:00:00 UTC (inclusive of both endpoints), so the hour-start timestamps are 00:00, 01:00, 02:00, and 03:00 on 2025-05-26.

Produce the feature table as follows:

1. Deduplicate events. The events table may contain duplicate rows for the same event_id (a late re-emission). For each event_id, keep only the row with the latest ts_utc.

2. Normalize telemetry to UTC. telemetry.ts_local is a wall-clock timestamp with no zone; the timezone column holds the IANA zone name (e.g. 'UTC', 'America/Los_Angeles'). Convert each reading to a UTC instant before bucketing it.

3. Build a complete hour grid. Output one row for every machine and every one of the 4 hour buckets in the window — even hours with no events and no telemetry. The set of machines is the distinct machines that appear in either the deduplicated events or the telemetry.

4. throughput (INT). Using the deduplicated, time-ordered events per machine, a completed production cycle is a 'stop' event whose immediately preceding event (by ts_utc, same machine) is a 'start'. Count, per machine and hour, how many such completed cycles have their 'stop' timestamp falling in that hour. Hours with no completed cycle have throughput = 0.

5. temp_p95. The 95th percentile (discrete, percentile_disc) of temperature_c over the normalized telemetry readings that fall in that machine/hour. NULL when the hour has no telemetry.

6. avg_power_kw. The average power_kw over the normalized telemetry readings in that machine/hour. NULL when the hour has no telemetry.

7. power_kw_z24. A rolling 24-hour z-score of avg_power_kw per machine, ordered by hour_start_utc, over the frame ROWS BETWEEN 23 PRECEDING AND CURRENT ROW (up to the previous 23 hours plus the current hour). The z-score is (avg_power_kw - mean) / stddev, where mean and the sample standard deviation (stddev_samp) are taken over the non-null hourly averages in that frame. If fewer than 2 non-null hourly averages are in the frame, or the sample standard deviation is 0, the z-score is NULL.

Return exactly these columns, one row per machine and hour: machine_id, hour_start_utc, throughput, temp_p95, avg_power_kw, power_kw_z24.

Sort the result by machine_id, then hour_start_utc ascending.

Table events

column	type	constraints
event_row_id	INT	PRIMARY KEY
event_id	INT	NOT NULL
machine_id	INT	NOT NULL
ts_utc	TIMESTAMP WITH TIME ZONE	NOT NULL
event_type	VARCHAR(10)	NOT NULL
batch_id	INT

Sample data:

batch_id	event_id	event_row_id	event_type	machine_id	ts_utc
101	1	1	start	1	2025-05-25T23:58:00Z
101	2	2	stop	1	2025-05-26T00:10:00Z
102	3	3	start	1	2025-05-26T01:05:00Z
102	4	4	stop	1	2025-05-26T01:45:00Z
102	4	5	stop	1	2025-05-26T01:45:01Z
201	5	6	start	2	2025-05-26T02:00:00Z
201	6	7	stop	2	2025-05-26T02:20:00Z
NULL	7	8	fault	2	2025-05-27T03:00:00Z

Table telemetry

column	type	constraints
id	INT	PRIMARY KEY
machine_id	INT	NOT NULL
ts_local	TIMESTAMP WITHOUT TIME ZONE	NOT NULL
temperature_c	DECIMAL(5,2)	NOT NULL
rpm	INT	NOT NULL
power_kw	DECIMAL(6,2)	NOT NULL
timezone	VARCHAR(50)	NOT NULL

Sample data:

id	machine_id	power_kw	rpm	temperature_c	timezone	ts_local
1	1	5	1000	70	UTC	2025-05-26T00:05:00
2	1	5.5	1100	72	UTC	2025-05-26T00:30:00
3	1	6	1150	75	UTC	2025-05-26T01:15:00
4	2	4	900	68	America/Los_Angeles	2025-05-25T19:10:00
5	2	4.5	950	69	America/Los_Angeles	2025-05-25T19:40:00
6	2	4.8	1000	71	America/Los_Angeles	2025-05-26T20:05:00

Table calendar

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
is_holiday	BOOLEAN	NOT NULL
shift	CHAR(1)	NOT NULL

Sample data:

date	is_holiday	shift
2025-05-26	false	A
2025-05-27	false	A
2025-05-28	false	B
2025-05-29	false	B
2025-05-30	false	C
2025-05-31	false	C
2025-06-01	true	A

Expected output

One row per machine and per hour in the 4-hour window (2025-05-26 00:00-03:00 UTC); columns are throughput (completed start->stop cycles stopping in the hour), the 95th-percentile temperature, the average power, and the rolling 24h z-score of that hourly average power. Hours with no telemetry have NULL temp/power/z-score and throughput 0.

avg_power_kw	hour_start_utc	machine_id	power_kw_z24	temp_p95	throughput
5.2500000000000000	2025-05-26T00:00:00+00:00	1	NULL	72.00	1
6.0000000000000000	2025-05-26T01:00:00+00:00	1	0.70710678118654752440084436210484	75.00	1
NULL	2025-05-26T02:00:00+00:00	1	NULL	NULL	0
NULL	2025-05-26T03:00:00+00:00	1	NULL	NULL	0
NULL	2025-05-26T00:00:00+00:00	2	NULL	NULL	0
NULL	2025-05-26T01:00:00+00:00	2	NULL	NULL	0
4.2500000000000000	2025-05-26T02:00:00+00:00	2	NULL	69.00	1
NULL	2025-05-26T03:00:00+00:00	2	NULL	NULL	0

Hints

1. Build the per-machine/per-hour skeleton first: cross every distinct machine with generate_series over the 4 hour-start timestamps, then LEFT JOIN events and telemetry onto it so empty hours survive.
2. Convert telemetry to UTC with ts_local AT TIME ZONE timezone (use a real IANA zone like 'America/Los_Angeles', not 'US/Pacific'), and detect completed cycles with LAG(event_type) over each machine ordered by ts_utc.
3. Compute the rolling z-score over a named window ROWS BETWEEN 23 PRECEDING AND CURRENT ROW, gating on COUNT(...) >= 2 and stddev_samp(...) > 0 so sparse or constant frames return NULL.

Build a tidy machine-hour panel with imputation and anomaly flags

Two source tables capture factory activity over a fixed time window:

• events — machine lifecycle events. Each physical event may be duplicated (same event_id inserted more than once with a slightly different ts_utc); keep only one copy per event_id (the latest ts_utc). ts_utc is already in UTC (TIMESTAMP WITH TIME ZONE). event_type is one of 'start', 'stop', 'fault'.
• telemetry — periodic sensor readings (temperature_c, power_kw) stored in machine-local time (ts_local, a naive timestamp) together with the IANA timezone of that machine. Normalize every reading to UTC with ts_local AT TIME ZONE timezone.

(The calendar table is provided for context only and is not required by this question.)

Build a tidy machine-hour panel over the window 2025-05-26 00:00:00 UTC through 2025-05-26 05:00:00 UTC, inclusive of both endpoints — i.e. the 6 hourly buckets 00:00, 01:00, 02:00, 03:00, 04:00, 05:00 (UTC). The grid must be complete: emit one row per machine_id and hour_start_utc even for hours with no events and no telemetry. A machine appears in the grid if it has at least one row in events or in telemetry.

Step 1 — per machine and hour, compute the raw hourly metrics:

• throughput = number of completed cycles ending in that hour. A completed cycle is a deduplicated 'stop' event whose immediately preceding event for the same machine (ordered by ts_utc) is a 'start'. Count a cycle in the hour bucket [hour_start_utc, hour_start_utc + 1 hour) that contains its 'stop' timestamp.
• temp_p95 = the 95th-percentile temperature (percentile_disc(0.95)) over telemetry readings whose UTC timestamp falls in that hour bucket; NULL if the hour has no telemetry.
• avg_power_kw = the average power_kw over telemetry readings in that hour bucket; NULL if none.
• power_kw_z24 = the rolling 24-hour z-score of avg_power_kw within each machine, over the window frame ROWS BETWEEN 23 PRECEDING AND CURRENT ROW ordered by hour_start_utc. Define it as (avg_power_kw - mean) / stddev_samp over that frame, and leave it NULL when fewer than 2 non-null values are in the frame or the sample standard deviation is 0.

Step 2 — impute missing values:

• throughput: replace NULL with 0.
• temp_p95: replace NULL with the mean of temp_p95 for that machine_id over the whole window (ignoring NULLs) → temp_p95_imputed.
• avg_power_kw: replace NULL with the mean of avg_power_kw for that machine_id over the whole window (ignoring NULLs) → avg_power_kw_imputed.
• power_kw_z24: replace NULL with 0 → power_kw_z24_imputed.

Step 3 — anomaly flag: add a boolean is_anomaly that is TRUE when ABS(power_kw_z24_imputed) > 3, else FALSE.

Output: one row per machine_id, hour_start_utc with the columns, in this order: machine_id, hour_start_utc, throughput, temp_p95_imputed, avg_power_kw_imputed, power_kw_z24_imputed, is_anomaly. Round the three imputed numeric columns to 4 decimal places. Order the result by machine_id, then hour_start_utc ascending.

Table events

column	type	constraints
event_row_id	INT	PRIMARY KEY
event_id	INT	NOT NULL
machine_id	INT	NOT NULL
ts_utc	TIMESTAMP WITH TIME ZONE	NOT NULL
event_type	VARCHAR(10)	NOT NULL
batch_id	INT

Sample data:

batch_id	event_id	event_row_id	event_type	machine_id	ts_utc
101	1	1	start	1	2025-05-25T23:58:00Z
101	2	2	stop	1	2025-05-26T00:10:00Z
102	3	3	start	1	2025-05-26T01:05:00Z
102	4	4	stop	1	2025-05-26T01:45:00Z
102	4	5	stop	1	2025-05-26T01:45:01Z
201	5	6	start	2	2025-05-26T02:00:00Z
201	6	7	stop	2	2025-05-26T02:20:00Z
NULL	7	8	fault	2	2025-05-27T03:00:00Z

Table telemetry

column	type	constraints
id	INT	PRIMARY KEY
machine_id	INT	NOT NULL
ts_local	TIMESTAMP WITHOUT TIME ZONE	NOT NULL
temperature_c	DECIMAL(5,2)	NOT NULL
rpm	INT	NOT NULL
power_kw	DECIMAL(6,2)	NOT NULL
timezone	VARCHAR(50)	NOT NULL

Sample data:

id	machine_id	power_kw	rpm	temperature_c	timezone	ts_local
1	1	5	1000	70	UTC	2025-05-26T00:05:00
2	1	5.5	1100	72	UTC	2025-05-26T00:30:00
3	1	6	1150	75	UTC	2025-05-26T01:15:00
4	2	4	900	68	America/Los_Angeles	2025-05-25T19:10:00
5	2	4.5	950	69	America/Los_Angeles	2025-05-25T19:40:00
6	2	4.8	1000	71	America/Los_Angeles	2025-05-26T20:05:00

Table calendar

column	type	constraints
date	DATE	PRIMARY KEY, NOT NULL
is_holiday	BOOLEAN	NOT NULL
shift	CHAR(1)	NOT NULL

Sample data:

date	is_holiday	shift
2025-05-26	false	A
2025-05-27	false	A

Expected output

A complete 2-machine x 6-hour UTC panel (12 rows) with throughput (NULL->0), temp_p95 and avg_power_kw imputed to each machine's windowed mean, z-score (NULL->0), and is_anomaly = ABS(z) > 3; ordered by machine_id then hour_start_utc.

avg_power_kw_imputed	hour_start_utc	is_anomaly	machine_id	power_kw_z24_imputed	temp_p95_imputed	throughput
5.2500	2025-05-26T00:00:00+00:00	false	1	0.0000	72.0000	1
6.0000	2025-05-26T01:00:00+00:00	false	1	0.7071	75.0000	1
5.6250	2025-05-26T02:00:00+00:00	false	1	0.0000	73.5000	0
5.6250	2025-05-26T03:00:00+00:00	false	1	0.0000	73.5000	0
5.6250	2025-05-26T04:00:00+00:00	false	1	0.0000	73.5000	0
5.6250	2025-05-26T05:00:00+00:00	false	1	0.0000	73.5000	0
4.2500	2025-05-26T00:00:00+00:00	false	2	0.0000	69.0000	0
4.2500	2025-05-26T01:00:00+00:00	false	2	0.0000	69.0000	0
4.2500	2025-05-26T02:00:00+00:00	false	2	0.0000	69.0000	1
4.2500	2025-05-26T03:00:00+00:00	false	2	0.0000	69.0000	0
4.2500	2025-05-26T04:00:00+00:00	false	2	0.0000	69.0000	0
4.2500	2025-05-26T05:00:00+00:00	false	2	0.0000	69.0000	0

Hints

1. Deduplicate events by event_id (keep latest ts_utc) BEFORE pairing start->stop with LAG, and convert telemetry to UTC with ts_local AT TIME ZONE timezone.
2. Use generate_series cross-joined to every machine to build the complete hourly grid, then LEFT JOIN the throughput and telemetry aggregates so empty hours survive.
3. Compute per-machine window means in a separate CTE and COALESCE them in for imputation; the anomaly flag is just ABS(COALESCE(power_kw_z24,0)) > 3.