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

[David_Brooks]

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

[David_Brooks]

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 

[David_Brooks]

@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.

[David_Brooks]

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

[DanPatterson]

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 

[David_Brooks]

@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

[DanPatterson]

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

[David_Brooks]

@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

[DanPatterson]

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

[David_Brooks]

@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?

[DanPatterson]

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