2

Using arcpy for ArcGIS Desktop 10.7.1...

In order to append data from a Pandas data frame to an ArcGIS table, I am attempting to create a temporary in-memory table from the Pandas data frame as follows:

tmpTable = "in_memory/tmpTable"
labResults = pandas.read_csv(csvPath, parse_dates=[3], dayfirst=True)

######  If this ONE next line is commented out and the following line is UNCOMMENTED, the error does NOT occur
labResults['ResultQualifier'] = np.where(labResults.Result.str[0].str.isdigit(), None, labResults.Result.str[0])
#labResults['ResultQualifier'] = np.where(labResults.Result.str[0].str.isdigit(), labResults.Result.str[0], labResults.Result.str[0])

labResults['ResultValue'] = pandas.to_numeric(np.where(labResults.Result.str[0].str.isdigit(), labResults.Result, np.where(labResults.Result.str[1].str.isdigit(), 
labResults.Result.str[1:], labResults.Result.str[2:])))
del labResults['Result']
labResults.rename(columns={'ResultValue': 'Result'}, inplace=True)

arr = np.array(np.rec.fromrecords(labResults.values))
arr.dtype.names = tuple(labResults.dtypes.index.tolist())
arcpy.da.NumPyArrayToTable(arr, tmpTable)
######  ERROR OCCURS on this next line
arcpy.Append_management(tmpTable, resultsTable, "NO_TEST")

But the arcpy.da.NumPyArrayToTable() fails with the frustratingly generic error:

`RuntimeError: Unspecified error`

I can print the data frame by entering labResults on the Python console, and it prints out the expected data. And labResults.dtypes produces the expected output also, so I'm confident that the data frame is all OK.

NB: I get the same error whether tmpTable is a fGDB table (fGDB exists, table does not) or an in_memory table.

How can I get this to work as expected?

Note: This code is based on: https://my.usgs.gov/confluence/display/cdi/pandas.DataFrame+to+ArcGIS+Table where there is a full explanation of what it all does.

SAMPLE DATA:

"JobNo","LabNo","SampleID","DateSampled","Method","AnalyteCode","Units","Result"
90529,366330,"site1",15/1/19 10:15:00,"Glyphosate and Glufosinate in Water by LC-MS/MS","Glyphosate","µg/L","<5"
90529,366330,"site1",15/1/19 10:15:00,"Solids, Total Suspended dried at 104 ± 3 °C","TSS","mg/L","21"
90529,366331,"site2",15/1/19 10:35:00,"Glyphosate and Glufosinate in Water by LC-MS/MS","Glyphosate","µg/L","<5"
90529,366331,"site2",15/1/19 10:35:00,"Solids, Total Suspended dried at 104 ± 3 °C","TSS","mg/L","577"
90529,366332,"site3",15/1/19 11:00:00,"Glyphosate and Glufosinate in Water by LC-MS/MS","Glyphosate","µg/L","<5"

UPDATE:

I have found that if I change the labResults['ResultQualifier'] = line to never include NULLs (None's), then the error does NOT occur. This line is there to create a new column in the data frame that includes either < if the first character of 'Result' is not a digit, or otherwise 'None' (NULL). I'm not sure why NULL values would be causing such a problem.

  • What is tmpTable set to? – Hornbydd Jul 12 at 0:08
  • If you don't mind hardcoding dtypes: arr = labResults.to_records(index=False, column_dtypes={'SampleID': 'S255', 'Method': '<U255', 'AnalyteCode': '<U10', 'Units': '<U4', 'Result': '<U4', 'DateSampled':'<M8[us]'}) – user2856 Jul 12 at 0:42
  • On further testing, it appears to be that it doesn't like NULLs in the ResultQualifier field. Are NULLs a problem in general with arcpy.da.NumPyArrayToTable()? I've updated the question with this information. – Son of a Beach Jul 12 at 0:44
  • Not a general issue, though you may have to clean up afterwards (I get a 'nan' string value coming through in empty string records), i.e. community.esri.com/thread/… – user2856 Jul 12 at 0:57
2

It appears that arcpy.da.NumPyArrayToTable() fails on NULL (None) values - at least in my case. This doesn't appear to be universal, as other people report that it converts such values to the strings None or NaN or other such variations. However, in my case it fails when there are NULL values and works when there are not NULL values. (Perhaps this is a new problem in 10.7.1, or perhaps there is some other factor at play here.) I cannot think of any reason why it should not be able to handle NULLs properly, as both Numpy arrays and ArcGIS Tables can handle NULLs (except for in Shapefiles).

So my tried, tested and proven work around is...

Replace the line:

labResults['ResultQualifier'] = np.where(labResults.Result.str[0].str.isdigit(), None, labResults.Result.str[0])

with:

labResults['ResultQualifier'] = np.where(labResults.Result.str[0].str.isdigit(), '', labResults.Result.str[0])

and then AFTER the arcpy.da.NumPyArrayToTable() add the line:

arcpy.CalculateField_management(tmpTable, 'ResultQualifier', "None if !ResultQualifier! == '' else !ResultQualifier!", "Python")

EXPLANATION:

Instead of creating NULL/None values in the Pandas data frame (and Numpy array), create empty strings instead (''). Then after converting to an ArcGIS table, run a calculate field to convert the empty strings to NULLs (Nones).

UPDATE

It appears that NaN values may also cause a similar problem, although in this case the error was 'Unable to create table'. Replacing them with -999999999 in Pandas data frame before arcpy.da.NumPyArrayToTable() and then converting these values to NULLs (None) in the GIS table afterwards worked around the issue for me again.

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.