Dataset that has multiple cities within a single cell

[DannyShapich]

I have a dataset that has multiple cities within a single cell (separated by pipes, colons, and commas).  Example: Suwon;South Korea;Asia|Seoul;South Korea;Asia

What can I use to separate those cities into different rows (along with the associated data) so that ArcGIS Pro can utilize it?

[DanPatterson_Retired]

are there other data associated with each row? or is that it?

[DannyShapich]

Yes there is data in other columns that would need to be copied to the rows (with separate cities).

Thanks,

Dan Shapich | Programmer/Analyst

Global Programs

316 Boucke Building

The Pennsylvania State University

University Park, PA 16802

Email: dms139@psu.edu

Phone: 814-865-2435

[DannyShapich]

Here is an example:

University of Leeds Leeds;United Kingdom;Europe|London;United Kingdom;Europe

I would require:

University of Leeds Leeds

University of Leeds London

I hope that helps.

Dan Shapich | Programmer/Analyst

Global Programs

316 Boucke Building

The Pennsylvania State University

University Park, PA 16802

Email: dms139@psu.edu

Phone: 814-865-2435

[DanPatterson_Retired]

can you post table (dbf or gdb, csv or excel sheet) with a couple of rows.

It is easy to do with python and numpy using TableToNumPyArray and processing the array.

You have to repeat the number of cities by an unknown amount.

parsing the cities isn't a real problem, setting up the repeats needs to be tested unless you know that there will always be 2 cities, no more, no less.

 a = np.asarray([["University of Leeds", "Leeds;United Kingdom;Europe|London;United Kingdom;Europe"]])

[i.split(";")[0] for i in a[0][1].split("|")]

['Leeds', 'London']‍‍‍‍‍