topic Re: Modify CSV writer script to define list of columns by field name in Python Questions

Modify CSV writer script to define list of columns by field name

David_Brooks — Fri, 26 Nov 2021 10:29:24 GMT

I have a script that writes a CSV based on fields from a feature class. Currently, the columns are defined by a field list, and a Search cursor loops through each item in the list to write to the CSV.

The list is defined by the positions of each field in the list. However, if the field order ever changes on the Feature Class, this list goes to pot.

How do I rewrite it so that I can define fld_list_final as a list of strings that match the actual field names, instead of their positions in the list?

So, for example, instead of the list being fn[5], fn[6] etc, i want to be able to define it as 'TreeRef', 'Species', etc

# Create CSV of data for schedule if there are veteran trees import csv import os def tableToCSV(input_tbl, csv_filepath): fld_list = arcpy.ListFields(input_tbl) fn = [fld.name for fld in fld_list] fld_list_final = [fn[3], fn[4], fn[5], fn[6], fn[7], fn[8], fn[38], fn[41], fn[22], fn[23], fn[24], fn[25], fn[26], fn[27], fn[28], fn[29], fn[30], fn[36], fn[40], fn[39], fn[33], fn[37]] with open(csv_filepath, 'w', newline='') as csv_file: writer = csv.writer(csv_file) writer.writerow(fld_list_final) with arcpy.da.SearchCursor(input_tbl, fld_list_final) as cursor: for row in cursor: writer.writerow(row) csv_file.close() out_csv = csv_path+"\\"+(os.path.basename(fc_pnt))+".csv" tableToCSV(fc_pnt, out_csv) arcpy.AddMessage("CSV Generated.")

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Fri, 26 Nov 2021 10:36:52 GMT

perhaps the suggestion of using a dictionary to alter field order when exporting to csv can be exploited

Solved: Changing Column Order with Python Export CSV - Esri Community

Re: Modify CSV writer script to define list of columns by field name

jcarlson — Fri, 26 Nov 2021 14:37:20 GMT

If you have a set list of field names that you want in your output, you can define those separately, then filter your field list with it. Try this? I don't have a dataset handy to test it on.

# Create CSV of data for schedule if there are veteran trees import csv import os def tableToCSV(input_tbl, csv_filepath): fld_list = arcpy.ListFields(input_tbl) keepers = ['some', 'list', 'of', 'field', 'names'] fn = [fld.name for fld in fld_list if fld.name in keepers] with open(csv_filepath, 'w', newline='') as csv_file: writer = csv.writer(csv_file) writer.writerow(fn) with arcpy.da.SearchCursor(input_tbl, fn) as cursor: for row in cursor: writer.writerow(row) csv_file.close() out_csv = f"{csv_path}\\{os.path.basename(fc_pnt)}.csv" tableToCSV(fc_pnt, out_csv) arcpy.AddMessage("CSV Generated.")

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Fri, 26 Nov 2021 20:01:41 GMT

@jcarlson thanks for the re-write, but I get

TypeError: list indices must be integers or slices, not str

....which i think is because Im not extracting items from a list by index, rather by string values. Any ideas?

Re: Modify CSV writer script to define list of columns by field name

jcarlson — Fri, 26 Nov 2021 21:46:43 GMT

What line is that error pointing you to? The change I made shouldn't really matter with respect to list indices.

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Fri, 26 Nov 2021 22:50:36 GMT

@jcarlson i've rewritten, and the csv now exports. however, the order of fields doesn't match the order i've written them in the keepers list

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Sat, 27 Nov 2021 03:34:54 GMT

