I&#39;m trying to do some aggregations on a pandas data frame. Here is a sample code:

    import pandas as pd
    
    df = pd.DataFrame({&quot;User&quot;: [&quot;user1&quot;, &quot;user2&quot;, &quot;user2&quot;, &quot;user3&quot;, &quot;user2&quot;, &quot;user1&quot;],
                      &quot;Amount&quot;: [10.0, 5.0, 8.0, 10.5, 7.5, 8.0]})
    
    df.groupby([&quot;User&quot;]).agg({&quot;Amount&quot;: {&quot;Sum&quot;: &quot;sum&quot;, &quot;Count&quot;: &quot;count&quot;}})

    Out[1]: 
          Amount      
             Sum Count
    User              
    user1   18.0     2
    user2   20.5     3
    user3   10.5     1

Which generates the following warning:

&gt; FutureWarning: using a dict with renaming is deprecated and will be
&gt; removed in a future version   return super(DataFrameGroupBy,
&gt; self).aggregate(arg, *args, **kwargs)

How can I avoid this?

Rename result columns from Pandas aggregation (&quot;FutureWarning: using a dict with renaming is deprecated&quot;)

Here is my T-SQL query

	SELECT 
		ProductID,
		VendorID,
		ProductName= MAX(ProductName),
		VendorName = MAX(VendorName),
		IsActive = MAX(IsActive) # This brings error 
	FROM ProductVendorAssoc 
	GROUP BY  
		ProductID,
		VendorID
			
I want to apply `GROUP BY` only for the `ProductID and VendorID` fields, but need to populate the `ProductID, VendorID, ProductName, VendorName, IsActive` fields.

Here I used the agreggate function `MAX(ProductName)` to avoid `ProductName` in the group by list. 

But the same trick is not working for `BIT` columns as operand data type bit is invalid for max operator.

How can i include `BIT` type column in `SELEC`T part with out including it on the `GROUP BY`?

**Update.**

What should I need to do if i need to include an `INT` column like `UserID` in `SELECT` in the same way

How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?

Im looking for something like `SELECT PRODUCT(table.price) FROM table GROUP BY table.sale` similar to how `SUM` works.

Have I missed something on the documentation, or is there really no `PRODUCT` function?

If so, why not?

Note: I looked for the function in postgres, mysql and mssql and found none so I assumed all sql does not support it.

Why is there no PRODUCT aggregate function in SQL?

I have a dataframe in R of the following form:

    &gt; head(data)
      Group Score Info
    1     1     1    a
    2     1     2    b
    3     1     3    c
    4     2     4    d
    5     2     3    e
    6     2     1    f

I would like to aggregate it following the `Score` column using the `max` function

    &gt; aggregate(data$Score, list(data$Group), max)

      Group.1         x
    1       1         3
    2       2         4

But I also would like to display the `Info` column associated to the maximum value of the `Score` column for each group. I have no idea how to do this. My desired output would be:

      Group.1         x        y
    1       1         3        c
    2       2         4        d

Any hint?

Aggregate a dataframe on a given column and display another column

How can I select the first and last row for each unique `id` in the following dataframe?

    tmp &lt;- structure(list(id = c(15L, 15L, 15L, 15L, 21L, 21L, 22L, 22L, 
    22L, 23L, 23L, 23L, 24L, 24L, 24L, 24L), d = c(1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), gr = c(2L, 1L, 
    1L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 2L), mm = c(3.4, 
    4.9, 4.4, 5.5, 4, 3.8, 4, 4.9, 4.6, 2.7, 4, 3, 3, 2, 4, 2), area = c(1L, 
    2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 3L)), .Names = c(&quot;id&quot;, 
    &quot;d&quot;, &quot;gr&quot;, &quot;mm&quot;, &quot;area&quot;), class = &quot;data.frame&quot;, row.names = c(NA, 
    -16L))
    tmp
    #&gt;    id d gr  mm area
    #&gt; 1  15 1  2 3.4    1
    #&gt; 2  15 1  1 4.9    2
    #&gt; 3  15 1  1 4.4    1
    #&gt; 4  15 1  1 5.5    2
    #&gt; 5  21 1  1 4.0    2
    #&gt; 6  21 1  2 3.8    2
    #&gt; 7  22 1  1 4.0    2
    #&gt; 8  22 1  1 4.9    2
    #&gt; 9  22 1  2 4.6    2
    #&gt; 10 23 1  1 2.7    2
    #&gt; 11 23 1  1 4.0    2
    #&gt; 12 23 1  2 3.0    2
    #&gt; 13 24 1  1 3.0    2
    #&gt; 14 24 1  1 2.0    3
    #&gt; 15 24 1  1 4.0    2
    #&gt; 16 24 1  2 2.0    3

