How to Create a WKT Field in Panda DataFrame or GeoDataFrame?

Question

I'm trying to add a WKT or object geometry field to my feature classes or panda dataframe or geodataframe. It works when I explicitly list the fields that I want in my panda dataframe using the token, Shape@WKT. However, when I want to import all my fields in the feature class and also add a WKT or object geometry field, I keep getting an error problems. I know how to get WKT from a 'Shape' field, but I am not sure how to create a new column in the dataframe with that multipoint WKT values. I appreciate any feedbacks or tips to get me moving in the right direction. Thanks for your help! Here is what I have so far:

import arcpy
import os
from arcpy.sa import *
import pandas as pd
import shapely
import fiona
import geopandas as gp
import shapely

#convert spatial data to panda dataframe 
def PandasDF(in_table,fields):
        try:
            if fields=="":
                fieldList = [field.name for field in arcpy.ListFields(in_table)]
            else:
                fieldList = fields

data = [row for row in arcpy.da.SearchCursor(in_table,fieldList)]
            fc_df = pd.DataFrame(data, columns = fieldList)
        except:
            arcpy.Message('Error: Unable to convert data to panda dataframe')
        return fc_df

#Declaring feature classes variables
output_folder = r'C:\Users\xyz'
FC_1 = os.path.join(output_folder,'Temp.gdb\FC_1')
FC_2 = os.path.join(output_folder,'Temp.gdb\FC_2')
FC_3= os.path.join(output_folder,'Temp.gdb\FC_3')

#Convert spatial to panda dataframe
FC1_df = PandasDF(FC_1,"")
FC2_df = PandasDF(FC_2,"")
FC3_df = PandasDF(FC_3,"")

#Obtain the wkt values
query= arcpy.SearchCursor(FC_1)
for row in query:
  the_geom=row.getValue('Shape') # Get Geometry field
  wkt = the_geom.WKT  #Not sure how to write out this wkt to df or gdf

#check if geometry field exist
geom_col = 'Geometry'
if geom_col not in df:
    #create geometry field
    geometry = FC1_df.wkt.map(shapely.wkt.loads)
    gdf = gp.GeoDataFrame(FC1_df, crs="EPSG:5070", geometry=geometry)  
else: 
    geometry = FC1_df[geom_col]
    gdf = gp.GeoDataFrame(FC1_df, crs="EPSG:5070", geometry=geometry)

#Converting the geodataframe to GIS
FC1 = gdf.to_file(FC)

Anonymous User · Answer

I suspect that you'll need to swap out the 'objectid' field with whatever you have as the unique id field...

FC1_df.loc[FC1_df['objectid'] == row[0], 'wkt_column'] = row[1]

adds the 'wkt_column to the df and sets it to the @WKT value.  In my testing:

before:

after:

but you can also create the column through other ways.

one is having a list of ['Shape@WKT'] and then add the fields from the ListFields:

wkt = ['SHAPE@WKT']
flds = wkt + [f.name for f in arcpy.ListFields(in_table)]

But I think you'd have to map the Shape@ to Shape@WKT in your PandasDF method.

Edit:

maybe do this in the function since the cursor can pull the wkt...

tzz_12 · Answer

Thanks Jeff! I recieved the following error:

Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match).

Here is the script that worked for me, but it would mean hard-coding the fields, which is not preferred:

#Converting feature class to panda dataframe
#df = self.PandasDF(FC1,"")
fieldList = ['ID_1','SiteNo','SiteName','Area_sqmi','Join_Flow','Shape@WKT']
data = [row for row in arcpy.da.SearchCursor(FC1,fieldList)]
guage_df = pd.DataFrame(data, columns = fieldList)

Ideally, I want to import all the fields into the panda dataframe with an additional field, "Shape@WKT." Any ideas?

Anonymous User · Answer

You should use the .da.SearchCursor, and I think if you did a loc based on matching objectid's, you can add the wkt.  This isn't tested and is a workflow idea:

Edit to fix the df indexing in loc

# add wkt column to df
FC1_df['wkt_column'] = 0

#Obtain the wkt values
with arcpy.da.SearchCursor(FC_1, ['objectid', "SHAPE@WKT"]) as sCur:
    for row in sCur:
        FC1_df.loc[FC1_df['objectid'] == row[0], 'wkt_column'] = row[1]