import arcpy import numpy as np tbl = r"C:\Arc_projects\Test_29\Test_29.gdb\sq2" flds = arcpy.ListFields(tbl) fnames = [f.name for f in flds] fnames ['OBJECTID', 'Shape', 'ids', 'CENTROID_X', 'CENTROID_Y', 'INSIDE_X', 'INSIDE_Y', 'PART_COUNT', 'PNT_COUNT', 'Sort_', 'Shape_Length', 'Shape_Area', 'Sort2_'] keepers = ['Sort2_', 'PNT_COUNT', 'PART_COUNT'] arr = arcpy.da.TableToNumPyArray(tbl, keepers) # -- def time ---------------------------------------- def save_txt(a, name="arr.txt", sep=", ", dt_hdr=True): """Save a NumPy structured/recarray to text. Parameters ---------- a : array Input array. name : filename Output filename and path otherwise save to script folder. sep : separator Column separator, include a space if needed. dt_hdr : boolean If True, add dtype names to the header of the file. """ a_names = ", ".join(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]) np.savetxt(name, a, fmt=frmt, header=hdr, comments="") print("\nFile saved...") # ----------------------------------------------------------- # now save it save_txt(arr, name="c:/temp/test.csv", sep=", ", dt_hdr=True)

results

Sort2_, PNT_COUNT, PART_COUNT 0, 11.0, 1.0 1, 8.0, 1.0 2, 6.0, 1.0 3, 5.0, 1.0 4, 10.0, 1.0 5, 10.0, 1.0 6, 5.0, 1.0

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Fri, 26 Nov 2021 23:06:11 GMT

This is what i have so far, but the CSV field order needs sorting to match the "Keepers" list order

# Create CSV of data for schedule if there are veteran trees import csv import os def tableToCSV(input_tbl, csv_filepath): fld_list = arcpy.ListFields(input_tbl) keepers = ['TreeRef','Species','OnOffSite','Height','NumStems','EstDiam','CalcActStemDiam','CrownRadii', 'AvgCanHt','F1stBranchHt','F1stBranchDir','LifeStage','SpecialImp','GeneralObs','HealthVit', 'StructCon','EstRemCon','BS5837_category','RPA_Radius_m','RPA_m2','TPO','Sort'] fn = [fld.name for fld in fld_list if fld.name in keepers] with open(csv_filepath, 'w', newline='') as csv_file: writer = csv.writer(csv_file) writer.writerow(fn) with arcpy.da.SearchCursor(input_tbl, fn) as cursor: for row in cursor: writer.writerow(row) csv_file.close() out_csv = f"{csv_path}\\{os.path.basename(fc_pnt)}.csv" tableToCSV(fc_pnt, out_csv) arcpy.AddMessage("CSV Generated.")

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Fri, 26 Nov 2021 23:14:30 GMT

tried this code but get the error

Traceback (most recent call last):
  File "C:\Users\DAVID\Desktop\BS5837_TreePoint_v1.2.4.py", line 100, in <module>
    np.savetxt(name, a, fmt=frmt, header=hdr, comments="")
  File "C:\Users\DAVID\Desktop\BS5837_TreePoint_v1.2.4.py", line 92, in save_txt
    If True, add dtype names to the header of the file.
NameError: name 'np' is not defined

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Sat, 27 Nov 2021 01:11:00 GMT

Ahhh, forgot the old

import numpy as np

It is assumed in numpy world and oft forgotten since my IDE automatically loads my needed packages 😉

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Sat, 27 Nov 2021 20:41:36 GMT

that did it. and we have a winner!!! code worked a treat. i'll have to get my head around numpy one day 💪

thanks @DanPatterson

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Sat, 27 Nov 2021 21:16:30 GMT

@DanPatterson , just found that NULL cells are being replaced by "nan". Is there any way to replace nan with ""? And, oddly, one of the string fields contains a load of empty spaces before the text, in a way that makes the text appear centrally justified in the cell. Any ideas why that's happening?? In fact, all cells containing text are doing this.

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Sun, 28 Nov 2021 00:49:02 GMT

Good! More numpy lessons

As for empty cells! you need to provide a null value, you can't have "" in a numeric column but you can provide another number like -999

