topic Re: How to create a new field with a combination based on another field with arcpy? in Python Questions

How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Tue, 23 Sep 2014 13:18:40 GMT

I like to create a new txt which has two columns: Id and GRIDCODE.

This is the original Table:

The Id column of the new txt should have just unique values. I’m able to do this with the following code. But the GRIDCODE -column of the new txt should have the possible combinations of one equal Id, for example:

The new Id field 47973 should have the new GRIDCODE field 7, 10

or the new Id field 47990 should have the new GRIDCODE field 4, 7.

To clarify the question I build an example-table with excel, this should be the result I like to write in the new txt. Do I have to work with an update cursor and if, else? What about the comma, how is it possible to use them without getting a new column but the combination in a column?

I would be very thankful to get a tip how I can go on with my code.

import arcpy
import os
from arcpy import env

env.overwriteOutput = True
env.workspace = r"D:\Users\ju\ers\cities_UA\resultfolder"

DataDict = {}
inputshp = r"D:\Users\ju\ers\cities_UA\resultfolder\innsbruckauswahl.shp"
outputfile = r"D:\Users\ju\ers\cities_UA\resultfolder\inns_test.txt"
f = open (outputfile, 'w')
f.write ("ID,Gridcode,\n")
f.close ()

#f = open (outputfile, 'a')

ID=[row[0] for row in arcpy.da.SearchCursor(inputshp, ["Id", "GRIDCODE"])]
#GRIDCODE = [row[0] for row in arcpy.da.SearchCursor(inputshp, ["GRIDCODE"])]
uniqueID = set(ID)
for ID in uniqueID:
print ID

# f.write((str(ID)) + "," + (str(GRIDCODE))+ "\n")
#f.close()

Re: How to create a new field with a combination based on another field with arcpy?

RichardFairhurst — Sat, 11 Dec 2021 19:29:23 GMT

The code to get the unique list of ID values and a concatenated list of GRIDCODE values is done with the search cursor outputting to a dictionary. The dictionary key makes sure that you will get a unique list of ID values, If you need the values sorted, then the dictionary does not do that unless you put it through a secondary process.


# get a dictionary of unique ID value keys and each key's unique gridcodes


valueDict = {}


with arcpy.da.SearchCursor(inputshp,  ["Id", "GRIDCODE"]) as searchRows:    


     for searchRow in searchRows:    


          keyValue = searchRow[0]


          gridcode = searchRow[1]       


          if not keyValue in valueDict:    


               valueDict[keyValue] = [gridcode]


          elif not gridcode in valueDict[keyValue]:



               valueDict[keyValue].append(gridcode)





# sort both the ID value keys and the gridcodes which are converted to a string list


for keyValue in sorted(valueDict.keys()):


     items = ""


     for item in sorted(valueDict[keyValue]):


          if items == "":


               items = str(item)


          else:


               items = items + ", " + str(item)


     # write to text file with the gridcode list enclosed in double quotes


     f.write(str(keyValue) + ',"' + items + '"\n' )

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Tue, 23 Sep 2014 15:12:18 GMT

Thank you for this code!!!

I will try it with other datasets tomorrow. THANKS

Re: How to create a new field with a combination based on another field with arcpy?

RichardFairhurst — Tue, 23 Sep 2014 15:19:03 GMT

The code is untested, so if it throws an error it is probably because I missed some minor syntax requirement. The overall logic structure should be correct. I drew upon this stackoverflow post and this Geonet post to come up with this code. It assumed that both the IDs and the listed gridcodes for each ID should be unique and sorted. If you wanted only the IDs unique and sorted and the listed items to allow repeated values or to be in their original order, the code would have to be modified slightly.

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Wed, 24 Sep 2014 08:17:21 GMT

The code is working without any syntax errors (first there was a little thing at line 17, where you already add a double ==). The gridcode should not be unique and sorted, just the combination of an equal ID.

Thanks a lot!

Re: How to create a new field with a combination based on another field with arcpy?

RichardFairhurst — Wed, 24 Sep 2014 15:56:22 GMT

