aggregate
How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?
Put a CASE expression in there, or convert it to int: IsActive = MAX(CASE WHEN IsActive=1 THEN 1 ELSE 0 END) or, IsActive = MAX(CONVERT(int,IsActive)) You should also be aware, obviously, that this means that the values in the ProductName, VendorName and IsActive columns in the result set may all come from different rows in … Read more
Rename result columns from Pandas aggregation (“FutureWarning: using a dict with renaming is deprecated”)
Use groupby apply and return a Series to rename columns Use the groupby apply method to perform an aggregation that Renames the columns Allows for spaces in the names Allows you to order the returned columns in any way you choose Allows for interactions between columns Returns a single level index and NOT a MultiIndex … Read more
Python Pandas: Is Order Preserved When Using groupby() and agg()?
See this enhancement issue The short answer is yes, the groupby will preserve the orderings as passed in. You can prove this by using your example like this: In [20]: df.sort_index(ascending=False).groupby(‘A’).agg([np.mean, lambda x: x.iloc[1] ]) Out[20]: B C mean <lambda> mean <lambda> A group1 11.0 10 101 100 group2 17.5 10 175 100 group3 11.0 … Read more
Extract the maximum value within each group in a dataframe [duplicate]
There are many possibilities to do this in R. Here are some of them: df <- read.table(header = TRUE, text=”Gene Value A 12 A 10 B 3 B 5 B 6 C 1 D 3 D 4″) # aggregate aggregate(df$Value, by = list(df$Gene), max) aggregate(Value ~ Gene, data = df, max) # tapply tapply(df$Value, df$Gene, … Read more
Add count of unique / distinct values by group to the original data
Here’s a solution with the dplyr package – it has n_distinct() as a wrapper for length(unique()). df %>% group_by(color) %>% mutate(unique_types = n_distinct(type))