I have a table with parcel numbers in one field and soil types in another. There are several soil types for each parcel, and therefore several records for each parcel. I am looking for a script (Python, SQL, VB, Arcade) that will iterate through each record grouped by APN, selecting (or outputting) the LAST record for each APN. Example input table: APNSOIL TYPE1A2A2A, B3A3A, E3A, E, G Desired result: APNSOIL TYPE1A2A, B3A, E, G It's a simple task that seems like it should be easy, but I can't seem to find a solution. I've been working in Excel, ModelBuilder inside of ArcGIS Pro, and Python in Jupyter Notebook, but I'm not very well versed in any one program or language; maybe there's a better route of attack. Thank you for any suggestions you might be able to provide!

Thank you Xander! Xander Bakker

Hi Joe Borgione , to explain a little more of what is happening with the dictionary in the process, see the snippet below: table = [ [ 1 , "A" ] , [ 2 , "A" ] , [ 2 , "A, B" ] , [ 3 , "A" ] , [ 3 , "A, E" ] , [ 3 , "A, E, G" ] ] dct = { } cnt = 0 for row in table : cnt += 1 apn = row [ 0 ] soil_type = row [ 1 ] dct [ apn ] = soil_type print ( "cnt:{}\tapn:{}\tsoil_type:{}\tdct:{}" . format ( cnt , apn , soil_type , dct ) ) This will print the dictionary for each row that is being read: cnt:1 apn:1 soil_type:A dct:{1: 'A'} cnt:2 apn:2 soil_type:A dct:{1: 'A', 2: 'A'} cnt:3 apn:2 soil_type:A, B dct:{1: 'A', 2: 'A, B'} cnt:4 apn:3 soil_type:A dct:{1: 'A', 2: 'A, B', 3: 'A'} cnt:5 apn:3 soil_type:A, E dct:{1: 'A', 2: 'A, B', 3: 'A, E'} cnt:6 apn:3 soil_type:A, E, G dct:{1: 'A', 2: 'A, B', 3: 'A, E, G'} So, when it finds the same apn key, the value that was stored in the dictionary for that apn key will be replaced by the last value (soil type).

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do I select the LAST of any number of duplicate records?

Dean_Heather

I have a table with parcel numbers in one field and soil types in another. There are several soil types for each parcel, and therefore several records for each parcel.

I am looking for a script (Python, SQL, VB, Arcade) that will iterate through each record grouped by APN, selecting (or outputting) the LAST record for each APN.

Example input table:

APN	SOIL TYPE
1	A
2	A
2	A, B
3	A
3	A, E
3	A, E, G

Desired result:

APN	SOIL TYPE
1	A
2	A, B
3	A, E, G

It's a simple task that seems like it should be easy, but I can't seem to find a solution. I've been working in Excel, ModelBuilder inside of ArcGIS Pro, and Python in Jupyter Notebook, but I'm not very well versed in any one program or language; maybe there's a better route of attack.

Thank you for any suggestions you might be able to provide!

Find more posts tagged with

Accepted answers

All comments

DanPatterson_Retired

Haven't checked all scenarios, but try this to get the index value for the elements of both

a <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN>

b <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">dif</SPAN><SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="string token">"""differencing"""</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> len<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> a<SPAN class="punctuation token">[</SPAN>i<SPAN class="number token">-1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">!=</SPAN> a<SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>i<SPAN class="number token">-1</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; b<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> b


dif<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN>

<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">5</SPAN><SPAN class="punctuation token">]</SPAN>&nbsp; <SPAN class="comment token"># zero based indexing</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

XanderBakker

If you create a dictionary and replace the value for the same key, you will end up with the last value for each key.

JoshuaBixby

If they are duplicates, how do you differentiate the first from last record, i.e., why not grab the first record? It looks like you are trying to select the record with the most number of soil types per APN, not the last record of duplicates. Depending on your answer, there may be a different approach to this.

JoeBorgione

I've been wondering the same thing: first, last.... Based on what?

DanPatterson_Retired

If python 3.6+ is being used, then dictionary order is retained...

If python 2.7 is being used, the dictionary approach will fail.

In that simple example, there is only one set of sequences and the ID number is given by the APN field, which is assumed to be sequential. If it were not an id field and the pattern represented classes, there could be duplicates in the sequence and the dictionary approach would fail again.

A blog is coming on sequences, patterns and replicants which will show how sequences and patterns of sequences and their representation and slicing (eg, start of sequence, sequence length and end of sequence) can all be obtained relatively simply and produce a summary table in ArcGIS PRO.

I will post back here hopefully within a day

XanderBakker

IMHO a dictionary will work just fine with sequential access to the table rows. See snippet below:

table <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A, B"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A, E"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A, E, G"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN>
dct <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> table<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; apn <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; soil_type <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; dct<SPAN class="punctuation token">[</SPAN>apn<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> soil_type

<SPAN class="keyword token">for</SPAN> apn<SPAN class="punctuation token">,</SPAN> soil_type <SPAN class="keyword token">in</SPAN> sorted<SPAN class="punctuation token">(</SPAN>dct<SPAN class="punctuation token">.</SPAN>items<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{} - {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>apn<SPAN class="punctuation token">,</SPAN> soil_type<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

This will yield:

1 - A
2 - A, B
3 - A, E, G<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

If they are completely sequential... not just consisting of sequences... that is not fully clear from the question posed, it might be one of those expanding questions.

upcoming details soon

JoeBorgione

Xander- I've been messing with this on and off all day. I created a file gdb table just like the one in the o.p. and with a da.SearchCursor created a list. That list was in the form of tuples so somewhat different than yours:

myList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A, B"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A, E"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"A, E, G"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

I then created a dictionary from that list and since the keys are not unique, the last one wins and that produces the desired result as shown in the o.p. But that's where things fall apart for me. If I pop() the last element and then insert() it back into the list at the first location, the resulting dictionary is quite different. 3:'A,E' wins.....

Would line 9 in your example take care of that?

Xander Bakker‌

XanderBakker

That's exactly what "should" happen, since the last element for APN 3 will be "A, E".

Or that exactly what I understand as objective from the question that the OP asked. It's best if the OP clarifies if this is the objective or that the LAST element should be interpreted differently (like last when sorted alphabetically or the one with the most "complex" soil type). Point is that with the solution I suggested the order of the data will influence the result and this is often not what one would like to happen.

DanPatterson_Retired

Perhaps way more than needed for the question... but patterns and sequences and their analysis are useful.

Tool url will appear after ArcGIS PRO Beta 2.2 is complete.

Light reading in the interim

Patterns, sequences, occurrence and position

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.