-1

I need to select all rows matching specific values across 3 columns and then iteratively change one value at a time and continue making selections. The reason is to capture certain summary statistics from other columns in each selection.

I plan to use SelectLayerByAttribute_management and am quite sure the following line will set up the necessary query:

query = '"{0}" = {1} AND "{2}" = {3} AND "{4}" = {5}'.format(column1, value1, column2, value2, column3, value3, column4, value4)

columns 1, 2, and 3 in my table are numeric along the following pattern:

column1  column2  column3
01      010      001
01      010      001
01      010      002
01      010      002
01      020      001
01      020      002
01      020      002
01      020      003
01      030      001
01      030      002
01      030      003
02      010      001
02      010      001
02      010      002

...etc...

So you can see how the numeric fields gradually increase one by one with multiple values of column3 inside a column2 and multiple values of column2 inside a column1. For example, I need to first select all the rows with "01, 010, 001" and then all the rows with "01, 010, 002", and then all the rows with "01, 010, 003", etc.

Obviously this will require repeated looping. Just not sure the best way to script this looping.

  • You've described in words what you want the output to look like but what should it look like in a picture like you have for the input? Also, those fields seem to all have strings rather than numbers. – PolyGeo Jun 15 '18 at 1:43
  • Those last 4 "questions" aren't really questions... just thought I'd indicate the direction I thought the script should go. Not helpful? OK, I'll delete them. – Waterman Jun 15 '18 at 1:44
  • Don't forget that we do not offer a script writing service. We are usually happy to help with where you are stuck on your scripts, but we want you tell us precisely what you are trying to do, and to illustrate precisely where you are stuck using a code snippet. – PolyGeo Jun 15 '18 at 1:46
  • Not seeking entire script writing. I really just added this question to illustrate why your logical suggestion of using the Summary Statistics tool wouldn't work. And yes, good call, in order to have the preceding zeroes the column values must actually be strings. – Waterman Jun 15 '18 at 1:51
  • This does not currently appear to be a question because there is no question mark anywhere within its body. If you have not reviewed them lately I think these two guidelines may be helpful to look at: gis.meta.stackexchange.com/a/3353/115 and gis.meta.stackexchange.com/a/4313/115 Asking clear questions and presenting concise code snippets are skills that will lead you to quick and clear answers here, and on other Stack Exchange sites. – PolyGeo Jun 15 '18 at 2:08
2

One possible (of many) solution would be to dissolve your dataset (A) by the three columns and save that as a separate dataset (B). Then, using a search cursor, for each row in dataset B, select the same rows in dataset A.

Doing it this way means you only need a single loop and it keeps things simple. However, if you have a very large dataset, dissolving can be quite an expensive operation.


Another possible method could be to create a column with the concatenation of the three columns. So, column 4 might look like this:

column1  column2  column3  column4
01      010      001       01010001
01      010      001       01010001
01      010      002       01010002
01      010      002       01010002
...

Then, using a search cursor, create a python set of all the possible values in column4.

Then, as in the previous solution, just iterate through the set and select the rows that way.

This is probably a faster method than dissolving. Note, sets are not ordered, so if required, you can create a sorted list of the set like this:

sorted(s)

where s is your set. Note, this will return a list.

  • Thanks for your answer. I like your second option best. – Waterman Sep 3 '18 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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