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
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.
Thanks Dan.
Unfortunately, I don’t have python knowledge to create an end user tool out the code you have already provided
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
Hi Jamal, the FeatureJoiner transformer in Data Interoperability extension has this capability.
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
This could be what you're looking for.
I've successfuly performed an inner join... Working on combining that with an outer join now will let you know how it works out.
Thanks DanielHuantes.
But I have no idea how to create a tool for an end user out of this code