topic Re: How can I use Python to separate thousands with commas in an attribute table in Python Questions

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

JamieTratalos — Wed, 10 Jan 2018 12:41:55 GMT

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!

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

DanPatterson_Retired — Sat, 11 Dec 2021 13:06:51 GMT

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

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

XanderBakker — Wed, 10 Jan 2018 14:44:54 GMT

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?

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

JoshuaBixby — Wed, 10 Jan 2018 14:46:31 GMT

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.

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

JamieTratalos — Wed, 10 Jan 2018 15:48:07 GMT

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

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

XanderBakker — Wed, 10 Jan 2018 16:09:56 GMT

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

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

DanPatterson_Retired — Wed, 10 Jan 2018 16:19:14 GMT

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

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.