I have a pandas data frame (df) that I want to put into an Esri table in sde. I am having a hard time dealing with the datatypes in an effective way. All the columns in the df have the datatype object. So, after some digging, it looks like strings get the data-type object in pandas. There is a good explication for why this is on StackOverflow:
python - Strings in a DataFrame, but dtype is object - Stack Overflow
All, well and good. After I convert it to a numpy array the datatype is 'O' and then to an Esri table it fails. I think the problem is in the datatype. When I set the datatype to '|S256' it works fine. I also suspect that some of the columns have a data type of int or float.
So, I need an elegant way to detect the data type and assign it to the numpy array. Suggestions?
This is the code so far. It works fine. I am dropping down to one column for testing. But, there are around 250 columns between two tables in the real data.
from sodapy import Socrata
import cama_helper
client = Socrata('data.muni.org', None)
if __name__ == '__main__':
client = Socrata('data.muni.org', None)
for cama_id, cama_name in {'r3di-nq2j': 'cama_residential', 'ijws-5rpw': 'cama_commercial'}.items():
df = cama_helper.get_data_as_panda(client, cama_id)
cama_helper.load_sde(df, cama_name)
# cama_helper
import pandas
import arcpy
import numpy
def get_data_as_panda(client, cama_id):
results = client.get(cama_id, limit=20)
df = pandas.DataFrame.from_records(results)
df.drop(df.columns[[0, 1, 2]], axis=1, inplace=True)
return df
def load_sde(df, cama_name, connection=None):
s = df.iloc[:, 1]
df = s.to_frame()
#numpy_array = df.to_records()
numpy_array = df.to_numpy()
numpy_array2 = numpy.array(numpy_array, numpy.dtype([('textfield', '|S256')]))
arcpy.da.NumPyArrayToTable(numpy_array2, r'Temp.gdb\{}'.format(cama_name))
