AnsweredAssumed Answered

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

Question asked by Heather.Dean@CalOES.ca.gov_CalEMA on May 23, 2018
Latest reply on May 25, 2018 by jborgion

Like • Show 0 Likes0
Comment • 13

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!

No one else has this question

Outcomes

13 Replies

Dan_Patterson May 23, 2018 6:10 PM
Mark Correct
Correct Answer
Haven't checked all scenarios, but try this to get the index value for the elements of both
```
a = [1, 2, 2, 3, 3, 3]

b = []
def dif(a):
    """differencing"""
    for i in range(1, len(a)):
        if a[i-1] != a[i]:
            b.append(i-1)
    b.append(i)
    return b


dif(a)

[0, 2, 5]  # zero based indexing
```
2 people found this helpful
Like • Show 0 Likes0
Actions
- xander_bakker @ Dan_Patterson on May 24, 2018 1:39 AM
  Mark Correct
  Correct Answer
  If you create a dictionary and replace the value for the same key, you will end up with the last value for each key.
  Like • Show 0 Likes0
  Actions
bixb0012 May 24, 2018 6:37 AM
Mark Correct
Correct Answer
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.
3 people found this helpful
Like • Show 0 Likes0
Actions
- jborgion @ bixb0012 on May 24, 2018 6:51 AM
  Mark Correct
  Correct Answer
  I've been wondering the same thing: first, last.... Based on what?
  Like • Show 0 Likes0
  Actions
Dan_Patterson May 24, 2018 10:05 AM
Mark Correct
Correct Answer
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
1 person found this helpful
Like • Show 0 Likes0
Actions
- xander_bakker @ Dan_Patterson on May 24, 2018 2:34 PM
  Mark Correct
  Correct Answer
  IMHO a dictionary will work just fine with sequential access to the table rows. See snippet below:
  table = [[1, "A"], [2, "A"], [2, "A, B"], [3, "A"], [3, "A, E"], [3, "A, E, G"]] dct = {} for row in table: apn = row[0] soil_type = row[1] dct[apn] = soil_type for apn, soil_type in sorted(dct.items()): print("{} - {}".format(apn, soil_type))
  This will yield:
  1 - A 2 - A, B 3 - A, E, G
  Like • Show 0 Likes0
  Actions
  - Dan_Patterson @ xander_bakker on May 24, 2018 2:50 PM
    Mark Correct
    Correct Answer
    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
    Like • Show 1 Like1
    Actions
  - jborgion @ xander_bakker on May 24, 2018 2:56 PM
    Mark Correct
    Correct Answer
    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 = [(1, "A"), (2, "A"), (2, "A, B"), (3, "A"), (3, "A, E"), (3, "A, E, G")]
    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
    Like • Show 0 Likes0
    Actions
    - xander_bakker @ jborgion on May 24, 2018 3:05 PM
      Mark Correct
      Correct Answer
      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.
      1 person found this helpful
      Like • Show 0 Likes0
      Actions
      - jborgion @ xander_bakker on May 24, 2018 3:14 PM
        Mark Correct
        Correct Answer
        Okay, cool. Dictionaries are still a bit of a mystery to me, so I'm glad I'm on the right track: it goes back to what Joshua suggested yesterday as to what constitutes the desired outcome......
        Like • Show 0 Likes0
        Actions
        xander_bakker @ jborgion on May 24, 2018 11:48 PM
        Mark Correct
        Correct Answer
        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).
        2 people found this helpful
        Like • Show 0 Likes0
        Actions
        jborgion @ xander_bakker on May 25, 2018 4:58 AM
        Mark Correct
        Correct Answer
        Thank you Xander!
        
        Xander Bakker
        Like • Show 0 Likes0
        Actions
Dan_Patterson May 24, 2018 4:08 PM
Mark Correct
Correct Answer
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
2 people found this helpful
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

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

Outcomes

Related Content

Recommended Content