The docs show how to apply multiple functions on a groupby object at a time using a dict with the output column names as the keys: In [563]: grouped[‘D’].agg({‘result1’ : np.sum, …..: ‘result2’ : np.mean}) …..: Out[563]: result2 result1 A bar -0.579846 -1.739537 foo -0.280588 -1.402938 However, this only works on a Series groupby object. … Read more
I am using Pandas as a database substitute as I have multiple databases (Oracle, SQL Server, etc.), and I am unable to make a sequence of commands to a SQL equivalent. I have a table loaded in a DataFrame with some columns: YEARMONTH, CLIENTCODE, SIZE, etc., etc. In SQL, to count the amount of different clients … Read more
This question already has answers here: Group By Multiple Columns (14 answers) Closed 6 years ago. public class ConsolidatedChild { public string School { get; set; } public string Friend { get; set; } public string FavoriteColor { get; set; } public List<Child> Children { get; set; } } public class Child { public string … Read more
I have a table that looks like this caller ‘makerar’ cname | wmname | avg ——–+————-+———————— canada | zoro | 2.0000000000000000 spain | luffy | 1.00000000000000000000 spain | usopp | 5.0000000000000000 And I want to select the maximum avg for each cname. SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname; but I will get … Read more
I learned something simple about SQL the other day: SELECT c FROM myTbl GROUP BY C Has the same result as: SELECT DISTINCT C FROM myTbl What I am curious of, is there anything different in the way an SQL engine processes the command, or are they truly the same thing? I personally prefer the … Read more
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave and I wanted to group by company_id to get something like: COMPANY_ID EMPLOYEE 1 Anna, … Read more
The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2009 p01 4.4 2002 p01 … Read more
I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7 I get this error: Expression #1 of SELECT list is not in … Read more
What is pivot? How do I pivot? Is this a pivot? Long format to wide format? I’ve seen a lot of questions that ask about pivot tables. Even if they don’t know that they are asking about pivot tables, they usually are. It is virtually impossible to write a canonical question and answer that encompasses … Read more
I have a data frame df and I use several columns from it to groupby: df[‘col1′,’col2′,’col3′,’col4’].groupby([‘col1′,’col2’]).mean() In the above way I almost get the table (data frame) that I need. What is missing is an additional column that contains number of rows in each group. In other words, I have mean but I also would … Read more