To eliminate the steps that make the gridcode list unique and sorted the following code, which changes line 9 to eliminate the unique gridcode process and line 15 to eliminate the sorted gridcode process, would work. The string list of gridcodes could technically be built in lines 8-10 allowing lines 14-19 to be eliminated with a minor tweak to line 21 if you don't need them sorted, but this structure makes it easy to add back the processes that make the list either unique or sorted. The previous code would make the gridcode list for ID 47991 be "3, 7, 9", while this code will make ID 47991 have the list "9, 7, 3".

# get a dictionary of unique ID value keys and each key's gridcodes  
valueDict = {}  
with arcpy.da.SearchCursor(inputshp,  ["Id", "GRIDCODE"]) as searchRows:      
    for searchRow in searchRows:      
          keyValue = searchRow[0]  
          gridcode = searchRow[1]        
          if not keyValue in valueDict:      
              valueDict[keyValue] = [gridcode]  
          else:  
              valueDict[keyValue].append(gridcode)  
  
# sort the ID value keys and convert the gridcodes to a string list  
for keyValue in sorted(valueDict.keys()):  
    items = ""  
    for item in valueDict[keyValue]:  
          if items == "":  
              items = str(item)  
          else:  
              items = items + ", " + str(item)  
    # write to text file with the gridcode list enclosed in double quotes  
    f.write(str(keyValue) + ',"' + items + '"\n' )

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Thu, 25 Sep 2014 07:47:26 GMT

That is so perfect, exactly what I need. Thank you so, so much!

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Thu, 02 Oct 2014 10:09:58 GMT

Richard Fairhurst

Hi Richard,

I like to ask you kindly how I can write more than one combination to the new txt. To make a better analysis I created new classifications and now I like to add there combinations to the txt. Therefore I copied your code two times and filled the new fieldname to the searchcursor and gave new names, the keyValue is always the same. But I have problems with the lines:

for keyValue in sortedDict:

f.write(str(keyValue) + ',"' + sortedDict[keyValue] + '"\n' )

f.close()

I tried this, but it isn’t working.

for keyValue in [sortedDict, valueDictA, valueDictB]:

f.write(str(keyValue) + ',"' + sortedDict[keyValue] + ',"' + sortedDictA[keyValue]+ ',"' + sortedDictB[keyValue]+'"\n' )

f.close()

At the momant I'm doing it 3 times, so not a nice solution... And than joins

Re: How to create a new field with a combination based on another field with arcpy?

RichardFairhurst — Thu, 02 Oct 2014 13:05:11 GMT

Jutta:

The code you wrote makes no sense to me. I would have to restructure the code from the beginning and not at the stage you have rewritten it to deal with 3 fields. If this is all coming from one record the code probably can be written using one dictionary.

I need to see an example of the data and the result you really want. Is this data all in one table? Are they from separate tables? Are there Nulls that have to be excluded?

The error is also dependent on the code your wrote that populates the dictionaries. I also don't think you can write a for loop the way you have written it. You cannot extract keys from 3 dictionaries at once. You have to get the keys from just one dictionary at a time and verify they are in the other dictionaries. Also, the dictionaries in the for statement do not match the dictionaries in the string concatenation statement.

So give me a real example of what the input data would look like and what you want the output to look like for value 47991.

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Mon, 06 Oct 2014 12:53:13 GMT

Sorry, for disturbing you, I will just do it three times. And in the end I will write it to one table. I’m not working for a long time with arcpy, but this is fine so. Thanks again for your code.

Re: How to create a new field with a combination based on another field with arcpy?

RichardFairhurst — Mon, 06 Oct 2014 13:12:12 GMT

I have no problem restructuring the code, but I need an example of what the input and output would look like to understand what you are doing. The code portion you provided assumed I knew what had happened before it and I didn't. I want to adapt the code. I have already rewritten it twice to adapt it to my own data and needs with multiple fields and reduced the processing time down by 4 times. Ultimately the more I do it the more likely I can create a generic structure that can be quickly adapted to many, many problems like this.

So I am only bothered if you raise the issue and don't share the example data.

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Tue, 07 Oct 2014 10:50:50 GMT

Richard Fairhurst

Ok, thank you. The problem was that I should create the combination not just for the field "GRIDCODE" because now there a tow more codes and I need also their combinations ("GRIDCODE2", "GRIDCODE3") .

But this is not the only thing I have to do: There is the field "area" and I have to get the sum from this field for the combination and also the percent of the area for one combination. I did this basically with arcpy.Statistics_analysis and arcpy.AddJoin_management. The code is very long at the moment...

