topic Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table" in Python Questions

arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Arne_Gelfert — Thu, 27 Dec 2018 20:03:45 GMT

After spending a few months working through a Python for Data Science curriculum, I have gotten to like woriking with pandas for data prep in Python. Naturally, I wanted to try using it with arcpy. Usually, I would attempt large batch GIS data manipulation or data moving with FME. But FME, while it is powerful, often takes too many settings or mouse-clicks to remember. Seems like I can never remember some parameter that is only visible after drilling down some UI tree. If I script it, I see all my settings. I found a great example of getting a pandas Dataframe into a geodatabase here.

import arcpy
from arcpy import env
env.workspace = "my_favorite_workspace" #pointing to SDE connection file
#I already have a dataframe df that is loaded from SQL Server using pyodbc
#import pandas as pd
#import pyodbc
#sql_conn = pyodbc.connect('''DRIVER={ODBC Driver 13 for SQL Server};
# SERVER=<myserver>;
# DATABASE=mydatabase>;
# Trusted_Connection=yes''')
#query = "<myquery>"
#df = pd.read_sql(query, sql_conn,columns=['count'])

This all works like a charm. I can now do all kinds of data manipulation.Then I create a structured array in numpy.

#first you turn your dataframe into a simple np series
nparray = df.values
#then you create a structured numpy array, see here for documentation
npstruct = np.rec.fromrecords(nparray)
#then you create a list of field names from the dataframe
colnames = df.dtypes.index.tolist()
#then you use that list to update column names in structured array
npstruct.dtype.names = tuple(colnames)

Again, everything works fine. I get my field names and I get a structured array that looks like it should based on the documentation.The final step is to use that array in arcpy.da.NumPyArrayToTable() to create a table. ESRI has additional documentation here.

arcpy.da.NumPyArrayToTable(np_struct, "newtable")

---------------------------------------------------------------------------RuntimeError                              Traceback (most recent call last)<ipython-input-31-14d9b2719bd8> in <module>----> 1 arcpy.da.NumPyArrayToTable(np_struct, "newtable")RuntimeError: create table

Need help figuring out what's the problem. I have my env.workspace pointing to an SDE connection. I've also tried using the same code to create "newtable.dbf" in a local directory. Don't think it's permissions either because the following is successful:

arcpy.CreateTable_management(env.workspace, 'new_table')

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

DanPatterson_Retired — Thu, 27 Dec 2018 20:25:02 GMT

make sure you are using a full path to the table in the geodatabase... skip dbf altogether

arcpy.da.NumPyArrayToTable(array, "c:/data/gdb.gdb/out_table")

where array is the structured array, and out_table is the table name to use in the *.gdb.

PS overwriteOutput has no affect. It will fail miserably even if set, so use a different name if you have saved it before.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Arne_Gelfert — Thu, 27 Dec 2018 21:46:19 GMT

Thanks, Dan. I tried that and get the exact same error. Could it be that I have to actually create the table before loading data? Ha! I will try that next.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

DanPatterson_Retired — Sun, 12 Dec 2021 08:04:46 GMT

tbl = r"c:\path\to_npy_file\sample_20.npy"  # save this to a volder

a = np.load(tbl)

a.dtype
dtype([('OBJECTID', '<i4'), ('f0', '<i4'), ('County', '<U2'), ('Town', '<U6'), ('Facility', '<U8'), ('Time', '<i4')])

a

