I need to delete one of the two rows that have the same DestinationID and bigger value in Total_TravelTime.
2
-
1Please Edit the Question to specify the data format of the layer source. If Enterprise geodatabase, specify which RDBMS.– VinceCommented Jun 29, 2020 at 11:53
-
Is this data is in a graph (matrix) format? If so, please keep in mind that these are pairwise values and deleting one of them makes the data rather useless in any modeling context. Besides, based on a given question, which observation is the correct one to delete? And, yes the is a correct answer that is quite context based. The best way to deal with data like this is by using a model that is designed for matrix regression or using a mixed effects model where the random effect is the unique pairwise identifier.– Jeffrey EvansCommented Jul 8, 2020 at 19:04
Add a comment
|
3 Answers
You can make use of the Find Identical tool and a little more Python code if you have an Advanced License (if you don't have an Advanced license, you can still accomplish this, you just need to write the code that does the Find Identical work).
The output from Find Identical will be a table with records that are found to be duplicated. You'd then write the following Python:
- Use the above output with a SearchCursor to loop through the new table on the FEAT_SEQ row. This will show you duplicated records from your DestinationID field. From this search cusrosr, you need to make a list of IN_FID
- Take the list of FIDs (which match your ObjectID from your original table), and use those to select records from your original table. You want to look at the Total_TravelTime in your original table.
- Sort your output for largest.
- Then delete the records in the table that aren't largest.
-
I am not sure I understand the first two steps. How do I select with the list? I tried something like this. arcpy.FindIdentical_management(in_dataset=AU2, out_dataset=AU2_FindIdentical, fields="DestinationID", xy_tolerance="", z_tolerance="0", output_record_option="ALL") myList = [] with arcpy.da.SearchCursor(AU2_FindIdentical, ["FEAT_SEQ", "IN_FID"]) as cursor: for row in cursor: myList.append(row[1]) Commented Jun 30, 2020 at 9:17
I managed to accomplish what I wanted by using subquery sort and where clause with a step.
sort_fields = [["DestinationID", "ASCENDING"], ["Total_TravelTime", "ASCENDING"]]
arcpy.Sort_management(AU2, out_AU2, sort_fields)
arcpy.TableSelect_analysis(in_table=out_AU2, out_table=Ajad, where_clause="MOD(OBJECTID+3,2) = 0")
SELECT
DestinationID,
Total_TravelTime,
COUNT(*) occurrences
FROM <TableName>
GROUP BY
DestinationID,
Total_TravelTime
HAVING
COUNT(*) > 1
and
Total_TravelTime > 47.3;
