Method to create unique values for two related attributes and make two variables

[CPoynter]

Hi All,

The situation:

1 FC / 3 related columns / definitionQuery on one field has ' as part of field name for only some records which is causing problems

Column 1 = numeric code
Coumns 2 and 3 = text (both have troublesome ' when it comes to definition query)

I want to use Column 1 value that relates to Column 3, use Column 1 value for definition query to get around ' issues, but then need value of Column 3 for layout text element.

How do I get a unique listing of the related column 1 and 3 and then seperate their values for two subsequent operations?

I am thinking da.SearchCursor but unsure how to get fields in, make unique values and seperate values.

Thankyou in advance.

Using ArcGIS 10.1

Regards,

Craig

[ArkadiuszMatoszka]

Hi,

If I get it right, you want to get values from  Column 1 (Numeric) which corresponds to unique values from Column 3 (Text).
Assuming there can be more then one unique numeric values for each text value from Column 3 i I would suggest using dictionary with Column 3 value as key and list of Column 1 values as value. One important thing is that each value in Column 1 can correspond only to one value in Column 3.
Considering given assumptions code would be something like:

map_dict = {}
with arcpy.da.SearchCursor(fc, ['Colmun 3', 'Column 1'] as collector:
  for row in collector:
    if not row[0] in map_dict.keys():
      map_dict[row[0]] = []
    if not row[1] in map_dict[row[0]]:
      map_dict[row[0]].append(row[1])

Now you can use

'Column1 in (%s)' % map_dict[Column3value].join(', ')

to build definition query, and Column3 value in other places.

Hope it will help.
Regards
Arek

[CPoynter · Accepted solution]

Code is working nicely that Arek supplied, but I think I need to clarify a bit more.

Column1 / Column3

1000 : PlaceA

1000 : PlaceA

1005 : PlaceA

1005 : PlaceA

1009 : PlaceB

1009 : PlaceB

After running the dictionary code I found similar named places in Column 3 but with different codes in Column 1. This was something I wasn't aware of until now. I will need to distinguish between same name using different codes.

I could not get the original definition query code to work:

'fldCode in (%s)' % map_dict[str(fldName)].join(', ')

so substituted with:

queryStr = "\"" + fldCode + "\" = " + str(row[1])

My complete code follows, but needs further work to achieve my desired output.

import arcpy  fc = r"D:\Locations.gdb\Places" fldName = "Place_Name" # Column 3 = row[0] fldCode = "Place_Code" # Column 1 = row[1]  map_dict = {}  with arcpy.da.SearchCursor(fc, (fldName, fldCode)) as collector:     uniqueValues = sorted(set(collector))     if not row[0] in map_dict.keys():       map_dict[row[0]] = []     if not row[1] in map_dict[row[0]]:       map_dict[row[0]].append(row[1])      queryStr = "\"" + fldCode + "\" = " + str(row[1])      mxd = arcpy.mapping.MapDocument(r"D:\Places.mxd")      elm = arcpy.mapping.ListLayoutElements(mxd, "TEXT_ELEMENT")[0]     elm.text = "Place: " + row[0]      df = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]      for lyr in arcpy.mapping.ListLayers(mxd, "", df):              if lyr.name == "Places":                  if lyr.supports("DEFINITIONQUERY"):                     lyr.definitionQuery = queryStr      arcpy.RefreshActiveView()      arcpy.mapping.ExportToPDF(mxd, r"D:\Places\Output" + "\\" + row[0] + "_" + row[1] + ".pdf")   del mxd

I am trying to make map outputs that will only show places based on their unique code values.

I think I may have what I need now, but will notify forum if issues remaining.

Will investigate dictionaries further.

Regards,

Craig

[CPoynter]

Code works a treat. Arek's code for dictionaries was the clincher.

Regards,

Craig