SUPP dataset merge by numeric variable

[HajimeShimizu]

Feature Description

CORE: 0.14.0

SUPP merge doesn't work in following case:
(1) Data is in Excel Format
(2) Merge by --SEQ variable (numeric)

Numeric data has ".0" in pandas dataframe in this case.

  STUDYID DOMAIN USUBJID SPDEVID AESEQ  ... 
0    TEST     AE    A001    None     1.0  ...   
1    TEST     AE    A001    None     2.0  ... 

On the other hand, IDVARVAL variable is character type.

  STUDYID RDOMAIN USUBJID IDVAR IDVARVAL  ... 
0    TEST     AE    A001    AESEQ     1  ...   
1    TEST     AE    A001    AESEQ     2  ... 

"def merge_pivot_supp_dataset" handles this merge (cdisc_rules_engine/utilities/data_processor.py)

I see the following, and this code recognize "1" vs "1.0" is unequal.

            if not is_blank:
                common_keys.append(dynamic_key)
                current_supp = right_dataset.rename(columns={"IDVARVAL": dynamic_key})
                current_supp = current_supp.drop(columns=["IDVAR"])
                left_dataset[dynamic_key] = left_dataset[dynamic_key].astype(str)
                current_supp[dynamic_key] = current_supp[dynamic_key].astype(str)

Maybe normalization of numeric variable data resolve this issue. Create a new function like this...

        def normalize_numeric_key(x):
            if pd.isna(x):
                return x
            try:
                f = float(x)
                if f.is_integer():
                    return str(int(f))
                return str(f)
            except (ValueError, TypeError):
                return str(x)

... and apply for left dataset

            if not is_blank:
                common_keys.append(dynamic_key)
                current_supp = right_dataset.rename(columns={"IDVARVAL": dynamic_key})
                current_supp = current_supp.drop(columns=["IDVAR"])
                left_dataset[dynamic_key] = left_dataset[dynamic_key].apply(normalize_numeric_key)
                current_supp[dynamic_key] = current_supp[dynamic_key].astype(str)

[gerrycampion]

Possible fixed by: #1653
But will need to confirm

[gerrycampion]

@HajimeShimizu Can you please provide test rule data and logic so that we can reproduce the issue?

[SFJohnson24]

@gerrycampion this was not addressed in #1653. There was however a similar issue in operators and

cdisc-rules-engine/cdisc_rules_engine/check_operators/dataframe_operators.py

Line 101 in 4114574

def _custom_str_conversion(self, x):

this was created to address the float trailing zero's causing issues for string comparisons.

This ticket should move that function to utils so it no longer an instance method and can be used on both the check operators as well as on the dynamic keys in preprocessing merge logic

[gerrycampion]

@gerrycampion this was not addressed in #1653. There was however a similar issue in operators and

cdisc-rules-engine/cdisc_rules_engine/check_operators/dataframe_operators.py

Line 101 in 4114574

def _custom_str_conversion(self, x):
this was created to address the float trailing zero's causing issues for string comparisons.
This ticket should move that function to utils so it no longer an instance method and can be used on both the check operators as well as on the dynamic keys in preprocessing merge logic

Ah, yes. I was referring to https://github.com/cdisc-org/cdisc-rules-engine/pull/1653/changes#diff-1dffb7e663f4da3b3f4ee46f399bc0514405e6d21479dda7f83c6c2e5264f8b1R156 but this is for RELRECs during Dataset Matching