CODEWITHSUNDEEP
pythonpandaspandas-groupby
Bython
Bython

Reputation: 1145

Grouping and aggregating by multiple columns while applying column as an aggregate argument in Pandas?

I am trying to group below DataFrame based on Expiration and Strike. After that, I would like to calculate the difference between all calls and puts with same Strike and Expiration Date. In below example only row 1 and 2 would yield a result (15.370001-1.495=) = 13.875

How exactly could I proceed without writing a for loop? I thought about something along the lines of:

df.groupby(["Expiration","Strike"]).agg(lambda x: x[x.Type == "call"].Price - x[x.Type == "put"].Price + x.Strike)

However, I am unsure how to pass such if (Type equals call) arguments to a groupby function?

Type       Price Expiration  Strike
0   put  145.000000 2021-01-15   420.0
1  call   15.370001 2018-11-30   262.0
2   put    1.495000 2018-11-30   262.0
3  call   14.930000 2018-11-30   262.5

Upvotes: 0

Views: 47

Answers (1)

jezrael
jezrael

Reputation: 863731

You can use custom function by GroupBy.apply with next and iter for get first value and if no match get NaNs:

def f(x):
    c = next(iter(x.loc[x.Type == "call", 'Price']),np.nan)
    p = next(iter(x.loc[x.Type == "put", 'Price']),np.nan) 
    x['new']= c - p + x.Strike
    return x

df = df.groupby(["Expiration","Strike"]).apply(f)
print (df)

   Type       Price  Expiration  Strike         new
0   put  145.000000  2021-01-15   420.0         NaN
1  call   15.370001  2018-11-30   262.0  275.875001
2   put    1.495000  2018-11-30   262.0  275.875001
3  call   14.930000  2018-11-30   262.5         NaN

Another solution:

#if possible `call` and `put` are not unique per groups
c = df[df.Type == "call"].groupby(["Expiration","Strike"])['Price'].first()
p = df[df.Type == "put"].groupby(["Expiration","Strike"])['Price'].first()

#if `call` and `put` are unique per groups
#c = df[df.Type == "call"].set_index(["Expiration","Strike"])['Price']
#p = df[df.Type == "put"].set_index(["Expiration","Strike"])['Price']

df1 = df.join((c - p).rename('new'), on=["Expiration","Strike"])
df1['new'] += df1['Strike']
print (df1)
   Type       Price  Expiration  Strike         new
0   put  145.000000  2021-01-15   420.0         NaN
1  call   15.370001  2018-11-30   262.0  275.875001
2   put    1.495000  2018-11-30   262.0  275.875001
3  call   14.930000  2018-11-30   262.5         NaN

Upvotes: 1

Related Questions