title: Pandas group: how to find the N largest values in multiple columns of each group?
body: <p>I log a device and read 3 values (<code>W1</code>, <code>W2</code>, <code>W3</code>) every 15 minutes. They could be repeated.</p>
  
  <p>I need to find <strong>for every hour what are the max 3 values among the 12 that has been read</strong> in that interval. I am not interested to know <em>when</em> they occurred, only their value.</p>
  
  <p>At the moment my algorithm is far from being efficient and fast:</p>
  
  <ul>
  <li>Loop through each group:
  
  <ol>
  <li>Transform W1, W2 and W3 to lists</li>
  <li>Combine the 3 lists together</li>
  <li>Sort the combined list</li>
  <li>Pick the largest elements.</li>
  </ol></li>
  </ul>
  
  <p><strong>I would like to remove the loop</strong> and use native pandas/numpy methods. Is it possible? </p>
  
  <p><strong>EDIT: A working solution is been proposed at the end of this post</strong></p>
  
  <p>This is the code:</p>
  
  <pre><code>from datetime import *
  import pandas as pd
  import numpy as np
  
  df = pd.DataFrame()
  
  date_ref = datetime(2017,12,8,0,0,0)
  days = pd.date_range(date_ref, date_ref + timedelta(0.11), freq='15min')
  
  np.random.seed(seed=1111)
  data1 = np.random.randint(1, high=100, size=len(days))
  data2 = data1 - np.random.randint(3, high=13, size=len(days))
  data3 = data2 - np.random.randint(3, high=13, size=len(days))
  
  df = pd.DataFrame({'TIME': days, 'W1': data1, 'W2': data2, 'W3': data3 })
  df = df.set_index('TIME')
  
  print("Original data")
  print("-------------")
  print(df)
  print("**********************************************")
  
  # groupby
  grouped = df.groupby(pd.Grouper(freq='1H'))
  print("Grouped data")
  print("------------")
  print list(grouped)
  print("**********************************************")
  
  print("3 largest values")
  print("----------------")
  for dtime, group in grouped:
      w  = list(group["W1"])
      w2 = list(group["W2"])
      w3 = list(group["W3"])
      w.extend(w2)
      w.extend(w3)
      w = sorted(w)
  
      max1 = w[-1]
      max2 = w[-2]
      max3 = w[-3]
  
      print(dtime, max1, max2, max3)
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>Original data
  -------------
                       W1  W2  W3
  TIME                           
  2017-12-08 00:00:00  78  67  57
  2017-12-08 00:15:00  73  64  59
  2017-12-08 00:30:00  55  50  47
  2017-12-08 00:45:00  67  58  51
  2017-12-08 01:00:00  62  51  40
  2017-12-08 01:15:00  52  40  32
  2017-12-08 01:30:00  70  64  56
  2017-12-08 01:45:00  74  67  63
  2017-12-08 02:00:00  72  61  56
  2017-12-08 02:15:00  70  58  55
  2017-12-08 02:30:00  61  49  39
  **********************************************
  Grouped data
  ------------
  [(Timestamp('2017-12-08 00:00:00', freq='H'),                      W1  W2  W3
  TIME                           
  2017-12-08 00:00:00  78  67  57
  2017-12-08 00:15:00  73  64  59
  2017-12-08 00:30:00  55  50  47
  2017-12-08 00:45:00  67  58  51), (Timestamp('2017-12-08 01:00:00', freq='H'),                      W1  W2  W3
  TIME                           
  2017-12-08 01:00:00  62  51  40
  2017-12-08 01:15:00  52  40  32
  2017-12-08 01:30:00  70  64  56
  2017-12-08 01:45:00  74  67  63), (Timestamp('2017-12-08 02:00:00', freq='H'),                      W1  W2  W3
  TIME                           
  2017-12-08 02:00:00  72  61  56
  2017-12-08 02:15:00  70  58  55
  2017-12-08 02:30:00  61  49  39)]
  **********************************************
  3 largest values
  ----------------
  (Timestamp('2017-12-08 00:00:00', freq='H'), 78, 73, 67)
  (Timestamp('2017-12-08 01:00:00', freq='H'), 74, 70, 67)
  (Timestamp('2017-12-08 02:00:00', freq='H'), 72, 70, 61)
  </code></pre>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>I had some troubles implementing the solution in my code, so I leave here the final version for posterity. Perhaps it can be useful for somebody.</p>
  
  <p>Even if @jezrael's solution works in my demo, it doesn't in my final version. It complains about a Timestamp that cannot be negated. It is pretty difficult to debug pandas groups, so I used @GeorgeLPerkins's. It is (to me) a lot easier to understand.</p>
  
  <p>The biggest problem has been <code>grouped.apply()</code> that returns a series of list. </p>
  
  <p>Extracting each element from each list is done using <code>str</code>: as the name suggest I thought it was involved with strings only and didn't consider it...</p>
  
  <p>Now every direct loop is avoided, and the result <code>gdf</code> is a dataframe that can be saved into a database with a single write operation. </p>
  
  <p>I am a newbie in pandas, alas, and I assume this can be highly optimized.</p>
  
  <pre><code>from datetime import *
  import pandas as pd
  import numpy as np
  
  df = pd.DataFrame()
  
  date_ref = datetime(2017,12,8,0,0,0)
  days = pd.date_range(date_ref, date_ref + timedelta(0.11), freq='15min')
  
  np.random.seed(seed=1111)
  data1 = np.random.randint(50, high=80, size=len(days))
  data2 = data1 - np.random.randint(3, high=13, size=len(days))
  data3 = data2 - np.random.randint(3, high=13, size=len(days))
  
  df = pd.DataFrame({'TIME': days, 'W1': data1, 'W2': data2, 'W3': data3 })
  df = df.set_index('TIME')
  
  #print("Original data")
  #print("-------------")
  #print(df)
  #print("**********************************************")
  
  # groupby
  grouped = df.groupby(pd.Grouper(freq='1H'))
  print("Grouped data")
  print("------------")
  print list(grouped)
  print("**********************************************")
  
  print("3 largest values")
  print("----------------")
  
  def operation(x):
      combinedcoltop3 = []
      combinedcoltop3.extend(list(x.nlargest(3, "W1")["W1"])) # reads the 3 largest W1 and return W1 only
      combinedcoltop3.extend(list(x.nlargest(3, "W2")["W2"]))
      combinedcoltop3.extend(list(x.nlargest(3, "W3")["W3"]))
      combinedcoltop3.sort(reverse=True)
      return combinedcoltop3[:3]  # returns a list!
  
  df1 = grouped.apply(operation) 
  
  gdf = pd.DataFrame()
  gdf["W1"] = df1.str[0]  # reads each element of the list for each row of df1
  gdf["W2"] = df1.str[1]
  gdf["W3"] = df1.str[2]
  
  print(gdf)
  
  # now gdf can be saved with a single write into the database
  </code></pre>