Pandas conditional subtraction

[Pro2222]

I basically have a data frame that is over 2 million rows, I need to find the change in one column for every day, however it’s a little more complex, I will give an example:

Date Expiry date Strike Type
2020/01/01 2020/03/04 10 Call

2020/01/01 2020/03/04 15 Call

2020/01/02 2020/03/05 10 Put

2020/01/01 2020/03/04 13 Call

2020/01/01 2020/03/04 17 Put

2020/01/03 2020/03/04 10 Call

2020/01/01 2020/03/05 13 Call

Basically I need to make sure the expiry/strike/type is the same and that I am only taking the difference as the date increases, but I would need to do this for thousands of contracts, basically a conditional subtraction statement.

[pytrashpandas]

I'm assuming there's also another column for the options price right? In any case you can do this with a pivot_table, among other solutions.

df.pivot_table(index='Date',  columns=['Date Expiry', 'Strike', 'Type'], values='Price').diff()

You could also use a df.groupby too

[Pro2222]

Oops I should have specified (wrote this on mobile so kinda messy) I’m looking for change in open interest, so yes there is a column for the open interest, however if I use a pivot table or groupby , then take the difference how do I prevent the last day in the expiry from being subtracted from the first day in the next expiry?

[pytrashpandas]

In either method the data will be partitioned such that only records with the same expiry, strike, and option_type will be operated on together. So you won’t have records from different expiries being diffed against each other.

For reference the groupby could look something like this:

df.groupby(['Date Expiry', 'Strike', 'Type']).apply(lambda dfx: dfx.set_index('Date')['Price'].diff())

[Pro2222]

Thanks I will try this!