Calculations on unordered Data frame

Question

Hi All i have created a dummy DF

left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                        'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})
 

right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3','K0', 'K1', 'K2', 'K3'],
                            'C': [7, 11, 9, 13,9, 6, 10, 5],
                            'D': [1, 2, 1, 2,2, 1, 2,1]})
result = pd.merge(left, right, on='key')

output:

    key A   B   C   D
0   K0  A0  B0  7   1
1   K0  A0  B0  9   2
2   K1  A1  B1  11  2
3   K1  A1  B1  6   1
4   K2  A2  B2  9   1
5   K2  A2  B2  10  2
6   K3  A3  B3  13  2
7   K3  A3  B3  5   1

The problem I am trying to solve is that I want to group the entries by the key Value, and perform a mathematical operation on it, you will notice that there is 2 entries in the column

so for each key, if the top value is less than the bottom value in column D, perform simple addition on the column C entries, in this case the math would only be applied to index =[4,5,6,7] and the calculations would be

9-10 = -1
13-5 = 8

Ideally these results would be stored in a list, I know the data structure is not ideal but this is what I have been given to work with, and i have no idea how to approach it

user_11097866 · Answer

# perform checking on column D (and add "check" column)
result["check"] = result.groupby('key')['D'].diff(periods=-1)
result
  key   A   B   C  D  check
0  K0  A0  B0   7  1   -1.0
1  K0  A0  B0   9  2    NaN
2  K1  A1  B1  11  2    1.0
3  K1  A1  B1   6  1    NaN
4  K2  A2  B2   9  1   -1.0
5  K2  A2  B2  10  2    NaN
6  K3  A3  B3  13  2    1.0
7  K3  A3  B3   5  1    NaN

# perform difference between column C values (and add it as a column)
result["diff"] = result.groupby('key')['C'].diff(periods=-1)
result
  key   A   B   C  D  check  diff
0  K0  A0  B0   7  1   -1.0  -2.0
1  K0  A0  B0   9  2    NaN   NaN
2  K1  A1  B1  11  2    1.0   5.0
3  K1  A1  B1   6  1    NaN   NaN
4  K2  A2  B2   9  1   -1.0  -1.0
5  K2  A2  B2  10  2    NaN   NaN
6  K3  A3  B3  13  2    1.0   8.0
7  K3  A3  B3   5  1    NaN   NaN

# filter dataframe to get only desired results
result[(result.check < 0)]['diff']
0   -2.0
4   -1.0

# results as a list
list(result[(result.check < 0)]['diff'])

[-2.0, -1.0]

Calculations on unordered Data frame

1 Answers