I am kinda new to Python and arcpy and have done some basic online courses on this. So I do have an idea how these scripts are structured/written and how they work. Now I have to perform a seemingly complicated analysis for my master thesis which goes something like this:

I have an excel file in matrix form of around 400 polygon features (municipalities). Each municipality has a relationship with others around it (no. of people commuting daily between the municipalities). I have to somehow make a script/model which dissolves each municipality with only that municipality (out of 400) which has the highest no. of commuters travelling towards it (not from it). This might make one municipality dissolve with multiple municipalities also which is fine.

Anything like this has been done before? I'm thinking about the script and I can't think how I make this selective dissolve?

The excel table.

This is how the table looks like its not really a matrix but I guess it can be converted into one like in mentioned by Richard. The first two columns are origin and destination IDs and the last column is the no. of commuters.

  • You should only get ~400 merged shapefiles, one for each municipality, at least based on your described process. That's still a lot, but not unmanageable.
    – Erica
    Jun 1 '15 at 12:12
  • You are right. I'm sorry I actually meant I'll get a lot of features in total (more than 400) because some features might be present in more than one merged shapefiles.
    – Salman
    Jun 1 '15 at 13:50
  • 1
    how is the relationship of commuters between municipalities defined? it seems to me that if this is a number given in an attribute column, could you run a loop to find the municipality with the highest number of commuters and run the merge tool based on that result?
    – JasonBK
    Jun 1 '15 at 14:11
  • I am unclear what attributes the merged shape should have. Will it have the attributes of the two Cities combined somehow? Will each shape have a destination ID and one or more origin IDs? If you merge the origin/destination shapes into one shape you will have 400 shapes in the end, not more, unless you expect two or more cities to have the same number of commuters to a given city and do not merge the shapes. Jun 1 '15 at 14:16
  • @RichardFairhurst Yes you are correct the shapes will be 400 but there will be features that will be included in more than one shape.
    – Salman
    Jun 1 '15 at 15:28

Here is one way to set up the City Matrix in Excel so that you can get the Destination City row to report the Origin City with the maximum trip rate.

Set Up In Excel to Find Max Origin City for Each Destination City

The formula for column G is simply:

=Max(B3:F3) for cell G3 and copied down.

Column H is a number for the number of rows down from the current row to find the City name at the bottom. In the case of a 400x400 matrix it would go from 401 to 2.

The formula for column I is only slightly more complex:

=HLOOKUP(G3,B3:F$8,H3,FALSE) for cell I3 and then copied down.

You would of course have to adjust the formula values to match your larger matrix set up.

Once you had your Cities listed, you could copy onto a new sheet just the Destination City Names and the associated Origin City Names using the Copy Values method to make it easier to import the essential spreadsheet for matching the shapes into ArcMap.

  • Thanks this is very helpful. But the problem is more about ArcMap I think. I only want to dissolve municipalities that are CONNECTED spatially. So I would only consider the maximum commuter flow from the connected municipalities. How do I see which municipalities are connected to each municipality? I can use the select by location tool but then I would have to do that 400 times.
    – Salman
    Jun 2 '15 at 10:31
  • I just found out that I have 55000 rows and I cannot make a matrix because I will then have 55000 columns whereas excel limit is 16384 columns only.
    – Salman
    Jun 2 '15 at 12:27
  • You said nothing about spatially connected municipalities as far as I saw. In any case, the matrix for the set of spatially connected municipalities will now come from the Spatial Join tool. You would join the layer to itself with the One to Many option and then eliminate the set of features where the city joined to itself. Once you see the output of the Spatial Join tool you will already have all of the possible the origin/destination cities in the record set. From there you can assign the commuters for each origin city and find the max in Excel or with Summary Statistics. Jun 2 '15 at 13:59
  • Once you have the set of origin/destination cities created by the Spatial Join tool they will have the pair of City IDs in the output of each feature created, just like your table. You cannot directly join on two fields, but you can use the tool I created to create a single Case_ID field to represent the values of the field pair between your table above and the Spatial Join output. See this thread for the Multiple Field Key to Single Field Key tool. geonet.esri.com/message/517458#517458 Jun 2 '15 at 14:06
  • The 55000 rows would not create a 55000x55000 matrix, because those rows do not represent the set of UNIQUE City names. To convert it to a true matrix you would need to get the set of unique values in the first column to become the rows and the set of unique values in the second column to become columns. So 55000 records is probably closer to a matrix of 235 unique cities (the square root of 55000). But since the matrix transposition process would not be easy and you don't need a true matrix, just the result of the Spatial Join tool, you can use your table exactly like it is. Jun 2 '15 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.