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; } } }

arcpy.SelectLayerByAttributes Where Clause

JoeBorgione

In post earlier today, (Another da.SearchCursor Issue ) through an oversight on my part I couldn't execute a select layer by attributes function. Silly me.... At rate, I've moved past that issue but now faced with constructing a where clause that involves membership in a dictionary.

Essentially, I've constructed a dictionary of street types that looks something like this:

typeDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="string token">"AVE"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"BLVD"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"CIR"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">}</SPAN>&nbsp; <SPAN class="comment token">#etc, etc‍‍‍</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

As best as I can tell, the way to see if a given string is a member of a dictionary, one can :

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> typeDict
<SPAN class="punctuation token">{</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="string token">"AVE"</SPAN> <SPAN class="keyword token">in</SPAN> typeDict
<SPAN class="token boolean">True</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="string token">"ST"</SPAN> <SPAN class="keyword token">in</SPAN> typeDict
<SPAN class="token boolean">False</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="string token">"ST"</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> typeDict
<SPAN class="token boolean">True</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">if</SPAN> <SPAN class="string token">"somevalue"</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> typeDict<SPAN class="punctuation token">:</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"nope"</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp; 
nope

‍‍‍‍‍‍‍‍<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>

Okay, easy enough. The problem is constructing a valid "where clause' for the SelectLayerByAttribute() when considering membership in a dictionary. I've tried dozens of {}.format() approaches but the red font on my screen looks something like the U.S. spend deficit. I need some advice to successfully select a record or records based on the dictionary:

intable <SPAN class="operator token">=</SPAN> "C<SPAN class="punctuation token">:</SPAN>\Path\To\File<SPAN class="punctuation token">.</SPAN>gdb\Table
arcpy<SPAN class="punctuation token">.</SPAN>MakeTableView_management<SPAN class="punctuation token">(</SPAN>intable<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"view"</SPAN><SPAN class="punctuation token">)</SPAN>
typeDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="string token">"AVE"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"BLVD"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"CIR"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">}</SPAN>&nbsp; <SPAN class="comment token">#etc, etc‍‍‍</SPAN>

fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"Type"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"StreetName"</SPAN><SPAN class="punctuation token">]</SPAN>
where <SPAN class="operator token">=</SPAN> "StreetName <SPAN class="operator token">>=</SPAN> <SPAN class="string token">'A'</SPAN>&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#just concerned with named streets, no numbers</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"view"</SPAN><SPAN class="punctuation token">,</SPAN>fields<SPAN class="punctuation token">,</SPAN>where<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Select <SPAN class="operator token">=</SPAN> <SPAN class="comment token">#need to format a where clause that</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># evalutes row[0] not in typeDict</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>SelectLayer<SPAN class="punctuation token">,</SPAN>ByAttribute<SPAN class="punctuation token">(</SPAN>view<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"ADD_TO_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN>Selection<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>‍</SPAN><SPAN>‍</SPAN></SPAN>

jamesfreddyc‌

bixb0012‌

Dan_Patterson‌

(I should just create a signature block with you guys in it; maybe I can convince my boss to put you on retainer....)

Find more posts tagged with

Accepted answers

deleted-user-qzyyiYme1rz9

Joe, first of all, you have the list in quotes, so it isn't reading your variable. Second, you need to change the list to string format with parentheses instead of brackets. See if this works.

>>> list = ['BLVD', 'AVE', 'CIR']
>>> list = str(list)
>>> list = list.replace("[", "(")
>>> list = list.replace("]", ")")
>>> print list
('BLVD', 'AVE', 'CIR')
>>> where = "Type NOT IN "+list
>>> print where
Type NOT IN ('BLVD', 'AVE', 'CIR')

All comments

JoeBorgione

Thankfully the data is tight" AVE, ST, BLVD etc. No Ave, no Str etc...

bixb0012

You have a field named "Type" and you mention domain-like functionality for that field. Is there only a set list of choices, or at least structured choices, for that field? That is, you would only find "AVE" but not "avenue?" Or, you would have "BLVD" but not "West BLVD?"

