Jump to content

Averaging string input based on x,y coordinate values

Christine Gopon

Recommended Posts

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


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 





Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...