ArcGIS Pro 2.6: How to preform left and right Join?

[JamalNUMAN]

ArcGIS Pro 2.6: How to preform left and right Join?

I couldn’t figure out if there is a geoprocessing tool that can perform left and right Join.

For example, in the screenshots below, I wanted to join sheet1 and sheet2 tables to end up with sheet3

Saleh Ahmed‌

[DanPatterson]

You would have to code a tool using pandas or numpy...

Each table would have to be converted to their appropriate array type, then the join made on a common field.

This can be done with TableToNumPyArray to get the arrays, then when the calculations are done, you can use NumPyArrayToTable to get the resultant back as a table.

Eliminating those steps, the numpy steps can be accomplished as follows.  Several join types are demonstrated.

import numpy.lib.recfunctions as rfn 

a  # ---- one table's field 'f0' with a sequence of 10 values
array([(0,), (1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,)],
      dtype=[('f0', '<i4')])

b  # ---- the second table, with a common 'key' field, 'f0' 
array([(0, 'a'), (1, 'b'), (8, 'c'), (9, 'd')],
      dtype=[('f0', '<i4'), ('f1', '<U2')])

inner_ = rfn.join_by('f0', a, b, 'inner', usemask=False)
outer_ = rfn.join_by('f0', a, b, 'outer', usemask=False)
lft_outer_ = rfn.join_by('f0', a, b, 'leftouter', usemask=False)

inner_
array([(0, 'a'), (1, 'b'), (8, 'c'), (9, 'd')],
      dtype=[('f0', '<i4'), ('f1', '<U2')])

outer_
array([(0, 'a'), (1, 'b'), (2, 'N/'), (3, 'N/'), (4, 'N/'), (5, 'N/'),
       (6, 'N/'), (7, 'N/'), (8, 'c'), (9, 'd')],
      dtype=[('f0', '<i4'), ('f1', '<U2')])

lft_outer_
array([(0, 'a'), (1, 'b'), (2, 'N/'), (3, 'N/'), (4, 'N/'), (5, 'N/'),
       (6, 'N/'), (7, 'N/'), (8, 'c'), (9, 'd')],
      dtype=[('f0', '<i4'), ('f1', '<U2')])

# --- 'N/' is nodata
# --- simply send the appropriate array back to Pro using NumPyArrayToTable
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Pandas does basically the same thing but just hides numpy functionality underneath.  

[JamalNUMAN]

Thanks Dan.

Unfortunately, I don’t have python knowledge to create an end user tool out the code you have already provided

Saleh Ahmed‌

Hani Draidi‌

[DanPatterson]

I will put it in my Table tools then, although the demand is low within the Pro environment

https://community.esri.com/people/danretired/blog/2020/05/18/free-tools-for-arcgis-pro-table-tools 

[BruceHarold]

Hi Jamal, the FeatureJoiner transformer in Data Interoperability extension has this capability.

FeatureJoiner 

[JamalNUMAN]

Thanks guys.

Right. The tool is there in FME safe. However, I would prefer to have this tool in Pro to keep working as much as possible in the same environment

[DHuantes]

This could be what you're looking for.  

https://github.com/Esri/arcgis-pro-sdk/wiki/ProSnippets-Geodatabase#evaluating-a-querydef-on-a-outer...

I've successfuly performed an inner join... Working  on combining that with an outer join now will let you know how it works out.

[JamalNUMAN]

Thanks DanielHuantes.

But I have no idea how to create a tool for an end user out of this code