How structured the data is strongly influences how much work is involved in building an SQL statement. If the data is loosely structured, i.e., it could have "AVE" and "avenue" or "ST" and "street", then using a cursor with Python to generate a selection set is probably better than building an complicated SQL WHERE statement.

JoeBorgione

Thanks Alex- I simply created list directly as:

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> list <SPAN class="operator token">=</SPAN> <SPAN class="string token">"('Blah','BlahBlay','BlahBlahBlah')"</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">print</SPAN> list
<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Blah'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'BlahBlay'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'BlahBlahBlah'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> where <SPAN class="operator token">=</SPAN> <SPAN class="string token">"NOT IN"</SPAN><SPAN class="operator token">+</SPAN>list
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">print</SPAN> where
NOT IN<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Blah'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'BlahBlay'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'BlahBlahBlah'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

JoeBorgione

I'll read it there!

Thanks!

DanPatterson_Retired

darn... so I guess you don't want to see my next incarnation... I will leave that to a blog

JoeBorgione

Thanks Dan, but way more than I need. Boiled down to extreme basics: I want to examine a table of records and determine if each record has a valid value in a given field. Validity is confirmed by virtue of comparing the field value against a list. If the record's field value is confirmed as valid, move on to the next record. If the record's field value is confirmed as not valid, write that record to another table: then move on to the next record in the original table for examination....

DanPatterson_Retired

I might have it now (I think)

Basically you want to check a street address to see if a street type occurs and if it does, you want to produce a new column which reclasses the street type.

Lets consider the following

from_s = ['Road', 'Street','Lane', 'Court']
to_s = [10, 20, 30, 40]

So in the above, if the address name contains any of the values in the 'from_s' you want to reclass that address to a new type for whatever purpose.

It isn't fully vectorized, but here is what I did in verbose form

