topic Re: Create additional rows based on a comma separated field? in Spatial Data Science Questions

Create additional rows based on a comma separated field?

AaronKoelker — Thu, 21 Sep 2017 15:08:21 GMT

I have a table of proposals which includes a field for counties that the proposal will affect. Unfortunately, the COUNTY field has multiple counties in it, separated by a comma. (Pic is from an excel attempt).

What I want to do is find a way to create a duplicate rows so that each only has one county in the county field. (The end result is that I need to have a map of counties that will be symbolized by the number of proposals affecting them.)

So the highlighted Proposal 4 above would turn into something like this:

Then I can join to a county shapefile and easily get a count. It's simple enough in Excel to split a cell into columns and then transpose them into rows, however I need to pull the additional data from the other fields as well. There are 1500 rows, so I need an automated way to do this either in ArcMap or through Excel.

Or perhaps I'm going about this all wrong and there is a way to relate the table to a county shapefile based on a comma separated field?

Any help would be appreciated!

Re: Create additional rows based on a comma separated field?

DanPatterson_Retired — Thu, 21 Sep 2017 17:04:47 GMT

tough one, but if your intent is to get summaries and the like, you might want to consider that queries can parse substrings from a string. Consider your Counties field, if you wanted to get all the records that are associated with Bay county, your query would be

Counties Like '%Bay%'‍

or variants such as

Counties = 'Bay' (explicit check)

(Counties = 'Bay') Or (Counties Like '%Bay,') (as above, but check for an immediately following comma)

Setting up queries to run through a Summarize might be easier that trying to reinvent/reformat a database structure which is being a bit stretched beyond its original purpose/vision.

Re: Create additional rows based on a comma separated field?

JoshuaBixby — Thu, 21 Sep 2017 20:53:59 GMT

I thought a very similar question was recently answered on GeoNet, but I can't find it at the moment. In the meantime, a similar question has been answered on StackExchange: arcgis desktop - Splitting strings into new rows, retaining geometry - Geographic Information Systems Stack Exchange .

I will post back if I find the GeoNet question I am thinking about, or if I come up with my own solution.

Re: Create additional rows based on a comma separated field?

AaronKoelker — Thu, 28 Sep 2017 21:04:03 GMT

That looks about what I'm trying to do. When I get a chance to try it out, I will post an update here. Thanks!