Select to view content in your preferred language

How to tidy up a field with comma separated values?

334
4
Jump to solution
a month ago
Labels (2)
BillSands
New Contributor II

I have a feature layer with bus stops on, and I've done a spatial join with a bus routes layer to add the bus route to the bus stop. I now have a field on my bus stops layer that shows the route. This field has comma separated values in.

The problem I have, is that the bus route layer has a route for each direction of the bus, which means that a but stop get's the bus route in twice.

How can I now clean out the entries for the route field, so that each route number is only shown once for each bus stop? The image below shows the route data highlighted on the right. The simplest example on there is Earlsthorpe Road, which shows "75, 75" but I'd like it to only show "75"

BillSands_0-1718987004992.png

I hope that is clear enough. 

I should note that I am very new to ArcGIS, so go gentle with any explanation please.

Thank you.

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Esteemed Contributor

You could do this with a Field Calculation. In Pro, you can do it in Python or Arcade. Here's what it would look like in Arcade, where there is a function, Distinct.

// get your routes into an array by splitting by the commas
var routes_arr = Split($feature.routes, ',') // replace "routes" with your field name

// get the distinct values
var distinct_routes = Distinct(routes_arr)

// return the distinct values, joined by commas
return Concatenate(distinct_routes, ',')

 

- Josh Carlson
Kendall County GIS

View solution in original post

4 Replies
jcarlson
MVP Esteemed Contributor

You could do this with a Field Calculation. In Pro, you can do it in Python or Arcade. Here's what it would look like in Arcade, where there is a function, Distinct.

// get your routes into an array by splitting by the commas
var routes_arr = Split($feature.routes, ',') // replace "routes" with your field name

// get the distinct values
var distinct_routes = Distinct(routes_arr)

// return the distinct values, joined by commas
return Concatenate(distinct_routes, ',')

 

- Josh Carlson
Kendall County GIS
BillSands
New Contributor II

Thank you @jcarlson 

That's much appreciated. I should note that I couldn't get it to work for a while as I kept getting an error message. It was due to the data being joined using a Spatial Join which was live. I eventually worked out that I could do a permanent Spatial Join, which meant that the Arcade script worked.

Thanks for your help.

Bill

0 Kudos
DanPatterson
MVP Esteemed Contributor
# replace 'a' with your field name enclosed in !  eg !your_field! 

a = "75, 75, 76, 1, 1, 99, 99, 99"

", ".join(list(set([i.strip() for i in a.split(",")])))
'76, 75, 1, 99'

# or 
", ".join(sorted(list(set([i.strip() for i in a.split(",")]))))
'1, 75, 76, 99'

... sort of retired...
BillSands
New Contributor II

Thank you @DanPatterson for your suggestion.

I tried the other suggestion first and made that work, so I didn't try your solution. Thanks for taking the time to reply though, it's very much appreciated.

Bill

0 Kudos