<SPAN class="comment token"># ---- Read in your street index and query fields shortcutting the searchcursor stuff</SPAN>
<SPAN class="comment token">#</SPAN>
in_fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\Your_spaceless\path_to_your\address.gdb\streets'</SPAN>
flds <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OID@'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Address_'</SPAN><SPAN class="punctuation token">]</SPAN>
args <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>in_fc<SPAN class="punctuation token">,</SPAN> flds<SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> None<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
cur <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>args<SPAN class="punctuation token">)</SPAN>
a <SPAN class="operator token">=</SPAN> cur<SPAN class="punctuation token">.</SPAN>_as_narray<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># ----</SPAN>
<SPAN class="comment token"># ---- Establish your 'from' and 'to' classes</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Make a temporary array for filling and ultimately joining</SPAN>
<SPAN class="comment token"># ----</SPAN>
from_s <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Road'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Street'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Lane'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Court'</SPAN><SPAN class="punctuation token">]</SPAN>
to_s <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">10</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">20</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">30</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">40</SPAN><SPAN class="punctuation token">]</SPAN>
dt <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'IDs'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Str_class'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
out <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>zeros<SPAN class="punctuation token">(</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><SPAN class="punctuation token">,</SPAN> dtype<SPAN class="operator token">=</SPAN>dt<SPAN class="punctuation token">)</SPAN>
out<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'IDs'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>arange<SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1001</SPAN><SPAN class="punctuation token">,</SPAN> dtype<SPAN class="operator token">=</SPAN><SPAN class="string token">'int32'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token"># ---- Let's rock and roll</SPAN>
cnt <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
<SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> from_s<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; idx <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>array<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>i <SPAN class="keyword token">for</SPAN> i<SPAN class="punctuation token">,</SPAN> item <SPAN class="keyword token">in</SPAN> enumerate<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Address_'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> f <SPAN class="keyword token">in</SPAN> item<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; out<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Str_class'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN>idx<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> to_s<SPAN class="punctuation token">[</SPAN>cnt<SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; cnt <SPAN class="operator token">+=</SPAN> <SPAN class="number token">1</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>ExtendTable<SPAN class="punctuation token">(</SPAN>in_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">,</SPAN> out<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'IDs'</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>‍</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>

The reveal... Is this what you want to do?

I should point out that would be prudent to use a null value for an integer field this could be 'None' for text fields or another value for integer fields, for example on line 15.5 you could add

out<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Str_class'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>fill<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

curtvprice

Here's how I do it:

list <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">]</SPAN>
list1 <SPAN class="operator token">=</SPAN> <SPAN class="string token">","</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"'{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>k<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> k <SPAN class="keyword token">in</SPAN> list<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
where <SPAN class="operator token">=</SPAN> <SPAN class="string token">"FIELD IN ({})"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>list1<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>where<SPAN class="punctuation token">)</SPAN> 
<SPAN class="comment token"># prints: FIELD IN ('BLVD','AVE','CIR')</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

DanPatterson_Retired

Joe... for the back burner... but using Feature or Table to NumpyArray... even for one column, conversions can be quasi-vectorized easily, once you have a couple of steps. Shown verbosely, but in essence, if you already have a dictionary of key/value pairs, they can be converted to an array, then the whole field can be queried by case rather than by row... a small demo to give you ideas for the future.

<SPAN class="comment token"># ---- Start with the beginning fluff of taking the data, making a temporary</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; storage array for output</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Convert your dictionary... then run the 'for' loop which queries by case</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rather than by row (this of course can be done all at once, but loops are </SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; good for now</SPAN>
<SPAN class="comment token">#</SPAN>
a <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Not'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Nope'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'nada'</SPAN><SPAN class="punctuation token">]</SPAN>
a <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>array<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">)</SPAN>
result <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>full<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">.</SPAN>shape<SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">,</SPAN> dtype<SPAN class="operator token">=</SPAN><SPAN class="string token">'int32'</SPAN><SPAN class="punctuation token">)</SPAN>
typeDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="string token">"AVE"</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"BLVD"</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"CIR"</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">}</SPAN>&nbsp; <SPAN class="comment token"># ad nauseum</SPAN>
dt <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Lookfor'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<U5'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'ReplaceWith'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
arr <SPAN class="operator token">=</SPAN> np<SPAN class="punctuation token">.</SPAN>array<SPAN class="punctuation token">(</SPAN>list<SPAN class="punctuation token">(</SPAN>typeDict<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> dtype<SPAN class="operator token">=</SPAN>dt<SPAN class="punctuation token">)</SPAN>
ins <SPAN class="operator token">=</SPAN> arr<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Lookfor'</SPAN><SPAN class="punctuation token">]</SPAN>
outs <SPAN class="operator token">=</SPAN> arr<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'ReplaceWith'</SPAN><SPAN class="punctuation token">]</SPAN>
frmt <SPAN class="operator token">=</SPAN> <SPAN class="string token">"""
Input data array
{}
Temporary holding array
{}\n
Dictionary to array conversion
{}
lookfor ..... {}
replace with. {}
-----
"""</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>frmt<SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> result<SPAN class="punctuation token">,</SPAN> arr<SPAN class="punctuation token">,</SPAN> ins<SPAN class="punctuation token">,</SPAN> outs<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>arr<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#result[np.where(a == arr['Lookfor']<I>)[0]] = arr['ReplaceWith']<I></I></I></SPAN>
&nbsp;&nbsp;&nbsp; result<SPAN class="punctuation token">[</SPAN>np<SPAN class="punctuation token">.</SPAN>where<SPAN class="punctuation token">(</SPAN>a <SPAN class="operator token">==</SPAN> ins<SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> outs<SPAN class="punctuation token">[</SPAN>i<SPAN class="punctuation token">]</SPAN>

frmt <SPAN class="operator token">=</SPAN> <SPAN class="string token">"""
magic done
In again, then out
{}
{}
"""</SPAN>
<SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>frmt<SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>a<SPAN class="punctuation token">,</SPAN> result<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>‍</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><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>

Then the results of the demo are

input data
<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'Not'</SPAN> <SPAN class="string token">'BLVD'</SPAN> <SPAN class="string token">'Nope'</SPAN> <SPAN class="string token">'CIR'</SPAN> <SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'BLVD'</SPAN> <SPAN class="string token">'CIR'</SPAN> <SPAN class="string token">'nada'</SPAN><SPAN class="punctuation token">]</SPAN>
output result
<SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">]</SPAN>
dictionary to array
<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
lookfor <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'BLVD'</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">]</SPAN>
replace <SPAN class="keyword token">with</SPAN><SPAN class="punctuation token">.</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN> <SPAN class="number token">2</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN><SPAN class="operator token">-</SPAN>


magic done
In again<SPAN class="punctuation token">,</SPAN> then out
<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'Not'</SPAN> <SPAN class="string token">'BLVD'</SPAN> <SPAN class="string token">'Nope'</SPAN> <SPAN class="string token">'CIR'</SPAN> <SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'AVE'</SPAN> <SPAN class="string token">'BLVD'</SPAN> <SPAN class="string token">'CIR'</SPAN> <SPAN class="string token">'nada'</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="punctuation token">[</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN>&nbsp; <SPAN class="number token">2</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN>&nbsp; <SPAN class="number token">3</SPAN>&nbsp; <SPAN class="number token">1</SPAN>&nbsp; <SPAN class="number token">1</SPAN>&nbsp; <SPAN class="number token">2</SPAN>&nbsp; <SPAN class="number token">3</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">]</SPAN>


<SPAN class="comment token"># ---- you use arcpy.da.ExtendTable to attach the output result to the original</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table...&nbsp; Works best when your cases are obviously less than your records</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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

deleted-user-qzyyiYme1rz9

Joe, first of all, you have the list in quotes, so it isn't reading your variable. Second, you need to change the list to string format with parentheses instead of brackets. See if this works.

JoeBorgione

I kind of went down that route with

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">print</SPAN> list
<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> where <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Type not in list"</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> where
<SPAN class="string token">'Type not in list'</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

but when I try to get where into something SQL likes, that's where things go south...

DarrenWiens2

Working backwards, you need to use SQL in your where clause, something like:

StreetName <SPAN class="operator token">not</SPAN> <SPAN class="operator token">in</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

So, you need to somehow format your dictionary keys into a list of quoted strings, like in the final line below:

typeDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="string token">"AVE"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"BLVD"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"CIR"</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">print</SPAN> typeDict
<SPAN class="keyword token">print</SPAN> typeDict<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> <SPAN class="string token">','</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>typeDict<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> <SPAN class="string token">','</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"'{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>w<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> w <SPAN class="keyword token">in</SPAN> typeDict<SPAN class="punctuation token">.</SPAN>keys<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>
<SPAN class="punctuation token">{</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="number token">3</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">[</SPAN><SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'CIR'</SPAN><SPAN class="punctuation token">]</SPAN>
BLVD<SPAN class="punctuation token">,</SPAN>AVE<SPAN class="punctuation token">,</SPAN>CIR
<SPAN class="string token">'BLVD'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'AVE'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'CIR'</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>

JoeBorgione

Joshua- this is part of an overall QA/QC procedure I'm trying to implement. In this particular case, I will be examining streets and the value of the "Type" field and if an editor has added a named street, the Type field must be populated by at least on value in the dictionary: a lot like validating a domain, but without the domain.

Members of the selected set are written to a table in memory called "Errors". Errors_in_memory has a field called "Error_Description" which I update with a arcpy.CalculateField_management(); once that is done, those records are appended to a table on disc that is used as a check list to 'fix' any errors. Errors_in_memory is then cleaned out with a DeleteRows_management() and I go on to the next QA/QC loop which could be a look at the numbered streets (where = "StreetName < 'A'") and select those where "Type is not None". They get written to Errors_In_Memory, flagged with a description and then appended to disc, and so on....

Thanks.

bixb0012

Instead of making specific code suggestions, let's take a step back to understand what you are trying to accomplish. It seems to me you are trying to select all streets that are not avenues, boulevards, circles, etc...., i.e., you have a set of criteria that you want to invert. Is this correct? If so, what do you plan on doing with the selected records?

Generally, Select By Layer or Select By Attribute within a cursor can be refactored, especially when the cursor and selections are all based on the same layer or view.

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.