How can I use Python to separate thousands with commas in an attribute table

7021
6
01-10-2018 04:41 AM
JamieTratalos
New Contributor

I want to format a large number of fields in an attribute table so that  they display with thousands separated by commas.

Is there any way to do this using python?

To do it via the GUI you would 1. display the attribute table, 2. right click on each field name, 3. select 'properties', 4. click on the '...' icon next to 'numeric', 5.click on 'show thousands separators' 6. select 'OK', 7. select 'OK' again.

I want to automate it so that the Python script would do this for all fields with an index number > 5 - that is, ignoring the first few fields which contain non-numeric data.

The reason I want to do this is that I want the field values to be displayed with thousands separators when they are displayed as labels in a map. 

Thanks!

0 Kudos
6 Replies
DanPatterson_Retired
MVP Emeritus

python allows that for strings in python 2.x, 

"{:,}".format(123456789)

'123,456,789'

but you will have to wait for python 3.6+ to get other options

https://www.python.org/dev/peps/pep-0515/

So if you want your numbers to have commas, then you have to make a text/string field to put the expression in

XanderBakker
Esri Esteemed Contributor

In Python you don't have access to change the properties of how a field is displayed. You can update the contents of the field or create additional fields as Dan mentioned. In ArcMap you would need ArcObjects to do this. How many fields do you have?

JamieTratalos
New Contributor

around 200 fields at the moment, but probably more in future work. 

0 Kudos
XanderBakker
Esri Esteemed Contributor

That is a lot of fields... I can imagine that you don't want to do that manually. However, how are you going to present this to the "end-user" how are they going to use this data? Having 200 fields or more with data may not be the easiest way to use the data. Can you explain a little more about the use case and what type of data you have?

Adding the text fields (as per the suggestions by Dan and Joshua) can be scripted, but then you will end up with twice the amount of fields... 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

What you are after is IFieldInfo.NumberFormat Property (ArcObjects .NET 10.5 SDK).  Unfortunately, Esri hasn't implemented that through ArcPy so the best you can do for now is either manually make the changes in the GUI or follow Dan's suggestion.

DanPatterson_Retired
MVP Emeritus

If there are 200 fields and more to come, I would strongly recommend that you have an alternate way for the client or you to 'view' the tabular information.  Options would include

  • scale the data...
    • ie instead of  meters.... change the field to kilometers
    • kilograms to tonnes
    • small to big .... whatever moves the commas and decimals
  • can't those popup things (? what are they called) present 'text' in the format I describe 
  • telling the client that it isn't possible given the current budget... or if it is just you... move on

OR

You could

  • export everything out to a numpy array,
  • do the data type conversion there and
  • bring in the new tabular information as a table join
    • (all this would use is TableToNumPyArray,
    • I can guide on the type conversion,
    • followed by an arcpy ExtendTable to bring the tabular information back in as a join

Multiple options, depending who the target audience is and how big a deal it is.