array([( 1,  0, 'B', 'B_', 'Hall', 11), ( 2,  1, 'A', 'A_', 'Hall', 24),
       ( 3,  2, 'C', 'C_', 'Hosp', 43), ( 4,  3, 'A', 'B_', 'Hall', 43),
       ( 5,  4, 'B', 'B_', 'Hall', 16), ( 6,  5, 'B', 'A_', 'Hall',  8),
       ( 7,  6, 'A', 'C_', 'Hall', 26), ( 8,  7, 'B', 'C_', 'Hall', 31),
       ( 9,  8, 'C', 'C_', 'Hall',  7), (10,  9, 'A', 'A_', 'Hall', 58),
       (11, 10, 'A', 'A_', 'Hosp', 20), (12, 11, 'C', 'A_', 'Hosp', 37),
       (13, 12, 'C', 'B_', 'Hall', 36), (14, 13, 'A', 'B_', 'Hosp', 33),
       (15, 14, 'C', 'C_', 'Hosp', 51), (16, 15, 'B', 'C_', 'Hosp', 53),
       (17, 16, 'C', 'A_', 'Hosp', 21), (18, 17, 'C', 'C_', 'Hosp', 42),
       (19, 18, 'A', 'B_', 'Hosp', 43), (20, 19, 'A', 'C_', 'Hall',  5)],
      dtype=[('OBJECTID', '<i4'), ('f0', '<i4'), ('County', '<U2'), ('Town', '<U6'), ('Facility', '<U8'), ('Time', '<i4')])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Load that then try NumPyArrayToTable. put it into a gdb and give it a name

I know it works, I use this as a test file all the time.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Arne_Gelfert — Thu, 03 Jan 2019 18:59:43 GMT

Thanks, Dan, and happy new year! - I tried your above example and it works for me just fine. I had to use the full path to the SDE or GDB table. Don't understand why using only table name and env.workspace doesn't work but I can create a new table with correct field names and data types of the structured array.

However, I am still not getting anywhere with my own example. The whole purpose of this exercise was to be able to work with a dataframe in pandas, and get the pandas datatypes converted to numpy so that the arcgis function can create a table from it. By now, I suspect this method may be the culprit - scipy.org search returns no results for it at all.

np.rec.fromrecords

In your case, the array you created "manually" looked like this:

array([ (A1, B1, C1),(A2, B2, C3) ....], dtype = [('FieldA', 'DatatypeA'),('FieldB', 'DatatypeB')...])

When I use the above method on the df.values and convert back into ndarray, I get :

x = nd.array(np.rec.fromrecords(df.values))
x
>>array([ (A1, B1, C1),(A2, B2, C3) ....], dtype = (numpy.record, [('FieldA', 'DatatypeA'),('FieldB', 'DatatypeB')...]))

So although np.array() is supposed to turn the record array back into a regular nd.array, there is still that "numpy.record" in the dtype.

The more I think about it, this probably isn't a question for Geonet any longer but rather a numpy question. But if you have any suggestions, let me know.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

DanPatterson_Retired — Thu, 03 Jan 2019 19:12:49 GMT

Arne, I just don't use pandas, There is really no need to if you are just bringing arrays (aka tables and/or rasters) to get pandas involved at all. It really doesn't offer much more to numpy except for a gentler 'shell' to the interface, but for the most part, I haven't found anything that pandas offers that I can't already handle (including pivot tables, groupby operations and joins).

I have dozens of blog posts on working with numpy and arcpy for both raster and tabular data

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Arne_Gelfert — Tue, 08 Jan 2019 22:56:10 GMT

Dan, thanks for chiming in again... I've been a little side tracked from looking at this again. I hear you. If you have a toolkit that does the trick, why learn another one that accomplishes the same. Especially in a world of wrappers around wrappers. But I kinda feel that way about pandas... I know it better than numpy and wanted to figure out how to do that. --- I did take a look at your blog just now and will be sure to return to it soon. Thanks.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

MarkZito — Tue, 11 Jun 2019 01:03:20 GMT

version .24 of pandas will have a .to_numpy() method to convert a data frame to a numpy array. ArcGIS Pro 2.3.2 ships with version .23 so the method is not available yet. In the meantime you can use the following.

#df is the pandas dataframe

x = df.reset_index()
z = np.rec.fromrecords(x.values, names=x.columns.tolist())
arcpy.da.NumPyArrayToTable(z, out_table)

more info on this thread

https://stackoverflow.com/questions/13187778/convert-pandas-dataframe-to-numpy-array

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

