Repository: dathere/qsv (language: rust)

Issue:
`joinp` keys --ignore-leading_zeros comparison suggestion
### Discussed in https://github.com/dathere/qsv/discussions/2397

<div type='discussions-op-text'>

<sup>Originally posted by **datatraveller1** January  1, 2025</sup>
Similar to the `--ignore-case` keys string comparison option, I would quite often need an `--ignore-leading_zeros` option for comparing keys with numbers for a join. 
With the help of this `--ignore-leading_zeros`option, e.g. `01 and 1` (or `000 and 0` or `0072 and 72`) could be matched as the same key for the join.
@jqnatividad Do you think this can be added to join and joinp? (joinp would be more important for me)

Maybe this topic is a bit related to https://github.com/dathere/qsv/discussions/2358</div>


Discussion / hints:
This is a good idea @datatraveller1.

Adding the option to `joinp`.

Fix patch:
diff --git a/src/cmd/joinp.rs b/src/cmd/joinp.rs
index 302d402b1..2602fc1a9 100644
--- a/src/cmd/joinp.rs
+++ b/src/cmd/joinp.rs
@@ -28,6 +28,10 @@ joinp arguments:
 
 joinp options:
     -i, --ignore-case      When set, joins are done case insensitively.
+-z, --ignore-leading-zeros  When set, joins are done ignoring leading zeros.
+                           Note that this is only applied to the join keys for
+                           both numeric and string columns. The output columns
+                           will not have leading zeros.
     --left                 Do a 'left outer' join. This returns all rows in
                            first CSV data set, including rows with no
                            corresponding row in the second data set. When no
@@ -220,7 +224,7 @@ use std::{
     str,
 };
 
-use polars::{datatypes::AnyValue, prelude::*, sql::SQLContext};
+use polars::{datatypes::AnyValue, prelude as pl, prelude::*, sql::SQLContext};
 use serde::Deserialize;
 use tempfile::tempdir;
 
