topic Re: Script to contatenate the same field from several records in Python Questions

Script to contatenate the same field from several records

JoseSanchez — Fri, 26 Sep 2014 12:37:38 GMT

Hi all,

I am looking for a script, preferably a Python script that reads all the records from a file and concatenates them in a result variable.

I am using ArcMap to "Select By Attributes" and I would like to have a way to format a list of IDs I am importing form a text file, or Ms Excell list. To build a Definition Query in the Query Builder window.

Input: (from xxx.txt)

341440

341444

341521

341534

341580

341664

341783

Output: (to zzz.txt)

ID=’341440’ OR ID= ‘341444’ OR ID= ‘341521’ OR ID= ‘341534’ OR ID= ‘341580’ OR ID= ‘341664’ OR ID= ‘341783’

Thanks

Re: Script to contatenate the same field from several records

StevenGraf1 — Fri, 26 Sep 2014 12:47:16 GMT

So you have 1 field with numbers such as 341423 and you want it to be ID = 341423?

You could field calculate using python. This will calculate every record and add ID = in front of all the numbers. Be sure that your field is a String/Text field. ArcMap may import it in as an integer or double field. I would create a new field and field calculate that one so you don't accidentally type something in wrong.

'ID = ' + !field!

Re: Script to contatenate the same field from several records

JoseSanchez — Fri, 26 Sep 2014 12:51:10 GMT

how do you build the result string:

Result = "ID=’341440’ OR ID= ‘341444’ OR ID= ‘341521’ OR ID= ‘341534’ OR ID= ‘341580’ OR ID= ‘341664’ OR ID= ‘341783’"

Re: Script to contatenate the same field from several records

IanGrasshoff — Fri, 26 Sep 2014 13:50:57 GMT

In python, you can open and read/write a file using..

f = open('your file - full path here', 'r')

You can also open it for writing using..

f = open('your file', 'w')

f.write("your text")

For your situation you might want to do this in a script (I am just giving you the overview)

1. Open the File

2. Read each line and concatenate the results to build your select query

using this after you open it..

for line in f:

print line

3. Print the concatenated results or store the result in another file using f.write()

*Note, you might want to write this as a python function so you can pass in the file to read from and the column name to use for your result query. I am not sure exactly how you are trying to use this.

I suggest you read this...

7. Input and Output — Python 2.7.8 documentation

Re: Script to contatenate the same field from several records

JamesCrandall — Sat, 11 Dec 2021 20:13:25 GMT

Here's a way using NumPy:


import numpy as np


fout = open(r'H:\Documents\blah\output.txt', 'w')
values = np.genfromtxt(r'H:\Documents\blah\input.txt')
for val in values:
    writevalue = "ID='" + str(val) + "' OR "
    fout.write(writevalue)
    
fout.close()

Re: Script to contatenate the same field from several records

JamesCrandall — Sat, 11 Dec 2021 20:13:28 GMT

I tried to modify my original post but it won't let me because GeoNet is not a real forum.

Anyway... I noticed that the values will come convert over as decimal, which you may not want. So, simply modify the genfromtxt() method to include the output format as strings.


values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')

Here's the updated code:


import numpy as np


fout = open(r'H:\Documents\blah\output.txt', 'w')
values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')
for val in values:
    writevalue = "ID='" + str(val) + "' OR "
    fout.write(writevalue)
    print writevalue

fout.close()

Re: Script to contatenate the same field from several records

JamesCrandall — Fri, 26 Sep 2014 15:42:04 GMT

Let's get a bit more pythonic and into just 4 lines:

fout = open(r'H:\Documents\blah\output.txt', 'w')
values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')
fout.writelines("ID='" + str(val) + "' OR " for val in values)
fout.close()

Re: Script to contatenate the same field from several records

curtvprice — Fri, 26 Sep 2014 16:54:18 GMT

Here's how I do it. I have a function as this something I find myself doing a lot! This function works in 10.0 and 10.1. It reads a table, not a text file. (If you add a field name to the top of your text file, and name the file with a .csv or .txt file extension you can read it directly as a table in ArcGIS.)

