Pandas groupby conditional subtraction

Question

I'm trying to create a new column based on a conditional subtraction. I want to first group the dataframe from column A, then take the row value of C where B is minimum, and subtract that value from all values in column C.

import pandas as pd

data = [
["R", 1, 2],
["R", 2, 4],
["R", 3, 6],
["R", 4, 8],
["S", 0, 5],
["S", 1, 4],
["S", 2, 1],
["S", 3, 3]]
df = pd.DataFrame(data=data, columns=["a", "b", "c"])
df

Out[1]:
    a   b   c
0   R   1   2
1   R   2   4
2   R   3   6
3   R   4   8
4   S   0   5
5   S   1   4
6   S   2   1
7   S   3   3

Want it to yield the column 'd' of:

Out[2]:
    a   b   c    d
0   R   1   2    0
1   R   2   4    2
2   R   3   6    4
3   R   4   8    6
4   S   0   5    0
5   S   1   4   -1
6   S   2   1   -4
7   S   3   3   -2

Anyone have any nice pythonic ways of doing this?

Thanks

Answer 1

Use

In [591]: df['d'] = df['c'] - df.loc[df.groupby('a')['b'].transform('idxmin'), 'c'].values

In [592]: df
Out[592]:
   a  b  c  d
0  R  1  2  0
1  R  2  4  2
2  R  3  6  4
3  R  4  8  6
4  S  0  5  0
5  S  1  4 -1
6  S  2  1 -4
7  S  3  3 -2

Answer 2

conditional_c = df.groupby('a').b.idxmin().map(df.c)
df.assign(d=df.c - df.a.map(conditional_c))

   a  b  c  d
0  R  1  2  0
1  R  2  4  2
2  R  3  6  4
3  R  4  8  6
4  S  0  5  0
5  S  1  4 -1
6  S  2  1 -4
7  S  3  3 -2