@@ -231,47 +235,48 @@ use crate::{
 
 #[derive(Deserialize)]
 struct Args {
-    arg_columns1:          String,
-    arg_input1:            String,
-    arg_columns2:          String,
-    arg_input2:            String,
-    flag_left:             bool,
-    flag_left_anti:        bool,
-    flag_left_semi:        bool,
-    flag_right:            bool,
-    flag_right_anti:       bool,
-    flag_right_semi:       bool,
-    flag_full:             bool,
-    flag_cross:            bool,
-    flag_coalesce:         bool,
-    flag_filter_left:      Option<String>,
-    flag_filter_right:     Option<String>,
-    flag_validate:         Option<String>,
-    flag_maintain_order:   Option<String>,
-    flag_nulls:            bool,
-    flag_streaming:        bool,
-    flag_try_parsedates:   bool,
-    flag_decimal_comma:    bool,
-    flag_infer_len:        usize,
-    flag_cache_schema:     i8,
-    flag_low_memory:       bool,
-    flag_no_optimizations: bool,
-    flag_ignore_errors:    bool,
-    flag_asof:             bool,
-    flag_left_by:          Option<String>,
-    flag_right_by:         Option<String>,
-    flag_strategy:         Option<String>,
-    flag_tolerance:        Option<String>,
-    flag_sql_filter:       Option<String>,
-    flag_datetime_format:  Option<String>,
-    flag_date_format:      Option<String>,
-    flag_time_format:      Option<String>,
-    flag_float_precision:  Option<usize>,
-    flag_null_value:       String,
-    flag_output:           Option<String>,
-    flag_delimiter:        Option<Delimiter>,
-    flag_quiet:            bool,
-    flag_ignore_case:      bool,
+    arg_columns1:              String,
+    arg_input1:                String,
+    arg_columns2:              String,
+    arg_input2:                String,
+    flag_left:                 bool,
+    flag_left_anti:            bool,
+    flag_left_semi:            bool,
+    flag_right:                bool,
+    flag_right_anti:           bool,
+    flag_right_semi:           bool,
+    flag_full:                 bool,
+    flag_cross:                bool,
+    flag_coalesce:             bool,
+    flag_filter_left:          Option<String>,
+    flag_filter_right:         Option<String>,
+    flag_validate:             Option<String>,
+    flag_maintain_order:       Option<String>,
+    flag_nulls:                bool,
+    flag_streaming:            bool,
+    flag_try_parsedates:       bool,
+    flag_decimal_comma:        bool,
+    flag_infer_len:            usize,
+    flag_cache_schema:         i8,
+    flag_low_memory:           bool,
+    flag_no_optimizations:     bool,
+    flag_ignore_errors:        bool,
+    flag_asof:                 bool,
+    flag_left_by:              Option<String>,
+    flag_right_by:             Option<String>,
+    flag_strategy:             Option<String>,
+    flag_tolerance:            Option<String>,
+    flag_sql_filter:           Option<String>,
+    flag_datetime_format:      Option<String>,
+    flag_date_format:          Option<String>,
+    flag_time_format:          Option<String>,
+    flag_float_precision:      Option<usize>,
+    flag_null_value:           String,
+    flag_output:               Option<String>,
+    flag_delimiter:            Option<Delimiter>,
+    flag_quiet:                bool,
+    flag_ignore_case:          bool,
+    flag_ignore_leading_zeros: bool,
 }
 
 pub fn run(argv: &[&str]) -> CliResult<()> {
@@ -963,6 +968,98 @@ impl Args {
             right_lf = right_lf.filter(filter_right_expr);
         }
 
+        if self.flag_ignore_leading_zeros {
+            // Transform the join keys in the left and right dataframe to handle leading zeros
+            // For each join key column:
+            //   1. Cast to string type
+            //   2. If ignore_case is true, convert to lowercase
+            //   3. Trim leading zeros, regardless of the original type
+            // This allows joins to match on values like "001" and "1", "0001ABZ" and "1ABZ".
+            // Note that the output columns will not have leading zeros for the join keys.
+            //
+            // We had to add ignore_case handling to ignore_leading_zeros in the join keys because
+            // the existing code for ignore_case was not working for leading zeros if they are both
+            // used at the same time.
+            // The ignore_case code was creating temporary join key columns while
+            // ignore_leading_zeros was using the existing join key columns.
+            let ignore_case = self.flag_ignore_case;
+            left_lf = left_lf.with_columns(
+                self.arg_columns1
+                    .split(',')
+                    .map(|col| {
+                        let col_name = col.to_string();
+                        pl::col(&col_name).cast(pl::DataType::String).map(
+                            move |s| {
+                                Ok(Some(
+                                    pl::Series::new(
+                                        col_name.clone().into(),
+                                        s.str()?
+                                            .into_iter()
+                                            .map(|x| {
+                                                x.map(|val| {
+                                                    let v = if ignore_case {
+                                                        val.to_lowercase()
+                                                    } else {
+                                                        val.to_string()
+                                                    };
+                                                    if v.starts_with('0') {
+                                                        v.trim_start_matches('0').to_string()
+                                                    } else {
+                                                        v.to_string()
+                                                    }
+                                                })
+                                                .unwrap_or_default()
+                                            })
+                                            .collect::<Vec<String>>(),
+                                    )
+                                    .into(),
+                                ))
+                            },
+                            pl::GetOutput::from_type(pl::DataType::String),
+                        )
+                    })
+                    .collect::<Vec<_>>(),
+            );
+
+            right_lf = right_lf.with_columns(
+                self.arg_columns2
+                    .split(',')
+                    .map(|col| {
+                        let col_name = col.to_string();
+                        pl::col(&col_name).cast(pl::DataType::String).map(
+                            move |s| {
+                                Ok(Some(
+                                    pl::Series::new(
+                                        col_name.clone().into(),
+                                        s.str()?
+                                            .into_iter()
+                                            .map(|x| {
+                                                x.map(|val| {
+                                                    let v = if ignore_case {
+                                                        val.to_lowercase()
+                                                    } else {
+                                                        val.to_string()
+                                                    };
+                                                    if v.starts_with('0') {
+                                                        v.trim_start_matches('0').to_string()
+                                                    } else {
+                                                        v.to_string()
+                                                    }
+                                                })
+                                                .unwrap_or_default()
+                                            })
+                                            .collect::<Vec<String>>(),
+                                    )
+                                    .into(),
+                                ))
+                            },
+                            pl::GetOutput::from_type(pl::DataType::String),
+                        )
+                    })
+                    .collect::<Vec<_>>(),
+            );
+        }
+
         Ok(JoinStruct {
             left_lf,
             left_sel: self.arg_columns1.clone(),


Test patch:
diff --git a/tests/test_joinp.rs b/tests/test_joinp.rs
index 86868ad0a..2e1719591 100644
--- a/tests/test_joinp.rs
+++ b/tests/test_joinp.rs
@@ -1813,3 +1813,163 @@ joinp_test_comments!(
         assert_eq!(got, expected);
     }
 );
+
+#[test]
+fn joinp_ignore_leading_zero() {
+    let wrk = Workdir::new("joinp_ignore_leading_zero");
+
+    wrk.create(
+        "left.csv",
+        vec![
+            svec!["id", "value"],
+            svec!["001", "a"],
+            svec!["02", "b"],
+            svec!["3", "c"],
+        ],
+    );
+
+    wrk.create(
+        "right.csv",
+        vec![
+            svec!["id", "desc"],
+            svec!["1", "one"],
+            svec!["02", "two"],
+            svec!["003", "three"],
+        ],
+    );
+
+    let mut cmd = wrk.command("joinp");
+    cmd.args(&["id", "left.csv", "id", "right.csv"]).arg("-z");
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["id", "value", "desc"],
+        svec!["1", "a", "one"],
+        svec!["2", "b", "two"],
+        svec!["3", "c", "three"],
+    ];
+    assert_eq!(got, expected);
+}
+
+#[test]
+fn joinp_ignore_leading_zero_with_non_numeric() {
+    let wrk = Workdir::new("joinp_ignore_leading_zero_with_non_numeric");
+
+    wrk.create(
+        "left.csv",
+        vec![
+            svec!["code", "value"],
+            svec!["001A", "a"],
+            svec!["02B", "b"],
+            svec!["ABC", "c"],
+        ],
+    );
+
+    wrk.create(
+        "right.csv",
+        vec![
+            svec!["code", "desc"],
+            svec!["1A", "one"],
+            svec!["0002B", "two"],
+            svec!["ABC", "three"],
+        ],
+    );
+
+    let mut cmd = wrk.command("joinp");
+    cmd.args(&["code", "left.csv", "code", "right.csv"])
+        .arg("--ignore-leading-zeros");
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["code", "value", "desc"],
+        svec!["1A", "a", "one"],
+        svec!["2B", "b", "two"],
+        svec!["ABC", "c", "three"],
+    ];
+    assert_eq!(got, expected);
+}
+
+#[test]
+fn joinp_ignore_leading_zero_multiple_columns() {
+    let wrk = Workdir::new("joinp_ignore_leading_zero_multiple_columns");
+
+    wrk.create(
+        "left.csv",
+        vec![
+            svec!["id", "code", "value"],
+            svec!["001", "001A", "a"],
+            svec!["02", "02B", "b"],
+            svec!["3", "ABC", "c"],
+        ],
+    );
+
+    wrk.create(
+        "right.csv",
+        vec![
+            svec!["id", "code", "desc"],
+            svec!["1", "1A", "one"],
+            svec!["002", "0002B", "two"],
+            svec!["03", "ABC", "three"],
+        ],
+    );
+
+    let mut cmd = wrk.command("joinp");
+    cmd.args(&["id,code", "left.csv", "id,code", "right.csv"])
+        .arg("--ignore-leading-zeros");
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["id", "code", "value", "desc"],
+        svec!["1", "1A", "a", "one"],
+        svec!["2", "2B", "b", "two"],
+        svec!["3", "ABC", "c", "three"],
+    ];
+    assert_eq!(got, expected);
+}
+
+#[test]
+fn joinp_ignore_case_and_leading_zeros() {
+    let wrk = Workdir::new("joinp_ignore_case_and_leading_zeros");
+
+    wrk.create(
+        "left.csv",
+        vec![
+            svec!["id", "code", "value"],
+            svec!["001", "001abc", "a"],
+            svec!["02", "02DEF", "b"],
+            svec!["3", "XYZ", "c"],
+        ],
+    );
+
+    wrk.create(
+        "right.csv",
+        vec![
+            svec!["id", "code", "desc"],
+            svec!["1", "00001ABC", "one"],
+            svec!["002", "0002def", "two"],
+            svec!["03", "xyz", "three"],
+        ],
+    );
+
+    let mut cmd = wrk.command("joinp");
+    cmd.args(&["id,code", "left.csv", "id,code", "right.csv"])
+        .arg("--ignore-leading-zeros")
+        .arg("--ignore-case");
+
+    wrk.assert_success(&mut cmd);
+
+    let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
+    let expected = vec![
+        svec!["id", "code", "value", "id_right", "code_right", "desc"],
+        svec!["1", "1abc", "a", "1", "1abc", "one"],
+        svec!["2", "2def", "b", "2", "2def", "two"],
+        svec!["3", "xyz", "c", "3", "xyz", "three"],
+    ];
+    assert_eq!(got, expected);
+}