The big goal is one table (txt or csv) with the first 40 rows from each combination sorted downward including the filename in a column, the combinationname, the percent, area and the frequency (like the table below).

I will try to write the output from all tables which are in the list among each other and because of the created codenamefield and the field with the name from the original shp it is comprehensible.

The huge problem is that this is the first big thing I'm doing with arcgis and arcpy. So I guess the code is not the fasted way to the goal, but it is nearly working... The other thing making my process a little bit hard is that the purpose for my result is chancing respectively is still in discussion.

My actual problem is to get the three result tables for one round in the loop to a big one without losing the combinations.

Here is one of the result-tables which should get to the big goal:

At the moment the txt produces a combi-field with the type double but I need a text-field otherwise the combination got lost like here:

So to go back to the opening question I copied your code three times and therefore I got the three combinations. So the question is a kind of answered, I guess there is a faster way. At the moment I don’t know how to get my big table including the combinations. Should I ask this in a new post?

import arcpy

import os, sys

from arcpy import env

env.overwriteOutput = True

env.workspace = r"D:\Users\julia\erste_aufg\cities_UA"

env.qualifiedFieldNames = False

fcResult = arcpy.ListFeatureClasses("*_result.shp")

for shpresult in fcResult:

shpresultName = os.path.splitext (shpresult) [0]

print shpresultName

outputfile = env.workspace + "\\" + shpresultName + "0_.txt"

f = open (outputfile, 'w')

f.write ("ID,Combi,\n")

f.close ()

f = open (outputfile, 'a')

valueDict = {}

with arcpy.da.SearchCursor(shpresult, ["Id", "GRIDCODE"]) as searchRows:

for searchRow in searchRows:

keyValue = searchRow[0]

gridcode = searchRow[1]

if not keyValue in valueDict:

valueDict[keyValue] = [gridcode]

elif not gridcode in valueDict[keyValue]:

valueDict[keyValue].append(gridcode)

# sort both the ID value keys and the gridcodes which are converted to a string list

sortedDict = {}

for keyValue in sorted(valueDict.keys()):

items = ""

for item in sorted(valueDict[keyValue]):

if items == "":

items = str(item)

else:

items = items + ", " + str(item)

sortedDict[keyValue] = items

# write to text file with the code list enclosed inside double quotes.

for keyValue in sortedDict:

f.write(str(keyValue) + ',"' + sortedDict[keyValue] + '"\n' )

f.close()

#get combi2

outputfile2 = env.workspace + "\\" + shpresultName + "2_.txt"

f = open (outputfile2, 'w')

f.write ("ID,Combi,\n")

f.close ()

f = open (outputfile2, 'a')

valueDict = {}

with arcpy.da.SearchCursor(shpresult, ["Id", "GRIDCODE2"]) as searchRows:

for searchRow in searchRows:

keyValue = searchRow[0]

gridcode = searchRow[1]

if not keyValue in valueDict:

valueDict[keyValue] = [gridcode]

elif not gridcode in valueDict[keyValue]:

valueDict[keyValue].append(gridcode)

# sort both the ID value keys and the gridcodes which are converted to a string list

sortedDict = {}

for keyValue in sorted(valueDict.keys()):

items = ""

for item in sorted(valueDict[keyValue]):

if items == "":

items = str(item)

else:

items = items + ", " + str(item)

sortedDict[keyValue] = items

# write to text file with the code list enclosed inside double quotes.

for keyValue in sortedDict:

f.write(str(keyValue) + ',"' + sortedDict[keyValue] + '"\n' )

f.close()

#get combi3

outputfile3 = env.workspace + "\\" + shpresultName + "3_.txt"

f = open (outputfile3, 'w')

f.write ("ID,Combi,\n")

f.close ()

f = open (outputfile3, 'a')

valueDict = {}

with arcpy.da.SearchCursor(shpresult, ["Id", "GRIDCODE3"]) as searchRows:

for searchRow in searchRows:

keyValue = searchRow[0]

gridcode = searchRow[1]

if not keyValue in valueDict:

valueDict[keyValue] = [gridcode]

elif not gridcode in valueDict[keyValue]:

valueDict[keyValue].append(gridcode)

# sort both the ID value keys and the gridcodes which are converted to a string list

sortedDict = {}

