How to find first occurrence of a significant difference in values of a pandas dataframe?

One idea is filter by Series.quantile with Series of differences with change order of differencies by -1 and aboslute values, last get first index:

df = pd.DataFrame({'A':[1, 1.1, 1.2, 1.3, 1.4, 1.5, 7, 7.1, 7.2, 15, 15.1]})


x = df['A'].diff(-1) .abs()
print (x)
0     0.1
1     0.1
2     0.1
3     0.1
4     0.1
5     5.5
6     0.1
7     0.1
8     7.8
9     0.1
10    NaN
Name: A, dtype: float64

idx = x.index[x >= x.quantile(.75)]
print (idx)
Int64Index([5, 7, 8], dtype='int64')

print (idx[0])
5

The main issue is of course how you define a "large difference". Your solution is pretty good to get the largest difference, improved only by using .diff(-1) and using absolute values as shown by Jezrael:

differences = df['A'].diff(-1).abs()

Using absolute values matters if your values are not sorted, in which case you can get negative differences.

Then, you should probably do some clustering on these values and get the smallest index of the cluster with largest values. Jezrael already showed a heuristic by using the largest quartile, however by only slightly modifying your example this doesn’t work:

df = pd.DataFrame({'A': [1, 1.05, 1.2, 1.3, 1.4, 1.5, 7, 7.1, 7.2, 15, 15.1]})
differences = df['A'].diff(-1).abs()
idx = differences.index[differences >= differences.quantile(.75)][0]
print(idx, differences[idx])

This returns 1 0.1499999999999999

Here's 3 other heuristics that might work better for you:

• If you have a value above which you consider a difference to be “large” (e.g. 1.5):

  idx = differences.index[differences >= 1.5][0]
  

• If you know how many large values there are, you can select those and get the smallest index (e.g. 2):

  idx = differences.nlargest(2).index.min()
  

• If you know all small values are grouped together (as are all the 0.1 in your example), you can filter what's larger than the mean (or the mean + 1 standard deviation if your “large” values are very close to the smaller ones).

  idx = differences.index[differences >= differences.mean()][0]
  

  This is because contrarily to the median, your few large differences will pull the mean up significantly.

If you really want to go for proper clustering, you can use the KMeans algorithm from scikit learn:

from sklearn.cluster import KMeans

kmeans = KMeans(n_clusters=2).fit(differences.values[:-1].reshape(-1, 1))
clusters = pd.Series(kmeans.labels_, index=differences.index[:-1])
idx = clusters.index[clusters.eq(np.squeeze(kmeans.cluster_centers_).argmax())][0]

This classifies the data into 2 classes, and then gets the classification into a pandas Series. We then filter this series’ index by selecting only the cluster that has the highest values, and finally get the first element of this filtered index.

If you have a Numpy Array, which you can use any dataframe row as, you can use numpy.argmax.

import numpy as np

import numpy as np

a = np.array([1, 1.1, 1.2, 1.3, 1.4, 1.5, 7, 7.1, 7.2, 15, 15.1])
diff = np.diff(a)
threshold = 2 # set your threshold
max_index = np.argwhere(diff> threshold) [[5],[8]]

References: https://numpy.org/doc/stable/reference/generated/numpy.diff.html https://numpy.org/doc/stable/reference/generated/numpy.argwhere.html

More Info:

pandas.diff will calculate the diff diff[i] = a[i] - a[i-1]
numpy.diff will calculate the diff diff[i] = a[i+1] - a[i],
with the exception of i=max len:

• a[i] = a[i]-a[i-1]

def shift(a):
  
  a_r = np.roll(a, 1) # right shift
  a_l = np.roll(a, -1) # left shift


  return np.stack([a_l, a_r], axis=1)

a = np.array([1, 1.1, 1.2, 1.3, 1.4, 1.5, 7, 7.1, 7.2, 15, 15.1])

diff = abs(shift(a) - a.reshape(-1, 1))

diff = diff[1:-1]

indices = diff.argmax(axis=0) - 2

a[indices]

array([7. , 1.5])