Cluster/Similarity problem with two datasets of different cardinality

Question

I want to cluster financial products according to their similarity. I have two dataset of different cardinality:

• One-to-One dataset: One ID has One attribute/feature per column - Describes a financial product
• One-to-Many: One ID has multiple attributes/features per column - What a financial product consists of and the corresponding weights within the financial product

the One-to-One dataset looks like:

the One-to-Many dataset looks like:

How to deal with such datasets, when trying to cluster/label the IDs into similar groups? I am afraid if joining the two datasets the repetition of the One-to-One table records (e.g. Region 'Europe' will be then recorded 8 times for ID 'XADV') will interfer with the estimation of a model/cluster etc.

Also I am unsure how to get the SUB_ID column in relation to the SUB_ID_WEIGHT as at the one side it is a categorical problem as well as a regression problem. SUB_ID rerg is in ID XADV & ZZZSD but with massive weight differences, while e.g. SUB_ID AA1111 is not in ID XADV at all.

Any idea in terms of dataset engineering or models/algorithms that can be used for such a use case?

Any pointing much appreciated! Best Max

Answer 1

There are different possible approaches. Without closer look into your data it is hard to tell which one would be the best. In the following, I will list multiple approaches.

Pivot Table

This is the straight forward approach: Create one columns for each SUB_ID-Value and fill the SUB_ID_WEIGHT in. You need to impute missing values. I assume 0 would be a good weight for SUB_IDs not listed for a given ID. Afterwards, you could merge both tables and get something like:

ID	REGION	STYLE	DURATION	dhce	rerg	dfbrt	vdfv	csefe	...	AA1111	BB3333
XADV	Europe	Fast	Short	0.023	0.034	0.064	0.12	0.004	...	0	0
ZZZSD	Europe	Slow	Short	0.0112	0.223	0.0123	0.5	0	...	0.011	0.0254
....

This table could then be used to perform the clustering (you will still have to deal with the categorical columns).

PROS: This approach is easy to implement.
CONS:

• Depending on the number of SUB_IDs, you might end up with MANY columns, which brings the curse of dimensionality, i.e. might not be well suited for clustering.
• In some cases, the distance between two rows (that is used for clustering algorithms) might not be the most meaningful, which leads to less meaningful clusters. But it needs domain expertise to deside about this.

Descriptive Statistics

You could extract for each ID a number of statistics over SUB_IDs and SUB_ID_WEIGHTs, e.g.

• Number of listed SUB_IDs
• Min / Max / Mean SUB_ID_WEIGHT

The augment the first table with these statistics

PROS: This approach is easy to implement.
CONS: The clustering result depends strongly on your choice of statistics.

Embedding

In order to reduce the dimensionality, one can use an emedding, i.e. a representation with less dimensions that preserves relevant information.

Prominent approaches are auto-encoders (by using the latent representation as , t-sne or umap. As input serves again the Pivot-Table from above, that can be used to train the embedding and transform each row in a lower dimension space.

PROS: One additional layer for which libraries exist.
CONS:

• Needs enough training data
• Builds on the distance mentioned above

Custom distance function

Most clustering algorithms just need a distance function between pairs of instances. All we did above was to find a representation that comes with such a distance (e.g. the euclidean). Instead you could define your own distance function between two IDs.

Examples

• Jaccard Distance: Each ID is represented by a set of SUB_IDs. The Jaccard Distance measures how similar these sets are. Downfall: this would ignore weights.
• Fuzzy Jaccard Distance: There are extensions that deal with fuzzy sets, i.e. include the weights. Unfortunately, I am no expert for these and I am not sure if there are good libraries for them.

If you have some more domain knowledge, e.g. that AA1111 is similar to BB3333, but not to rerg, you could also include this in the definition of the distance function.

PROS: Can be designed to work exactly for your use case.
CONS: Need work to come up with a meaningful distance function.

Multi-Instance-Clustering

Multi-Instance-Learning is a field that deals with exactly your type of 1:n relation. Unfortunately, Multi-Instance-Learning concentrates on supervised classification, but there are some works for clustering as well.

PROS: Most flexible approach.
CONS: Not much work done, yet. You might end up implementing a research paper

Answer 2

Most commonly used clustering software packages assume data to be in the tidy data layout, where each instance is only in a single row and all features for that instance are columns. Those two separate data sets would have to be merged into a single dataset formatted to be "tidy".

Missing data then has to be handled. The most common options are dropping the missing data, encoding that a value is missing with an indicator value (e.g., NaN), or imputing the missing values.

Next, a clustering algorithm can be applied. It appears that there are both categorical and numerical features so a distance metric like Gower’s distance might be a useful choice.