Select the first and last row by group in a data frame

I am looking for some better explanation of the aggregate functionality that is available via spark in python.  

The example I have is as follows (using pyspark from Spark 1.2.0 version)

    sc.parallelize([1,2,3,4]).aggregate(
      (0, 0),
      (lambda acc, value: (acc[0] + value, acc[1] + 1)),
      (lambda acc1, acc2: (acc1[0] + acc2[0], acc1[1] + acc2[1])))

Output:

    (10, 4)


I get the expected result `(10,4)` which is sum of `1+2+3+4` and 4 elements.  If I change the initial value passed to the aggregate function to `(1,0)` from `(0,0)`  I get the following result

    sc.parallelize([1,2,3,4]).aggregate(
        (1, 0),
        (lambda acc, value: (acc[0] + value, acc[1] + 1)),
        (lambda acc1, acc2: (acc1[0] + acc2[0], acc1[1] + acc2[1])))

Output:

    (19, 4)


The value increases by 9.  If I change it to `(2,0)`,  the value goes to `(28,4)` and so on.

Can someone explain to me how this value is calculated?  I expected the value to go up by 1 not by 9, expected to see `(11,4)` instead I am seeing `(19,4)`.  


Explain the aggregate functionality in Spark (with Python and Scala)

I have been trying out a few index views and am impressed but I nearly always need a max or a min as well and can not understand why it doesn&#39;t work with these, can anyone explain why?

I KNOW they are not allowed, I just can&#39;t understand why!!! Count etc. is allowed why not MIN/MAX, I&#39;m looking for explanation...

Why can&#39;t indexed views have a MAX() aggregate?

I have a data-frame likeso:

	x &lt;-
	id1 id2    val1  val2 val3 val4
	1   a   x    1    9
	2   a   x    2    4
	3   a   y    3    5
	4   a   y    4    9
	5   b   x    1    7
	6   b   y    4    4
	7   b   x    3    9
	8   b   y    2    8

I wish to aggregate the above by id1 &amp; id2. I want to be able to get the means for val1, val2, val3, val4 at the same time.

How do i do this?

This is what i currently have but it works just for 1 column:

    agg &lt;- aggregate(x$val1, list(id11 = x$id1, id2= x$id2), mean)
    names(agg)[3] &lt;- c(&quot;val1&quot;)  # Rename the column

Also, how do i rename the columns which are outputted as means in the same statement given above

Aggregate multiple columns at once

How can I do GroupBy multiple columns in LINQ

Something similar to this in SQL: 

    SELECT * FROM &lt;TableName&gt; GROUP BY &lt;Column1&gt;,&lt;Column2&gt;

How can I convert this to LINQ:

    QuantityBreakdown
	(
		MaterialID int,
		ProductID int,
		Quantity float
	)

    INSERT INTO @QuantityBreakdown (MaterialID, ProductID, Quantity)
	SELECT MaterialID, ProductID, SUM(Quantity)
	FROM @Transactions
	GROUP BY MaterialID, ProductID


Group By Multiple Columns

Is there a pandas built-in way to apply two different aggregating functions `f1, f2` to the same column `df[&quot;returns&quot;]`, without having to call `agg()` multiple times?

Example dataframe:

    import pandas as pd
    import datetime as dt
    import numpy as np

    pd.np.random.seed(0)
    df = pd.DataFrame({
             &quot;date&quot;    :  [dt.date(2012, x, 1) for x in range(1, 11)], 
             &quot;returns&quot; :  0.05 * np.random.randn(10), 
             &quot;dummy&quot;   :  np.repeat(1, 10)
    }) 

The syntactically wrong, but intuitively right, way to do it would be:

    # Assume `f1` and `f2` are defined for aggregating.
    df.groupby(&quot;dummy&quot;).agg({&quot;returns&quot;: f1, &quot;returns&quot;: f2})

