title: How to apply window case-when functions on pandas groups
body: <p>I have a table with two identifier columns <code>w</code> and <code>x</code>. The columns <code>y</code> brings order to the column <code>z</code>, which is the destination variable. Every sequence identified by <code>w</code> and <code>x</code> has at least two rows and all distinct values in <code>z</code>. I want to count for each of the <code>z</code>s how often they occur in the sequences in which position. Position-wise I'm interested in whether it's the first, last or any other.
  My approach in R's <code>dplyr</code>is the following (for those unfamiliar with <code>%&gt;%</code>, it's the pipe operator that takes the left hand output and puts it as first argument into the right hand function, you can read it as "and then"):</p>
  
  <pre><code>library(tidyverse)
  library(reticulate)
  
  data &lt;- tribble(
    ~w,   ~x,  ~y,   ~z,
     1,    1,    1,  "a",
     1,    1,    2,  "b",
     1,    2,    1,  "b",
     1,    2,    2,  "a",
     1,    2,    3,  "c",
     1,    2,    4,  "d",
     2,    1,    1,  "b",
     2,    1,    2,  "a",
     2,    1,    3,  "c",
     2,    1,    4,  "d"
  )
  </code></pre>
  
  <p>First I add a sequence index to each ordered <code>w</code> and <code>x</code> groups and a marker that determine the categorical position of <code>z</code> in the sequence from it (the <code>position_in_sequence</code>).</p>
  
  <pre><code>data_with_markers &lt;- 
    data %&gt;%
    group_by(w, x) %&gt;%
    arrange(y, .by_group = TRUE) %&gt;%
    mutate(
      sequence_id = row_number(),
      position_in_sequence = case_when(
        sequence_id == first(sequence_id) ~ "first",
        sequence_id == last(sequence_id) ~ "last",
        TRUE ~ "other" # this is the "else"
      )
    ) %&gt;%
    ungroup()
  
  
  &gt;data_with_markers
  # A tibble: 10 x 6
         w     x     y z     sequence_id position_in_sequence 
     &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;chr&gt;       &lt;int&gt; &lt;chr&gt;
   1     1     1     1 a               1 first
   2     1     1     2 b               2 last 
   3     1     2     1 b               1 first
   4     1     2     2 a               2 other
   5     1     2     3 c               3 other
   6     1     2     4 d               4 last 
   7     2     1     1 b               1 first
   8     2     1     2 a               2 other
   9     2     1     3 c               3 other
  10     2     1     4 d               4 last 
  </code></pre>
  
  <p>Then I do a simple count by the <code>position_in_sequence</code> and <code>z</code>.</p>
  
  <pre><code>data_summary &lt;- data_with_markers %&gt;% 
    group_by(position_in_sequence, z) %&gt;% 
    count() %&gt;% 
    ungroup()
  
  &gt; data_summary
  # A tibble: 6 x 3
    position_in_sequence  z         n
    &lt;chr&gt; &lt;chr&gt; &lt;int&gt;
  1 first a         1
  2 first b         2
  3 last  b         1
  4 last  d         2
  5 other a         2
  6 other c         2
  </code></pre>
  
  <p>For pandas, I'm stuck with getting the <code>position_in_sequence</code> variable (I'm using the <code>reticulate</code> package here):</p>
  
  <pre><code>import pandas as pd
  data = r.data
  data
  
       w    x    y  z
  0  1.0  1.0  1.0  a
  1  1.0  1.0  2.0  b
  2  1.0  2.0  1.0  b
  3  1.0  2.0  2.0  a
  4  1.0  2.0  3.0  c
  5  1.0  2.0  4.0  d
  6  2.0  1.0  1.0  b
  7  2.0  1.0  2.0  a
  8  2.0  1.0  3.0  c
  9  2.0  1.0  4.0  d
  
  data_sorted = data.sort_values(['w', 'x', 'y'])
  
  data_sorted['sequence_id'] = data_sorted.groupby(['w', 'x']).cumcount() + 1
  data_sorted
  
       w    x    y  z  sequence_id
  0  1.0  1.0  1.0  a            1
  1  1.0  1.0  2.0  b            2
  2  1.0  2.0  1.0  b            1
  3  1.0  2.0  2.0  a            2
  4  1.0  2.0  3.0  c            3
  5  1.0  2.0  4.0  d            4
  6  2.0  1.0  1.0  b            1
  7  2.0  1.0  2.0  a            2
  8  2.0  1.0  3.0  c            3
  9  2.0  1.0  4.0  d            4
  </code></pre>
  
  <p>I fiddled around with <code>.apply</code> but I would need to access the column <code>sequence_id</code> at a certain row and the column's min and max at the same time to compare, but I didn't get it to work.</p>