I am trying to do some analysis on some data that comes from special glasses that track a few things including pupil size and gaze velocity. I would like to calculate the correlation between two glasses on two different people. At the moment I cannot use df.correlate() because the timestamps are not identical and therefore the data looks something like this:

index | ts | r_person | l_person
0     | 23 | 3.0      | NAN
1     | 25 | NAN      | 3.2
2     | 28 | 3.1      | NAN
3     | 32 | 3.0      | NAN

I was wondering if there was still any way to calculate a correlation directly.

At the moment I was thinking of possible filling the NAN values with the averages of the data points above and below. For example row 2 column r_person would become $3.05$.

This would be less trivial than it seems because even at the start it wasn't always one data point R one data point L and after cleaning the data it has become less so. In other words multiple NAN values might appear in the same column as you can see in the example. I can still deal with that by just spreading out the average. My second technique was going to try and merge the values together if they were close enough. Bearing in mind the data was collected at 50hz.

My question is whether anyone has a quicker or better way of aligning the data without losing it or changing it too much?

  • $\begingroup$ It also depends on the error of your measurements. If you think they are noisy, you may use EWMAs or build and estimate some other model. $\endgroup$
    – Valentas
    Feb 15, 2018 at 7:21

2 Answers 2


At the moment I was thinking of possible filling the NAN values with the averages of the data points above and below. For example row 2 column r_person would become $3.05$.

The problem here is that the missing data point isn't necessarily associated with a timestamp that is the average of the timestamps above and below. If it were, the procedure you suggested would be equivalent to a linear interpolation, i.e. drawing a line between the points before and after the missing timestamp, and using that line to construct a local prediction for the missing observation. Which is what you should actually do instead.

For example in the case above, the line for the point your trying to interpolate is $\text{r_person}(ts) = 3.0 +\frac{.1}{5}(ts-23)$, so $\text{r_person}(25) = 3.04$ (I think... doing this in my head right now).

  • $\begingroup$ It’s not necessary a linear interpolation but I’m not sure what else to do. At the moment I have implemented a linear interpolation. We are taking about 150,000+ data points. $\endgroup$
    – Tank
    Feb 15, 2018 at 6:10
  • $\begingroup$ There's absolutely nothing wrong with a linear interpolation. It's quick and dirty but it gets the job done. You could get fancier with LOESS smoothing or b-splines, but the result probably wouldn't be very different. $\endgroup$
    – David Marx
    Feb 15, 2018 at 7:52

You could collect your time points into half-open intervals like $\dots, [20, 25), [25, 30), [30, 35), \dots$ and correlate the average observations for r_person and l_person. An example with a few more data points:

import numpy as np
import pandas as pd

df = pd.DataFrame([[0, 23, 3.0, np.nan],
                   [1, 24, 2.9, np.nan],
                   [2, 25, np.nan, 3.2],
                   [3, 27, 3.0, np.nan],
                   [4, 27, np.nan, 3.3],
                   [5, 28, 3.1, np.nan],
                   [6, 29, np.nan, 3.2],
                   [7, 32, 3.0, np.nan]],
                  columns=['index', 'tx', 'r_person', 'l_person'])

bin_min = df['tx'].min() // 5 * 5
bin_max = (df['tx'].max() // 5 + 1) * 5
bins = range(bin_min, bin_max + 1, 5)

df['tx_bins'] = pd.cut(df['tx'], bins)
df_binned = df[['tx_bins', 'r_person', 'l_person']].groupby('tx_bins').mean()

This yields

>>> df_binned
          r_person  l_person
(20, 25]      2.95      3.20
(25, 30]      3.05      3.25
(30, 35]      3.00       NaN

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.