concatenate multi values in one record without duplication
- by mikehjun
I have a dbf table like below which is the result of one to many join from two tables. I want to have unique zone values from one Taxlot id field.
table name: input table
tid ----- zone
1 ------ A
1 ------ A
1 ------ B
1 ------ C
2 ------ D
2 ------ E
3 ------ C
Desirable output table
table name: input table
tid ----- zone
1 ------ A, B, C
2 ------ D, E
3 ------ C
I got some help but couldn't make it to work.
inputTbl = r"C:\temp\input.dbf"
taxIdZoningDict = {}
searchRows = gp.searchcursor(inputTbl)
searchRow = searchRows.next()
while searchRow:
if searchRow.TID in taxIdZoningDict:
taxIdZoningDict[searchRow.TID].add(searchRow.ZONE)
else:
taxIdZoningDict[searchRow.TID] = set() #a set prevents dulpicates!
taxIdZoningDict[searchRow.TID].add(searchRow.ZONE)
searchRow = searchRows.next()
outputTbl = r"C:\temp\output.dbf"
gp.CreateTable_management(r"C:\temp", "output.dbf")
gp.AddField_management(outputTbl, "TID", "LONG")
gp.AddField_management(outputTbl, "ZONES", "TEXT", "", "", "20")
tidList = taxIdZoningDict.keys()
tidList.sort() #sorts in ascending order
insertRows = gp.insertcursor(outputTbl)
for tid in tidList:
concatString = ""
for zone in taxIdZoningDict[tid]
concatString = concatString + zone + ","
insertRow = insertRows.newrow()
insertRow.TID = tid
insertRow.ZONES = concatString[:-1]
insertRows.insertrow(insertRow)
del insertRow
del insertRows
