While working on my spatial project, I found a useful technique for data parsing a field with 2 different formats (commonly seen in reports).
Side note: You’ll notice that my files are labelled “Brittany’s Activity Data”. One of my friends in Calgary, CA was kind enough to give me her running data for the last 2 years. She’s stopped for the last couple of months but hopefully we’ll find some insightful info to get her to run again! If you’re interested, she uses the Garmin system for her run tracking.
First step was to download all those kml files from the Garmin portal and configure Alteryx to port all the files from this designated folder.
Here’s what it looks like after I’ve imported the .kml files. Her individual runs stack on top of each other for a table of 25K records.
There’s 2 key problems here (the second of which I didn’t discover until yesterday morning). The first problem is that there are 2 different file formats and pieces of data in the same column (see the “Name” field where it includes data about 1) when Brittany started/ended her runs and 2) her run times, heart rate, elevation and max speed during the lap. We see this commonly in reports that have been ported into reports. I felt this was a good “data wrangling” moment haha.
The solution? Separate the data into 2 segments and treat separately. When Carl (one of our superstar consultants!) first suggested this, I couldn’t wrap my mind around it. If this is also you, here’s the workflow.
Here’s what the files look like after the the transformation
File for when the “Lap?” field equals “0”
File for when the “Lap?” field equals “-1”
Once I got here, I “text-to-columns” the Description field (away I go!). Hopefully this helps someone facing a similar “Data Wrangling” scenario a solution 😀
Anyone figured out what the second problem was? Stay tuned for the another post!