Obviously, Python doesn&#39;t allow duplicate keys. Is there any other manner for expressing the input to `agg()`? Perhaps a list of tuples `[(column, function)]` would work better, to allow multiple functions applied to the same column? But `agg()` seems like it only accepts a dictionary.

Is there a workaround for this besides defining an auxiliary function that just applies both of the functions inside of it? (How would this work with aggregation anyway?)

Multiple aggregations of the same column using pandas GroupBy.agg()

I have a `data.frame` and I need to calculate the mean per group (i.e. per `Month`, below).

    Name     Month  Rate1     Rate2
    Aira       1      12        23
    Aira       2      18        73
    Aira       3      19        45
    Ben        1      53        19
    Ben        2      22        87
    Ben        3      19        45
    Cat        1      22        87
    Cat        2      67        43
    Cat        3      45        32

My desired output is like below, where the values for `Rate1` and `Rate2` are the group means. Please disregard the value, I have made it up for the example.

    Name       Rate1       Rate2
    Aira        23.21       12.2
    Ben         45.23       43.9
    Cat         33.22       32.2


Mean per group in a data.frame

From a data frame, is there a easy way to aggregate (`sum`, `mean`, `max` et c) multiple variables simultaneously?

Below are some sample data:

    library(lubridate)
    days = 365*2
    date = seq(as.Date(&quot;2000-01-01&quot;), length = days, by = &quot;day&quot;)
    year = year(date)
    month = month(date)
    x1 = cumsum(rnorm(days, 0.05)) 
    x2 = cumsum(rnorm(days, 0.05))
    df1 = data.frame(date, year, month, x1, x2)

I would like to simultaneously aggregate the `x1` and `x2` variables from the `df2` data frame by year and month.  The following code aggregates the `x1` variable, but is it also possible to simultaneously aggregate the `x2` variable?

    ### aggregate variables by year month
    df2=aggregate(x1 ~ year+month, data=df1, sum, na.rm=TRUE)
    head(df2)



Aggregate / summarize multiple variables per group (e.g. sum, mean)

This might sound lame, but  I have not been able to find a really good explanation of `Aggregate`.

Good means short, descriptive, comprehensive with a small and clear example.

LINQ Aggregate algorithm explained

I&#39;m struggling a bit with the dplyr-syntax. I have a data frame with different variables and one grouping variable. Now I want to calculate the mean for each column within each group, using dplyr in R.

    df &lt;- data.frame(
        a = sample(1:5, n, replace = TRUE), 
        b = sample(1:5, n, replace = TRUE), 
        c = sample(1:5, n, replace = TRUE), 
        d = sample(1:5, n, replace = TRUE), 
        grp = sample(1:3, n, replace = TRUE)
    )
    df %&gt;% group_by(grp) %&gt;% summarise(mean(a))

This gives me the mean for column &quot;a&quot; for each group indicated by &quot;grp&quot;.

My question is: is it possible to get the means for each column within each group at once? Or do I have to repeat `df %&gt;% group_by(grp) %&gt;% summarise(mean(a))` for each column?

What I would like to have is something like

    df %&gt;% group_by(grp) %&gt;% summarise(mean(a:d)) # &quot;mean(a:d)&quot; does not work

Summarizing multiple columns with dplyr?

I have a dataframe and I would like to count the number of rows within each group. I reguarly use the `aggregate` function to sum data as follows:

    df2 &lt;- aggregate(x ~ Year + Month, data = df1, sum)

Now, I would like to count observations but can&#39;t seem to find the proper argument for `FUN`. Intuitively, I thought it would be as follows:

    df2 &lt;- aggregate(x ~ Year + Month, data = df1, count)

But, no such luck.

Any ideas?

-------

Some toy data:

    set.seed(2)
    df1 &lt;- data.frame(x = 1:20,
                      Year = sample(2012:2014, 20, replace = TRUE),
                      Month = sample(month.abb[1:3], 20, replace = TRUE))

Count number of rows within each group

This [FAQ][1] is about Aggregates and PODs and covers the following material:

- What are ***Aggregates***? 
- What are ***POD***&lt;!----&gt;s (Plain Old Data)? 
- How are they related? 
- How and why are they special? 
- What changes for C++11?


  [1]: https://stackoverflow.com/tags/c%2b%2b-faq/info

