How to convert a feature class attribute table to csv using arcpy?
11633
9
11-05-2019 07:44 AM
by
by
by
by
by
In the beginning, there was a table
The desire was to produce a csv file... there are lots of ways... here is one
# ---- convert a table or featureclass table to a numpy array
t = tbl_data(in_fc)
t
array([( 1, 2, 19, 0, 'A', 'a', 10, 0.9 , 0),
( 2, 2, 19, 0, 'B', 'a', 8, 0.93, 5),
( 3, 1, 4, 0, 'A', 'a', 3, 0.74, 1),
( 4, 1, 5, 0, 'B', 'b', 4, 1.06, 7),
( 5, 1, 7, 0, 'C', 'b', 7, 1.15, 11),
( 6, 1, 30, 0, 'A', 'b', 6, 0.87, 3),
( 7, 1, 24, 0, 'A', 'b', 2, 0.88, 4),
( 8, 1, 7, 0, 'B', 'a', 8, 0.96, 6),
( 9, 1, 7, 0, 'C', 'a', 3, 0.98, 9),
(10, 1, 4, 0, 'A', 'a', 7, 0.47, 2),
(11, 1, 6, 0, 'B', 'b', 8, 1.06, 8),
(12, 1, 13, 0, 'C', 'a', 9, 1.04, 10)],
dtype=[('OID_', '<i4'), ('X_cent', '<i4'),
('Y_cent', '<i4'), ('Curves', '<i4'),
('A0', '<U5'), ('A1', '<U5'),
('Value_0', '<i4'), ('Area_perim', '<f8'),
('srt_a0a1', '<i4')])
# ---- amazing things can be done with arraysNow on to simple things.
save_txt(t, name="c:/Temp/arr.csv", sep=", ", dt_hdr=True)
File saved...
# ----
OID_, X_cent, Y_cent, Curves, A0, A1, Value_0, Area_perim, srt_a0a1
1, 2, 19, 0, A, a, 10, 0.9020246711679641, 0
2, 2, 19, 0, B, a, 8, 0.9285714285714286, 5
3, 1, 4, 0, A, a, 3, 0.7443162540690755, 1
4, 1, 5, 0, B, b, 4, 1.0606601717798239, 7
5, 1, 7, 0, C, b, 7, 1.1538461538461537, 11
6, 1, 30, 0, A, b, 6, 0.86944295103144, 3
7, 1, 24, 0, A, b, 2, 0.883142045739337, 4
8, 1, 7, 0, B, a, 8, 0.9561553109261145, 6
9, 1, 7, 0, C, a, 3, 0.9750407519379175, 9
10, 1, 4, 0, A, a, 7, 0.4661397467216941, 2
11, 1, 6, 0, B, b, 8, 1.0570527149133586, 8
12, 1, 13, 0, C, a, 9, 1.036191067705632, 10Now the code for posterity.
def tbl_data(in_tbl):
"""Pull all editable attributes from a featureclass tables. During the
process, <null> values are changed to an appropriate type.
Parameters
----------
in_tbl : text
Path to the input featureclass.
Notes
-----
The output objectid and geometry fields are renamed to
`OID_`, `X_cent`, `Y_cent`, where the latter two are the centroid values.
"""
flds = ['OID@']
null_dict, fld_names = _make_nulls_(in_tbl, include_oid=True,
int_null=-999)
if flds not in fld_names:
new_names = out_flds = fld_names
if fld_names[0] == 'OID@':
out_flds = flds + fld_names[1:]
new_names = ['OID_', 'X_cent', 'Y_cent'] + out_flds[3:]
a = arcpy.da.TableToNumPyArray(
in_tbl, out_flds, skip_nulls=False, null_value=null_dict
)
a.dtype.names = new_names
return np.asarray(a)
def save_txt(a, name="arr.txt", sep=", ", dt_hdr=True):
"""Save a NumPy structured, recarray to text.
Parameters
----------
a : array
input array
fname : filename
output filename and path otherwise save to script folder
sep : separator
column separater, include a space if needed
dt_hdr : boolean
if True, add dtype names to the header of the file
"""
a_names = ", ".join(i for i in a.dtype.names)
hdr = ["", a_names][dt_hdr] # use "" or names from input array
s = np.array(a.tolist(), dtype=np.unicode_)
widths = [max([len(i) for i in s[:, j]])
for j in range(s.shape[1])]
frmt = sep.join(["%{}s".format(i) for i in widths])
# vals = ", ".join([i[1] for i in a.dtype.descr])
np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
print("\nFile saved...")
And if you don't want to deal with those pesky nulls, you can include
def _make_nulls_(in_fc, include_oid=True, int_null=-999):
"""Return null values for a list of fields objects, excluding objectid
and geometry related fields. Throw in whatever else you want.
Parameters
----------
in_fc : featureclass or featureclass table
Uses arcpy.ListFields to get a list of featureclass/table fields.
int_null : integer
A default to use for integer nulls since there is no ``nan`` equivalent
Other options include
>>> np.iinfo(np.int32).min # -2147483648
>>> np.iinfo(np.int16).min # -32768
>>> np.iinfo(np.int8).min # -128
>>> [i for i in cur.__iter__()]
>>> [[j if j else -999 for j in i] for i in cur.__iter__() ]
"""
nulls = {'Double': np.nan, 'Single': np.nan, 'Float': np.nan,
'Short': int_null, 'SmallInteger': int_null, 'Long': int_null,
'Integer': int_null, 'String': str(None), 'Text': str(None),
'Date': np.datetime64('NaT'), 'Geometry': np.nan}
#
desc = arcpy.da.Describe(in_fc)
if desc['dataType'] not in ('FeatureClass', 'Table'):
print("Only Featureclasses and tables are supported")
return None, None
in_flds = desc['fields']
good = [f for f in in_flds if f.editable and f.type != 'Geometry']
fld_dict = {f.name: f.type for f in good}
fld_names = list(fld_dict.keys())
null_dict = {f: nulls[fld_dict[f]] for f in fld_names}
# ---- insert the OBJECTID field
if include_oid and desc['hasOID']:
oid_name = 'OID@' # desc['OIDFieldName']
oi = {oid_name: -999}
null_dict = dict(list(oi.items()) + list(null_dict.items()))
fld_names.insert(0, oid_name)
return null_dict, fld_namesFailed to mention blindingly fast for very large data sets and you can change the separator, include the field names and blindingly fast.. and no Pandas or Excel and everything is built into Arc. See line 23 in tbl_data … that is one of those arcpy functions!
I did mention nulls...
OID_, Int_nulls, Float_nulls, Text_nulls
1, 1, nan, a
2, -999, 6.1, None
3, -999, 2.1, None
4, -999, 8.1, None
5, -999, 12.1, None
6, -999, 4.1, None
7, 5, nan, b
8, 7, nan, d
9, -999, 10.1, None
10, 3, nan, c
11, 9, nan, f
12, 11, nan, eIn the _make_nulls_ script, integer nulls were assigned a value of -999, floating point numbers are assigned 'nan' (not a number, and test fields a text representation of None... not None itself, but None in text form... got it 
by
