Repository: dathere/qsv (language: rust)

Issue:
`pivot`: new command to pivot tables
see if we can leverage polars to do this as well...


Discussion / hints:
Stale issue message
Stale issue message
Per https://github.com/jqnatividad/qsv/discussions/1493, let's get his implemented along with unpivot.
Revive this...
Try to do it while implementing #1655 
Requeued.  Enabled polars `pivot` feature...

Fix patch:
diff --git a/Cargo.lock b/Cargo.lock
index 0766a3e9d..51fd2d8ec 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -3231,9 +3231,9 @@ dependencies = [
 
 [[package]]
 name = "instability"
-version = "0.3.3"
+version = "0.3.5"
 source = "registry+https://github.com/rust-lang/crates.io-index"
-checksum = "b829f37dead9dc39df40c2d3376c179fdfd2ac771f53f55d3c30dc096a3c0c6e"
+checksum = "898e106451f7335950c9cc64f8ec67b5f65698679ac67ed00619aeef14e1cf75"
 dependencies = [
  "darling",
  "indoc",
@@ -3421,9 +3421,9 @@ dependencies = [
 
 [[package]]
 name = "kiddo"
-version = "5.0.2"
+version = "5.0.3"
 source = "registry+https://github.com/rust-lang/crates.io-index"
-checksum = "b5ddb5dd747fb7e5037d57e03e291632152a9d7be007935dde69f4bfc4721a3a"
+checksum = "c6e580022eee0bcd05b8c1c43d3512e79bfa001ccf88ef3dcfd1a3ac74b4e390"
 dependencies = [
  "aligned-vec",
  "array-init",
@@ -4222,9 +4222,9 @@ dependencies = [
 
 [[package]]
 name = "object"
-version = "0.36.5"
+version = "0.36.7"
 source = "registry+https://github.com/rust-lang/crates.io-index"
-checksum = "aedf0a2d09c573ed1d8d85b30c119153926a2b36dce0ab28322c09a117a4683e"
+checksum = "62948e14d923ea95ea2c7c86c71013138b66525b86bdc08d2dcc262bdb497b87"
 dependencies = [
  "memchr",
 ]
@@ -5369,6 +5369,7 @@ dependencies = [
  "parking_lot",
  "phf",
  "polars",
+ "polars-ops",
  "publicsuffix",
  "pyo3",
  "qsv-dateparser",
diff --git a/Cargo.toml b/Cargo.toml
index 7c709598a..6692352e8 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -177,6 +177,7 @@ polars = { version = "0.45", features = [
     "cross_join",
     "decompress-fast",
     # "dtype-full",
+    "dtype-struct",
     "extract_jsonpath",
     "ipc",
     "json",
@@ -184,13 +185,14 @@ polars = { version = "0.45", features = [
     "object",
     "parquet",
     "performant",
-    # "pivot",
+    "pivot",
     "semi_anti_join",
     "serde-lazy",
     "sql",
     "streaming",
     "timezones",
 ], optional = true }
+polars-ops = { version = "0.45", optional = true }
 publicsuffix = { version = "2.2", optional = true }
 pyo3 = { version = "0.23", features = ["auto-initialize"], optional = true }
 qsv-dateparser = "0.12"
@@ -336,6 +338,7 @@ strum_macros = { git = "https://github.com/dathere/strum", branch = "bump-phf-to
 # QSV_POLARS_REV=5f791b4
 # polars = { git = "https://github.com/pola-rs/polars", tag = "py-1.17.1" }
 polars = { git = "https://github.com/pola-rs/polars", rev = "5f791b4" }
+polars-ops = { git = "https://github.com/pola-rs/polars", rev = "5f791b4" }
 
 [features]
 default = ["mimalloc"]
@@ -388,7 +391,7 @@ geocode = [
     "sled",
 ]
 luau = ["mlua", "sanitize-filename"]
-polars = ["dep:polars", "bytemuck"]
+polars = ["dep:polars", "bytemuck", "dep:polars-ops"]
 prompt = ["rfd"]
 python = ["pyo3"]
 to = ["csvs_convert"]
diff --git a/README.md b/README.md
index c10b90061..0aa773ea0 100644
--- a/README.md
+++ b/README.md
@@ -63,6 +63,7 @@
 | [lens](/src/cmd/lens.rs#L2)✨ | Interactively view, search & filter a CSV using the [csvlens](https://github.com/YS-L/csvlens#csvlens) engine.
 | <a name="luau_deeplink"></a><br>[luau](/src/cmd/luau.rs#L2) 👑✨<br>📇🌐🔣📚 ![CKAN](docs/images/ckan.png) | Create multiple new computed columns, filter rows, compute aggregations and build complex data pipelines by executing a [Luau](https://luau-lang.org) [0.653](https://github.com/Roblox/luau/releases/tag/0.653) expression/script for every row of a CSV file ([sequential mode](https://github.com/dathere/qsv/blob/bb72c4ef369d192d85d8b7cc6e972c1b7df77635/tests/test_luau.rs#L254-L298)), or using [random access](https://www.webopedia.com/definitions/random-access/) with an index ([random access mode](https://github.com/dathere/qsv/blob/bb72c4ef369d192d85d8b7cc6e972c1b7df77635/tests/test_luau.rs#L367-L415)).<br>Can process a single Luau expression or [full-fledged data-wrangling scripts using lookup tables](https://github.com/dathere/qsv-lookup-tables#example) with discrete BEGIN, MAIN and END sections.<br> It is not just another qsv command, it is qsv's [Domain-specific Language](https://en.wikipedia.org/wiki/Domain-specific_language) (DSL) with [numerous qsv-specific helper functions](https://github.com/dathere/qsv/blob/113eee17b97882dc368b2e65fec52b86df09f78b/src/cmd/luau.rs#L1356-L2290) to build production data pipelines. |
 | [partition](/src/cmd/partition.rs#L2)<br>👆 | Partition a CSV based on a column value. |
+| [pivotp](/src/cmd/pivotp.rs#L2)✨<br>🚀🐻‍❄️🪄 | Pivot CSV data. |
 | [pro](/src/cmd/pro.rs#L2) | Interact with the [qsv pro](https://qsvpro.dathere.com) API. |
 | [prompt](/src/cmd/prompt.rs#L2)✨ | Open a file dialog to either pick a file as input or save output to a file. |
 | [pseudo](/src/cmd/pseudo.rs#L2)<br>🔣👆 | [Pseudonymise](https://en.wikipedia.org/wiki/Pseudonymization) the value of the given column by replacing them with an incremental identifier.  |
@@ -441,7 +442,7 @@ Luau will also serve as the backbone of a whole library of **qsv recipes** - reu
  as command line interfaces go :shrug:. Its commands have numerous options but have sensible defaults. The usage text is written for a data analyst audience, not developers; and there are numerous examples in the usage text, with the tests doubling as examples as well. With [qsv pro](https://qsvpro.dathere.com), it has much expanded functionality while being easier to use with its Graphical User Interface.
 * **As Secure as Possible** - qsv is designed to be secure. It has no external runtime dependencies, is [written](https://aws.amazon.com/blogs/opensource/why-aws-loves-rust-and-how-wed-like-to-help/) [in](https://msrc.microsoft.com/blog/2019/07/why-rust-for-safe-systems-programming/) [Rust](https://opensource.googleblog.com/2023/06/rust-fact-vs-fiction-5-insights-from-googles-rust-journey-2022.html), and it's codebase is automatically audited for security vulnerabilities with automated [DevSkim](https://github.com/microsoft/DevSkim#devskim), ["cargo audit"](https://rustsec.org) and [Codacy](https://app.codacy.com/gh/dathere/qsv/dashboard) Github Actions workflows.  
 It uses the latest stable Rust version, with an aggressive MSRV policy and the latest version of all its dependencies.
-It has an extensive test suite with ~1,650 tests, including several [property tests](https://medium.com/criteo-engineering/introduction-to-property-based-testing-f5236229d237) which [randomly generate](https://github.com/BurntSushi/quickcheck#quickcheck) parameters for oft-used commands.   
+It has an extensive test suite with ~1,670 tests, including several [property tests](https://medium.com/criteo-engineering/introduction-to-property-based-testing-f5236229d237) which [randomly generate](https://github.com/BurntSushi/quickcheck#quickcheck) parameters for oft-used commands.   
 Its prebuilt binary archives are [zipsigned](https://github.com/Kijewski/zipsign#zipsign), so you can [verify their integrity](#verifying-the-integrity-of-the-prebuilt-binaries-zip-archives). Its self-update mechanism automatically verifies the integrity of the prebuilt binaries archive before applying an update.
 See [Security](docs/SECURITY.md) for more info.
 * **As Easy to Contribute to as Possible** - qsv is designed to be easy to contribute to, with a focus on maintainability. It's modular architecture allows the easy addition of self-contained commands gated by feature flags, the source code is heavily commented, the usage text is embedded, and there are helper functions that make it easy to create additional commands and supporting tests. See [Features](docs/FEATURES.md) and [Contributing](CONTRIBUTING.md) for more info.
@@ -454,7 +455,7 @@ It can process well-formed CSVs in _any_ language so long as its UTF-8 encoded.
 Finally, though the default Geonames index of the `geocode` command is English-only, the index can be rebuilt with the `geocode index-update` subcommand with the `--languages` option to return place names in multiple languages ([with support for 254 languages](http://download.geonames.org/export/dump/alternatenames/)).
 
 ## Testing
-qsv has ~1,650 tests in the [tests](https://github.com/dathere/qsv/tree/master/tests) directory.
+qsv has ~1,670 tests in the [tests](https://github.com/dathere/qsv/tree/master/tests) directory.
 Each command has its own test suite in a separate file with the convention `test_<COMMAND>.rs`.
 Apart from preventing regressions, the tests also serve as good illustrative examples, and are often linked from the usage text of each corresponding command.
 
@@ -500,7 +501,7 @@ Dual-licensed under MIT or the [UNLICENSE](https://unlicense.org).
 
 ## Origins
 
-qsv is a fork of the popular [xsv](https://github.com/BurntSushi/xsv) utility, merging several pending PRs [since xsv 0.13.0's May 2018 release](https://github.com/BurntSushi/xsv/issues/267). On top of xsv's 20 commands, it adds numerous new features; 43 additional commands; 4 `apply` subcommands & 38 operations; 4 `to` subcommands; 3 `cat` subcommands; 7 `geocode` subcommands & 4 index operations; and 4 `snappy` subcommands.
+qsv is a fork of the popular [xsv](https://github.com/BurntSushi/xsv) utility, merging several pending PRs [since xsv 0.13.0's May 2018 release](https://github.com/BurntSushi/xsv/issues/267). On top of xsv's 20 commands, it adds numerous new features; 44 additional commands; 4 `apply` subcommands & 38 operations; 4 `to` subcommands; 3 `cat` subcommands; 7 `geocode` subcommands & 4 index operations; and 4 `snappy` subcommands.
 See [FAQ](https://github.com/dathere/qsv/discussions/categories/faq) for more details.
 
 ## Sponsor
diff --git a/docs/ENVIRONMENT_VARIABLES.md b/docs/ENVIRONMENT_VARIABLES.md
index 428465c0b..1f6a62b54 100644
--- a/docs/ENVIRONMENT_VARIABLES.md
+++ b/docs/ENVIRONMENT_VARIABLES.md
@@ -51,7 +51,7 @@ Several dependencies also have environment variables that influence qsv's perfor
   qsv uses reqwest and will honor [proxy settings](https://docs.rs/reqwest/latest/reqwest/index.html#proxies) set through the `HTTP_PROXY`, `HTTPS_PROXY`, `ALL_PROXY` & `NO_PROXY` environment variables.
 
 * Polars   
-  qsv uses [polars](https://github.com/pola.rs/polars) for several commands - currently `count`, `joinp` and `sqlp`. Polars has its own set of environment variables that can be set to influence its behavior (see [here](https://github.com/pola-rs/polars/blob/dd1fc86b65ae39b741f46edc6da01d024bed50b6/crates/polars/src/lib.rs#L366-L408)). The most relevant ones are:
+  qsv uses [polars](https://github.com/pola.rs/polars) for several commands - currently `count`, `joinp`, `pivotp` and `sqlp`. Polars has its own set of environment variables that can be set to influence its behavior (see [here](https://github.com/pola-rs/polars/blob/dd1fc86b65ae39b741f46edc6da01d024bed50b6/crates/polars/src/lib.rs#L366-L408)). The most relevant ones are:
 
   * `POLARS_VERBOSE` - if set to 1, polars will output logging messages to stderr.
   * `POLARS_PANIC_ON_ERR` - if set to 1, panics on polars-related errors, instead of returning an error.
diff --git a/docs/FEATURES.md b/docs/FEATURES.md
index b01b8b817..865d221f2 100644
--- a/docs/FEATURES.md
+++ b/docs/FEATURES.md
@@ -10,7 +10,7 @@
 * `geocode` - enable `geocode` command.
 * `lens` - enable `lens` command.
 * `luau` - enable `luau` command. Embeds a [Luau](https://luau-lang.org) interpreter into qsv. [Luau has type-checking, sandboxing, additional language operators, increased performance & other improvements](https://luau-lang.org/2022/11/04/luau-origins-and-evolution.html) over Lua. Luau is the DSL of qsv - as its statically linked, has a MUCH smaller footprint (in both file size and memory without having to deal with Python's infamous [Global Interpreter Lock](https://wiki.python.org/moin/GlobalInterpreterLock)) & is faster (in both startup & execution time) than Python.
-* `polars` - enables all [Polars](https://pola.rs)-powered commands (currently, `joinp` and `sqlp`. Also enables polars mode in `count`). Note that Polars is a very powerful library, but it has a lot of dependencies that drastically increases both compile time and binary size.
+* `polars` - enables all [Polars](https://pola.rs)-powered commands (currently, `joinp`, `pivotp` and `sqlp`. Also enables polars mode in `count`). Note that Polars is a very powerful library, but it has a lot of dependencies that drastically increases both compile time and binary size.
 * `prompt` - enable `prompt` command.
 * `python` - enable `py` command. Note that qsv will look for the shared library for the Python version (Python 3.8 & above supported) it was compiled against & will abort on startup if the library is not found, even if you're NOT using the `py` command. Check [Python](#python) section for more info. Though Luau is the preferred DSL for qsv for all the reasons stated above, Python is still the lingua franca of data wrangling.
 * `to` - enables the `to` command.
diff --git a/src/cmd/mod.rs b/src/cmd/mod.rs
index 31980c19c..6b1ea16ab 100644
--- a/src/cmd/mod.rs
+++ b/src/cmd/mod.rs
@@ -61,6 +61,11 @@ pub mod lens;
 pub mod luau;
 #[cfg(any(feature = "feature_capable", feature = "lite"))]
 pub mod partition;
+#[cfg(all(
+    feature = "polars",
+    any(feature = "feature_capable", feature = "datapusher_plus")
+))]
+pub mod pivotp;
 #[cfg(any(feature = "feature_capable", feature = "lite"))]
 pub mod pro;
 #[cfg(feature = "prompt")]
diff --git a/src/cmd/pivotp.rs b/src/cmd/pivotp.rs
new file mode 100644
index 000000000..ad65f8fbf
--- /dev/null
+++ b/src/cmd/pivotp.rs
@@ -0,0 +1,187 @@
+static USAGE: &str = r#"
+Pivots CSV data using the Polars engine.
+
+The pivot operation consists of:
+- One or more index columns (these will be the new rows)
+- A column that will be pivoted (this will create the new columns)
+- A values column that will be aggregated
+- An aggregation function to apply
+
+Usage:
+    qsv pivotp [options] <on-cols> <input>
+    qsv pivotp --help
+
+pivotp arguments:
+    <input>       is the input CSV file. The file must have headers.
+                  Stdin is not supported.
+    <on-cols>     The column(s) to pivot (creates new columns).
+
+
+pivotp options:
+    -i, --index <cols>      The column(s) to use as the index (row labels).
+                            Specify multiple columns by separating them with a comma.
+                            The output will have one row for each unique combination of the index’s values.
+                            If None, all remaining columns not specified on --on and --values will be used.
+                            At least one of --index and --values must be specified.
+    -v, --values <cols>     The column(s) containing values to aggregate.
+                            If an aggregation is specified, these are the values on which the aggregation
+                            will be computed. If None, all remaining columns not specified on --on and --index
+                            will be used. At least one of --index and --values must be specified.
+    -a, --agg <func>        The aggregation function to use:
+                              first - First value encountered
+                              sum - Sum of values
+                              min - Minimum value
+                              max - Maximum value
+                              mean - Average value
+                              median - Median value
+                              count - Count of values
+                              last - Last value encountered
+    --sort-columns          Sort the transposed columns by name. Default is by order of discovery.
+    --col-separator <arg>   The separator in generated column names in case of multiple --values columns.
+                            [default: _]
+    --try-parsedates        When set, will attempt to parse columns as dates.
+    --infer-len <arg>       Number of rows to scan when inferring schema.
+                            Set to 0 to scan entire file. [default: 10000]
+    --decimal-comma         Use comma as decimal separator.
+    --ignore-errors         Skip rows that can't be parsed.
+
+Common options:
+    -h, --help              Display this message
+    -o, --output <file>     Write output to <file> instead of stdout.
+    -d, --delimiter <arg>   The field delimiter for reading/writing CSV data.
+                            Must be a single character. (default: ,)
+"#;
+
+use std::{fs::File, io, io::Write, path::Path};
+
+use polars::prelude::*;
+use polars_ops::pivot::{pivot_stable, PivotAgg};
+use serde::Deserialize;
+
+use crate::{config::Delimiter, util, CliResult};
+
+#[derive(Deserialize)]
+struct Args {
+    arg_input:           String,
+    arg_on_cols:         String,
+    flag_index:          Option<String>,
+    flag_values:         Option<String>,
+    flag_agg:            Option<String>,
+    flag_sort_columns:   bool,
+    flag_col_separator:  String,
+    flag_try_parsedates: bool,
+    flag_infer_len:      usize,
+    flag_decimal_comma:  bool,
+    flag_ignore_errors:  bool,
+    flag_output:         Option<String>,
+    flag_delimiter:      Option<Delimiter>,
+}
+
+pub fn run(argv: &[&str]) -> CliResult<()> {
+    let args: Args = util::get_args(USAGE, argv)?;
+
+    // Parse on column(s)
+    let on_cols: Vec<String> = args
+        .arg_on_cols
+        .as_str()
+        .split(',')
+        .map(std::string::ToString::to_string)
+        .collect();
+
+    // Parse index column(s)
+    let index_cols = if let Some(flag_index) = args.flag_index {
+        let idx_cols: Vec<String> = flag_index
+            .as_str()
+            .split(',')
+            .map(std::string::ToString::to_string)
+            .collect();
+        Some(idx_cols)
+    } else {
+        None
+    };
+
+    // Parse values column(s)
+    let value_cols = if let Some(flag_values) = args.flag_values {
+        let val_cols: Vec<String> = flag_values
+            .as_str()
+            .split(',')
+            .map(std::string::ToString::to_string)
+            .collect();
+        Some(val_cols)
+    } else {
+        None
+    };
+
+    if index_cols.is_none() && value_cols.is_none() {
+        return fail_incorrectusage_clierror!(
+            "Either --index <cols> or --values <cols> must be specified."
+        );
+    }
+
+    // Get aggregation function
+    let agg_fn = if let Some(agg) = args.flag_agg {
+        Some(match agg.to_lowercase().as_str() {
+            "first" => PivotAgg::First,
+            "sum" => PivotAgg::Sum,
+            "min" => PivotAgg::Min,
+            "max" => PivotAgg::Max,
+            "mean" => PivotAgg::Mean,
+            "median" => PivotAgg::Median,
+            "count" => PivotAgg::Count,
+            "last" => PivotAgg::Last,
+            _ => return fail_clierror!("Invalid pivot aggregation function: {agg}"),
+        })
+    } else {
+        None
+    };
+
+    // Set delimiter if specified
+    let delim = if let Some(delimiter) = args.flag_delimiter {
+        delimiter.as_byte()
+    } else {
+        b','
+    };
+
+    // Create CSV reader config
+    let csv_reader = LazyCsvReader::new(&args.arg_input)
+        .with_has_header(true)
+        .with_try_parse_dates(args.flag_try_parsedates)
+        .with_decimal_comma(args.flag_decimal_comma)
+        .with_separator(delim)
+        .with_ignore_errors(args.flag_ignore_errors)
+        .with_infer_schema_length(Some(args.flag_infer_len));
+
+    // Read the CSV into a DataFrame
+    let df = csv_reader.finish()?.collect()?;
+
+    // Perform pivot operation
+    let mut pivot_result = pivot_stable(
+        &df,
+        on_cols,
+        index_cols,
+        value_cols,
+        args.flag_sort_columns,
+        agg_fn,
+        Some(&args.flag_col_separator),
+    )?;
+
+    // Write output
+    let mut writer = match args.flag_output {
+        Some(ref output_file) => {
+            // no need to use buffered writer here, as CsvWriter already does that
+            let path = Path::new(&output_file);
+            Box::new(File::create(path).unwrap()) as Box<dyn Write>
+        },
+        None => Box::new(io::stdout()) as Box<dyn Write>,
+    };
+    CsvWriter::new(&mut writer)
+        .include_header(true)
+        .with_datetime_format(Some("%Y-%m-%d %H:%M:%S".to_string()))
+        .with_separator(delim)
+        .finish(&mut pivot_result)?;
+
+    // Print shape to stderr
+    eprintln!("{:?}", pivot_result.shape());
+
+    Ok(())
+}
diff --git a/src/main.rs b/src/main.rs
index bdd38f8b4..3d86d4f53 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -161,10 +161,12 @@ fn main() -> QsvExitCode {
     #[cfg(all(feature = "luau", feature = "feature_capable"))]
     enabled_commands.push_str("    luau        Execute Luau script on CSV data\n");
 
-    enabled_commands.push_str(
-        "    partition   Partition CSV data based on a column value
-    pro         Interact with the qsv pro API\n",
-    );
+    enabled_commands.push_str("    partition   Partition CSV data based on a column value\n");
+
+    #[cfg(all(feature = "polars", feature = "feature_capable"))]
+    enabled_commands.push_str("    pivotp      Pivots CSV files using the Pola.rs engine\n");
+
+    enabled_commands.push_str("    pro         Interact with the qsv pro API\n");
 
     #[cfg(all(feature = "prompt", feature = "feature_capable"))]
     enabled_commands.push_str("    prompt      Open a file dialog to pick a file\n");
@@ -379,6 +381,8 @@ enum Command {
     #[cfg(all(feature = "luau", feature = "feature_capable"))]
     Luau,
     Partition,
+    #[cfg(all(feature = "polars", feature = "feature_capable"))]
+    PivotP,
     Pro,
     #[cfg(all(feature = "prompt", feature = "feature_capable"))]
     Prompt,
@@ -477,6 +481,8 @@ impl Command {
             #[cfg(all(feature = "luau", feature = "feature_capable"))]
             Command::Luau => cmd::luau::run(argv),
             Command::Partition => cmd::partition::run(argv),
+            #[cfg(all(feature = "polars", feature = "feature_capable"))]
+            Command::PivotP => cmd::pivotp::run(argv),
             Command::Pro => cmd::pro::run(argv),
             #[cfg(all(feature = "prompt", feature = "feature_capable"))]
             Command::Prompt => cmd::prompt::run(argv),
diff --git a/src/maindp.rs b/src/maindp.rs
index 51dd5908e..ea7113b58 100644
--- a/src/maindp.rs
+++ b/src/maindp.rs
@@ -68,6 +68,7 @@ static COMMAND_LIST: &str = r#"
     input       Read CSVs w/ special quoting, skipping, trimming & transcoding rules
     joinp       Join CSV files using the Pola.rs engine
     luau        Execute Luau script on CSV data
+    pivotp      Pivot CSV data
     pseudo      Pseudonymise the values of a column
     rename      Rename the columns of CSV data efficiently
     replace     Replace patterns in CSV data
@@ -257,6 +258,8 @@ enum Command {
     JoinP,
     #[cfg(feature = "luau")]
     Luau,
+    #[cfg(feature = "polars")]
+    PivotP,
     Pseudo,
     Rename,
     Replace,
@@ -314,6 +317,8 @@ impl Command {
             Command::JoinP => cmd::joinp::run(argv),
             #[cfg(feature = "luau")]
             Command::Luau => cmd::luau::run(argv),
+            #[cfg(feature = "polars")]
+            Command::PivotP => cmd::pivotp::run(argv),
             Command::Pseudo => cmd::pseudo::run(argv),
             Command::Rename => cmd::rename::run(argv),
             Command::Replace => cmd::replace::run(argv),


Test patch:
diff --git a/tests/test_pivotp.rs b/tests/test_pivotp.rs
new file mode 100644
index 000000000..3e8aa6835
--- /dev/null
+++ b/tests/test_pivotp.rs
@@ -0,0 +1,493 @@
+use crate::workdir::Workdir;
+
+macro_rules! pivotp_test {
+    ($name:ident, $fun:expr) => {
+        mod $name {
+            use std::process;
+
+            #[allow(unused_imports)]
+            use super::setup;
+            use crate::workdir::Workdir;
+
+            #[test]
+            fn testpivotp() {
+                let wrk = setup(stringify!($name));
+                let cmd = wrk.command("pivotp");
+                $fun(wrk, cmd);
+            }
+        }
+    };
+}
+
+fn setup(name: &str) -> Workdir {
+    // Sample data for testing pivot operations
+    let sales = vec![
+        svec!["date", "product", "region", "sales"],
+        svec!["2023-01-01", "A", "North", "100"],
+        svec!["2023-01-01", "B", "North", "150"],
+        svec!["2023-01-01", "A", "South", "200"],
+        svec!["2023-01-02", "B", "South", "250"],
+        svec!["2023-01-02", "A", "North", "300"],
+        svec!["2023-01-02", "B", "North", "350"],
+    ];
+
+    let wrk = Workdir::new(name);
+    wrk.create("sales.csv", sales);
+    wrk
+}
+
+// Test basic pivot with single index
+pivotp_test!(pivotp_basic, |wrk: Workdir, mut cmd: process::Command| {
+    cmd.args(&[
+        "product",
+        "--index",
+        "date",
+        "--values",
+        "sales",
+        "--agg",
+        "first",
+        "sales.csv",
+    ]);
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["date", "A", "B"],
+        svec!["2023-01-01", "100", "150"],
+        svec!["2023-01-02", "300", "250"],
+    ];
+    assert_eq!(got, expected);
+});
+
+// Test pivot with multiple index columns
+pivotp_test!(
+    pivotp_multi_index,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "date,region",
+            "--values",
+            "sales",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["date", "region", "A", "B"],
+            svec!["2023-01-01", "North", "100", "150"],
+            svec!["2023-01-01", "South", "200", ""],
+            svec!["2023-01-02", "South", "", "250"],
+            svec!["2023-01-02", "North", "300", "350"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with sum aggregation
+pivotp_test!(pivotp_sum_agg, |wrk: Workdir, mut cmd: process::Command| {
+    cmd.args(&[
+        "product",
+        "--index",
+        "region",
+        "--values",
+        "sales",
+        "--agg",
+        "sum",
+        "sales.csv",
+    ]);
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["region", "A", "B"],
+        svec!["North", "400", "500"],
+        svec!["South", "200", "250"],
+    ];
+    assert_eq!(got, expected);
+});
+
+// Test pivot with mean aggregation
+pivotp_test!(
+    pivotp_mean_agg,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "region",
+            "--values",
+            "sales",
+            "--agg",
+            "mean",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["region", "A", "B"],
+            svec!["North", "200.0", "250.0"],
+            svec!["South", "200.0", "250.0"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with min aggregation
+pivotp_test!(pivotp_min_agg, |wrk: Workdir, mut cmd: process::Command| {
+    cmd.args(&[
+        "product",
+        "--index",
+        "region",
+        "--values",
+        "sales",
+        "--agg",
+        "min",
+        "sales.csv",
+    ]);
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["region", "A", "B"],
+        svec!["North", "100", "150"],
+        svec!["South", "200", "250"],
+    ];
+    assert_eq!(got, expected);
+});
+
+// Test pivot with max aggregation
+pivotp_test!(pivotp_max_agg, |wrk: Workdir, mut cmd: process::Command| {
+    cmd.args(&[
+        "product",
+        "--index",
+        "region",
+        "--values",
+        "sales",
+        "--agg",
+        "max",
+        "sales.csv",
+    ]);
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["region", "A", "B"],
+        svec!["North", "300", "350"],
+        svec!["South", "200", "250"],
+    ];
+    assert_eq!(got, expected);
+});
+
+// Test pivot with median aggregation
+pivotp_test!(
+    pivotp_median_agg,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "region",
+            "--values",
+            "sales",
+            "--agg",
+            "median",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["region", "A", "B"],
+            svec!["North", "200.0", "250.0"],
+            svec!["South", "200.0", "250.0"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with count aggregation
+pivotp_test!(
+    pivotp_count_agg,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "region",
+            "--values",
+            "sales",
+            "--agg",
+            "count",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["region", "A", "B"],
+            svec!["North", "2", "2"],
+            svec!["South", "1", "1"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with last aggregation
+pivotp_test!(
+    pivotp_last_agg,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "region",
+            "--values",
+            "sales",
+            "--agg",
+            "last",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["region", "A", "B"],
+            svec!["North", "300", "350"],
+            svec!["South", "200", "250"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with sorted columns
+pivotp_test!(
+    pivotp_sort_columns,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "date",
+            "--values",
+            "sales",
+            "--sort-columns",
+            "--agg",
+            "first",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["date", "A", "B"], // Columns will be sorted alphabetically
+            svec!["2023-01-01", "100", "150"],
+            svec!["2023-01-02", "300", "250"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with custom column separator
+pivotp_test!(
+    pivotp_col_separator,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product",
+            "--index",
+            "date",
+            "--values",
+            "sales",
+            "--col-separator",
+            "::",
+            "--agg",
+            "first",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["date", "A", "B"],
+            svec!["2023-01-01", "100", "150"],
+            svec!["2023-01-02", "300", "250"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with custom delimiter
+pivotp_test!(
+    pivotp_delimiter,
+    |wrk: Workdir, mut cmd: process::Command| {
+        // Create data with semicolon delimiter
+        let sales = vec![
+            svec!["date;product;region;sales"],
+            svec!["2023-01-01;A;North;100"],
+            svec!["2023-01-01;B;North;150"],
+        ];
+        wrk.create("sales_semicolon.csv", sales);
+
+        cmd.args(&[
+            "product",
+            "--index",
+            "date",
+            "--values",
+            "sales",
+            "--delimiter",
+            ";",
+            "sales_semicolon.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![svec!["date;A;B"], svec!["2023-01-01;100;150"]];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with no explicit index (uses remaining columns)
+pivotp_test!(
+    pivotp_no_index,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&["product", "--values", "sales", "--agg", "sum", "sales.csv"]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["date", "region", "A", "B"],
+            svec!["2023-01-01", "North", "100", "150"],
+            svec!["2023-01-01", "South", "200", ""],
+            svec!["2023-01-02", "South", "", "250"],
+            svec!["2023-01-02", "North", "300", "350"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with multiple on-cols
+pivotp_test!(
+    pivotp_multi_on_cols,
+    |wrk: Workdir, mut cmd: process::Command| {
+        cmd.args(&[
+            "product,region", // Multiple on-cols
+            "--index",
+            "date",
+            "--values",
+            "sales",
+            "--agg",
+            "sum",
+            "sales.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec![
+                "date",
+                "{\"A\",\"North\"}",
+                "{\"B\",\"North\"}",
+                "{\"A\",\"South\"}",
+                "{\"B\",\"South\"}"
+            ],
+            svec!["2023-01-01", "100", "150", "200", ""],
+            svec!["2023-01-02", "300", "350", "", "250"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+// Test pivot with multiple value columns
+pivotp_test!(
+    pivotp_multi_values,
+    |wrk: Workdir, mut cmd: process::Command| {
+        // Create test data with multiple value columns
+        let sales_multi = vec![
+            svec!["date", "product", "region", "sales", "quantity"],
+            svec!["2023-01-01", "A", "North", "100", "10"],
+            svec!["2023-01-01", "B", "North", "150", "15"],
+            svec!["2023-01-01", "A", "South", "200", "20"],
+            svec!["2023-01-02", "B", "South", "250", "25"],
+            svec!["2023-01-02", "A", "North", "300", "30"],
+            svec!["2023-01-02", "B", "North", "350", "35"],
+        ];
+        wrk.create("sales_multi.csv", sales_multi);
+
+        cmd.args(&[
+            "product",
+            "--index",
+            "date",
+            "--values",
+            "sales,quantity", // Multiple value columns
+            "--agg",
+            "sum",
+            "sales_multi.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec!["date", "sales_A", "sales_B", "quantity_A", "quantity_B"],
+            svec!["2023-01-01", "300", "150", "30", "15"],
+            svec!["2023-01-02", "300", "600", "30", "60"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
+
+pivotp_test!(
+    pivotp_multi_values_custom_col_separator,
+    |wrk: Workdir, mut cmd: process::Command| {
+        let sales_multi = vec![
+            svec!["date", "product", "region", "sales", "quantity"],
+            svec!["2023-01-01", "A", "North", "100", "10"],
+            svec!["2023-01-01", "B", "North", "150", "15"],
+            svec!["2023-01-01", "A", "South", "200", "20"],
+            svec!["2023-01-02", "B", "South", "250", "25"],
+            svec!["2023-01-02", "A", "North", "300", "30"],
+            svec!["2023-01-02", "B", "North", "350", "35"],
+        ];
+        wrk.create("sales_multi.csv", sales_multi);
+
+        cmd.args(&[
+            "product",
+            "--index",
+            "date",
+            "--values",
+            "sales,quantity",
+            "--agg",
+            "sum",
+            "--col-separator",
+            "<->",
+            "sales_multi.csv",
+        ]);
+
+        wrk.assert_success(&mut cmd);
+
+        let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+        let expected = vec![
+            svec![
+                "date",
+                "sales<->A",
+                "sales<->B",
+                "quantity<->A",
+                "quantity<->B"
+            ],
+            svec!["2023-01-01", "300", "150", "30", "15"],
+            svec!["2023-01-02", "300", "600", "30", "60"],
+        ];
+        assert_eq!(got, expected);
+    }
+);
diff --git a/tests/tests.rs b/tests/tests.rs
index 57480eb69..522bfc3f0 100644
--- a/tests/tests.rs
+++ b/tests/tests.rs
@@ -93,6 +93,8 @@ mod test_jsonl;
 mod test_luau;
 #[cfg(any(feature = "feature_capable", feature = "lite"))]
 mod test_partition;
+#[cfg(feature = "polars")]
+mod test_pivotp;
 #[cfg(feature = "prompt")]
 mod test_prompt;
 mod test_pseudo;