0

I have a map to which I added a zip code layer and another layer which contains values assigned to every square kilometer within each zip code. I'm trying to find out how many different values there are in each zip code and what they are.

So for example, if a zip code has 10 values assigned to it in total, 5 of them being 1.5, 3 of them being 2.1 and the remaining two being 6.4, this zip code basically has 3 different values, namely 1.5, 2.1 and 6.4. That is what I want to know. Is there a way to do this without manually checking all the values in every zip code?


This is a screenshot of my data. The value that I'm working with is under the column BI. I need a table which gives me a list of the long/lat in every zip code and the corresponding values. I can't do anything on excel yet since I don't know which lat/long goes with which zip code.

data spreadsheet

  • It sounds like you'll want to use Spatial Join. – Paul Jun 4 '14 at 13:43
  • @Paul, I did use a join for the two layers, but it only gives me a table with the avg, min, max, total count etc. I don't need that. It'll even help if it would give me a list of all the values but I don't know how to get that. – V_N Jun 4 '14 at 14:06
  • @V_N Once you do your Spatial Join, you could export your Attribute Table to a dbf and open it in Excel to do the analysis you need to do. – Sara Barnes Jun 4 '14 at 14:51
  • @V_N Also please go back to the two previous questions you asked and mark them as answered if any of those questions helped you. You can also post follow ups there rather than posting a new question each time. If you're not familiar with how this site works, you can read about it on the help pages. – Sara Barnes Jun 4 '14 at 14:53
  • @V_N One more thing - when you mention that having a list of all of the values would be helpful, that's what the Attribute Table is. You right click on your layer and choose Attribute Table to view it. If you export it and bring it into Excel like I mentioned, you can do something like this to get what you are looking for: office.microsoft.com/en-us/excel-help/… – Sara Barnes Jun 4 '14 at 15:06
3

The solution to your question is the Frequency tool, but you need to do some data prep work before using it. Note this data prep work (steps 1-6) also needs to be done for your other statistics by area questions like the min-max question.

  1. Create a file geodatabase to work from.
  2. In ArcMap, find and run the Make XY Event Layer using either the Search button or going through the System Toolbox > Data Management > Layers and Table views.
  3. To specify the table, click the folder button and browse to your Excel file and then the appropriate worksheet. Set the X field to your lon column, the Y field to the lat column, and give the output layer a name. Note this layer is temporary. For spatial reference you can select the appropriate Geographic Coordinate System if you know what your Lat/Lon values were derived from. A common assumed CRS is WGS84. Since your coordinates only go out to two decimal places, it shouldn't much matter which you use as long as it's Geographic and appropriate to the area (looks like West Virginia based on your coordinates). XY dialog
  4. Once it has created the points you must export them to make them permanent and do further work with them. Right-click the layer in the Table of Contents (ToC) and choose Data > Export Data. Specify a feature class name (data_points) in the geodatabase you created in step 1. You could also just export it to a shapefile if you want. Once you've done this you can remove the temporary event layer from the ToC - you won't need it anymore.
  5. Add your zip code polygons to the map. If using the geodatabase you may want to import them to that to keep everything together. If you do not have polygons, you can try getting them here as suggested at one of your other questions. You must have polygons for this method to work. You also need to check that your points and your polygons are lining up in the right areas - if not, you'll have some coordinate system work to do.
  6. Run the Intersect tool and use your data_points feature class and zip code polygons as the inputs. Other than specifying an output feature class (data_points_located), you don't need to set any other options though you may want to depending on which attributes from the two files you want to keep and how close the points are to the zip boundaries. The only critical fields are going to be GRID (assuming that's a unique point identifier), Bl (your value field), and zip code (your statistical grouping field). The result should be a new point feature class with those critical fields as attributes.
  7. Run the Frequency tool with your new data_points_located feature class as input and select both your Bl and zip code fields as the Fequency Fields. You can leave the summary field blank, as this just adds up the total value of each frequency (ie if 2 occurs 5 times in the 55555 zip code, the summary field would have a value of 10). Frequency Dialog The output should be a table with a list of zip codes (multiple records for each zip), the values occurring in each (one record for each value in each zip), and the count of those occurences (value x occurs y times in zip code n).

Be aware that Frequency requires an Advanced/Info license. If you do not have access to this, @SaraBarnes method of exporting your data_points_located attributes to a new table and opening it in Excel to use a Frequency analysis there may be a viable alternative.

  • I've tried everything and it's not quite working. I think the problem is correlating lat long with zip codes. My data is given according to lat & long. I would like to group them by zip code. I tried a few different spatial joins but it didn't do what I wanted it to do, the closest I came was that it gave me a table with the zip codes and the value at the middle of every zip code. I don't think the software is being able to correlate the points according to zip code, since they wee originally given according to lat/long @ChrisW – V_N Jun 10 '14 at 17:03
  • @V_N I have revised and expanded my answer (sorry it took so long, I didn't get notice of your edit). If you still have any trouble with any of the steps, let me know. – Chris W Jun 12 '14 at 22:04
  • Thanks for the illustrated steps. It worked! :D We did get an error during step 3, so we did right-clicked and did 'Display XY data' instead. Thanks again, this really saved us a lot of time! @ChrisW – V_N Jun 13 '14 at 13:43

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.