pandas groupby,上下组成员之间的差异

Assume I have df:

df = pd.DataFrame({'ID': ['a', 'b', 'b', 'b', 'c', 'c'],
                  'V1': [1,2,3,4,5,6],
                  'V2': [7,8,9,19,11,12]})

I want to create a new column V3, indicating the difference between V2 for the "top" group member, and V1 for the "bottom" group member. The result would look like:

    ID  V1  V2  V3
0   a   1   7   6
1   b   2   8   4
2   b   3   9   4
3   b   4   19  4
4   c   5   11  5
5   c   6   12  5

我尝试了类似的方法,但不起作用:

df.groupby('ID').apply(lambda x: x.head(1).V2-x.tail(1).V1)
评论
  • Pete
    Pete 回复

    Use GroupBy.transform with first and last and subtract by Series.sub:

    df['V3'] = df.groupby('ID').V2.transform('first').sub(df.groupby('ID').V1.transform('last'))
    

    Your solution shoud be changed by selecting by positions and Series.map:

    s = df.groupby('ID').apply(lambda x: x.V2.iat[0]-x.V1.iat[-1])
    df['V3'] = df['ID'].map(s)
    print (df)
      ID  V1  V2  V3
    0  a   1   7   6
    1  b   2   8   4
    2  b   3   9   4
    3  b   4  19   4
    4  c   5  11   5
    5  c   6  12   5