Different backend support with Ibis 🪽¶
icanexplain is implemented with Ibis. This means that it is framework agnostic, and can work with different backends. This example shows how to use it with DuckDB.
import ibis
import icanexplain as ice
products_df = ice.datasets.load_product_footprints()
con = ibis.connect("duckdb://example.ddb")
con.create_table(
"products", products_df, overwrite=True
)
DatabaseTable: example.main.products year int64 category string product_id string footprint float64 units int64
['products']
┏━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━┓ ┃ year ┃ category ┃ product_id ┃ footprint ┃ units ┃ ┡━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━┩ │ int64 │ string │ string │ float64 │ int64 │ ├───────┼──────────┼────────────┼───────────┼───────┤ │ 2021 │ DRESS │ 848be709 │ 96.04 │ 803 │ │ 2021 │ DRESS │ 658f92b3 │ 58.15 │ 3367 │ │ 2021 │ DRESS │ 3a26f323 │ 82.94 │ 240 │ │ 2021 │ DRESS │ 6221dca6 │ 85.94 │ 432 │ │ 2021 │ DRESS │ 46864ac5 │ 84.99 │ 816 │ └───────┴──────────┴────────────┴───────────┴───────┘
explainer = ice.SumExplainer(
fact='footprint',
count='units',
group='category',
period='year'
)
explanation = explainer(products)
explanation
┏━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓ ┃ year ┃ category ┃ inner ┃ mix ┃ ┡━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩ │ int64 │ string │ float64 │ float64 │ ├───────┼──────────┼───────────────┼───────────────┤ │ 2022 │ DRESS │ 3.931932e+06 │ -1.881370e+07 │ │ 2022 │ JACKET │ -1.510008e+07 │ -9.238617e+07 │ │ 2022 │ PANTS │ 4.002506e+07 │ 5.295190e+07 │ │ 2022 │ SHIRT │ -1.484809e+06 │ -5.791456e+06 │ │ 2022 │ SWEATER │ -2.676209e+07 │ 1.181504e+07 │ │ 2022 │ TSHIRT │ 6.650940e+06 │ -2.311836e+07 │ │ 2023 │ DRESS │ -4.078094e+06 │ -1.240339e+07 │ │ 2023 │ JACKET │ -6.793317e+06 │ -4.924036e+07 │ │ 2023 │ PANTS │ -1.636299e+07 │ -2.295608e+08 │ │ 2023 │ SHIRT │ 8.920908e+05 │ -4.019144e+06 │ │ … │ … │ … │ … │ └───────┴──────────┴───────────────┴───────────────┘
ibis.expr.types.relations.Table
| year | category | inner | mix | |
|---|---|---|---|---|
| 0 | 2022 | DRESS | 3931932.447552 | -18813695.617552 |
| 1 | 2022 | JACKET | -15100081.026506 | -92386166.203493 |
| 2 | 2022 | PANTS | 40025058.515251 | 52951900.074743 |
| 3 | 2022 | SHIRT | -1484809.008950 | -5791455.871050 |
| 4 | 2022 | SWEATER | -26762091.196715 | 11815043.806715 |
| 5 | 2022 | TSHIRT | 6650940.137541 | -23118359.127541 |
| 6 | 2023 | DRESS | -4078094.357618 | -12403387.412381 |
| 7 | 2023 | JACKET | -6793316.568952 | -49240364.221049 |
| 8 | 2023 | PANTS | -16362993.150075 | -229560842.809919 |
| 9 | 2023 | SHIRT | 892090.812023 | -4019143.772023 |
| 10 | 2023 | SWEATER | -5701391.450394 | -113050731.029606 |
| 11 | 2023 | TSHIRT | -11503906.840478 | -83913226.849522 |
SELECT
*
FROM (
SELECT
"t9"."year",
"t9"."category",
"t9"."count_lag" * (
"t9"."mean" - "t9"."mean_lag"
) AS "inner",
(
"t9"."count" - "t9"."count_lag"
) * "t9"."mean" AS "mix"
FROM (
SELECT
"t8"."category",
"t8"."year",
"t8"."mean",
"t8"."count",
LAG("t8"."mean", 1) OVER (PARTITION BY "t8"."category" ORDER BY "t8"."year" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "mean_lag",
LAG("t8"."count", 1) OVER (PARTITION BY "t8"."category" ORDER BY "t8"."year" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "count_lag"
FROM (
SELECT
"t7"."category",
"t7"."year",
COALESCE("t7"."mean", 0) AS "mean",
COALESCE("t7"."count", 0) AS "count"
FROM (
SELECT
"t3"."category",
"t4"."year",
"t6"."mean",
"t6"."count"
FROM (
SELECT DISTINCT
"t0"."category"
FROM "products" AS "t0"
) AS "t3"
CROSS JOIN (
SELECT DISTINCT
"t0"."year"
FROM "products" AS "t0"
) AS "t4"
LEFT OUTER JOIN (
SELECT
"t0"."category",
"t0"."year",
SUM("t0"."footprint" * "t0"."units") / SUM("t0"."units") AS "mean",
SUM("t0"."units") AS "count"
FROM "products" AS "t0"
GROUP BY
1,
2
) AS "t6"
ON "t3"."category" = "t6"."category" AND "t4"."year" = "t6"."year"
) AS "t7"
) AS "t8"
) AS "t9"
ORDER BY
"t9"."year" ASC,
"t9"."category" ASC
) AS "t10"
WHERE
"t10"."year" IS NOT NULL
AND "t10"."category" IS NOT NULL
AND "t10"."inner" IS NOT NULL
AND "t10"."mix" IS NOT NULL