anofox-forecast-data-prep

Name: anofox-forecast-data-prep
Author: DataZooDE

35stars3forks

How to install anofox-forecast-data-prep?

datazoode/anofox-forecast/anofox-forecast-data-prep

$npx -y skills add DataZooDE/anofox-forecast --skill anofox-forecast-data-prep

Installs into the current project.

Summary

Data preparation for the anofox_forecast DuckDB extension — filling gaps, imputing nulls, dropping bad series, differencing, detrending, hierarchical key operations. Use when preparing raw time series for downstream forecasting or backtesting with ts_forecast_by / `ts_cv_folds_

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Data Preparation Cheat Sheet
2
3	Extension: `anofox_forecast` v0.15.3 \| DuckDB: v1.4.5 LTS / v1.5.4+ \| Dual naming: `ts_` and `anofox_fcst_ts_` (identical)
4
5	Prep raw time series so downstream forecasting / CV has clean input: no gaps, no unwanted NULLs, no degenerate series, correct hierarchy.
6
7	## Critical gotcha — materialise between `_by` steps
8
9	`_by` table functions do not chain in CTEs — under parallel execution they silently return 0 rows. Always `CREATE TABLE` between pipeline steps.
10
11	```sql
12	-- BROKEN (silent 0 rows under parallelism):
13	WITH step1 AS (SELECT * FROM ts_fill_gaps_by('raw', id, ds, y, '1d', MAP{}))
14	SELECT * FROM ts_fill_nulls_const_by('step1', id, ds, y, 0.0);
15
16	-- CORRECT:
17	CREATE TABLE step1 AS SELECT * FROM ts_fill_gaps_by('raw', id, ds, y, '1d', MAP{});
18	CREATE TABLE step2 AS SELECT * FROM ts_fill_nulls_const_by('step1', id, ds, y, 0.0);
19	```
20
21	## Gap filling
22
23	### `ts_fill_gaps_by`
24	Insert missing date rows (NULL value) so every series has a complete regular grid.
25
26	```sql
27	ts_fill_gaps_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,
28	frequency VARCHAR) → TABLE
29	```
30
31	No params — pure frequency-driven grid completion. Inserted rows have NULL in `value_col`; use `ts_fill_nulls_*_by` next to impute.
32
33	Frequencies: `'1d'`, `'1h'`, `'30m'`, `'1w'`, `'1mo'`, `'1q'`, `'1y'` (Polars-style) or `'1 day'` (DuckDB INTERVAL) or raw int (days).
34
35	```sql
36	CREATE TABLE gaps_filled AS
37	SELECT * FROM ts_fill_gaps_by('raw', product_id, ds, y, '1d');
38	```
39
40	## NULL imputation
41
42	Table macros. Signatures:
43
44	\| Function \| Signature \|
45	\|---\|---\|
46	\| `ts_fill_nulls_forward_by(source, group_col, date_col, value_col)` \| Last observation carried forward \|
47	\| `ts_fill_nulls_backward_by(source, group_col, date_col, value_col)` \| Next observation carried backward \|
48	\| `ts_fill_nulls_mean_by(source, group_col, date_col, value_col)` \| Per-group mean (skips NULLs) \|
49	\| `ts_fill_nulls_const_by(source, group_col, date_col, value_col, fill_value)` \| Constant (e.g. 0.0 for retail counts) \|
50	\| `ts_fill_forward_by(...)` \| Streaming per-group forward fill; also `ts_fill_forward_operator` for windowed variants \|
51	\| `ts_mark_unknown_by` \| Marks values matching a sentinel as unknown \|
52	\| `ts_fill_unknown_by` \| Fills previously-marked unknowns \|
53
54	```sql
55	-- Retail: zero-fill absent-day rows produced by ts_fill_gaps_by
56	CREATE TABLE nulls_filled AS
57	SELECT * FROM ts_fill_nulls_const_by('gaps_filled', product_id, ds, y, 0.0);
58	```
59
60	## Series filtering — drop degenerate cases
61
62	\| Function \| Drops when \|
63	\|---\|---\|
64	\| `ts_drop_constant_by` \| All values equal (no signal to forecast) \|
65	\| `ts_drop_short_by(source, group_col, min_length)` \| Series length < min \|
66	\| `ts_drop_gappy_by(source, group_col, value_col, max_gap_ratio)` \| Gap ratio exceeds threshold (0.1 = 10 %) \|
67	\| `ts_drop_zeros_by` \| All zero values \|
68	\| `ts_drop_leading_zeros_by` \| Trim leading zeros (product not yet launched) \|
69	\| `ts_drop_trailing_zeros_by` \| Trim trailing zeros (product discontinued) \|
70	\| `ts_drop_edge_zeros_by` \| Trim both leading and trailing zeros \|
71
72	```sql
73	CREATE TABLE clean AS
74	SELECT * FROM ts_drop_short_by('nulls_filled', product_id, 24); -- ≥ 2 years monthly
75	```
76
77	## Transforms
78
79	- `ts_diff_by(source, group_col, date_col, value_col, diff_order INTEGER)` — first / second differencing.
80	- `ts_detrend_by(source, group_col, date_col, value_col, method)` — remove linear or polynomial trend.
81	- `ts_validate_timestamps_by` — check for out-of-order / duplicate timestamps.
82
83	```sql
84	CREATE TABLE stationary AS
85	SELECT * FROM ts_diff_by('clean', product_id, ds, y, 1); -- first-difference
86	```
87
88	## Hierarchical key operations
89
90	Retail and IoT panels usually have composite keys (region × store × item). These macros manipulate them:
91
92	\| Function \| Purpose \|
93	\|---\|---\|
94	\| `ts_combine_keys` \| Concatenate multiple ID columns into one, `sep`-delimited \|
95	\| `ts_split_keys` \| Reverse — split back into components \|
96	\| `ts_aggregate_hierarchy` \| Sum-aggregate up a hierarchy level (region+store+item → region+store) \|
97	\| `ts_validate_separator` \| Verify separator string isn't present in the source columns \|
98
99	```sql
100	-- Combine two IDs for grouping
101	CREATE TABLE with_combined AS
102	SELECT * FROM ts_combine_keys('raw', region_id, store_id, ds, y, MAP{'sep': '\|'});
103
104	-- Aggregate to store level
105	CREATE TABLE store_level AS
106	SELECT * FROM ts_aggregate_hierarchy('raw', region_id, store_id, item_id, ds, y, 2);
107	```
108
109	## Canonical prep pipeline
110
111	```sql
112	-- 1. Regularise the time grid
113	CREATE OR REPLACE TABLE p1 AS
114	SELECT * FROM ts_fill_gaps_by('raw', product_id, ds, y, '1d'

People who liked this also viewed

6 picks

Type

anofox-forecast-data-prep

35stars3forks

View on GitHub

How to install anofox-forecast-data-prep?

datazoode/anofox-forecast/anofox-forecast-data-prep

$npx -y skills add DataZooDE/anofox-forecast --skill anofox-forecast-data-prep

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Anofox Forecast — Data Preparation Cheat Sheet
2
3	Extension: `anofox_forecast` v0.15.3 \| DuckDB: v1.4.5 LTS / v1.5.4+ \| Dual naming: `ts_` and `anofox_fcst_ts_` (identical)
4
5	Prep raw time series so downstream forecasting / CV has clean input: no gaps, no unwanted NULLs, no degenerate series, correct hierarchy.
6
7	## Critical gotcha — materialise between `_by` steps
8
9	`_by` table functions do not chain in CTEs — under parallel execution they silently return 0 rows. Always `CREATE TABLE` between pipeline steps.
10
11	```sql
12	-- BROKEN (silent 0 rows under parallelism):
13	WITH step1 AS (SELECT * FROM ts_fill_gaps_by('raw', id, ds, y, '1d', MAP{}))
14	SELECT * FROM ts_fill_nulls_const_by('step1', id, ds, y, 0.0);
15
16	-- CORRECT:
17	CREATE TABLE step1 AS SELECT * FROM ts_fill_gaps_by('raw', id, ds, y, '1d', MAP{});
18	CREATE TABLE step2 AS SELECT * FROM ts_fill_nulls_const_by('step1', id, ds, y, 0.0);
19	```
20
21	## Gap filling
22
23	### `ts_fill_gaps_by`
24	Insert missing date rows (NULL value) so every series has a complete regular grid.
25
26	```sql
27	ts_fill_gaps_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,
28	frequency VARCHAR) → TABLE
29	```
30
31	No params — pure frequency-driven grid completion. Inserted rows have NULL in `value_col`; use `ts_fill_nulls_*_by` next to impute.
32
33	Frequencies: `'1d'`, `'1h'`, `'30m'`, `'1w'`, `'1mo'`, `'1q'`, `'1y'` (Polars-style) or `'1 day'` (DuckDB INTERVAL) or raw int (days).
34
35	```sql
36	CREATE TABLE gaps_filled AS
37	SELECT * FROM ts_fill_gaps_by('raw', product_id, ds, y, '1d');
38	```
39
40	## NULL imputation
41
42	Table macros. Signatures:
43
44	\| Function \| Signature \|
45	\|---\|---\|
46	\| `ts_fill_nulls_forward_by(source, group_col, date_col, value_col)` \| Last observation carried forward \|
47	\| `ts_fill_nulls_backward_by(source, group_col, date_col, value_col)` \| Next observation carried backward \|
48	\| `ts_fill_nulls_mean_by(source, group_col, date_col, value_col)` \| Per-group mean (skips NULLs) \|
49	\| `ts_fill_nulls_const_by(source, group_col, date_col, value_col, fill_value)` \| Constant (e.g. 0.0 for retail counts) \|
50	\| `ts_fill_forward_by(...)` \| Streaming per-group forward fill; also `ts_fill_forward_operator` for windowed variants \|
51	\| `ts_mark_unknown_by` \| Marks values matching a sentinel as unknown \|
52	\| `ts_fill_unknown_by` \| Fills previously-marked unknowns \|
53
54	```sql
55	-- Retail: zero-fill absent-day rows produced by ts_fill_gaps_by
56	CREATE TABLE nulls_filled AS
57	SELECT * FROM ts_fill_nulls_const_by('gaps_filled', product_id, ds, y, 0.0);
58	```
59
60	## Series filtering — drop degenerate cases
61
62	\| Function \| Drops when \|
63	\|---\|---\|
64	\| `ts_drop_constant_by` \| All values equal (no signal to forecast) \|
65	\| `ts_drop_short_by(source, group_col, min_length)` \| Series length < min \|
66	\| `ts_drop_gappy_by(source, group_col, value_col, max_gap_ratio)` \| Gap ratio exceeds threshold (0.1 = 10 %) \|
67	\| `ts_drop_zeros_by` \| All zero values \|
68	\| `ts_drop_leading_zeros_by` \| Trim leading zeros (product not yet launched) \|
69	\| `ts_drop_trailing_zeros_by` \| Trim trailing zeros (product discontinued) \|
70	\| `ts_drop_edge_zeros_by` \| Trim both leading and trailing zeros \|
71
72	```sql
73	CREATE TABLE clean AS
74	SELECT * FROM ts_drop_short_by('nulls_filled', product_id, 24); -- ≥ 2 years monthly
75	```
76
77	## Transforms
78
79	- `ts_diff_by(source, group_col, date_col, value_col, diff_order INTEGER)` — first / second differencing.
80	- `ts_detrend_by(source, group_col, date_col, value_col, method)` — remove linear or polynomial trend.
81	- `ts_validate_timestamps_by` — check for out-of-order / duplicate timestamps.
82
83	```sql
84	CREATE TABLE stationary AS
85	SELECT * FROM ts_diff_by('clean', product_id, ds, y, 1); -- first-difference
86	```
87
88	## Hierarchical key operations
89
90	Retail and IoT panels usually have composite keys (region × store × item). These macros manipulate them:
91
92	\| Function \| Purpose \|
93	\|---\|---\|
94	\| `ts_combine_keys` \| Concatenate multiple ID columns into one, `sep`-delimited \|
95	\| `ts_split_keys` \| Reverse — split back into components \|
96	\| `ts_aggregate_hierarchy` \| Sum-aggregate up a hierarchy level (region+store+item → region+store) \|
97	\| `ts_validate_separator` \| Verify separator string isn't present in the source columns \|
98
99	```sql
100	-- Combine two IDs for grouping
101	CREATE TABLE with_combined AS
102	SELECT * FROM ts_combine_keys('raw', region_id, store_id, ds, y, MAP{'sep': '\|'});
103
104	-- Aggregate to store level
105	CREATE TABLE store_level AS
106	SELECT * FROM ts_aggregate_hierarchy('raw', region_id, store_id, item_id, ds, y, 2);
107	```
108
109	## Canonical prep pipeline
110
111	```sql
112	-- 1. Regularise the time grid
113	CREATE OR REPLACE TABLE p1 AS
114	SELECT * FROM ts_fill_gaps_by('raw', product_id, ds, y, '1d'

People who liked this also viewed

6 picks

Type