cdisc-org / cdisc-org/cdisc-rules-engine

Allow left joining the same dataset multiple times in Match Datasets

Open
#778 3 comments 0 reactions 0 assignees View on GitHub
DDF
Dominant language
Python
Stars
113
Forks
43
Avg merge
14h 51m
Merged PRs (30d)
14

Description

If the same dataset is referenced more than once in `Match Dataset` with `Join Type: left`, the following error is reported:
> Cannot use name of an existing column for indicator column

The error occurs because the left join functionality currently adds an "indicator" column with a name assigned as "\_merge\_\" - so, if the same dataset is left-joined more than once, a duplicate indicator column name will be assigned. Note that the `suffixes` option of the merge also uses the right dataset name as a suffix for columns whose names appear in both left and right datasets, but the resultant duplicate column names are not reported as errors (but they result in errors during later processing of the merged dataset).

To prevent these errors, the following function could be added to `base_data_service.py` to return a unique column suffix by adding and incrementing a numeric suffix to input suffix an unused suffix is found:
```python
@staticmethod
def _get_unique_suffix(dataset: DatasetInterface, suffix: str) -> str:
def _any_columns_with_suffix(suffix: str) -> bool:
for col in dataset.columns:
if col.endswith("." + suffix):
return True
return False
if _any_columns_with_suffix(suffix):
idx: int = 1
while _any_columns_with_suffix(suffix + str(idx)):
idx =+ 1
return suffix + str(idx)
else:
return suffix
```
and the `merge_sdtm_datasets` method in `data_processor.py` could then be updated to use the new method to obtain a unique suffix and use it both in the name of the indicator column and in the `suffixes` option of the merge:
```python
@staticmethod
def merge_sdtm_datasets(
left_dataset: DatasetInterface,
right_dataset: DatasetInterface,
left_dataset_match_keys: List[str],
right_dataset_match_keys: List[str],
right_dataset_domain_name: str,
join_type: JoinTypes,
) -> DatasetInterface:
sfx = DummyDataService._get_unique_suffix(left_dataset,right_dataset_domain_name)
result = left_dataset.merge(
right_dataset.data,
how=join_type.value,
left_on=left_dataset_match_keys,
right_on=right_dataset_match_keys,
suffixes=("", f".{sfx}"),
indicator=(
False
if join_type is JoinTypes.INNER
else f"_merge_{sfx}"
),
)
if join_type is JoinTypes.LEFT:
if "left_only" in result[f"_merge_{sfx}"].values:
DummyDataService._replace_nans_in_numeric_cols_with_none(result)
result.data.loc[
result[f"_merge_{sfx}"] == "left_only",
result.columns.symmetric_difference(
left_dataset.columns.union(
[f"_merge_{sfx}"]
)
),
] = None
return result
```

These changes would prevent the current error and allow left joining of the same dataset multiple times. However, to give additional flexibility and control, it would be worth considering the addition of a new `Match Datasets` parameter to allow a table alias to be assigned for each joined dataset.

Contributor guide

Open the contributing guide

Research direction

Start by reading base_data_service.py and data_processor.py, focusing on the proposed suffix helper and merge_sdtm_datasets. Trace how Match Dataset performs repeated left joins and how indicator and column suffix names are generated. Done means repeated left joins of the same dataset no longer raise duplicate-column errors and later merged-data processing remains valid.

Written by the indexing model from the issue text.

Assessment

Tech stack
python
Domain
data-engineering
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.