title: Rolling window percentile rank over a multi-index Pandas DataFrame
body: <p>I am creating a percentile rank over a rolling window of time and would like help refining my approach. </p>
  
  <p>My DataFrame has a multi-index with the first level set to datetime and the second set to an identifier. Ultimately, I’d like the rolling window to evaluate the trailing n periods, including the current period, and produce the corresponding percentile ranks.</p>
  
  <p>I referenced the posts shown below but found they were working with the data a bit differently than how I intend to. In those posts, the final functions group results by identifier and then by datetime, whereas I'm looking to use rolling panels of data in my function (dates and identifiers).</p>
  
  <p><a href="https://stackoverflow.com/questions/19188217/using-rolling-functions-on-multi-index-dataframe-in-pandas">using rolling functions on multi-index dataframe in pandas
  </a></p>
  
  <p><a href="https://stackoverflow.com/questions/38856551/panda-rolling-window-percentile-rank">Panda rolling window percentile rank</a></p>
  
  <p>This is an example of what I am after.</p>
  
  <p>Create a sample DataFrame:</p>
  
  <pre><code>num_days = 5
  np.random.seed(8675309)
  
  stock_data = {
      "AAPL": np.random.randint(1, max_value, size=num_days),
      "MSFT": np.random.randint(1, max_value, size=num_days),
      "WMT": np.random.randint(1, max_value, size=num_days),
      "TSLA": np.random.randint(1, max_value, size=num_days)
  }
  
  dates = pd.date_range(
      start="2013-01-03", 
      periods=num_days, 
      freq=BDay()
  )
  
  sample_df = pd.DataFrame(stock_data, index=dates)
  sample_df = sample_df.stack().to_frame(name='data')
  sample_df.index.names = ['date', 'ticker']
  </code></pre>
  
  <p>Which outputs:</p>
  
  <pre><code>date       ticker      
  2013-01-03 AAPL       2
             MSFT      93
             TSLA      39
             WMT       21
  2013-01-04 AAPL     141
             MSFT      43
             TSLA     205
             WMT       20
  2013-01-07 AAPL     256
             MSFT      93
             TSLA     103
             WMT       25
  2013-01-08 AAPL     233
             MSFT      60
             TSLA      13
             WMT      104
  2013-01-09 AAPL      19
             MSFT     120
             TSLA     282
             WMT      293
  </code></pre>
  
  <p>The code below breaks out the <code>sample_df</code> into 2 day increments and produces a rank vs. ranking over a rolling window of time. So it's close, but not what I'm after.</p>
  
  <pre><code>sample_df.reset_index(level=1, drop=True)[['data']] \
  .apply(
      lambda x: x.groupby(pd.Grouper(level=0, freq='2d')).rank()
  )
  </code></pre>
  
  <p>I then tried what's shown below without much luck either.</p>
  
  <pre><code>from scipy.stats import rankdata
  
  def rank(x):
      return rankdata(x, method='ordinal')[-1]
  
  sample_df.reset_index(level=1, drop=True) \
  .rolling(window="2d", min_periods=1) \
  .apply(
      lambda x: rank(x)
  )
  </code></pre>
  
  <p>I finally arrived at the output I'm looking for but the formula seems a bit contrived, so I'm hoping to identify a more elegant approach if one exists. </p>
  
  <pre><code>import numpy as np
  import pandas as pd
  from pandas.tseries.offsets import BDay
  
  window_length = 1
  target_column = "data"
  
  def rank(df, target_column, ids, window_length):
  
      percentile_ranking = []
      list_of_ids = []
  
      date_index = df.index.get_level_values(0).unique()
  
      for date in date_index:
          rolling_start_date = date - BDay(window_length)
          first_date = date_index[0] + BDay(window_length)
          trailing_values = df.loc[rolling_start_date:date, target_column]
  
          # Only calc rolling percentile after the rolling window has lapsed
          if date &lt; first_date:
              pass
          else:
              percentile_ranking.append(
                  df.loc[date, target_column].apply(
                      lambda x: stats.percentileofscore(trailing_values, x, kind="rank")
                  )
              )
  
              list_of_ids.append(df.loc[date, ids])
  
      ranks, output_ids = pd.concat(percentile_ranking), pd.concat(list_of_ids)
  
      df = pd.DataFrame(
          ranks.values, index=[ranks.index, output_ids], columns=["percentile_rank"]
           )
  
      return df
  
  ranks = rank(
      sample_df.reset_index(level=1), 
      window_length=1, 
      ids='ticker', 
      target_column="data"
  )
  
  sample_df.join(ranks)
  </code></pre>
  
  <p>I get the feeling that my <code>rank</code> function is more than what's needed here. I appreciate any ideas/feedback to help in simplifying this code to arrive at the output below. Thank you!</p>
  
  <pre><code>                   data  percentile_rank
  date       ticker                       
  2013-01-03 AAPL       2              NaN
             MSFT      93              NaN
             TSLA      39              NaN
             WMT       21              NaN
  2013-01-04 AAPL     141             87.5
             MSFT      43             62.5
             TSLA     205            100.0
             WMT       20             25.0
  2013-01-07 AAPL     256            100.0
             MSFT      93             50.0
             TSLA     103             62.5
             WMT       25             25.0
  2013-01-08 AAPL     233             87.5
             MSFT      60             37.5
             TSLA      13             12.5
             WMT      104             75.0
  2013-01-09 AAPL      19             25.0
             MSFT     120             62.5
             TSLA     282             87.5
             WMT      293            100.0
  </code></pre>