topic Re: Interpolation in Alteryx Designer Desktop Discussions

Interpolation

StephenMack — Thu, 24 May 2018 03:57:17 GMT

I have a dataset with lat/lon information. My problem is that I have multiple duplicate entries and would like to interpolate between them.

For instance say I have 1000 unique GPS points, but 10,000 recorded data points (associated with other unique information that updated faster than GPS when collecting).

It's not always a fixed amount of points between unique GPS points so it has to be somewhat of a dynamic solution. I can probably write a C# or python script to handle it and run it externally from the run command, but that's a pretty ugly solution, is there a way to handle it within alteryx?

I was thinking multi-line formula, but the fact that there are different numbers of duplicates in a row is throwing me off.

Re: Interpolation

NicoleJohnson — Tue, 11 Jul 2017 14:36:13 GMT

Can you please provide some sample or dummy data to illustrate your starting data and expected result? That will help us visualize your question better! Thanks!

Re: Interpolation

StephenMack — Tue, 11 Jul 2017 14:41:24 GMT

I've attached a file. So every row has unique data, but GPS didn't update fast enough so some rows have old duplicate GPS locations. If possible I'd like to interpolate between them.

For example:

in the attached CSV rows 14,15,16 have duplicate GPS points. I'd like to interpolate 15 and 16 using row 14 and 17

Re: Interpolation

Philip — Tue, 11 Jul 2017 15:12:15 GMT

Hi @StephenMack

By interpolate, do you mean take the average of the values found at duplicate point locations?

Re: Interpolation

NicoleJohnson — Tue, 11 Jul 2017 15:56:13 GMT

Assuming the answer to @Philip question is yes, i.e. straight-line interpolation, then I believe the following will work. Although there is very likely a much faster/cleaner way to get there (this felt like a lot of tools to get to a fairly simple result)... it should work.

1. Add RecordID

2. Find Unique Lat/Lon combos

3. Use Multi-Row Formulas to find the difference between each Lat/Lon Combo, then divide by the number of rows between them, i.e. if there is a difference of 0.2 between RecordID 1 and RecordID 3, then the incremental amount that will be added to RecordID 2 when the data is brought back together should be 0.1, or 0.2/(3-1).

4. Find & Replace tool to join the NewLat & NewLon incremental amounts back to the original data by RecordID

5. Sort by Descending RecordID

6. If NewLat/NewLon is null, then use the row above

7. Sort by Ascending RecordID

8. Tile by Unique Value to find groupings by Lat/Lon combo

9. Formula: If the Tile Sequence = 1 then leave Lat/Lon as is, otherwise add the NewLat/NewLon to determine the interpolated location

Let me know if this gets you the data you need, or if additional tweaking/simplification is needed?

Cheers!

Re: Interpolation

StephenMack — Tue, 11 Jul 2017 16:05:48 GMT

That looks wonderful thank you!

I was having trouble working out the correct way to use the multi-line formula, but I think how you added the recordID and separated out unique values to put it back together later is the right way to approach it.

Thank you!!

Re: Interpolation

jbh1128d1 — Fri, 17 Aug 2018 19:24:03 GMT

What if there are different numbers of rows. For example, what if you have two null value in location, three in another, and 1 in another? In excel, you'd hit F4 to set the second value and row ID but I'm not sure how you would do that in Alteryx.

Re: Interpolation

StephenMack — Fri, 17 Aug 2018 19:33:38 GMT

I'm not totally sure what you mean.

You're saying if you have:

location

Are you asking how would you interpolate the different number of redundant rows between each unique location? So each individual interpolation could be different lengths? I believe the solution above handles that.

Re: Interpolation

jbh1128d1 — Fri, 17 Aug 2018 19:35:08 GMT

See this: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Linear-Interpolation-of-Values/m-p/198668

Re: Interpolation

StephenMack — Fri, 17 Aug 2018 19:45:44 GMT

I think I understand, and maybe you can force your problem to fit into what I have in this post.

Can you force all the nulls to equal the last known value? Maybe on a multiline formula you can say

if [Value]=Null then [Row-1:Value] else [Value] endif

That way instead of having something like

null

you'll get

and then using the solution in this post all the non-unique sections will be interpolated

Re: Interpolation

jbh1128d1 — Fri, 17 Aug 2018 19:57:12 GMT

I'm getting zero's for some reason. I'll keep plugging. Thank you for helping.

Re: Interpolation

MAAbdullahAlMubarah — Sun, 27 Jan 2019 14:11:32 GMT

Thank you @NicoleJohnson for your valuable post, now if we have the opposite case, let's say there is (GPS OFF) and back again on, how can we find the missing coordinates e.g.: (missing n points) and display it again on the map from the data when the GPS off?
I don't have data but if possible can you simulate it from the original data available in this post?

Re: Interpolation

StephenMack — Mon, 28 Jan 2019 14:05:02 GMT

So you lat/lon would have gaps in it? That's essentially what my data set looked like but instead of dropping in NULLs the software I was using simply used the last good GPS point.

So if you use a multi-line tool and say something like

if isNull([this lat]) then [this lat: -1] else [this lat] endif

so you would fill in the gaps with the last known good GPS point. From there the above solution should work for you.