for keyValue in sorted(valueDict.keys()):

items = ""

for item in sorted(valueDict[keyValue]):

if items == "":

items = str(item)

else:

items = items + ", " + str(item)

sortedDict[keyValue] = items

# write to text file with the code list enclosed inside double quotes.

for keyValue in sortedDict:

f.write(str(keyValue) + ',"' + sortedDict[keyValue] + '"\n' )

f.close()

#Execute TableToDBASE 3times

arcpy.TableToDBASE_conversion(outputfile, env.workspace)

arcpy.MakeTableView_management (outputfile, "outputfile_ly")

arcpy.TableToDBASE_conversion(outputfile2, env.workspace)

arcpy.MakeTableView_management (outputfile2, "outputfile2_ly")

arcpy.TableToDBASE_conversion(outputfile3, env.workspace)

arcpy.MakeTableView_management (outputfile3, "outputfile3_ly")

# get the percent of the sum area for each combi

outtable= shpresultName +"_outtable.dbf"

arcpy.Statistics_analysis(shpresult, outtable, [["area", "SUM"]], "Id")

outtableSUM= shpresultName + "_outtableSUM.dbf"

arcpy.Statistics_analysis(outtable, outtableSUM, [["SUM_area", "SUM"]])

with arcpy.da.SearchCursor(outtableSUM, "SUM_SUM_ar") as cursor:

for row in cursor:

print (int(row[0]))

fieldsum = (int(row[0]))

print fieldsum

arcpy.AddField_management(outtable, "PERCENT", "DOUBLE")

arcpy.CalculateField_management(outtable, "PERCENT", "([SUM_area]*100)/{}".format(fieldsum) , "VB")

arcpy.MakeTableView_management(outtable, "outtable_ly")

#add Join

arcpy.AddJoin_management ("outtable_ly", "Id", "outputfile_ly", "ID","KEEP_ALL")

outJoin= shpresultName +"_outJoin.dbf"

arcpy.CopyRows_management ("outtable_ly", outJoin)

# sum percent for unique combi

joinSum = shpresultName + "_joinSum.dbf"

arcpy.Statistics_analysis (outJoin, joinSum, [["PERCENT", "SUM"],["SUM_area", "SUM"]], "Combi")

inputtable= shpresultName +"_joinSum.dbf"

arcpy.MakeTableView_management (inputtable, "inputtable_ly")

outsort = shpresultName + "sort_sorted.dbf"

arcpy.Sort_management("inputtable_ly", outsort, [["SUM_PERCEN", "DESCENDING"]])

# new fields and create the name+codename

arcpy.AddField_management(outsort, "NAME", "TEXT")

arcpy.AddField_management(outsort, "CODE", "TEXT")

fields = ["NAME", "CODE"]

with arcpy.da.UpdateCursor(outsort, fields) as cursor:

for row in cursor:

row[0] = shpresultName

row[1] = "CODE1"

cursor.updateRow(row)

# get the percent: Combi2

outtable= shpresultName +"_outtable.dbf"

arcpy.Statistics_analysis(shpresult, outtable, [["area", "SUM"]], "Id")

outtableSUM= shpresultName + "_outtableSUM.dbf"

arcpy.Statistics_analysis(outtable, outtableSUM, [["SUM_area", "SUM"]])

with arcpy.da.SearchCursor(outtableSUM, "SUM_SUM_ar") as cursor:

for row in cursor:

print (int(row[0]))

fieldsum = (int(row[0]))

print fieldsum

arcpy.AddField_management(outtable, "PERCENT", "DOUBLE")

arcpy.CalculateField_management(outtable, "PERCENT", "([SUM_area]*100)/{}".format(fieldsum) , "VB")

arcpy.MakeTableView_management(outtable, "outtable_ly")

#add Join

arcpy.AddJoin_management ("outtable_ly", "Id", "outputfile2_ly", "ID","KEEP_ALL")

outJoin2= shpresultName +"_outJoin2.dbf"

arcpy.CopyRows_management ("outtable_ly", outJoin2)

# sum percent for unique combi

joinSum2 = shpresultName + "_joinSum2.dbf"

arcpy.Statistics_analysis (outJoin2, joinSum2, [["PERCENT", "SUM"],["SUM_area", "SUM"]], "Combi")

inputtable2= shpresultName +"_joinSum2.dbf"

