AnsweredAssumed Answered

Pandas dataframe to Esri Table

Question asked by fallingdog Champion on Oct 28, 2019
Latest reply on Oct 30, 2019 by fallingdog

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

Outcomes