def ListUnique(inTable, Field):
    """Create a list of unique values from a table/tableview.

    arguments

    inTable  Table or table view
    Field    Field name
    """
    try:
        try:
            # this will only work for 10.1
            import arcpy.da
            lstValues = [r[0] for r in
                    arcpy.da.SearchCursor(inTable, Field)]
        except ImportError:
            # 10.0 version
            try:
                Rows = arcpy.SearchCursor(inTable, "", "", Field, Field)
                Row = Rows.next()
                lstValues = []
                while Row:
                    lstValues.append(Row.getValue(Field))
                    Row = Rows.next()
            except:
                raise
            finally:
                del Row, Rows
        # unique-ize and sort the list
        lstValues = sorted(set(lstValues))
        return lstValues
    except Exception as msg:
        raise Exception("ListUnique failed\n" + str(msg))

Once you have the list of values, you can make this into a SQL expression from a list like this:

numlist = ",".join(str(L))
strlist = ",".join(["'{}'".format(vv) for vv in L]) # need quotes for string values
where = '"{}" IN ({})'.format(field, numlist) # "FIELD" IN (1,2,3)

James wanted me to use the FIELD = 1 OR FIELD = 2 syntax, so here's a way to do that (this replaces line 03 above):

where = " OR ".join(['"{}" = {}'.format(field, k) for k in strlist])

Re: Script to contatenate the same field from several records

Zeke — Fri, 26 Sep 2014 17:28:39 GMT

Even more pythonic! Except I don't know how to format as code on the new forums here...

with open(r'H:\Documents\blah\'output.txt', 'w') as fout:
values = np.genfromtxt(r'H:\Documents\blah\input.txt',dtype='str')
fout writelines("ID='" + str(val) + "' OR " for val in values)

Also, for your output, rather than a bunch of OR clauses, you could make a python list and test using IN:

ID IN ['34012', 35078', '39222'...]

Re: Script to contatenate the same field from several records

curtvprice — Fri, 26 Sep 2014 17:33:30 GMT

see Posting Code blocks in the new GeoNet‌

Re: Script to contatenate the same field from several records

Zeke — Fri, 26 Sep 2014 21:25:02 GMT

Thanks Curtis! I saw the >> icon, but hovering over it said Insert, which is not quite as intuitive as something like Format.

Re: Script to contatenate the same field from several records

JamesCrandall — Mon, 29 Sep 2014 18:02:19 GMT

Keep in mind that the "IN" statement does have limits (depending upon database). I think Oracle limit is 1000? Not exactly sure but I recently had to break up my list of values that built my IN portion of a WHERE clause.

Re: Script to contatenate the same field from several records

curtvprice — Mon, 29 Sep 2014 22:32:10 GMT

I am not very familiar with "real" RDBMS's so I'm wondering what string length fails if you use the more basic form

FIELD = 1 OR FIELD = 2 or FIELD = 3 ...

For 1000 fields that is at least 14K of text!

I would guess that RDBMS would not support indexes when you use the IN operator - but in theory that wouldn't be a problem with many in the list because IN would parallelize the comparisons (ie all comparisons would be done on each record, in one pass).

Re: Script to contatenate the same field from several records

JamesCrandall — Tue, 30 Sep 2014 12:38:02 GMT

Curtis,

In RDBMS-landia, if I have >1000 values I'd probably insert them into a temporary table of the values for my IN operator and setup a JOIN to the main table I am interested in querying. I know that doesn't specifically address your post but is likely the best solution. Or if I were in SQL Server I'd likely opt for the Table variable instead of a Temp Table.

Re: Script to contatenate the same field from several records

curtvprice — Wed, 01 Oct 2014 00:52:27 GMT

Yeah - I've heard this type of join to select rows called a "filter".

There used be an awesome ARCPLOT command that I still miss. (Those were the days.)

reselect <dataset> <dstype> keyfile <table> <field> keyitem <key_field>

I've tried to reproduce this functionality using layers and the Add Join tool but found it slow and unreliable.

Maybe the Make Query tool... or a python solution using sets....