Pandas get index where values change in column

Micmizer Source

I have a dataframe with either 1's or 0's in multiple "stance" columns. I effectively want to know the count of the chunks of 0's or 1's which represent to buy, or "sell" (go to cash) for that day. i.e. How many days was I in cash or long. I have a good idea of how I can loop over each row and append the index where the value in my column changes but I want to avoid a loop as much as possible.

For example here is a sample DF. The index for this would be for example 540, 543 with a count of 3:

df = df[['Date', 'Ticker', 'Open', 'High', 'Low', 'Close', 'all_4ma-13ma_Stance']]
print (df.tail())
          Date Ticker    Open    High     Low   Close  all_4ma-13ma_Stance
540 2018-02-26   NVDA  247.80  247.98  244.06  246.58                    1
541 2018-02-27   NVDA  245.50  248.91  245.13  246.06                    1
542 2018-02-28   NVDA  246.50  248.10  241.53  242.00                    1
543 2018-03-01   NVDA  241.91  244.09  228.12  232.21                    0
544 2018-03-02   NVDA  227.86  236.80  221.85  236.54                    0

I really like one of the solutions in this post but I do not fully understand what it is doing (and I dont have enough points to comment): Get index where value changes in pandas dataframe column

I am referring to the dictionary solution:

{k: s.index[s].tolist() for k, s in df.ne(df.shift()).filter(like='Group').items()}

In my case it would be:

mydict = {k: s.index[s].tolist() for k, s in df.ne(df.shift()).filter(like='all_4ma-13ma_Stance').items()}

I could hardly find any documentation on df.ne to understand why it is important here. Can someone explain?

Is it also possible to split out the groups of 0's and 1's? For now I am mostly just interested in the groups of 1's, the count of each group and the mean of all groups of 1's.

Update

Here is a better sample DF with more then one "group" of 1's

          Date Ticker    Open    High     Low   Close  all_4ma-13ma_Stance
515 2018-01-19   NVDA  228.09  231.09  227.00  230.11                    1
516 2018-01-22   NVDA  230.43  233.76  228.10  233.69                    1
517 2018-01-23   NVDA  235.85  239.83  235.10  238.91                    1
518 2018-01-24   NVDA  239.04  240.49  233.55  235.80                    1
519 2018-01-25   NVDA  238.00  239.75  235.75  236.35                    1
520 2018-01-26   NVDA  238.12  243.34  237.60  243.33                    1
521 2018-01-29   NVDA  242.74  248.11  240.61  246.85                    1
522 2018-01-30   NVDA  241.11  246.42  238.41  242.72                    1
523 2018-01-31   NVDA  245.77  249.27  244.45  245.80                    1
524 2018-02-01   NVDA  238.52  246.90  238.06  240.50                    1
525 2018-02-02   NVDA  237.00  237.97  231.17  233.52                    1
526 2018-02-05   NVDA  227.00  233.23  205.00  213.70                    0
527 2018-02-06   NVDA  204.40  225.70  204.00  225.58                    0
528 2018-02-07   NVDA  229.58  234.97  226.70  228.80                    0
529 2018-02-08   NVDA  233.88  235.22  217.51  217.52                    0
530 2018-02-09   NVDA  238.25  238.89  217.52  232.08                    0
531 2018-02-12   NVDA  235.36  235.48  225.03  228.03                    0
532 2018-02-13   NVDA  226.56  234.50  225.25  232.63                    0
533 2018-02-14   NVDA  231.00  242.59  230.55  241.42                    0
534 2018-02-15   NVDA  244.78  248.37  241.52  246.50                    1
535 2018-02-16   NVDA  245.40  250.00  243.47  243.84                    1
536 2018-02-20   NVDA  244.75  251.87  244.60  249.08                    1
537 2018-02-21   NVDA  251.69  251.97  241.36  241.51                    1
538 2018-02-22   NVDA  242.73  245.63  239.50  242.15                    1
539 2018-02-23   NVDA  244.57  245.93  242.52  245.93                    1
540 2018-02-26   NVDA  247.80  247.98  244.06  246.58                    1
541 2018-02-27   NVDA  245.50  248.91  245.13  246.06                    1
542 2018-02-28   NVDA  246.50  248.10  241.53  242.00                    1
543 2018-03-01   NVDA  241.91  244.09  228.12  232.21                    0
544 2018-03-02   NVDA  227.86  236.80  221.85  236.54                    0

In this sample there is two groups of 1's, or transitions from 0 to 1 (lets just assume index 514 is a 0). The first group count is 11 and the second group is 9. Therefore we have two buys and the mean is 10. I plan on storing this like:

  Ticker Period  Strategy  Num Buys  Avg Days
0   NVDA    all  4ma-13ma         2        10

The dictionary solution works and looks like the following. I would like to break this out into two dictionaries where one is all index's of the group of 0's and another dictionary or list of the group of 1's.

{'all_4ma-13ma_Stance': [0, 32, 43, 55, 64, 91, 107, 129, 148, 150, 163, 180, 
193, 204, 211, 218, 231, 241, 253, 266, 280, 302, 314, 327, 337, 340, 373, 
382, 394, 401, 405, 416, 418, 419, 424, 427, 436, 443, 458, 459, 475, 476, 
481, 495, 526, 534, 543]}
pythonpandasiteration

Answers

comments powered by Disqus