Get the row in a CSV where xyz coordinates stop changing

Question

I have a measurement of an object with xyz coordinates and a timeline in ms. My CSV looks like this:

TimeInSec  X.6   Y.6  Z.6
0.000000    -1.383422   -0.058891   0.023290    
0.004167    -1.387636   -0.058947   0.023584
0.008333    -1.391491   -0.058972   0.023989
...

I want to find the row in my dataset where the xyz coordinates stop changing (within a threshold). The key feature I want is a time from row 0 to the stop point of my object.

My Code:

dummy.data <- read.csv (file="D:\\tmp\\dummy.csv", header = TRUE, skip = 6
dummy.data %>%
  gather(key,value, X.6, X.7, X.8, Y.6, Y.7, Y.8, Z.6, Z.7, Z.8) %>%
  ggplot(aes(x=Time..Seconds., y=value, colour=key)) +
  geom_line()

Many Thanks for your help!

Sample Graph: Sample Graph

Here is the link to the RawData CSV RawData

Andrew Chisholm · Accepted Answer · 2020-03-03T22:11:41.363

Here's an updated example that uses exactly the same code as before but now I made some dummy data that shows different offsets and the data settles to a constant value eventually. The point is that successive points will get closer and closer so a Euclidean distance (think of this as the actual distance) between successive points will get smaller. Once below the threshold, we declare the points to have settled.

library(tidyverse)
library(ggplot2)
numberofpoints <- 100
threshold <- 0.01
set.seed(1)
dummy.data <- # make some dummy data with offsets
    data.frame(
        X.6=runif(numberofpoints), X.7=runif(numberofpoints), X.8=runif(numberofpoints),
        Y.6=runif(numberofpoints), Y.7=runif(numberofpoints), Y.8=runif(numberofpoints),
        Z.6=runif(numberofpoints), Z.7=runif(numberofpoints), Z.8=runif(numberofpoints)) %>%
    mutate(
        X.6=3+X.6/row_number(), X.7=1+X.7/row_number(), X.8=2+X.8/row_number(),
        Y.6=4+Y.6/row_number(), Y.7=6+Y.7/row_number(), Y.8=9+Y.8/row_number(),
        Z.6=5+Z.6/row_number(), Z.7=7+Z.7/row_number(), Z.8=10+Z.8/row_number()
    )

distances <- dist(dummy.data)  # find distances between all pairs of readings (will be slow for large data)
distances.matrix <- as.matrix(distances)
# distances between adjacent readings
distancechange <- c(NA,unlist(sapply(1:numberofpoints-1, function(r) distances.matrix[r,r+1])))
# the first point below the threshold
changebelowthreshold <- min(which(distancechange < threshold))

# Plot something
dummy.data$Time <- 1:nrow(dummy.data)
thresholdtime <- dummy.data$Time[changebelowthreshold]
plotdata <- dummy.data %>% pivot_longer(cols=c(X.6, X.7, X.8, Y.6, Y.7, Y.8, Z.6, Z.7, Z.8))
gg <- ggplot(plotdata, aes(x=Time, y=value, colour=name)) + geom_line() + geom_vline(xintercept = thresholdtime)

This makes the following plot.

The vertical line shows where the data is below a threshold.

Thanks for your help! your Graph is excet what i'm looking for. But how can get it work when each of the values x,y and z have a diffrent null point. my data was gernerated by tracking a moving Object in a 3d room. so each of the Points are diffrent. Have a look at my chart genreated by my sample code — holber, Feb 28 '20 at 11:43
@holber, can you transform the data to make it have a common null-point? My guess is that you would be interested in _relative_ values, and not _absolute_, thus a transformation may be warranted. — mhovd, Feb 28 '20 at 12:11
@holber - you should post your data - use `dput` to do this and include the output in the original question. — Andrew Chisholm, Feb 28 '20 at 12:28
I updated the answer with some new dummy data to match what you are looking for. The code is unchanged. — Andrew Chisholm, Feb 28 '20 at 13:41
thanks for all your help. i posted the RawData CSV i get from my tracking software only the markers from X.6, X.7, X.8, Y.6, Y.7, Y.8, Z.6, Z.7, Z.8 are relevant — holber, Mar 03 '20 at 14:04
@huber - you need to post your data in a reproducible and standalone way so the code can be run without relying on external sites. See https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example for details. But basically, you need to do something like `dput(head(your_tidied_data))` and put that in the original question. Having said, that, I have imported your data and with the code above, I can find when the points settle. — Andrew Chisholm, Mar 03 '20 at 22:18

score 1 · Answer 2 · answered Feb 28 '20 at 10:37

I think from your description you just want to identify a point where the differences between x, y and z values in consecutive rows drop below a certain threshold:

threshold <- 0.001
stop_row  <- which(abs(diff(dummy.data$X.6)) < threshold &
                   abs(diff(dummy.data$Y.6)) < threshold &
                   abs(diff(dummy.data$Z.6)) < threshold )

So now you can do:

result <- dummy.data$TimeInSec[stop_row] - dummy.data$TimeInSec[1]

Get the row in a CSV where xyz coordinates stop changing

2 Answers2