# Join Tables Filter

**URL:** <https://discourse.paraview.org/t/join-tables-filter/8333>\
**Category:** Development\
**Tags:** proposal\
**Created:** [November 5, 2021, 9:17am UTC](https://discourse.paraview.org/t/join-tables-filter/8333 "2021-11-05T09:17:53Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![aschieb](https://discourse.paraview.org/letter_avatar_proxy/v4/letter/a/6de8d8/32.png) [@aschieb](https://discourse.paraview.org/u/aschieb)\
**Post date:** [November 5, 2021, 9:17am UTC](https://discourse.paraview.org/t/join-tables-filter/8333/1 "2021-11-05T09:17:54Z")

</div>

Hello to the ParaView discourse community!

In the context of improving Table data processing and manipulation in ParaView, a few features which I have been contributing to have recently been implemented. These contributions include :

- The implementation of the [Threshold Table Filter](https://gitlab.kitware.com/paraview/paraview/-/merge_requests/5272)
- [Table support](https://gitlab.kitware.com/paraview/paraview/-/merge_requests/5280/) for the Python Calculator.

Following the direction of these changes, I would like to improve the handling of data coming from different sources. Let’s say the user has two different Tables containing different data attributes :

 ![Screenshot from 2021-11-05 09-45-39](https://discourse.paraview.org/uploads/default/original/2X/a/a97ae5b78826773b6830122436a0ac67d9f9d810.png)

We would like to “merge” these two tables into one bigger table with more columns and just as many rows, like an SQL JOIN operation :

 ![Screenshot from 2021-11-05 09-50-17](https://discourse.paraview.org/uploads/default/original/2X/b/b31ed0e40f7215005a6e4253c0ce439f205801c9.png)

There exist very few reliable ways to do this in ParaView, especially if the rows don’t follow the same order in the two input tables. ParaView offers the generic **Append Attributes** Filter which almost does the job, but only works in the very particular case where both input tables have the same number of rows, and fails to realize that the rows have been ordered differently.

 ![Screenshot from 2021-11-05 09-56-23](https://discourse.paraview.org/uploads/default/original/2X/2/20d7607147b7d5493de2cf8beea4d4b0fb92e9f9.png)

VTK also has a vtkMergeTables class but it has nothing to do with the JOIN operation that we are trying to achieve here, since it just concatenates both input tables “on top” of each other.

**Filter idea :**

The idea is to create a filter that allows the user to combine data coming from two different tables based on a “key” column, just like a JOIN operation in SQL. This filter would take exactly two input Tables and output a single Table.

Note that the python library pandas already has an [SQL style JOIN operation](https://pandas.pydata.org/docs/reference/api/pandas.merge.html) implemented, the list of arguments exposed by this function is quite extensive. For a ParaView implementation, one can assume that not all of them would make sense.

The parameters of this filter would include :

- **Column name to base the merge operation on**. For the merge to make sense, the two tables must have at least a column in common. In ParaView, this parameter can be implemented in the form of a combo box.
- **Join method** : Combo box {“inner”, “full”, “left”, “right”} specifying the join type. See [this doc](https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15) for reference.
- **Dealing with undefined values** : let the user decide whether to impute an arbitrary value, or leave the cell empty.

What are your thoughts on this proposal?

Would it make more sense to modify and improve an already existing filter (ie. Append Attributes), or to start from scratch and create a completely new filter?

Do you think of other options or parameters that would make sense to be implemented?

Are there edge cases that could expose issues?

Thanks in advance for your help!

---

<div class="post-metadata">

**Author:** ![mwestphal](https://discourse.paraview.org/user_avatar/discourse.paraview.org/mwestphal/32/17_2.png) [@mwestphal](https://discourse.paraview.org/u/mwestphal)\
**Post date:** [November 5, 2021, 2:31pm UTC](https://discourse.paraview.org/t/join-tables-filter/8333/2 "2021-11-05T14:31:29Z")

</div>

> [@aschieb](#):
>
> What are your thoughts on this proposal?

Very good idea !

> [@aschieb](#):
>
> Would it make more sense to modify and improve an already existing filter (ie. Append Attributes), or to start from scratch and create a completely new filter?

This merge or join operation only make sense in a vtkTable environnement, you can’t append attributes from one dataset into another if they do not have the same geometry as you would also have to merge/join the geometries, which is completelly out of scope.

Then this is a new filter, specific to tables. Since MergeTables already exists, I suppose JoinTables makes sense ?

> **Column name to base the merge operation on**

ParaView basically would rely on the ID here. So the user would be able to select one column in the first input and one column in the second input to do the merge ?

> **Join method** : Combo box {“inner”, “full”, “left”, “right”}

I’m not a SQL person, could you give some details ?

> Dealing with undefined values : let the user decide whether to impute an arbitrary value, or leave the cell empty.

Empty is not a thing, NaN or arbitrary value could be fine

> Do you think of other options or parameters that would make sense to be implemented?  
> Are there edge cases that could expose issues?

When you compare the content of the two columns for the merge, many edge cases can appears:

- Column of different types( eg: 1.0f == 1.0d or 1.0f == 1i ?)
- Column of different way to represent same values (eg: 1.0 == “1.0” ?)
- Column with numerical imprecisions (1.0000000000000001 == 1.0 ?)

A solution would be to specify the multi type comparison is not supported, add a tolerance parameter for floating point comparison, or even restrict to String/vtkIdType. In any case, this should be considered.

---

<div class="post-metadata">

**Author:** ![Francois\_Mazen](https://discourse.paraview.org/user_avatar/discourse.paraview.org/francois_mazen/32/6483_2.png) [@Francois\_Mazen](https://discourse.paraview.org/u/Francois_Mazen)\
**Post date:** [November 8, 2021, 8:18am UTC](https://discourse.paraview.org/t/join-tables-filter/8333/3 "2021-11-08T08:18:36Z")

</div>

@mwesphal: For the join methods from SQL:

 ![](https://discourse.paraview.org/uploads/default/original/2X/4/4085f8a733bd5e493232effa30d3ba701c70cc1d.png)

We should probably pick other names to match Scientific Visualization world : “difference”, “union”, etc.

> [@mwestphal](#):
>
> restrict to String/vtkIdType

This sounds the most reasonable solution.
