3

After reviewing all the possible related solutions to my problem, I could not piece together a solution to process and import multiple sets of groundwater analytical data that contains different data types from CSV format (example below) into ArcGIS 10.1 for heat mapping (or generating isoconcentration lines).

Ultimately, I need a CSV file that contains only numeric data types.

enter image description here

As you can see, I have integer, floating point, and non-numeric values (e.g. ND = not detected, NA = not analyzed).

I can substitute NDs with 0's, but I don't know how to handle the NAs since they have no value but cause ArcGIS to interpret my fields as strings.

If I substitute the NAs with no value, then when I export my data to a shapefile, it automatically populates the blank fields with 0's which is not accurate.

I want to find a solution where I don't have to manually add a new field for each constituent and run the field calculator to redefine my data types.

I was hoping to find a ModelBuilder solution, but I couldn't figure out how to avoid the string problem.

I am not opposed to using Python directly, but I am not an experienced user.

  • 2
    0 for not detected should be fine, it wont skew your results, but for N/A you really need <NULL> to show that no data is available. Shapefiles don't support null values, a geodatabase feature class (file or personal) might be the only solution. You could use a dummy value (like -1) in place of N/A but the danger is if a statistic is taken without care it will skew your results; null values should be skipped by tools like Summary Statistics but if that causes a problem start with a query like 'Field is not NULL' to easily sort the values from no-values. – Michael Stimson Jul 29 '15 at 22:51
  • 1
    Replace ND with 0.0. Replace NA with ridiculous -9999. Make sure 1st row in Excel contains numeric (if there is a number) with desirable decimal accuracy. When it comes to interpolation, select rows where CONCENTRATION <>-9999. – FelixIP Jul 30 '15 at 2:22
1

As suggested by @FelixIP you can pre-format your CSV to:

Replace ND with 0.0. Replace NA with ridiculous -9999. Make sure 1st row in Excel contains numeric (if there is a number) with desirable decimal accuracy.

then:

When it comes to interpolation, select rows where CONCENTRATION <>-9999.

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.