# -- Reference **** # https://numpy.org/doc/stable/reference/generated/numpy.savetxt.html def save_txt(a, name="arr.txt", sep=", ", dt_hdr=True): """Save a NumPy structured/recarray to text.""" a_names = ", ".join(a.dtype.names) hdr = ["", a_names][dt_hdr] # use "" or names from input array s = np.array(a.tolist(), dtype=np.unicode_) widths = [5, 5] frmt = sep.join(["%-{}s".format(i) for i in widths]) # ** note "%-{}s" np.savetxt(name, a, fmt=frmt, header=hdr, comments="") # print("\nFile saved...")

A text sample gdb table with just text columns

Package, Required_by atomicwrites, pytest; spyder chardet, requests; spyder cloudpickle, spyder; spyder-kernels diff-match-patch, spyder intervaltree, spyder jedi , ipython; python-language-server; spyder jinja2, jupyterlab; jupyterlab_server; nbconvert; notebook; sphinx; spyder-notebook keyring, arcgis; spyder nbconvert, notebook; spyder nbformat, ipywidgets; nbconvert; notebook; spyder-notebook notebook, arcgis; jupyterlab; jupyterlab_server; pro_notebook_integration; spyder-notebook; widgetsnbextension paramiko, spyder parso, jedi; spyder pexpect, spyder pickleshare, ipython; spyder psutil, spyder qdarkstyle, spyder; spyder-notebook qt , pyqt; sphinx; spyder qtawesome, spyder qtconsole, spyder requests, arcgis; jupyterlab_server; requests-kerberos; requests-oauthlib; requests-toolbelt; requests_ntlm; sphinx; spyder-notebook rtree, spyder sphinx, numpydoc; sphinx; spyder traitlets, ipykernel; ipython; ipywidgets; jupyter_client; jupyter_core; nbconvert; nbformat; notebook; qtconsole; spyder-notebook watchdog, spyder

text, left justified minimum width per column rather than let it pad out

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Sun, 28 Nov 2021 06:28:21 GMT

@DanPatterson thanks I'll try that out.

Can I not just convert all columns to string in order to remove nan values? Otherwise I'll leave as nan and remove in excel afterwards.

Where do you specify to replace empty cells with -9999? Cheers

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Sun, 28 Nov 2021 09:52:57 GMT

@DanPatterson adding in the '-' does remove blank spaces, but there's still a single space before the start of each string cell, and lots of white space after strings (padding out to match the number of characters in the other rows for that column), see below.

TreeRef, Species, OnOffSite, Height, NumStems, EstDiam, CalcActStemDiam, CrownRadii, AvgCanHt, F1stBranchHt, F1stBranchDir, LifeStage, SpecialImp, GeneralObs, HealthVit, StructCon, EstRemCon, BS5837_category, RPA_Radius_m, RPA_m2, SpecImpBuff, TPO, Sort T6 , Whitebeam (Swedish), On, 6.0, 1, None, 260.0, 3-3-2-2, 2.0, 2.0, N , SM, Veteran, Located in raised bed along north boundary of the site. Boundary retaining wall to north. Prolific mistletoe and dieback of crown. , Poor, Fair, <10, U , 3.1, 31.0, 8.5, None, 6 T7 , Whitebeam (Swedish), On, 6.0, 1, None, 280.0, 3-3-2-2, 2.0, 2.0, N , SM, None , Located in raised bed along north boundary of the site. Boundary retaining wall to north. Redundant stake and guard embedded in trunk. , Fair, Fair, 10+, C2, 3.3, 35.0, nan, None, 7 T8 , Whitebeam (Swedish), On, 6.0, 1, None, 280.0, 3-3-2-2, 2.0, 2.0, E , SM, None , Located in raised bed along north boundary of the site. Boundary retaining wall to north. Redundant stake and guard embedded in trunk. Cavity in trunk from 1.5-2.5m. , Fair, Fair, 10+, C2, 3.3, 35.0, nan, None, 8 T9 , Whitebeam (Swedish), On, 6.0, 1, None, 250.0, 3-3-2-2, 2.0, 2.0, E , SM, None , Located in raised bed along north boundary of the site. Boundary retaining wall to north. Original trunk severely decayed and hollowed; crown regenerating from secondary trunks. , Fair, Poor, <10, U , 3.0, 28.0, nan, None, 9 T10, Whitebeam (Swedish), On, 6.0, 1, None, 260.0, 3-2-2-2, 2.0, 1.5, NW, SM, None , Located in raised bed along north boundary of the site. Boundary retaining wall to north. Cavity in trunk from 1.5-2.5m. , Fair, Poor, 10+, C2, 3.1, 31.0, nan, None, 10 T11, Whitebeam (Swedish), On, 5.0, 1, None, 190.0, 3-2-2-1, 2.0, 2.0, E , SM, None , Located in raised bed along north boundary of the site. Boundary retaining wall to north. Trunk leans to east. , Fair, Fair, 10+, C2, 2.3, 16.0, nan, None, 11

