3

I have a pandas dataframe with a few columns, one called 'strike.' If the value of a row of the strike column is greater than 100 plus the previous row of the strike column, I want to split the dataframe into two at that point (they'd still have the same column names) and so on. I'm quite new at pandas and couldn't figure out a simple way to do this after looking up some functions.

An example: The following dataframe:

strike crv vol
1400   w   a 
1450   x   b
1600   y   c
1800   z   d

would come out to be 3 dataframes:

strike crv vol
1400   w   a 
1450   x   b

strike crv vol
1600   y   c

strike crv vol
1800   z   d

Thanks!

user3078608
  • 103
  • 1
  • 5
  • 15
  • 1
    You mean something like `df[(df['strike'] > 100) & (df['strike'].shift() > 100)].index[0]`? – EdChum Jul 21 '15 at 15:43

1 Answers1

12

IIUC, this is yet another example of the compare-cumsum-groupby pattern:

>>> df
   strike crv vol
0    1400   w   a
1    1450   x   b
2    1600   y   c
3    1800   z   d
>>> group_ids = (df["strike"] > (df["strike"].shift() + 100)).cumsum()
>>> grouped = df.groupby(group_ids)
>>> for k,g in grouped:
...     print("-----")
...     print(g)
...     
-----
   strike crv vol
0    1400   w   a
1    1450   x   b
-----
   strike crv vol
2    1600   y   c
-----
   strike crv vol
3    1800   z   d

And you can put this into a list or dictionary if you'd like:

>>> group_list = [g for k,g in grouped]
>>> group_list[2]
   strike crv vol
3    1800   z   d
>>> group_dict = dict(list(grouped))
>>> group_dict[1]
   strike crv vol
2    1600   y   c

This works because we build the group ids taking advantage of the fact that True == 1 and False == 0:

>>> df["strike"] > (df["strike"].shift() + 100)
0    False
1    False
2     True
3     True
Name: strike, dtype: bool
>>> (df["strike"] > (df["strike"].shift() + 100)).cumsum()
0    0
1    0
2    1
3    2
Name: strike, dtype: int64

and we can then group on these values.

DSM
  • 342,061
  • 65
  • 592
  • 494
  • Awesome, just what l was looking for. Thanks. – user3078608 Jul 21 '15 at 15:58
  • @user3078608: I wouldn't ordinarily mention it, but it doesn't look like you've ever accepted an answer. Are you familiar with the process? – DSM Jul 21 '15 at 16:00
  • @user3078608: no worries. It looks like there are a few other people who have helped you in previous questions who could use some accepts too (always nice to get a late accept!) – DSM Jul 21 '15 at 16:14