group-by

sql query – how to apply limit within group by

Here’s a fairly portable query to do what you want: SELECT * FROM table1 a WHERE a.”ROWID” IN ( SELECT b.”ROWID” FROM table1 b WHERE b.”Score” >= 20 AND b.”ROWID” IS NOT NULL AND a.”CID” = b.”CID” ORDER BY b.”CID”, b.”SortKey” LIMIT 2 ) ORDER BY a.”CID”, a.”SortKey”; The query uses a correlated subquery with … Read more

group by range in mysql

Here is general code to group by range since doing a case statement gets pretty cumbersome. The function ‘floor’ can be used to find the bottom of the range (not ’round’ as Bohemian used), and add the amount (19 in the example below) to find the top of the range. Remember to not overlap the … Read more

Pandas groupby and aggregation output should include all the original columns (including the ones not aggregated on)

agg with a dict of functions Create a dict of functions and pass it to agg. You’ll also need as_index=False to prevent the group columns from becoming the index in your output. f = {‘NET_AMT’: ‘sum’, ‘QTY_SOLD’: ‘sum’, ‘UPC_DSC’: ‘first’} df.groupby([‘month’, ‘UPC_ID’], as_index=False).agg(f) month UPC_ID UPC_DSC NET_AMT QTY_SOLD 0 2017.02 111 desc1 10 2 1 … Read more

Pandas groupby and qcut

import pandas as pd df = pd.DataFrame({‘A’:’foo foo foo bar bar bar’.split(), ‘B’:[0.1, 0.5, 1.0]*2}) df[‘C’] = df.groupby([‘A’])[‘B’].transform( lambda x: pd.qcut(x, 3, labels=range(1,4))) print(df) yields A B C 0 foo 0.1 1 1 foo 0.5 2 2 foo 1.0 3 3 bar 0.1 1 4 bar 0.5 2 5 bar 1.0 3