...is there a way to remove all the excess spaces?

and if i change the width code to match yours

widths = [5, 5]

...then i get the following error;

Traceback (most recent call last):
  File "C:\Users\DAVID\Desktop\BS5837_TreePoint_v1.2.4.py", line 174, in <module>
    #          for j in range(s.shape[1])]
  File "C:\Users\DAVID\Desktop\BS5837_TreePoint_v1.2.4.py", line 170, in save_txt
    a_names = ", ".join(a.dtype.names)
  File "<__array_function__ internals>", line 6, in savetxt
  File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\Lib\site-packages\numpy\lib\npyio.py", line 1413, in savetxt
    raise error
ValueError: fmt has wrong number of % formats:  %-5s, %-5s

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Sun, 28 Nov 2021 10:22:42 GMT

widths = [5, 5]
frmt = sep.join(["%-{}s".format(i) for i in widths]) # ** note "%-{}s"

5 is the minimum number of spaces, some of your columns have 2 characters, so reduce it

widths = [5, 5]

is for 2 fields

Now, lets go back to the original which formatted each column to the maximum entry in each column. We could change it to some other value, or specify the minimum knowing that entries won't be truncated.

# changed 'max' to 'min' s = np.array(a.tolist(), dtype=np.unicode_) widths = [min([len(i) for i in s[:, j]]) for j in range(s.shape[1])] frmt = sep.join(["%{}s".format(i) for i in widths])

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Sun, 28 Nov 2021 10:30:23 GMT

Blog coming, bear with me, I have a number of helper functions and I will be showing how to bring in featureclass tables and standalone table replacing the horrible null with appropriate null values.

The originals are part of Table Tools for Pro - Esri Community

which I am currently updating to introduce new functionality and flexibility

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Sun, 28 Nov 2021 21:24:53 GMT

@DanPatterson thanks for your time. changing the value to min and also removing the blanks spaces in the sep="," variables fixed my issue.

I think the only last issue I have is that if a field contains any commas, these get utilised as separators, so the data shifts right in the columns. I need to wrap this text in double quotations?

Re: Modify CSV writer script to define list of columns by field name

DanPatterson — Sun, 28 Nov 2021 21:21:11 GMT

You are going to have that issue if you are converting to csv, period. There are many threads on this on the web and they usually entail editing your source files

Comma-separated values - Wikipedia

compare

1997,Ford,E350,"Super, luxurious truck"

1997,Ford,E350,"Super, ""luxurious"" truck"

what do you think would happen?

My solution? removing the commas is generally easiest if working with existing data

Re: Modify CSV writer script to define list of columns by field name

David_Brooks — Sun, 28 Nov 2021 21:44:31 GMT

in which case, i'll replace commas with semicolons before exporting. Might be the only way around it