arcpy.MakeTableView_management (inputtable2, "inputtable2_ly")

outsort2 = shpresultName + "sort_sorted2.dbf"

arcpy.Sort_management("inputtable2_ly", outsort2, [["SUM_PERCEN", "DESCENDING"]])

# new fields and create the name+codename

arcpy.AddField_management(outsort2, "NAME", "TEXT")

arcpy.AddField_management(outsort2, "CODE", "TEXT")

fields = ["NAME", "CODE"]

with arcpy.da.UpdateCursor(outsort2, fields) as cursor:

for row in cursor:

row[0] = shpresultName

row[1] = "CODE2"

cursor.updateRow(row)

# get the percent: Combi3

outtable= shpresultName +"_outtable.dbf"

arcpy.Statistics_analysis(shpresult, outtable, [["area", "SUM"]], "Id")

outtableSUM= shpresultName + "_outtableSUM.dbf"

arcpy.Statistics_analysis(outtable, outtableSUM, [["SUM_area", "SUM"]])

with arcpy.da.SearchCursor(outtableSUM, "SUM_SUM_ar") as cursor:

for row in cursor:

print (int(row[0]))

fieldsum = (int(row[0]))

print fieldsum

arcpy.AddField_management(outtable, "PERCENT", "DOUBLE")

arcpy.CalculateField_management(outtable, "PERCENT", "([SUM_area]*100)/{}".format(fieldsum) , "VB")

arcpy.MakeTableView_management(outtable, "outtable_ly")

#add Join

arcpy.AddJoin_management ("outtable_ly", "Id", "outputfile3_ly", "ID","KEEP_ALL")

outJoin3= shpresultName +"_outJoin3.dbf"

arcpy.CopyRows_management ("outtable_ly", outJoin3)

# sum percent for unique combi

joinSum3 = shpresultName + "_joinSum3.dbf"

arcpy.Statistics_analysis (outJoin3, joinSum3, [["PERCENT", "SUM"],["SUM_area", "SUM"]], "Combi")

inputtable3= shpresultName +"_joinSum3.dbf"

arcpy.MakeTableView_management (inputtable3, "inputtable3_ly")

outsort3 = shpresultName + "sort_sorted3.dbf"

arcpy.Sort_management("inputtable3_ly", outsort3, [["SUM_PERCEN", "DESCENDING"]])

# new fields and create the name+codename

arcpy.AddField_management(outsort3, "NAME", "TEXT")

arcpy.AddField_management(outsort3, "CODE", "TEXT")

fields = ["NAME", "CODE"]

with arcpy.da.UpdateCursor(outsort3, fields) as cursor:

for row in cursor:

row[0] = shpresultName

row[1] = "CODE3"

cursor.updateRow(row)

#new folder

# Set local variables

out_folder_path = env.workspace

out_name = "resultfolder"

# Execute CreateFolder

arcpy.CreateFolder_management(out_folder_path, out_name)

newpath= env.workspace + "\\" + out_name

resulttxt = newpath + "\\" + "resultSUM.txt"

f = open (resulttxt, 'w')

f.write ("Combi,FREQUENCY,SUM_PERCEN,SUM_SUM_ar,NAME,CODE,\n")

f.close ()

f = open (resulttxt, 'a')

fc_tables = arcpy.ListTables("*sort_sort*")

for tableR in fc_tables:

fieldsSe= ["Combi","FREQUENCY" ,"SUM_PERCEN" ,"SUM_SUM_ar", "NAME", "CODE"]

with arcpy.da.SearchCursor(tableR, fieldsSe, """"OID" < 4""") as sCursor:

for row in sCursor:

print row [0], row [1],row [2],row [3],row [4],row [5]

f.write(str(row[0]) + "," + str(row[1])+ "," +str(row[2]) + "," + str(row[3])+ "," +str(row[4])+ "," +str(row[5]) + "\n")

f.close()

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Tue, 07 Oct 2014 11:20:00 GMT

Richard Fairhurst‌

I mean with the faster way just that I guess my code is not perfect, I’m totally fine with your solution and you helped me a lot with that.

Re: How to create a new field with a combination based on another field with arcpy?

JuttaSchiller — Tue, 07 Oct 2014 11:59:59 GMT

with arcpy.Merge_management I'm able to create the big table.