DanPatterson_Retired — Sun, 12 Dec 2021 08:04:49 GMT

Numpy also added two more functions to facilitate array conversion of ndarrays to structured arrays and from structured arrays to ndarrays.

structured_to_unstructured

unstructured_to_structured

from numpy.lib.recfunctions import structured_to_unstructured as stu
from numpy.lib.recfunctions import unstructured_to_structured as uts‍‍‍‍‍‍

Which I have named 'stu' and cousin 'uts'

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Arne_Gelfert — Wed, 03 Jul 2019 21:58:31 GMT

Cool - now I just need to get back to where I was stu+n months ago. Thanks for recording this though. Great to see someone in GIS is keeping up with what's happening in the numpy world.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Anonymous User — Mon, 26 Aug 2019 15:28:33 GMT

I am having this exact same problem - has anyone worked it out?

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Anonymous User — Mon, 26 Aug 2019 17:35:02 GMT

Mark,

I am attempting to use your proposed solution and I get an error from Arcpy telling me that the the in_array is not a numpy array (TypeError: 'in_array' is not numpy array). Now my IDE recognizes it as an 'recarry'.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

MarkZito — Tue, 27 Aug 2019 14:04:23 GMT

change the 2nd line to the following. You need to pass in the values of the dataframe, not the dataframe itself.

z = np.rec.fromrecords(x.values, names=x.columns.tolist())

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Anonymous User — Sun, 12 Dec 2021 16:53:02 GMT

Mark,

Thanks for the help, but it isn't working. Here is what I have (with some things Xed out):

import pandas as pd
import pyodbc
import arcpy
import numpy as np

cnctStr='Driver={SQL Server Native Client 11.0};Server=XXXXXX;Database=XXXXXX;Trusted_Connection=yes;'
conn = pyodbc.connect(cnctStr)

SQL_Query = pd.read_sql_query(
'''SELECT        FILE_NUMBER, TYPE_CODE, STATUS_CODE, REVIEWER_ID, PREVIOUS_REVIEWER, OWNERS, WATERBODY_NAME, PROJECT_CATEGORY, PRELIMINARY_CATEGORY, APPLICANTS_REQUESTED_IMPACT, CALCULATED_AREA_OF_IMPACT, IMPACT_WETLAND_TYPE_CODE, CITY_NAME, COUNTY, LAST_ACTION_DATE, DATE_LAST_INFO_LETTER_SENT, IS_MORE_INFO_LETTER_SENT, DATE_MORE_INFO_LETTER_RETURNED, DATE_RECEIVED
FROM            WETLANDS_PERMIT_V
WHERE        (STATUS_CODE IN ('REVIEW EXT', 'PENDING', 'EA_ISSUED', 'MI RECEIVE', 'MI REQUEST')) AND (DATE_RECEIVED >= '01/01/2014')''', conn)

df = pd.DataFrame(SQL_Query, columns=['FILE_NUMBER','TYPE_CODE','STATUS_CODE','REVIEWER_ID','PREVIOUS_REVIEWER','OWNERS',
                                      'WATERBODY_NAME','PROJECT_CATEGORY','PRELIMINARY_CATEGORY','APPLICANTS_REQUESTED_IMPACT',
                                      'CALCULATED_AREA_OF_IMPACT','IMPACT_WETLAND_TYPE_CODE','CITY_NAME','COUNTY','LAST_ACTION_DATE',
                                      'DATE_LAST_INFO_LETTER_SENT','IS_MORE_INFO_LETTER_SENT','DATE_MORE_INFO_LETTER_RETURNED','DATE_RECEIVED'])

arcpy.env.overwriteOutput = True
arcpy.env.workspace = 'XXXXXXXXX.gdb'

x = df.reset_index()
z = np.rec.fromrecords(x.values, names=x.columns.tolist())
arcpy.da.NumPyArrayToTable(x, 'EA_Dash')

