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

Arne_Gelfert

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')

HannesZiegler

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)