无序数据帧的计算

Question

嗨，我已经创建了一个虚拟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')

产出：

    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

我试图解决的问题是，我希望按键值对条目进行分组，并对其执行数学操作，您将注意到列中有两个条目。

因此，对于每个键，如果顶值小于D列中的底部值，则对C列项执行简单的加法，在这种情况下，数学只应用于索引=4、5、6、7，计算为

9-10 = -1
13-5 = 8

理想情况下，这些结果将存储在一个列表中，我知道数据结构并不理想，但这正是我所要处理的，我不知道如何处理它。

Answer 1

# 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]