symbology of multiple features

[SusanneJoy]

I have a spreadsheet that I need to link with a map. No problem with that; however, I have multiple features for one location but only want to show one symbol, where the size varies based on number of features belonging to that location. Further, I want this to be represented as kind of pie charts, where each color represents a different attribute field (not necessarly numeric). Is this possible and how would I go about getting the features represented the way I envision? Thanks in advance!

[RichardFairhurst]

What feature type are you working with?  Point, Polyline or Polygon?  Is the spreadsheet being used as an XY layer?

First of all here is a decent youtube video showing the basic approach to configuring pie chart symbology.  Only numeric fields can be used, so non-numeric attribute have to be converted to a numeric value, like a count.  The assumption is that you have separate columns for each pie wedge and that the wedges are all in one row.  If your pie wedge values are currently in a single column with different values for each feature that you need counted, the Pivot Table tool (which requires an Advanced license) is the best tool to convert the one column into separate columns named for each of the values with a summary of the rows into a count, sum, min, max, or average.  The Pivot field would need to be something that would define each overlapping location, so that you could create a single feature at that location using a Dissovle tool using that field as the unique case field and then join the pivot table to the Dissolved feature to create the pie charts from the summary columns.  The Dissolve tool can also create the overall summary values using sums or the feature count that can set the overall pie size.

Getting that common value that defines the dissolve based on a common spatial location will depend on how you are linking your spreadsheet.  Are these multiple features in one location identical in shape?  If so, then creating that spatial relationship is easiest with a point.  For lines and polygons the point can be extracted as a Centroid that is inside the feature using several different approaches (I can go into that more after you tell me what feature type you are working with).  I collapse overlapping points with either the Collect Events tool, Dissolve tool or a python cursor script and use an XY coordinate concatenation string field as the location join field/pivot field.

[SusanneJoy]

Thanks for your response. I’ve attached an example of the spreadsheet I am using to populate the map. I can use it as an XY layer, or relate it to a new layer – whichever gets the best results. These will be represented as point features. You’ll see that there are a different number of records for each Location, and the point will need to be larger or smaller, based on the number of records for that location (not just x number of points in the same location). However, if you look at the Description and Contractor fields, you’ll notice that they also vary and there may be multiple different types per location. This is the part that I need color coded.

I know that a standard naming convention is imparative and I'm working on that now. But is there anything else I need to add and/or change to the spreadsheet to to get my desired results?

The Pivot Table tool looks promising – playing around with it now.

Edited to add attachment.

[RichardFairhurst]

I do not see any attachment.  Most likely the easiest is to use the spreadsheet as an X/Y layer.  Since that means you already have the X and Y coordinates, I assume that they are identical in each overlapping point.  If that is the case, then the creation of a concatenated field is easy.  In Excel you can create a concatenated XY coordinate column called something like X_Y_LINK with the expression like this where Row 1 has your column headers and column A has your X coordinate and column B has your Y coordinate:

CONCATENATE(A2,"/",B2)

Then you can copy and paste that formula for all rows with values, then copy the entire column and paste over it using the Paste Values options from the Paste Special menu (since formulas do not work in ArcGIS).

You can then use the Summary Statistics tool rather than the Dissolve tool to create a new table of just a single row for each location that contains the count of the location rows and any sums of numeric columns that you want to use to set your pie chart size.  The Unique Case fields will be the X_Y_LINK field and the X and Y column.  The summaries can be derived from any column values you like.  This will be your X/Y Event layer to create a single point that controls the pie chart.  You can also create sums of these summary columns if it makes sense to add them together to represent the entire pie size.

The Pivot table will use the original spreadsheet and use the X_Y_LINK field as the Pivot field.  Then you can choose any other field to create columns from the values in that field and can optionally count that field or sum another field that relates to those values.  The output can be joined to the Summary Statistics output after it has been converted to an X/Y event layer and then you can use those values as the pie chart wedge settings.  You may want to export the joined table to create a new table that contains all of the fields in a single table.  You can then join to multiple pivots and expand the number of columns that can make up your pie chart wedges.

Whether or not you can create a single map that combines all of these columns together to form wedges of a single pie chart depends on whether or not it makes sense to add them all together to form 100% of the objects in the pie.  If they are not really supposed to be added together, then you probably should create multiple map views with different pie charts set ups where the wedges add up to a whole set of related values for each location.