What are Aggregates and PODs and how/why are they special?

Let&#39;s say I have a log of user activity and I want to generate a report of the total duration and the number of unique users per day.

    import numpy as np
    import pandas as pd
    df = pd.DataFrame({&#39;date&#39;: [&#39;2013-04-01&#39;,&#39;2013-04-01&#39;,&#39;2013-04-01&#39;,&#39;2013-04-02&#39;, &#39;2013-04-02&#39;],
        &#39;user_id&#39;: [&#39;0001&#39;, &#39;0001&#39;, &#39;0002&#39;, &#39;0002&#39;, &#39;0002&#39;],
        &#39;duration&#39;: [30, 15, 20, 15, 30]})

Aggregating duration is pretty straightforward:
    
    group = df.groupby(&#39;date&#39;)
    agg = group.aggregate({&#39;duration&#39;: np.sum})
    agg
                duration
    date
    2013-04-01        65
    2013-04-02        45

What I&#39;d like to do is sum the duration and count distincts at the same time, but I can&#39;t seem to find an equivalent for count_distinct:

    agg = group.aggregate({ &#39;duration&#39;: np.sum, &#39;user_id&#39;: count_distinct})

This works, but surely there&#39;s a better way, no?

    group = df.groupby(&#39;date&#39;)
    agg = group.aggregate({&#39;duration&#39;: np.sum})
    agg[&#39;uv&#39;] = df.groupby(&#39;date&#39;).user_id.nunique()
    agg
                duration  uv
    date
    2013-04-01        65   2
    2013-04-02        45   1

I&#39;m thinking I just need to provide a function that returns the count of distinct items of a Series object to the aggregate function, but I don&#39;t have a lot of exposure to the various libraries at my disposal. Also, it seems that the groupby object already knows this information, so wouldn&#39;t I just be duplicating the effort?

Pandas aggregate count distinct

What is the best way to do a groupby on a Pandas dataframe, but exclude some columns from that groupby? e.g. I have the following dataframe:
```none
Code   Country      Item_Code   Item    Ele_Code    Unit    Y1961    Y1962   Y1963
2      Afghanistan  15          Wheat   5312        Ha      10       20      30
2      Afghanistan  25          Maize   5312        Ha      10       20      30
4      Angola       15          Wheat   7312        Ha      30       40      50
4      Angola       25          Maize   7312        Ha      30       40      50
```
I want to groupby the column Country and Item_Code and only compute the sum of the rows falling under the columns Y1961, Y1962 and Y1963. The resulting dataframe should look like this:
```none
Code   Country      Item_Code   Item    Ele_Code    Unit    Y1961    Y1962   Y1963
2      Afghanistan  15          C3      5312        Ha      20       40       60
4      Angola       25          C4      7312        Ha      60       80      100
```
Right now I am doing this:

    df.groupby(&#39;Country&#39;).sum()

However this adds up the values in the Item_Code column as well. Is there any way I can specify which columns to include in the `sum()` operation and which ones to exclude?

Pandas sum by groupby, but exclude certain columns

I have the following data frame

    x &lt;- read.table(text = &quot;  id1 id2 val1 val2
    1   a   x    1    9
    2   a   x    2    4
    3   a   y    3    5
    4   a   y    4    9
    5   b   x    1    7
    6   b   y    4    4
    7   b   x    3    9
    8   b   y    2    8&quot;, header = TRUE)

I want to calculate the mean of val1 and val2 grouped by id1 and id2, and simultaneously count the number of rows for each id1-id2 combination. I can perform each calculation separately: 

    # calculate mean
    aggregate(. ~ id1 + id2, data = x, FUN = mean)

    # count rows
    aggregate(. ~ id1 + id2, data = x, FUN = length)

In order to do both calculations in one call, I tried

    do.call(&quot;rbind&quot;, aggregate(. ~ id1 + id2, data = x, FUN = function(x) data.frame(m = mean(x), n = length(x))))

However, I get a garbled output along with a warning:

    #     m   n
    # id1 1   2
    # id2 1   1
    #     1.5 2
    #     2   2
    #     3.5 2
    #     3   2
    #     6.5 2
    #     8   2
    #     7   2
    #     6   2
    # Warning message:
    #   In rbind(id1 = c(1L, 2L, 1L, 2L), id2 = c(1L, 1L, 2L, 2L), val1 = list( :
    #   number of columns of result is not a multiple of vector length (arg 1)

I could use the plyr package, but my data set is quite large and plyr is very slow (almost unusable) when the size of the dataset grows.

How can I use `aggregate` or other functions to perform several calculations in one call?
 





Apply several summary functions on several variables by group in one call

I wish to (1) group data by one variable (`State`), (2) within each group find the row of minimum value of another variable (`Employees`), and (3) extract the entire row.

(1) and (2) are easy one-liners, and I feel like (3) should be too, but I can&#39;t get it.

Here is a sample data set:

    &gt; data
      State Company Employees
    1    AK       A        82
    2    AK       B       104
    3    AK       C        37
    4    AK       D        24
    5    RI       E        19
    6    RI       F       118
    7    RI       G        88
    8    RI       H        42

    data &lt;- structure(list(State = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 
            2L), .Label = c(&quot;AK&quot;, &quot;RI&quot;), class = &quot;factor&quot;), Company = structure(1:8, .Label = c(&quot;A&quot;, 
            &quot;B&quot;, &quot;C&quot;, &quot;D&quot;, &quot;E&quot;, &quot;F&quot;, &quot;G&quot;, &quot;H&quot;), class = &quot;factor&quot;), Employees = c(82L, 
            104L, 37L, 24L, 19L, 118L, 88L, 42L)), .Names = c(&quot;State&quot;, &quot;Company&quot;, 
            &quot;Employees&quot;), class = &quot;data.frame&quot;, row.names = c(NA, -8L))

Calculate `min` by group is easy, using `aggregate`:

    &gt; aggregate(Employees ~ State, data, function(x) min(x))
      State Employees
    1    AK        24
    2    RI        19

...or `data.table`:
    
    &gt; library(data.table)
    &gt; DT &lt;- data.table(data)
    &gt; DT[ , list(Employees = min(Employees)), by = State]
       State Employees
    1:    AK        24
    2:    RI        19

But how do I extract the entire row corresponding to these `min` values, i.e. also including `Company` in the result? 

Extract row corresponding to minimum value of a variable by group

I want to aggregate one column in a data frame according to two grouping variables, and separate the individual values by a comma.

Here is some data:

    data &lt;- data.frame(A = c(rep(111, 3), rep(222, 3)), B = rep(1:2, 3), C = c(5:10))
    data
    #     A B  C
    # 1 111 1  5
    # 2 111 2  6
    # 3 111 1  7
    # 4 222 2  8
    # 5 222 1  9
    # 6 222 2 10    

&quot;A&quot; and &quot;B&quot; are grouping variables, and &quot;C&quot; is the variable that I want to collapse into a comma separated `character` string. I have tried:
    
    library(plyr)
    ddply(data, .(A,B), summarise, test = list(C))

        A B  test
    1 111 1  5, 7
    2 111 2     6
    3 222 1     9
    4 222 2 8, 10

but when I tried to convert test column to `character` it becomes like this:

    ddply(data, .(A,B), summarise, test = as.character(list(C)))
    #     A B     test
    # 1 111 1  c(5, 7)
    # 2 111 2        6
    # 3 222 1        9
    # 4 222 2 c(8, 10)

How can I keep the `character` format and separate them by a comma? For example, row 1 should be only `&quot;5,7&quot;`, and not as c(5,7).


Collapse / concatenate / aggregate a column to a single comma separated string within each group

I have a data frame with two columns. First column contains categories such as &quot;First&quot;, &quot;Second&quot;, &quot;Third&quot;, and the second column has numbers that represent the number of times I saw the specific groups from &quot;Category&quot;.

For example:

    Category     Frequency
    First        10
    First        15
    First        5
    Second       2
    Third        14
    Third        20
    Second       3

I want to sort the data by Category and sum all the Frequencies:

    Category     Frequency
    First        30
    Second       5
    Third        34

How would I do this in R?

How to sum a variable by group

I&#39;m trying to aggregate a &#39;STRING&#39; field in SQLServer. I would like to find the same function LISTAGG like in Oracle .

Do you know how to do the same function or an another method?


For Example,

    Field A | Field B
    1       |  A
    1       |  B
    2       |  A

And I would like that the result of this query will be 


    1 | AB
    2 | A




ListAGG in SQLSERVER

I have a pandas data frame `df` like:

    a b
    A 1
    A 2
    B 5
    B 5
    B 4
    C 6

I want to **group by the first column and get second column as lists in rows**:

    A [1,2]
    B [5,5,4]
    C [6]

Is it possible to do something like this using pandas groupby?

How to group dataframe rows into list in pandas groupby

I&#39;m using `groupby` on a pandas dataframe to drop all rows that don&#39;t have the minimum of a specific column. Something like this: 

    df1 = df.groupby(&quot;item&quot;, as_index=False)[&quot;diff&quot;].min()

However, if I have more than those two columns, the other columns (e.g. `otherstuff` in my example) get dropped. Can I keep those columns using `groupby`, or am I going to have to find a different way to drop the rows?

My data looks like: 
    
        item    diff   otherstuff
       0   1       2            1
       1   1       1            2
       2   1       3            7
       3   2      -1            0
       4   2       1            3
       5   2       4            9
       6   2      -6            2
       7   3       0            0
       8   3       2            9

and should end up like:

        item   diff  otherstuff
       0   1      1           2
       1   2     -6           2
       2   3      0           0

but what I&#39;m getting is:
       
        item   diff
       0   1      1           
       1   2     -6           
       2   3      0                 

I&#39;ve been looking through the documentation and can&#39;t find anything. I tried:

    df1 = df.groupby([&quot;item&quot;, &quot;otherstuff&quot;], as_index=false)[&quot;diff&quot;].min()

    df1 = df.groupby(&quot;item&quot;, as_index=false)[&quot;diff&quot;].min()[&quot;otherstuff&quot;]
    
    df1 = df.groupby(&quot;item&quot;, as_index=false)[&quot;otherstuff&quot;, &quot;diff&quot;].min()

But none of those work (I realized with the last one that the syntax is meant for aggregating after a group is created).

Keep other columns when doing groupby

I want to pass the numpy `percentile()` function through pandas&#39; `agg()` function as I do below with various other numpy statistics functions.

Right now I have a dataframe that looks like this:

    AGGREGATE	MY_COLUMN
    A	        10
    A	        12
    B	        5
    B	        9
    A	        84
    B	        22

And my code looks like this:

    grouped = dataframe.groupby(&#39;AGGREGATE&#39;)
    column = grouped[&#39;MY_COLUMN&#39;]
    column.agg([np.sum, np.mean, np.std, np.median, np.var, np.min, np.max])

The above code works, but I want to do something like 

    column.agg([np.sum, np.mean, np.percentile(50), np.percentile(95)])

I.e., specify various percentiles to return from `agg()`.

How should this be done?

Pass percentiles to pandas agg function

Greetings,
I&#39;ve been toying with an idea for a new project and was wondering if anyone has any idea on how a service like Kayak.com is able to aggregate data from so many sources so quickly and accurately. More specifically, do you think Kayak.com is interacting with APIs or are they crawling/scraping airline and hotel websites in order to fulfill user requests? I know there isn&#39;t one right answer for this sort of thing but I&#39;m curious to know what others think would be a good way to go about this. If it helps, pretend you are going to create kayak.com tomorrow ... where is your data coming from?

How to use GROUP BY to concatenate strings in MySQL?

I wish to count the number of unique values by grouping of a second variable, and then add the count to the existing data.frame as a new column. For example, if the existing data frame looks like this:

      color  type
    1 black chair
    2 black chair
    3 black  sofa
    4 green  sofa
    5 green  sofa
    6   red  sofa
    7   red plate
    8  blue  sofa
    9  blue plate
    10 blue chair

I want to add for each `color`, the count of unique `types` that are present in the data:

      color  type unique_types
    1 black chair            2
    2 black chair            2
    3 black  sofa            2
    4 green  sofa            1
    5 green  sofa            1
    6   red  sofa            2
    7   red plate            2
    8  blue  sofa            3
    9  blue plate            3
    10 blue chair            3

I was hoping to use `ave`, but can&#39;t seem to find a straightforward method that doesn&#39;t require many lines. I have &gt;100,000 rows, so am also not sure how important efficiency is.

It&#39;s somewhat similar to this issue: https://stackoverflow.com/questions/7450600/how-do-i-count-aggregate-values-from-a-data-frame-and-reincorporate-them-into-th

Add count of unique / distinct values by group to the original data

I have a data frame with a grouping variable (&quot;Gene&quot;) and a value variable (&quot;Value&quot;):


    Gene   Value
    A      12
    A      10
    B      3
    B      5
    B      6
    C      1
    D      3
    D      4


For each level of my grouping variable, I wish to extract the maximum value. The result should thus be a data frame with one row per level of the grouping variable:

    Gene   Value
    A      12
    B      6
    C      1
    D      4

Could `aggregate` do the trick?



Extract the maximum value within each group in a dataframe

    public class ConsolidatedChild
    {
        public string School { get; set; }
        public string Friend { get; set; }
        public string FavoriteColor { get; set; }
        public List&lt;Child&gt; Children { get; set; }
    }

    public class Child
    {
        public string School { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Friend { get; set; }
        public string Mother { get; set; }
        public string FavoriteColor { get; set; }
    }

Given the two classes above, I would like to use LINQ to create a List&lt;ConsolidatedChild&gt; from the List&lt;Child&gt;, grouped by the School, Friend and FavoriteColor properties. Is this possible with LINQ?

Please ignore the properties, the code has been written just to help with the question.

C# Linq Group By on multiple columns

I know I can *re*name columns after I aggregate the data:

    blubb &lt;- aggregate(dat$two ~ dat$one, ...)
    colnames(blubb) &lt;- c(&quot;One&quot;, &quot;Two&quot;)

Nothing wrong with that. But is there a way to aggregate and name the columns in one go? Sort of like:

    blubb &lt;- aggregate( ... , cols = c(&quot;One&quot;, &quot;Two&quot;))

It would be escpecially nice (and typo-proof) to somehow catch the original column names and do like:

    blubb &lt;- aggregate( ... , cols = c(name_of_dat$one, name_of_dat$two.&quot;_Mean&quot;))

Name columns within aggregate in R

I have a data frame DF.

Say DF is:

      A B
    1 1 2
    2 1 3
    3 2 3
    4 3 5
    5 3 6 

Now I want to combine together the rows by the column A and to have the sum of the column B.

For example:

      A B
    1 1 5
    2 2 3
    3 3 11

I am doing this currently using an SQL query with the sqldf function. But for some reason it is very slow. Is there any more convenient way to do that? I could do it manually too using a for loop but it is again slow. My SQL query is &quot; Select A,Count(B) from DF group by A&quot;. 

In general whenever I don&#39;t use vectorized operations and I use for loops the performance is extremely slow even for single procedures.

data.frame Group By column

This is in response to [a question asked on the r-help mailing list](http://r.789695.n4.nabble.com/top-10-n-values-for-each-classes-tt4658074.html#a4658095).

[Here are *lots* of examples](https://stackoverflow.com/search?q=top+value+by+group) of how to find top values by group using `sql`, so I imagine it&#39;s easy to convert that knowledge over using the R `sqldf` package.

An example: when `mtcars` is grouped by `cyl`, here are the top three records for each distinct value of `cyl`. Note that ties are excluded in this case, but it&#39;d be nice to show some different ways to treat ties.

						 mpg cyl  disp  hp drat    wt  qsec vs am gear carb ranks
	Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1   2.0
	Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2   1.0
	Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1   2.0
	Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4   3.0
	Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4   1.0
	Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4   1.5
	Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4   1.5
	Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4   3.0

How to find the top or bottom (maximum or minimum) N records per group?

Select the top N values by group

I am using this data frame:

    Fruit   Date      Name  Number
    Apples  10/6/2016 Bob    7
    Apples  10/6/2016 Bob    8
    Apples  10/6/2016 Mike   9
    Apples  10/7/2016 Steve 10
    Apples  10/7/2016 Bob    1
    Oranges 10/7/2016 Bob    2
    Oranges 10/6/2016 Tom   15
    Oranges 10/6/2016 Mike  57
    Oranges 10/6/2016 Bob   65
    Oranges 10/7/2016 Tony   1
    Grapes  10/7/2016 Bob    1
    Grapes  10/7/2016 Tom   87
    Grapes  10/7/2016 Bob   22
    Grapes  10/7/2016 Bob   12
    Grapes  10/7/2016 Tony  15

I want to aggregate this by `Name` and then by fruit to get a total number of `Fruit` per `Name`. For example:

    Bob,Apples,16

I tried grouping by `Name` and `Fruit` but how do I get the total number of Fruit?

How do I Pandas group-by to get sum?

I have a need that I imagine could be satisfied by `aggregate` or `reshape`, but I can&#39;t quite figure out.

I have a list of names (`brand`), and accompanying ID number (`id`).  This data is in long form, so names can have multiple ID&#39;s.  I&#39;d like to de-dupicate by the name (`brand`) and concatenate the multiple possible `id`&#39;s into a string separated by a comment.

For example: 

    brand            id 
    RadioShack	     2308
    Rag &amp; Bone	     4466
    Ragu	         1830
    Ragu	         4518
    Ralph Lauren	 1638
    Ralph Lauren	 2719
    Ralph Lauren	 2720
    Ralph Lauren	 2721
    Ralph Lauren	 2722 

should become:

    RadioShack	     2308
    Rag &amp; Bone	     4466
    Ragu	         1830,4518
    Ralph Lauren	 1638,2719,2720,2721,2722

How would I accomplish this?

Aggregating by unique identifier and concatenating related values into a string

I&#39;ve frequented used pandas&#39; `agg()` function to run summary statistics on every column of a data.frame.  For example, here&#39;s how you would produce the mean and standard deviation:

    df = pd.DataFrame({&#39;A&#39;: [&#39;group1&#39;, &#39;group1&#39;, &#39;group2&#39;, &#39;group2&#39;, &#39;group3&#39;, &#39;group3&#39;],
                       &#39;B&#39;: [10, 12, 10, 25, 10, 12],
                       &#39;C&#39;: [100, 102, 100, 250, 100, 102]})
    
    &gt;&gt;&gt; df
    [output]
            A   B    C
    0  group1  10  100
    1  group1  12  102
    2  group2  10  100
    3  group2  25  250
    4  group3  10  100
    5  group3  12  102

In both of those cases, the order that individual rows are sent to the agg function does not matter.  But consider the following example, which:

    df.groupby(&#39;A&#39;).agg([np.mean, lambda x: x.iloc[1] ])
    
    [output]
    
            mean  &lt;lambda&gt;  mean  &lt;lambda&gt;
    A                                     
    group1  11.0        12   101       102
    group2  17.5        25   175       250
    group3  11.0        12   101       102

In this case the lambda functions as intended, outputting the second row in each group.  However, I have not been able to find anything in the pandas documentation that implies that this is guaranteed to be true in all cases.  I want use `agg()` along with a weighted average function, so I want to be sure that the rows that come into the function will be in the same order as they appear in the original data frame.

Does anyone know, ideally via somewhere in the docs or pandas source code, if this is guaranteed to be the case?


Python Pandas: Is Order Preserved When Using groupby() and agg()?

Say I have a `data.frame` object:

    df &lt;- data.frame(name=c(&#39;black&#39;,&#39;black&#39;,&#39;black&#39;,&#39;red&#39;,&#39;red&#39;),
                     type=c(&#39;chair&#39;,&#39;chair&#39;,&#39;sofa&#39;,&#39;sofa&#39;,&#39;plate&#39;),
                     num=c(4,5,12,4,3))

Now I want to count the number of rows (observations) of for each combination of `name` and `type`. This can be done like so:

    table(df[ , c(&quot;name&quot;,&quot;type&quot;)])

or possibly also with `plyr`, (though I am not sure how).

However, how do I get the results incorporated into the original data frame?  So that the results will look like this:

    df
    #    name  type num count
    # 1 black chair   4     2
    # 2 black chair   5     2
    # 3 black  sofa  12     1
    # 4   red  sofa   4     1
    # 5   red plate   3     1

where `count` now stores the results from the aggregation.

A solution with `plyr` could be interesting to learn as well, though I would like to see how this is done with base R.

All Aggregate Solutions on Gang of Coders

Total of 38 Aggregate Solutions

Rename result columns from Pandas aggregation ("FutureWarning: using a dict with renaming is deprecated")