Messy Coordinates Field Needs Cleanup!

[vbaumel]

Hi everyone-

I've got a field that's coming in as type v_wstring and it's data representing coordinates. There are a lot of different formats the values take, however, and I'm struggling to find the best way forward. I'd love to hear any ideas you all might have on how to make this data useful - here's a sample of what it looks like:

4.0818°S 39.72°E
04°02′50″S39°43′57″E
[Null]
25°35′S 32°59′E
13.048°S 45.195°E
2°N 47°E
9°32′N 50°59′E
7°52′6″S 39°14′24″E
6°8′54″S 39°11′36″E
5°39′23″S 39°28′27″E
1°59′2″S 32°19′7″E
0°39′23″S 32°8′41″E
[Null]
17°00′09″S49°50′31″E
[Null]
36°45′N 05°11′E
36°44′N 1°31′E
36°52′N 05°01′E
37°0′N 3°0′E
36°49′13″N 3°9′55″E
36°15′N 00°15′E
37°1′12″N 5°12′6″E
36°56′0″N 5°40′0″E
31°18′N 29°49′E
31°45′N 25°31′E
[Null]
[Null]
31.46°N 28.00°E
[Null]
32°4′24″N 24°4′42″E
32°4′33″N23°58′21″E

My initial thoughts went to RegEx to split the data out by format then re-union them all together, but if there's a better option I'm all ears. The null values can stay null - I'd rather not plot them than plot them somewhere inaccurately. In the end I'm going to be pushing this data into Tableau for visualization, if that's relevant.

Thanks,

Vince

[Maskell_Rascal · Accepted solution]

Hi @vbaumel 

I'd use a RegEx Tool to parse out the two coordinates. Below is the code and screenshot of the configuration and output. 

RegEx Code:

(\d.{1,}[A-Z])\s?(\d.{1,}[A-Z])

Workflow/Output:

Let me know if this helps.

Cheers!

Phil

[binuacs]

@vbaumel Another way of doing this with the Regex_Replace() function

[vbaumel]

Thanks guys! Both of these solutions work, though I'm curious what the '$1' and '$2' refer to in regex_replace approach. Do those just identify which grouping within the regex to bring forward?

-Vince

[Maskell_Rascal]

@vbaumel  - You're spot on there. Each statement in the formula provided by @binuacs is grouped using parentheses. The $1 refers to the first group and the $2 refers to the second group. There is a really great overview of Alteryx and RegEx from @MarqueeCrew from the 2021 Alteryx Inspire conference. 

You can check it out here: https://community.alteryx.com/t5/Inspire-Presentations/To-RegEx-or-Not-to-RegEx-That-Is-the-Question...

Cheers!

Phil

[MarqueeCrew]

@Maskell_Rascal ,

Thanks for the call-out and for being the one person who saw this session :)

I'll post the link on social.  I didn't even know that it was floating around out there.

Cheers,

Mark