You want to find top n rows per group. This answer provides a generic solution using example data that is different from OP. In MySQL 8 or later you can use the ROW_NUMBER, RANK or DENSE_RANK function depending on the exact definition of top 5. Below are the numbers generated by these functions based on … Read more
Reddits old formula and a little drop off Basically you can use Reddit’s formula. Since your system only supports upvotes you could weight them, resulting in something like this: def hotness(track) s = track.playedCount s = s + 2*track.downloadCount s = s + 3*track.likeCount s = s + 4*track.favCount baseScore = log(max(s,1)) timeDiff = (now … Read more
Reddit uses the Wilson score interval for comment ranking, an explanation and python implementation can be found here #Rewritten code from /r2/r2/lib/db/_sorts.pyx from math import sqrt def confidence(ups, downs): n = ups + downs if n == 0: return 0 z = 1.0 #1.44 = 85%, 1.96 = 95% phat = float(ups) / n return … Read more
The Levenshtein distance is the algorithm I would recommend. It calculates the minimum number of operations you must do to change 1 string into another. The fewer changes means the strings are more similar…
Using scipy, the function you are looking for is scipy.stats.rankdata: In [13]: import scipy.stats as ss In [19]: ss.rankdata([3, 1, 4, 15, 92]) Out[19]: array([ 2., 1., 3., 4., 5.]) In [20]: ss.rankdata([1, 2, 3, 3, 3, 4, 5]) Out[20]: array([ 1., 2., 4., 4., 4., 6., 7.]) The ranks start at 1, rather than … Read more
There are lots of different arguments you can pass to rank; it looks like you can use rank(“dense”, ascending=False) to get the results you want, after doing a groupby: >>> df[“rank”] = df.groupby(“group_ID”)[“value”].rank(method=”dense”, ascending=False) >>> df group_ID item_ID value rank 0 0S00A1HZEy AB 10 2 1 0S00A1HZEy AY 4 3 2 0S00A1HZEy AC 35 1 … Read more
You could use argsort() like Say, input = 3 In [198]: input = 3 In [199]: df.iloc[(df[‘num’]-input).abs().argsort()[:2]] Out[199]: num 2 4 4 2 df_sort is the dataframe with 2 closest values. In [200]: df_sort = df.iloc[(df[‘num’]-input).abs().argsort()[:2]] For index, In [201]: df_sort.index.tolist() Out[201]: [2, 4] For values, In [202]: df_sort[‘num’].tolist() Out[202]: [4, 2] Detail, for the … Read more
Simon White of Catalysoft wrote an article about a very clever algorithm that compares adjacent character pairs that works really well for my purposes: http://www.catalysoft.com/articles/StrikeAMatch.html Simon has a Java version of the algorithm and below I wrote a PL/Ruby version of it (taken from the plain ruby version done in the related forum entry comment … Read more
You could use GROUP_CONCAT aggregated function to get all years into a single column, grouped by id and ordered by rate: SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id Result: ———————————————————– | ID | GROUPED_YEAR | ———————————————————– | p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 | | p02 | 2001,2004,2002,2003,2000,2006,2007 | ———————————————————– And then … Read more