I have the following Excel data, I want to summarize the P-value within the zones of map dataset.

I have sereached a lot and learned I might need to convert Excel data into XY table, and then into a raster before using Zonal Statistics tool. So my question might eventually be how to convert the Excel data into a raster.


LAT LONG    P-value
42  19      0
-32 -57     0.000199835
-25 -56     0.000148499
-11 -73     4.36478E-06
-11 -72     1.30944E-05
-11 -36     0
-8  -75     0
2   -58     3.41092E-05

  • Well from the title of your question it makes it sound like you have another dataset of zones already (maybe country, district, etc... type polygons) that you are trying to use to summarize the P values. Is this the case or do you want to create a new dataset geographically representing similar zones (ex: a polygon or raster pixel value representing all areas where P value is between X - Y, another where P value is between Y - Z, etc...). If the first case using known existing polygons, there are much easier ways than converting to raster. Please let us know if so. – John Feb 18 '14 at 22:48
  • And what Zonal Statistics tool do you plan to use? Is it stand-alone or part of another GIS package (which one)? Please edit the question with any new info. – Martin F Feb 18 '14 at 22:56
  • I am in the first case. Yes, another dataset of zones is ready to use. – user25458 Feb 18 '14 at 23:02

Ok, since you didn't specify which software you are using I am just going to assume you are using ArcGIS Desktop software (some of the basic concepts will cross software platforms but you'd have to figure out which tools match up where if you're using a different software). Anyway, what I would recommend is you use the Make XY Event Layer geoprocessing tool (ArcToolbox > Data Management Tools > Layers and Table Views > Make XY Event Layer). Then from that output temporary feature layer, use the Feature Class to Feature Class geoprocessing tool (ArcToolbox > Conversion Tools > To Geodatabase > Feature Class to Feature Class) to save it as a permanent feature class (or if don't the geoprocessing interactively in ArcMap you can just right click on the new layer in the table of contents and click Data > Export data and save it to a permanent file). Saving it allows you to run multiple different processes with the same data if you need to but you could just use the feature layer as the input to the next tool without saving it if you want. Then you could use the Spatial Join geoprocessing tool (ArcToolbox > Analysis Tools > Overlay > Spatial Join) on the newly created points and the zone polygons to calculate the desired statistics. Within the Spatial Join tool you would probably want to set the zone polygons as the Target Features and the points as the Join Features. Then make sure to leave the Join Operation as JOIN_ONE_TO_ONE. And finally you'd just have to set go through the Field Map of Join Features box and manually set the join rules you'd like to use to summarize the P Values (you could calc the sum, count, average, Std Deviation, etc... ) of all the points that match up with each polygon (using the Match Option set to the default INTERSECT would most likely meet what you need but you can play with those if you've got a special case such as points on boundaries of polygons or points outside any polygon). Anyway, hope that helps, you can get more details on spatial joint tool at http://resources.arcgis.com/en/help/main/10.1/index.html#//00080000000q000000

| improve this answer | |
  • Spatial Join fails to work with error code 999999. – user25458 Feb 19 '14 at 3:50
  • Can you specify what steps you took that resulted in the error code maybe so we can figure out what the problem might be? Or, I hate to suggest, but sometimes ESRI support can actually manage to connect you with a helpful analyst, but... they're going to ask for your data and take a couple week to "diagnose", so... Any other details on what you did, results from previous steps, etc... That error code basically means we don't know what's wrong, so... – John Feb 19 '14 at 6:28
  • I followed your instruction to make a XY Event Layer with using the Excel data. Then I tried to use the Spatial Join geoprocessing tool to combine the newly created layer and another dataset of zones using zones dataset as Target Features. I didn't set up Field Map of Join Feaures before clicking 'OK'. But it doesn't work. I have to clarify it here that my zones dataset is called 'GADM database of Global Administrative Areas' as ESRI file geodatabase. It provides different levels of subdivision. see gadm.org/version2 – user25458 Feb 19 '14 at 7:39
  • I'd try saving the event layer out to a feature class or shapefile first then and see if it might just be an issue with using layer data as an input. If that doesn't work, maybe try using other smaller basic datasets (still do points joined to polygon in same way) and make sure the tool runs correctly on your machine with different data and isn't a machine settings issue. If works with other data but not this data... I don't know if this would matter, but maybe if you're polygons are multi-part try it with those split into separate polygons... you can dissolve them back together after if so. – John Feb 19 '14 at 15:38
  • The problem is solved!! Neither 'Spatial Join geoprocessing tool' nor my polygons database is wrong. The problem is caused by the layer created by Make XY Event Layer geoprocessing tool, for any reason I don't really understand, the layer's attribute table is empty. Instead of converting the Excel data into layer, I manually 'Add XY data' and then export data as shp. format. Spatial Join eventually works with it. – user25458 Feb 20 '14 at 8:27

Assuming that all of these coordinates are not assigned to a grid, I would convert these to points and then create an interpolated raster surface. Then run your zonal statistics. You will have to decide for yourself if the p-values can legitimately be interpolated and/or summarized via zonal statistics.

For example, here is an example of interpolated elevation point data:

enter image description here

More Details:

Understanding interpolation analysis

| improve this answer | |
  • Will predicted values between points be created through this way? Then I am afraid the calculation will be contaminated. If not, would you please tell me what exactly tool should be used Interpolation? Thanks! – user25458 Feb 19 '14 at 4:02
  • Yes, values will be predicted. – Aaron Feb 19 '14 at 13:03

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.