If you’re using SQL Server 2005 or newer, you can use the ranking functions and a CTE to achieve this: ;WITH HairColors AS (SELECT id, name, hair, score, ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as ‘RowNum’ ) SELECT id, name, hair, score FROM HairColors WHERE RowNum <= 3 This CTE will “partition” your … Read more
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
I’m sure this is a duplicate, but the answer is simple: sort(table(variable),decreasing=TRUE)[1:3]
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…
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 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
This kind of question has been asked and answered on many occasions, but I can’t seem to find a good duplicate. Regardless, you would use the second output of sort on the rank vector to give you the position of where each value would appear in the sorted result. Specifically, for each value in the … Read more