Which produces this error:

TypeError: 'in_array' is not numpy array

Snapshot of the 'z' object:

I think my code matches you're exactly? So, any idea what gives?

Tom

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

MarkZito — Tue, 27 Aug 2019 15:48:21 GMT

I think I see the issue, in the last line where you call arcpy.da.NumPyArrayToTable(x, 'EA_Dash'), change the variable 'x' to 'z'.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

Anonymous User — Tue, 27 Aug 2019 16:39:45 GMT

Okay, well that was a big 'Duh!' on my part.

Fixing that though, it seems there is still some sort of problem. I then get this error:

RuntimeError: create table

And I think it has to do with Python 'Object' types in the array not able to be handled by the arcpy function:

Here are my dtypes - with 'O' being python 'Objects':

(numpy.record, [('index', '<i4'), ('FILE_NUMBER', '<U10'), ('TYPE_CODE', '<U10'), ('STATUS_CODE', '<U10'), ('REVIEWER_ID', 'O'), ('PREVIOUS_REVIEWER', 'O'), ('OWNERS', '<U80'), ('WATERBODY_NAME', 'O'), ('PROJECT_CATEGORY', 'O'), ('PRELIMINARY_CATEGORY', 'O'), ('APPLICANTS_REQUESTED_IMPACT', '<f8'), ('CALCULATED_AREA_OF_IMPACT', '<f8'), ('IMPACT_WETLAND_TYPE_CODE', 'O'), ('CITY_NAME', '<U17'), ('COUNTY', '<U3'), ('LAST_ACTION_DATE', 'O'), ('DATE_LAST_INFO_LETTER_SENT', 'O'), ('IS_MORE_INFO_LETTER_SENT', 'O'), ('DATE_MORE_INFO_LETTER_RETURNED', 'O'), ('DATE_RECEIVED', 'O')])

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

deleted-user-D2l0W3ToJYe- — Mon, 07 Oct 2019 09:52:51 GMT

This worked for me.

Basically, I breakdown the pandas Dataframes to multiple simple arrays. Use the arrays to get their numpy datatype, because arcpy.da.NumPyArrayToTable() is having trouble handling pandas object dtype. Then create a new Structured Array with np.rec.fromarrays function

Get the columns names:

names = sms_new.columns

Get the data in the columns:

arrays = [ sms_new[col].get_values() for col in names ]

Get the data type for each data array:

formats = [f'{array.astype(str).dtype}' for array in arrays ]

Create new Structured Array with .fromarrays function:

   sms_array = np.rec.fromarrays( arrays, dtype={'names': names, 'formats': formats} )

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

TijsLips — Fri, 30 Apr 2021 12:18:54 GMT

Hi,

Just putting this out here in case someone has the same "RuntimeError: create table" and tried all above input as well . I suddenly got this error while the exact same script was working well before. Realized that my dataframe had some NaN's in it. When filled the NaN's with empty strings (if field is a text field..)

df = df.fillna('')

the script suddenly worked again. If specific fields are e.g. numbers, fill them with a 0.

Re: arcpy.da.NumPyArrayToTable() returns "RuntimeError: create table"

HannesZiegler — Wed, 17 Feb 2021 19:09:26 GMT

As noted by someone else earlier, I find that sometimes the conversion from numpy array to pandas converts some data types to Object 'O' data types. This might occur if you're converting from FeatureClass to numpy ndarray and then the ndarray to a pandas DataFrame.

arr = arcpy.da.FeatureClassToNumPyArray(data, fields_list) df = pandas.DataFrame(arr)

You could just grab the data types from the original array as a dict and supply them to df.to_records, like this (assuming you haven't changed the fields and data types, otherwise you'll have to create your own dict or edit the one obtained from arr):

recs = df.to_records(index=False, column_dtypes=dict(arr.dtype.descr))

Then converting back to database table or featureclass should work. Here's example converting to database table.

arcpy.da.NumPyArrayToTable(recs, out_table)