In the table attached you can see the destination cities in 'Destination' field. Many of them are same - Werne Werne Werne.......
I want to create a script/model such that when run, it loops through all the rows with the same destination city (all the rows that have Werne for example) and then out of them selects the row that has the maximum value in the 'Commuters' field and then dissolves the destination city with the origin city ('Origin' field) for that row.
For example for Werne the row with the maximum value in the commuter field is the first row. So the tool should identify that and then dissolve Werne with Bergkamen in the map.
And then this should be done for every destination city. So basically the looping must happen only until the rows with same destination cities. Then after dissolving that city with its origin city a new loop should be created for the next destination city in the 'Destination' field.
I did notice two Gemeindes with some problems:
Alpen and Billerbeck link to themselves and have no connections I don't think this is correct...
For the rest connections can be made and will result in 40-45 merged polygons.
Should the process include anything with the attributes?
OK, I created some code that seems to yield the correct result, but it is pretty ugly and unreadable... The result look like this (thick black lines):
... and the attribute lists the Gemeindes that were used to create the merged polygons.
I have attached the resulting shapefile, so you can have a look and check the result.
The code used to create the output is listed below:
def main():
import arcpy
arcpy.env.overwriteOutput = True
# input fc
fc = r"C:\Forum\Gemeinde\NRW_Gemeinde_SpatiaJoin.shp"
fld_from = "GEN"
fld_to = "GEN_1"
fld_val = "Value"
# output field (will be added to input fc)
fld_out = "Gemeindes"
# output fc
fc_out = r"C:\Forum\Gemeinde\NRW_Gemeinde_diss01.shp"
# detemine the max commutors per from gemeinde
dct_max = {}
with arcpy.da.SearchCursor(fc, (fld_from, fld_to, fld_val)) as curs:
for row in curs:
from_g = row[0]
to_g = row[1]
val = row[2]
# check that from_Gemeinde is not equal to to_Gemeinde
if from_g != to_g:
if from_g in dct_max:
# check if value is higher
tpl = dct_max[from_g]
max_to_g = tpl[0]
max_val = tpl[1]
if val > max_val:
# update entry in dct
dct_max[from_g] = (to_g, val)
else:
# insert value
dct_max[from_g] = (to_g, val)
lst_init = []
for from_g, v in sorted(dct_max.items()):
to_g = v[0]
merged = False
for i in range(0, len(lst_init)):
lst = lst_init
if from_g in lst or to_g in lst:
# merge
lst.extend([from_g, to_g])
lst_init = lst
merged = True
if merged == False:
lst_init.append([from_g, to_g])
lst_init = [list(set(lst)) for lst in lst_init]
lst_all = []
for lst in lst_init:
for a in lst:
lst_all.append(a)
lst_all = list(set(lst_all))
dct = {}
for a in lst_all:
lst_ids = []
for i in range(0, len(lst_init)):
lst = lst_init
if a in lst:
lst_ids.append(i)
dct=lst_ids
lst_res = []
for gem, lst_ids in sorted(dct.items()):
# print gem, lst_ids
lst_ids2 = lst_ids
for ids in lst_ids:
lst = lst_init[ids]
for gem2 in lst:
ids = dct[gem2]
lst_ids2.extend(ids)
lst_ids2 = list(set(lst_ids2))
lst_res.append(lst_ids2)
lst_res = sorted(lst_res)
lst_res2 = []
res = []
for i in range(0, len(lst_res)):
prev_res = res
res = sorted(lst_res)
if res != prev_res:
lst_res2.append(res)
for i in range(len(lst_res2)-1, -1, -1):
res = lst_res2
for j in range(0, i+1):
res2 = lst_res2 = sorted(list(set(res)))
if i <> j:
lst_res2.pop(i)
lst_missing = []
for i in range(0, len(lst_init)):
bln_found = False
for lst in lst_res2:
if i in lst:
bln_found = True
break
if bln_found == False:
lst_missing.append(i)
lst_res2.append(lst_missing)
lst_fin = []
for res in sorted(lst_res2):
lst_elem = []
for ids in res:
lst_gem = lst_init[ids]
lst_elem.extend(lst_gem)
lst_fin.append(list(set(lst_elem)))
dct_fin = {}
for fin in lst_fin:
for g in fin:
dct_fin = ",".join(fin)
if len(arcpy.ListFields(fc, wild_card=fld_out)) == 0:
arcpy.AddField_management(in_table=fc, field_name=fld_out, field_type="TEXT", field_length=255)
flds = (fld_from, fld_out)
with arcpy.da.UpdateCursor(fc, flds) as curs:
for row in curs:
gem = row[0]
if gem in dct_fin:
row[1] = dct_fin[gem]
else:
row[1] = gem
curs.updateRow(row)
# dissolve gemeindes
arcpy.Dissolve_management(in_features=fc, out_feature_class=fc_out,
dissolve_field=fld_out, statistics_fields="",
multi_part="MULTI_PART")
if __name__ == '__main__':
main()
Hi, I tried to use your script above to make the connecting lines on my dataset. I got the lines but there are no arrows. Do I need to do something else? Here is the dataset - http://www.dropbox.com/sh/rj2lhcgfnjmlxgf/AACHHHXDRR_1NdtlD-BlZaQ_a?dl=0
