| #!/usr/bin/env nu | |
| # PR 2026-07-15 11:23:32 | |
| # https://mavenanalytics.io/data-drills/cart-combos | |
| #Your dataset contains one year of point-of-sale transactions | | | # from a grocery store, including approximately 88,000 line items | | | # across 20,000 customer orders. | | | # | | | #Your task is to identify the five product pairs that are purchased | | | # together most often. | | | # | | | #Two products are "purchased together" if they appear in the same | | | # transaction | | | # | | | #A pair must consist of two different products | | | # | | | #Product pairs are unordered, meaning (A, B) and (B, A) should | | | # be treated as the same pair | | | # | | | #Count each pair once per transaction, regardless of the quantity | | | # purchased | | | # ********************************************************************** | | | # code from gemini (36 seconds) | | | # Load the transactions and select only the relevant columns | | | open grocery_transactions.csv | | | | select transaction_id product_name | | | # Remove duplicate products within the same transaction to count them once per transaction | | | | uniq | | | # Group the dataset by transaction ID | | | | group-by transaction_id | | | | values | | | # For each transaction, generate all unique product pairs | | | | each { |tx| | | | let products = ($tx.product_name | sort) | | | $products | enumerate | each { |item| | | | let idx = $item.index | | | let p1 = $item.item | |
| $products | skip ($idx + 1) | each { |p2| | |
| { product_a: $p1, product_b: $p2 } | |
| } | | | } | flatten | | | } | | | | flatten | | | # Count the occurrences of each unique pair | | | | uniq --count | | | # Sort by count descending and take the top 5 | | | | sort-by --reverse count | | | | first 5 | | | ╭───┬────────────────────────────────┬───────╮ | | | │ # │ value │ count │ | | | ├───┼────────────────────────────────┼───────┤ | | | │ 0 │ ╭───────────┬────────────────╮ │ 5358 │ | | | │ │ │ product_a │ Salsa │ │ │ | | | │ │ │ product_b │ Tortilla Chips │ │ │ | | | │ │ ╰───────────┴────────────────╯ │ │ | | | │ 1 │ ╭───────────┬─────────╮ │ 4483 │ | | | │ │ │ product_a │ Bananas │ │ │ | | | │ │ │ product_b │ Milk │ │ │ | | | │ │ ╰───────────┴─────────╯ │ │ | | | │ 2 │ ╭───────────┬─────────╮ │ 2970 │ | | | │ │ │ product_a │ Bananas │ │ │ | | | │ │ │ product_b │ Bread │ │ │ | | | │ │ ╰───────────┴─────────╯ │ │ | | | │ 3 │ ╭───────────┬─────────╮ │ 2874 │ | | | │ │ │ product_a │ Bananas │ │ │ | | | │ │ │ product_b │ Salsa │ │ │ | | | │ │ ╰───────────┴─────────╯ │ │ | | | │ 4 │ ╭───────────┬────────────────╮ │ 2857 │ | | | │ │ │ product_a │ Bananas │ │ │ | | | │ │ │ product_b │ Tortilla Chips │ │ │ | | | │ │ ╰───────────┴────────────────╯ │ │ | | | ╰───┴────────────────────────────────┴───────╯ | | | #********************************************************************** | | | # code from lumo.ai (30 seconds) | | | # Top 5 product pairs purchased together most often | | | open grocery_transactions.csv | | | | select transaction_id product_name | | | | group-by transaction_id | | | | items { |tid, rows| | |
| let products = ($rows.product_name | uniq | sort) | |
| let n = ($products | length) | |
| if $n < 2 { [] } else { | |
| seq 0 ($n - 2) | |
| | each { |i| | | | seq ($i + 1) ($n - 1) | | | | each { |j| | | | $"($products | get $i) × ($products | get $j)" | | | } | | | } | | | | flatten | | | } | | | } | | | | flatten | |
| | uniq --count | |
| | sort-by count --reverse | |
| | first 5 | | | ╭───┬──────────────────────────┬───────╮ | | | │ # │ value │ count │ | | | ├───┼──────────────────────────┼───────┤ | | | │ 0 │ Salsa × Tortilla Chips │ 5358 │ | | | │ 1 │ Bananas × Milk │ 4483 │ | | | │ 2 │ Bananas × Bread │ 2970 │ | | | │ 3 │ Bananas × Salsa │ 2874 │ | | | │ 4 │ Bananas × Tortilla Chips │ 2857 │ | | | ╰───┴──────────────────────────┴───────╯ | | | #********************************************************************** | | | # nushell with polars (0.5 seconds) | | | # from gemini | | | # 1. Load the CSV file as a Polars LazyFrame and select relevant columns | | | let df = (polars open grocery_transactions.csv | | | | polars select [transaction_id product_name] | | | # Remove duplicate products within the same transaction | | | | polars unique --subset [transaction_id product_name] | | | ) | | | # 2. Prepare a clone of the DataFrame for the self-join | | | let df_right = ($df | | | | polars rename product_name product_b | | | ) | | | # Rename the original DataFrame's product column for matching | | | let df_left = ($df | | | | polars rename product_name product_a | | | ) | | | # 3. Perform the self-join, filter, group, count, sort, and fetch top 5 | | | $df_left | | | | polars join $df_right [transaction_id] [transaction_id] | | | # Keep only pairs where product_a comes alphabetically before product_b | | | | polars filter-with ((polars col product_a) < (polars col product_b)) | | | # Group by the distinct product pairs | | | | polars group-by [product_a product_b] | | | # Count occurrences of each pair | | | | polars agg [ | | | ((polars col transaction_id | polars count) | polars as count) | | | ] | |
| # Sort by count descending (Passing '[true]' to '--reverse') | |
| | polars sort-by count --reverse [true] | |
| # Fetch the top 5 pairs | | | | polars first 5 | | | # Convert back to a standard Nushell table to view | | | | polars collect | | | | polars into-nu | | | ╭───┬───────────┬────────────────┬───────╮ | | | │ # │ product_a │ product_b │ count │ | | | ├───┼───────────┼────────────────┼───────┤ | | | │ 0 │ Salsa │ Tortilla Chips │ 5358 │ | | | │ 1 │ Bananas │ Milk │ 4483 │ | | | │ 2 │ Bananas │ Bread │ 2970 │ | | | │ 3 │ Bananas │ Salsa │ 2874 │ | | | │ 4 │ Bananas │ Tortilla Chips │ 2857 │ | | | ╰───┴───────────┴────────────────┴───────╯ | | | #********************************************************************** | | | # nushell with polars (0.5 seconds) | | | # from luno.ai | | | # Load, select relevant columns, deduplicate (transaction_id, product_name) pairs | | | let df = ( | | | polars open grocery_transactions.csv | | | | polars select transaction_id product_name | | | | polars drop-duplicates | | | ) | | | # Create left/right copies with renamed product columns for self-join | | | let df_left = ($df | polars rename product_name prod_a) | | | let df_right = ($df | polars rename product_name prod_b) | | | # Self-join on transaction_id to generate all product combinations per transaction | | | $df_left | | | | polars join $df_right transaction_id transaction_id | | | # Keep only unique unordered pairs (prod_a < prod_b eliminates self-pairs and dupes) | | | | polars filter ((polars col prod_a) < (polars col prod_b)) | | | # Count how many transactions contain each pair | | | | polars group-by prod_a prod_b | | | | polars agg [(polars len | polars as "count")] | | | | polars collect | | | # Sort by count descending and take top 5 | | | | polars sort-by count -r [true] | | | | polars first 5 | | | ╭───┬─────────┬────────────────┬───────╮ | | | │ # │ prod_a │ prod_b │ count │ | | | ├───┼─────────┼────────────────┼───────┤ | | | │ 0 │ Salsa │ Tortilla Chips │ 5358 │ | | | │ 1 │ Bananas │ Milk │ 4483 │ | | | │ 2 │ Bananas │ Bread │ 2970 │ | | | │ 3 │ Bananas │ Salsa │ 2874 │ | | | │ 4 │ Bananas │ Tortilla Chips │ 2857 │ | | | ╰───┴─────────┴────────────────┴───────╯ | | | #********************************************************************** | | | # xan code (with nushell) (0.5 seconds) | | | # from lumo.ai | | | # Top 5 product pairs using Xan (final corrected version) | | | try {rm temp.csv} | | | # Step 1: Select and deduplicate | | | xan select transaction_id,product_name grocery_transactions.csv | | | | xan dedup -s transaction_id,product_name | | | | save temp.csv | | | # Step 2: Self-join, filter to unique unordered pairs, create pair column | | | xan join -r right_ -D left transaction_id temp.csv temp.csv | | | | xan filter "lt(product_name, right_product_name)" | | | | xan map "concat(product_name, ' & ', right_product_name) as pair" | | | # Step 3: Frequency already returns results sorted by count descending | | | # frequency -s pair -l 5 gives us the top 5 pairs by count | | | | xan frequency -s pair -l 5 | | | # Step 4: Convert to Nushell table | | | | from csv | | | ## Cleanup | | | #rm temp.csv | | | ╭───┬───────┬──────────────────────────┬────────╮ | | | │ # │ field │ value │ count │ | | | ├───┼───────┼──────────────────────────┼────────┤ | | | │ 0 │ pair │ Salsa & Tortilla Chips │ 5358 │ | | | │ 1 │ pair │ Bananas & Milk │ 4483 │ | | | │ 2 │ pair │ Bananas & Bread │ 2970 │ | | | │ 3 │ pair │ Bananas & Salsa │ 2874 │ | | | │ 4 │ pair │ Bananas & Tortilla Chips │ 2857 │ | | | │ 5 │ pair │ <rest> │ 156773 │ | | | ╰───┴───────┴──────────────────────────┴────────╯ | |