I believe this is what you want: table.groupby(‘YEARMONTH’).CLIENTCODE.nunique() Example: In [2]: table Out[2]: CLIENTCODE YEARMONTH 0 1 201301 1 1 201301 2 2 201301 3 1 201302 4 2 201302 5 2 201302 6 3 201302 In [3]: table.groupby(‘YEARMONTH’).CLIENTCODE.nunique() Out[3]: YEARMONTH 201301 2 201302 3
var consolidatedChildren = from c in children group c by new { c.School, c.Friend, c.FavoriteColor, } into gcs select new ConsolidatedChild() { School = gcs.Key.School, Friend = gcs.Key.Friend, FavoriteColor = gcs.Key.FavoriteColor, Children = gcs.ToList(), }; var consolidatedChildren = children .GroupBy(c => new { c.School, c.Friend, c.FavoriteColor, }) .Select(gcs => new ConsolidatedChild() { School = gcs.Key.School, … Read more
In [1]: df Out[1]: Sp Mt Value count 0 MM1 S1 a 3 1 MM1 S1 n 2 2 MM1 S3 cb 5 3 MM2 S3 mk 8 4 MM2 S4 bg 10 5 MM2 S4 dgd 1 6 MM4 S2 rd 2 7 MM4 S2 cb 2 8 MM4 S2 uyi 7 In [2]: … Read more
MusiGenesis’ response is functionally the correct one with regard to your question as stated; the SQL Server is smart enough to realize that if you are using “Group By” and not using any aggregate functions, then what you actually mean is “Distinct” – and therefore it generates an execution plan as if you’d simply used … Read more
[*] Yes, this is a common aggregation problem. Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*]. To workaround this issue, you must calculate the aggregate in a sub-query and then join it with itself to get the additional columns you’d need to show: SELECT m.cname, m.wmname, t.mx FROM ( SELECT … Read more
PostgreSQL 9.0 or later: Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for: SELECT company_id, string_agg(employee, ‘, ‘) FROM mytable GROUP BY company_id; Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all … 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
I would just add group_id to the GROUP BY. When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how … Read more
We start by answering the first question: Question 1 Why do I get ValueError: Index contains duplicate entries, cannot reshape This occurs because pandas is attempting to reindex either a columns or index object with duplicate entries. There are varying methods to use that can perform a pivot. Some of them are not well suited … Read more
Quick Answer: The simplest way to get row counts per group is by calling .size(), which returns a Series: df.groupby([‘col1′,’col2’]).size() Usually you want this result as a DataFrame (instead of a Series) so you can do: df.groupby([‘col1’, ‘col2’]).size().reset_index(name=”counts”) If you want to find out how to calculate the row counts and other statistics for each … Read more