bikhaab bikhaab - 6 months ago 16
Python Question

Applying diff on selected rows

I have a data frame like this that want to apply diff function on:

test = pd.DataFrame({ 'Observation' : ['0','1','2',
'3','4','5',
'6','7','8'],
'Value' : [30,60,170,-170,-130,-60,-30,10,20]
})

Observation Value
0 30
1 60
2 170
3 -170
4 -130
5 -60
6 -30
7 10
8 20


The column 'Value' is in degrees. So, the difference between
-170
and
170
should be
20
, not
-340
. In other words, when
d2*d1 < 0
, instead of
d2-d1
, I'd like to get
360-(abs(d1)+abs(d2))


Here's why I try. But then I don't know how to continue it without using a for loop:

test['Value_diff_1st_attempt'] = test['Value'].diff(1)
test['sign_temp'] = test['Value'].shift()
test['Sign'] = np.sign(test['Value']*test['sign_temp'])


Here's what the result should look like:

Observation Value Delta_Value
0 30 NAN
1 60 30
2 170 110
3 -170 20
4 -130 40
5 -60 70
6 -30 30
7 10 40
8 20 10


Eventually I'd like to get just the magnitude of differences all in positive values. Thanks.

Update: So, the value results are derived from
math.atan2
function. The values are from
0<theta<180
or
-180<theta<0
. The problem arises when we are dealing with a change of direction from
170
(upper left corner) to
-170
(lower left corner) for example, where the change is really just
20
degrees. However, when we go from
-30
(Lower right corner) to
10
(upper right corner), the change is really
40
degrees. I hope I explained it well.

Answer

I believe this should work (took the definition from @JasonD's answer):

test["Value"].rolling(2).apply(lambda x: 180 - abs(abs(x[0] - x[1]) - 180))
Out[45]: 
0      NaN
1     30.0
2    110.0
3     20.0
4     40.0
5     70.0
6     30.0
7     40.0
8     10.0
Name: Value, dtype: float64

How it works:

Based on your question, the two angles a and b are between 0 and +/-180. For 0 < d < 180 I will write d < 180 and for -180 < d < 0 I will write d < 0. There are four possibilities:

  • a < 180, b < 180 -> the result is simply |a - b|. And since |a - b| - 180 cannot be greater than 180, the formula will simplify to a - b if a > b and b - a if b > a.
  • a < 0, b < 0 - > The same logic applies here. Both negative and their absolute difference cannot be greater than 180. The result will be |a - b|.
  • a < 180, b < 0 - > a - b will be greater than 0 for sure. For the cases where |a - b| > 180, we should look at the other angle and this translates to 360 - |a - b|.
  • a < 0, b < 180 -> again, similar to the above. If the absolute difference is greater than 180, calculate 360 - absolute difference.

For the pandas part: rolling(n) creates arrays of size n. For 2: (row 0, row1), (row1, row2), ... With apply, you apply that formula to every rolling pair where x[0] is the first element (a) and x[1] is the second element.