Dataset that has multiple cities within a single cell

Question

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 · Answer

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']‍‍‍‍‍

DannyShapich · Answer

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

DannyShapich · Answer

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

Thanks,