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‌

JoinLeftRight.gdb.zip

JoaoPinto_2-1645462298759.png

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.