2

I have a CSV of integers containing meter numbers varying from 9 digits to 11 digits. I've written a simple script to read CSV to a list.

import pandas as pd
path = "example_path.csv"
df = pd.read_csv(path)
meter_list = df['MeterNumber'].to_list()

From here I want to select all features that have a meter number within meter_list. The issue is the field in my feature class's data type is text. I've converted meter_list to have string values but the issue is there are inconsistent character lengths in my feature class. For example, ('123456789', '123456789 ', '1234567890'). I've tried using wildcard searches in SQL but none have worked so far.

Any suggestions on how to approach this problem?

1
  • I dont understand. 1234 in csv you want to select all features starting with 1234?
    – BERA
    Aug 22, 2022 at 13:33

1 Answer 1

3

Use the IN() query. There are several options for building an IN() query using a list in python. Probably all of these were taken from this Stack Exchange over the years

id_list = df['MeterNumber'].to_list()

#This option is easiest to read, but requires than the list have at least 2 entries.
#And I think it also requires the list / target field to be the same type (numeric/text)
where_clause = '"IDfield" IN ' + str(tuple(id_list))

#This approach works even if the list only has one entry.
#Use different quotes if the field you are selecting on is text vs numeric
where_clause = "NumberField IN ({:s})".format(','.join(f"{x}" for x in id_list))
where_clause = "TextField IN ({:s})".format(','.join(f"'{x}'" for x in id_list))

#Then just select by attribute
arcpy.management.SelectLayerByAttribute(tbl, 'NEW_SELECTION', where_clause)

5
  • The second approach worked perfectly thank you!
    – cb101
    Aug 22, 2022 at 14:01
  • One follow-up question. What would the Python 2.7 alternative be for the where_clause?
    – cb101
    Aug 22, 2022 at 14:49
  • 1
    @cb101, the Python version does not impact SQL syntax, and ArcGIS Desktop/ArcMap has a where_clause parameter just like Pro.
    – bixb0012
    Aug 22, 2022 at 21:55
  • @bixb0012 I'm aware of that but in the Text Field where_clause it uses an f-string in the join which doesn't work in 2.7. When I remove the f-string it just repeats {x}.
    – cb101
    Aug 23, 2022 at 11:49
  • @cb101, Replace "f{x}" with "{}".format(x)
    – bixb0012
    Aug 23, 2022 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.