Averaging string input based on x,y coordinate values

[Christine Gopon]

I am working with a string dataset with spatial coordinates (x,y). For this example I'll just use colors for the string inputs to make it easier to visualize. I have attached an image with an example dataset.

The goals I am trying to accomplish are:

 

For each color value (in this example, let's use red), I want to know thexdistance for each set of consecutive similar color values for each value of y.

 

e.g. for y=1, there would be a return of 3 values: 1, 1, and 2

For y=2, there would be a return of 2 values: 2, and 2.

 

 

I would then like to create an average x distance (red) for each Y value.

 

e.g. for y=1, the average would be (1+1+2)/3 = 1.33

for y=2, the average would be (2+2)/2 = 2.

 

 

 

Hopefully the x-y plot visualization helps to illustrate what I am trying to get at. Any suggestions for executing this in Spotfire Is it easierto try to try and do some of the number crunching in another program such as excel first

Thanks!

[Fabian Duerr]

Can you please clarify your examples for y = 1 and y = 2

 

For y = 1 I see 4 pairs for red (X/Y): (1,1) (3,1) (5,1) (6,1). So the X distance would be 2, 2, 1

 

For y = 2 I see 4 pairs for red(X/Y): (1,2) (2,2) (4,2) (5,2). So the X distance would be 1, 2, 1

 

Either your png doesn't match your text or I don't get the idea ;)

 

When this is solved we can continue with your question.

[Christine Gopon]

Ah, yes, I think I see what you are doing. I am interested in consecutive points, and treating each block of consecutive points as its own set. I suppose I really want to know the number of points in each set. So for a single red point with no red neighbors, the "x distance" (for lack of a better term), or number of points, would be equal to 1. For two consecutive red points, the x distance/number of points = 2. For three consecutive points (not pictured in this example) the x distance/number of points = 3, etc. Perhaps calling it "x distance" was a bit misleading, apologies. 

 

 

 

[Fabian Duerr]

I think it is rather complicated to solve this purely with calculated column. The biggest task is to group your data together. You can have a look at OVER (Intersect(..., Next())) and OVER (Intersect(..., Previous())) functions. They could be helpful.

 

I prefer to write simple data functions that iterate thru each row for tasks like this. Have you ever worked with data functions