I have a data frame `df` and I use several columns from it to `groupby`:

    df[&#39;col1&#39;,&#39;col2&#39;,&#39;col3&#39;,&#39;col4&#39;].groupby([&#39;col1&#39;,&#39;col2&#39;]).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 like to know how many number were used to get these means. For example in the first group there are 8 values and in the second one 10 and so on.

In short: How do I get **group-wise** statistics for a dataframe?




Get statistics for each group (such as count, mean, etc) using pandas GroupBy?

I&#39;m starting with input data like this

    df1 = pandas.DataFrame( { 
        &quot;Name&quot; : [&quot;Alice&quot;, &quot;Bob&quot;, &quot;Mallory&quot;, &quot;Mallory&quot;, &quot;Bob&quot; , &quot;Mallory&quot;] , 
        &quot;City&quot; : [&quot;Seattle&quot;, &quot;Seattle&quot;, &quot;Portland&quot;, &quot;Seattle&quot;, &quot;Seattle&quot;, &quot;Portland&quot;] } )

Which when printed appears as this:

       City     Name
    0   Seattle    Alice
    1   Seattle      Bob
    2  Portland  Mallory
    3   Seattle  Mallory
    4   Seattle      Bob
    5  Portland  Mallory


Grouping is simple enough:

    g1 = df1.groupby( [ &quot;Name&quot;, &quot;City&quot;] ).count()

and printing yields a `GroupBy` object:

                      City  Name
    Name    City
    Alice   Seattle      1     1
    Bob     Seattle      2     2
    Mallory Portland     2     2
            Seattle      1     1


But what I want eventually is another DataFrame object that contains all the rows in the GroupBy object. In other words I want to get the following result:


                      City  Name
    Name    City
    Alice   Seattle      1     1
    Bob     Seattle      2     2
    Mallory Portland     2     2
    Mallory Seattle      1     1

I can&#39;t quite see how to accomplish this in the pandas documentation. Any hints would be welcome.


Converting a Pandas GroupBy output from Series to DataFrame

- What is pivot?
- How do I pivot?
- Is this a pivot?
- Long format to wide format?

I&#39;ve seen a lot of questions that ask about pivot tables.  Even if they don&#39;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 all aspects of pivoting...

... But I&#39;m going to give it a go.

___

The problem with existing questions and answers is that often the question is focused on a nuance that the OP has trouble generalizing in order to use a number of the existing good answers.  However, none of the answers attempt to give a comprehensive explanation (because it&#39;s a daunting task)

Look a few examples from my [**Google Search**][1]

1. https://stackoverflow.com/q/28337117/2336654
 - Good question and answer.  But the answer only answers the specific question with little explanation.
2. https://stackoverflow.com/q/42708193/2336654
 - In this question, the OP is concerned with the output of the pivot.  Namely how the columns look.  OP wanted it to look like R.  This isn&#39;t very helpful for pandas users.
3. https://stackoverflow.com/q/11400181/2336654
- Another decent question but the answer focuses on one method, namely `pd.DataFrame.pivot`

So whenever someone searches for `pivot` they get sporadic results that are likely not going to answer their specific question.

___

# Setup

You may notice that I conspicuously named my columns and relevant column values to correspond with how I&#39;m going to pivot in the answers below.

    import numpy as np
    import pandas as pd
    from numpy.core.defchararray import add

    np.random.seed([3,1415])
    n = 20

    cols = np.array([&#39;key&#39;, &#39;row&#39;, &#39;item&#39;, &#39;col&#39;])
    arr1 = (np.random.randint(5, size=(n, 4)) // [2, 1, 2, 1]).astype(str)

    df = pd.DataFrame(
        add(cols, arr1), columns=cols
    ).join(
        pd.DataFrame(np.random.rand(n, 2).round(2)).add_prefix(&#39;val&#39;)
    )
    print(df)

         key   row   item   col  val0  val1
    0   key0  row3  item1  col3  0.81  0.04
    1   key1  row2  item1  col2  0.44  0.07
    2   key1  row0  item1  col0  0.77  0.01
    3   key0  row4  item0  col2  0.15  0.59
    4   key1  row0  item2  col1  0.81  0.64
    5   key1  row2  item2  col4  0.13  0.88
    6   key2  row4  item1  col3  0.88  0.39
    7   key1  row4  item1  col1  0.10  0.07
    8   key1  row0  item2  col4  0.65  0.02
    9   key1  row2  item0  col2  0.35  0.61
    10  key2  row0  item2  col1  0.40  0.85
    11  key2  row4  item1  col2  0.64  0.25
    12  key0  row2  item2  col3  0.50  0.44
    13  key0  row4  item1  col4  0.24  0.46
    14  key1  row3  item2  col3  0.28  0.11
    15  key0  row3  item1  col1  0.31  0.23
    16  key0  row0  item2  col3  0.86  0.01
    17  key0  row4  item0  col3  0.64  0.21
    18  key2  row2  item2  col0  0.13  0.45
    19  key0  row2  item0  col4  0.37  0.70

### Question(s)

1. Why do I get `ValueError: Index contains duplicate entries, cannot reshape`

2. How do I pivot `df` such that the `col` values are columns, `row` values are the index, and mean of `val0` are the values?

        col   col0   col1   col2   col3  col4
        row
        row0  0.77  0.605    NaN  0.860  0.65
        row2  0.13    NaN  0.395  0.500  0.25
        row3   NaN  0.310    NaN  0.545   NaN
        row4   NaN  0.100  0.395  0.760  0.24

3. How do I pivot `df` such that the `col` values are columns, `row` values are the index, mean of `val0` are the values, and missing values are `0`?

        col   col0   col1   col2   col3  col4
        row
        row0  0.77  0.605  0.000  0.860  0.65
        row2  0.13  0.000  0.395  0.500  0.25
        row3  0.00  0.310  0.000  0.545  0.00
        row4  0.00  0.100  0.395  0.760  0.24

4. Can I get something other than `mean`, like maybe `sum`?

        col   col0  col1  col2  col3  col4
        row
        row0  0.77  1.21  0.00  0.86  0.65
        row2  0.13  0.00  0.79  0.50  0.50
        row3  0.00  0.31  0.00  1.09  0.00
        row4  0.00  0.10  0.79  1.52  0.24

5. Can I do more that one aggregation at a time?

               sum                          mean
        col   col0  col1  col2  col3  col4  col0   col1   col2   col3  col4
        row
        row0  0.77  1.21  0.00  0.86  0.65  0.77  0.605  0.000  0.860  0.65
        row2  0.13  0.00  0.79  0.50  0.50  0.13  0.000  0.395  0.500  0.25
        row3  0.00  0.31  0.00  1.09  0.00  0.00  0.310  0.000  0.545  0.00
        row4  0.00  0.10  0.79  1.52  0.24  0.00  0.100  0.395  0.760  0.24

6. Can I aggregate over multiple value columns?

              val0                             val1
        col   col0   col1   col2   col3  col4  col0   col1  col2   col3  col4
        row
        row0  0.77  0.605  0.000  0.860  0.65  0.01  0.745  0.00  0.010  0.02
        row2  0.13  0.000  0.395  0.500  0.25  0.45  0.000  0.34  0.440  0.79
        row3  0.00  0.310  0.000  0.545  0.00  0.00  0.230  0.00  0.075  0.00
        row4  0.00  0.100  0.395  0.760  0.24  0.00  0.070  0.42  0.300  0.46

7. Can Subdivide by multiple columns?

        item item0             item1                         item2
        col   col2  col3  col4  col0  col1  col2  col3  col4  col0   col1  col3  col4
        row
        row0  0.00  0.00  0.00  0.77  0.00  0.00  0.00  0.00  0.00  0.605  0.86  0.65
        row2  0.35  0.00  0.37  0.00  0.00  0.44  0.00  0.00  0.13  0.000  0.50  0.13
        row3  0.00  0.00  0.00  0.00  0.31  0.00  0.81  0.00  0.00  0.000  0.28  0.00
        row4  0.15  0.64  0.00  0.00  0.10  0.64  0.88  0.24  0.00  0.000  0.00  0.00

8. Or

        item      item0             item1                         item2
        col        col2  col3  col4  col0  col1  col2  col3  col4  col0  col1  col3  col4
        key  row
        key0 row0  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.86  0.00
             row2  0.00  0.00  0.37  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.50  0.00
             row3  0.00  0.00  0.00  0.00  0.31  0.00  0.81  0.00  0.00  0.00  0.00  0.00
             row4  0.15  0.64  0.00  0.00  0.00  0.00  0.00  0.24  0.00  0.00  0.00  0.00
        key1 row0  0.00  0.00  0.00  0.77  0.00  0.00  0.00  0.00  0.00  0.81  0.00  0.65
             row2  0.35  0.00  0.00  0.00  0.00  0.44  0.00  0.00  0.00  0.00  0.00  0.13
             row3  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.28  0.00
             row4  0.00  0.00  0.00  0.00  0.10  0.00  0.00  0.00  0.00  0.00  0.00  0.00
        key2 row0  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.40  0.00  0.00
             row2  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.00  0.13  0.00  0.00  0.00
             row4  0.00  0.00  0.00  0.00  0.00  0.64  0.88  0.00  0.00  0.00  0.00  0.00

9. Can I aggregate the frequency in which the column and rows occur together, aka &quot;cross tabulation&quot;?

        col   col0  col1  col2  col3  col4
        row
        row0     1     2     0     1     1
        row2     1     0     2     1     2
        row3     0     1     0     2     0
        row4     0     1     2     2     1

10. How do I convert a DataFrame from long to wide by pivoting on ONLY two columns? Given,

        np.random.seed([3, 1415])
        df2 = pd.DataFrame({&#39;A&#39;: list(&#39;aaaabbbc&#39;), &#39;B&#39;: np.random.choice(15, 8)})
        df2
           A   B
        0  a   0
        1  a  11
        2  a   2
        3  a  11
        4  b  10
        5  b  10
        6  b  14
        7  c   7

    The expected should look something like

              a     b    c
        0   0.0  10.0  7.0
        1  11.0  10.0  NaN
        2   2.0  14.0  NaN
        3  11.0   NaN  NaN

11. How do I flatten the multiple index to single index after `pivot`?

    From

           1  2
           1  1  2
        a  2  1  1
        b  2  1  0
        c  1  0  0


    To

           1|1  2|1  2|2
        a    2    1    1
        b    2    1    0
        c    1    0    0


  [1]: https://www.google.com/search?q=how%20to%20pivot%20a%20pandas%20dataframe&amp;oq=How%20do%20I%20pivot%20a%20pandas%20dataframe





How can I pivot a dataframe?

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

How do I find all rows in a pandas DataFrame which have the max value for `count` column, after grouping by `[&#39;Sp&#39;,&#39;Mt&#39;]` columns?

**Example 1:** the following DataFrame, which I group by `[&#39;Sp&#39;,&#39;Mt&#39;]`:

       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**

Expected output: get the result rows whose count is max in each group, like:

    0  MM1  S1   a      **3**
    2  MM1  S3   cb     **5**
    3  MM2  S3   mk     **8**
    4  MM2  S4   bg     **10** 
    8  MM4  S2   uyi    **7**


**Example 2:** this DataFrame, which I group by `[&#39;Sp&#39;,&#39;Mt&#39;]`: 

	   Sp   Mt   Value  count
	4  MM2  S4   bg     10
	5  MM2  S4   dgd    1
	6  MM4  S2   rd     2
	7  MM4  S2   cb     8
	8  MM4  S2   uyi    8

For the above example, I want to get **all** the rows where `count` equals max, in each group e.g:

	MM2  S4   bg     10
	MM4  S2   cb     8
	MM4  S2   uyi    8

Get the row(s) which have the max value in groups using groupby

I need to count unique `ID` values in every `domain`.

I have data:

    ID, domain
    123, &#39;vk.com&#39;
    123, &#39;vk.com&#39;
    123, &#39;twitter.com&#39;
    456, &#39;vk.com&#39;
    456, &#39;facebook.com&#39;
    456, &#39;vk.com&#39;
    456, &#39;google.com&#39;
    789, &#39;twitter.com&#39;
    789, &#39;vk.com&#39;

I try `df.groupby([&#39;domain&#39;, &#39;ID&#39;]).count()`

But I want to get

    domain, count
    vk.com   3
    twitter.com   2
    facebook.com   1
    google.com   1


Count unique values per groups with Pandas

I have a DataFrame with many missing values in columns which I wish to groupby:

    import pandas as pd
    import numpy as np
    df = pd.DataFrame({&#39;a&#39;: [&#39;1&#39;, &#39;2&#39;, &#39;3&#39;], &#39;b&#39;: [&#39;4&#39;, np.NaN, &#39;6&#39;]})
    
    In [4]: df.groupby(&#39;b&#39;).groups
    Out[4]: {&#39;4&#39;: [0], &#39;6&#39;: [2]}

see that Pandas has dropped the rows with NaN target values. (I want to include these rows!)

*Since I need many such operations (many cols have missing values), and use more complicated functions than just medians (typically random forests), I want to avoid writing too complicated pieces of code.*

Any suggestions? Should I write a function for this or is there a simple solution?

pandas GroupBy columns with NaN (missing) values

DataFrame:

      c_os_family_ss c_os_major_is l_customer_id_i
    0      Windows 7                         90418
    1      Windows 7                         90418
    2      Windows 7                         90418

Code:

    print df
    for name, group in df.groupby(&#39;l_customer_id_i&#39;).agg(lambda x: &#39;,&#39;.join(x)):
        print name
        print group

I&#39;m trying to just loop over the aggregated data, but I get the error:
&gt; ValueError: too many values to unpack

@EdChum, here&#39;s the expected output:

                                                        c_os_family_ss  \
    l_customer_id_i
    131572           Windows 7,Windows 7,Windows 7,Windows 7,Window...
    135467           Windows 7,Windows 7,Windows 7,Windows 7,Window...
    
                                                         c_os_major_is
    l_customer_id_i
    131572           ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...
    135467           ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...

The output is not the problem, I wish to loop over every group.

How to loop over grouped Pandas dataframe?

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()

How do I access the corresponding groupby dataframe in a groupby object by the key?

With the following groupby:

    rand = np.random.RandomState(1)
    df = pd.DataFrame({&#39;A&#39;: [&#39;foo&#39;, &#39;bar&#39;] * 3,
                       &#39;B&#39;: rand.randn(6),
                       &#39;C&#39;: rand.randint(0, 20, 6)})
    gb = df.groupby([&#39;A&#39;])

I can iterate through it to get the keys and groups:

    In [11]: for k, gp in gb:
                 print &#39;key=&#39; + str(k)
                 print gp
    key=bar
         A         B   C
    1  bar -0.611756  18
    3  bar -1.072969  10
    5  bar -2.301539  18
    key=foo
         A         B   C
    0  foo  1.624345   5
    2  foo -0.528172  11
    4  foo  0.865408  14

I would like to be able to access a group by its key:

    In [12]: gb[&#39;foo&#39;]
    Out[12]:  
         A         B   C
    0  foo  1.624345   5
    2  foo -0.528172  11
    4  foo  0.865408  14

But when I try doing that with `gb[(&#39;foo&#39;,)]` I get this weird `pandas.core.groupby.DataFrameGroupBy` object thing which doesn&#39;t seem to have any methods that correspond to the DataFrame I want.

The best I could think of is:

    In [13]: def gb_df_key(gb, key, orig_df):
                 ix = gb.indices[key]
                 return orig_df.ix[ix]
        
             gb_df_key(gb, &#39;foo&#39;, df)
    Out[13]:
         A         B   C
    0  foo  1.624345   5
    2  foo -0.528172  11
    4  foo  0.865408  14  

but this is kind of nasty, considering how nice pandas usually is at these things.  
What&#39;s the built-in way of doing this?

How to access pandas groupby dataframe by key

I want to merge several strings in a dataframe based on a groupedby in Pandas. 

This is my code so far:

    import pandas as pd
    from io import StringIO
    
    data = StringIO(&quot;&quot;&quot;
    &quot;name1&quot;,&quot;hej&quot;,&quot;2014-11-01&quot;
    &quot;name1&quot;,&quot;du&quot;,&quot;2014-11-02&quot;
    &quot;name1&quot;,&quot;aj&quot;,&quot;2014-12-01&quot;
    &quot;name1&quot;,&quot;oj&quot;,&quot;2014-12-02&quot;
    &quot;name2&quot;,&quot;fin&quot;,&quot;2014-11-01&quot;
    &quot;name2&quot;,&quot;katt&quot;,&quot;2014-11-02&quot;
    &quot;name2&quot;,&quot;mycket&quot;,&quot;2014-12-01&quot;
    &quot;name2&quot;,&quot;lite&quot;,&quot;2014-12-01&quot;
    &quot;&quot;&quot;)
    
    # load string as stream into dataframe
    df = pd.read_csv(data,header=0, names=[&quot;name&quot;,&quot;text&quot;,&quot;date&quot;],parse_dates=[2])
    
    # add column with month
    df[&quot;month&quot;] = df[&quot;date&quot;].apply(lambda x: x.month)


I want the end result to look like this:

![enter image description here][1]


  [1]: http://i.stack.imgur.com/xoEhO.png

I don&#39;t get how I can use groupby and apply some sort of concatenation of the strings in the column &quot;text&quot;. Any help appreciated!

Concatenate strings from several rows using Pandas groupby

I have a data frame with three string columns. I know that the only one value in the 3rd column is valid for every combination of the first two. To clean the data I have to group by data frame by first two columns and select most common value of the third column for each combination.

My code:

    import pandas as pd
    from scipy import stats

    source = pd.DataFrame({&#39;Country&#39; : [&#39;USA&#39;, &#39;USA&#39;, &#39;Russia&#39;,&#39;USA&#39;], 
                      &#39;City&#39; : [&#39;New-York&#39;, &#39;New-York&#39;, &#39;Sankt-Petersburg&#39;, &#39;New-York&#39;],
                      &#39;Short name&#39; : [&#39;NY&#39;,&#39;New&#39;,&#39;Spb&#39;,&#39;NY&#39;]})

    print source.groupby([&#39;Country&#39;,&#39;City&#39;]).agg(lambda x: stats.mode(x[&#39;Short name&#39;])[0])

Last line of code doesn&#39;t work, it says &quot;Key error &#39;Short name&#39;&quot; and if I try to group only by City, then I got an AssertionError. What can I do fix it?

GroupBy pandas DataFrame and select most common value

Consider a csv file:

    string,date,number
    a string,2/5/11 9:16am,1.0
    a string,3/5/11 10:44pm,2.0
    a string,4/22/11 12:07pm,3.0
    a string,4/22/11 12:10pm,4.0
    a string,4/29/11 11:59am,1.0
    a string,5/2/11 1:41pm,2.0
    a string,5/2/11 2:02pm,3.0
    a string,5/2/11 2:56pm,4.0
    a string,5/2/11 3:00pm,5.0
    a string,5/2/14 3:02pm,6.0
    a string,5/2/14 3:18pm,7.0

I can read this in, and reformat the date column into datetime format:

    b=pd.read_csv(&#39;b.dat&#39;)
    b[&#39;date&#39;]=pd.to_datetime(b[&#39;date&#39;],format=&#39;%m/%d/%y %I:%M%p&#39;)

I have been trying to group the data by month. It seems like there should be an obvious way of accessing the month and grouping by that. But I can&#39;t seem to do it. Does anyone know how?

What I am currently trying is re-indexing by the date:

    b.index=b[&#39;date&#39;]

I can access the month like so:

    b.index.month

However I can&#39;t seem to find a function to lump together by month.



pandas dataframe groupby datetime month

I would like to add a cumulative sum column to my Pandas dataframe so that:

    name | day       | no
    -----|-----------|----
    Jack | Monday    | 10
    Jack | Tuesday   | 20
    Jack | Tuesday   | 10
    Jack | Wednesday | 50
    Jill | Monday    | 40
    Jill | Wednesday | 110

becomes:


    Jack | Monday     | 10  | 10
    Jack | Tuesday    | 30  | 40
    Jack | Wednesday  | 50  | 90
    Jill | Monday     | 40  | 40
    Jill | Wednesday  | 110 | 150

I tried various combos of `df.groupby` and `df.agg(lambda x: cumsum(x))` to no avail.


Pandas groupby cumulative sum

Trying to create a new column from the `groupby` calculation. In the code below, I get the correct calculated values for each date (see group below) but when I try to create a new column (`df[&#39;Data4&#39;]`) with it I get NaN. So I am trying to create a new column in the dataframe with the sum of `Data3` for the all dates and apply that to each date row. For example, 2015-05-08 is in 2 rows (total is 50+5 = 55) and in this new column I would like to have 55 in both of the rows. 


	import pandas as pd
	import numpy as np
	from pandas import DataFrame

	df = pd.DataFrame({
        &#39;Date&#39; : [&#39;2015-05-08&#39;, &#39;2015-05-07&#39;, &#39;2015-05-06&#39;, &#39;2015-05-05&#39;, &#39;2015-05-08&#39;, &#39;2015-05-07&#39;, &#39;2015-05-06&#39;, &#39;2015-05-05&#39;], 
        &#39;Sym&#39;  : [&#39;aapl&#39;, &#39;aapl&#39;, &#39;aapl&#39;, &#39;aapl&#39;, &#39;aaww&#39;, &#39;aaww&#39;, &#39;aaww&#39;, &#39;aaww&#39;], 
        &#39;Data2&#39;: [11, 8, 10, 15, 110, 60, 100, 40],
        &#39;Data3&#39;: [5, 8, 6, 1, 50, 100, 60, 120]
    })

	group = df[&#39;Data3&#39;].groupby(df[&#39;Date&#39;]).sum()

	df[&#39;Data4&#39;] = group

How do I create a new column from the output of pandas groupby().sum()?

This should be straightforward, but the closest thing I&#39;ve found is this post:
https://stackoverflow.com/questions/18265930/pandas-filling-missing-values-within-a-group, and I still can&#39;t solve my problem....

Suppose I have the following dataframe

    df = pd.DataFrame({&#39;value&#39;: [1, np.nan, np.nan, 2, 3, 1, 3, np.nan, 3], &#39;name&#39;: [&#39;A&#39;,&#39;A&#39;, &#39;B&#39;,&#39;B&#39;,&#39;B&#39;,&#39;B&#39;, &#39;C&#39;,&#39;C&#39;,&#39;C&#39;]})

      name  value
    0    A      1
    1    A    NaN
    2    B    NaN
    3    B      2
    4    B      3
    5    B      1
    6    C      3
    7    C    NaN
    8    C      3

and I&#39;d like to fill in &quot;NaN&quot; with mean value in each &quot;name&quot; group, i.e.

          name  value
    0    A      1
    1    A      1
    2    B      2
    3    B      2
    4    B      3
    5    B      1
    6    C      3
    7    C      3
    8    C      3

I&#39;m not sure where to go after:

    grouped = df.groupby(&#39;name&#39;).mean()

Thanks a bunch.

Pandas: filling missing values by mean in each group

How to group values of pandas dataframe and select the latest(by date) from each group? 

For example, given a dataframe sorted by date:

    	id     product   date
    0   220    6647     2014-09-01 
    1   220    6647     2014-09-03 
    2   220    6647     2014-10-16
    3   826    3380     2014-11-11
    4   826    3380     2014-12-09
    5   826    3380     2015-05-19
    6   901    4555     2014-09-01
    7   901    4555     2014-10-05
    8   901    4555     2014-11-01

grouping by id or product, and selecting the earliest gives:

    	id     product   date
    2   220    6647     2014-10-16
    5   826    3380     2015-05-19
    8   901    4555     2014-11-01

group by pandas dataframe and select latest in each group

I have a dataframe that looks like this:

                  Company Name              Organisation Name  Amount
    10118  Vifor Pharma UK Ltd  Welsh Assoc for Gastro &amp; Endo 2700.00
    10119  Vifor Pharma UK Ltd    Welsh IBD Specialist Group,  169.00
    10120  Vifor Pharma UK Ltd             West Midlands AHSN 1200.00
    10121  Vifor Pharma UK Ltd           Whittington Hospital   63.00
    10122  Vifor Pharma UK Ltd                 Ysbyty Gwynedd   75.93

How do I sum the `Amount` and count the `Organisation Name`, to get a new dataframe that looks like this?

                  Company Name             Organisation Count   Amount
    10118  Vifor Pharma UK Ltd                              5 11000.00

I know how to sum *or* count:

    df.groupby(&#39;Company Name&#39;).sum()
    df.groupby(&#39;Company Name&#39;).count()

But not how to do both!

  [1]: http://i.stack.imgur.com/zzA4C.png

Group dataframe and get sum AND count?

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 have the following dataframe:

    df = pd.DataFrame([
        (1, 1, &#39;term1&#39;),
        (1, 2, &#39;term2&#39;),
        (1, 1, &#39;term1&#39;),
        (1, 1, &#39;term2&#39;),
        (2, 2, &#39;term3&#39;),
        (2, 3, &#39;term1&#39;),
        (2, 2, &#39;term1&#39;)
    ], columns=[&#39;id&#39;, &#39;group&#39;, &#39;term&#39;])

I want to group it by `id` and `group` and calculate the number of each term for this id, group pair.

So in the end I am going to get something like this:

[![enter image description here][1]][1]

I was able to achieve what I want by looping over all the rows with `df.iterrows()` and creating a new dataframe, but this is clearly inefficient. (If it helps, I know the list of all terms beforehand and there are ~10 of them).

It looks like I have to group by and then count values, so I tried that with `df.groupby([&#39;id&#39;, &#39;group&#39;]).value_counts()` which does not work because [value_counts][2] operates on the groupby series and not a dataframe.

Anyway I can achieve this without looping?


  [1]: http://i.stack.imgur.com/DIwPYm.png
  [2]: http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.core.groupby.SeriesGroupBy.value_counts.html

Groupby value counts on the dataframe pandas

So I have a dataframe, `df1`, that looks like the following:

           A      B      C
    1     foo    12    California
    2     foo    22    California
    3     bar    8     Rhode Island
    4     bar    32    Rhode Island
    5     baz    15    Ohio
    6     baz    26    Ohio

I want to group by column `A` and then sum column `B` while keeping the value in column `C`. Something like this:

          A       B      C
    1    foo     34    California
    2    bar     40    Rhode Island
    3    baz     41    Ohio

The issue is, when I say 
```
df.groupby(&#39;A&#39;).sum()
```
column `C` gets removed, returning

          B
    A
    bar  40
    baz  41
    foo  34

How can I get around this and keep column `C` when I group and sum?

Pandas Groupby and Sum Only One Column

Given the following data frame:

    import pandas as pd
    import numpy as np
    df=pd.DataFrame({&#39;A&#39;:[&#39;A&#39;,&#39;A&#39;,&#39;A&#39;,&#39;B&#39;,&#39;B&#39;,&#39;B&#39;],
                    &#39;B&#39;:[&#39;a&#39;,&#39;a&#39;,&#39;b&#39;,&#39;a&#39;,&#39;a&#39;,&#39;a&#39;],
                    })
    df

     	A 	B
    0 	A 	a 
    1 	A 	a 
    2 	A 	b 
    3 	B 	a 
    4 	B 	a 
    5 	B 	a

I&#39;d like to create column &#39;C&#39;, which numbers the rows within each group in columns A and B like this:

     	A 	B   C
    0 	A 	a 	1
    1 	A 	a 	2
    2 	A 	b 	1
    3 	B 	a   1
    4 	B 	a   2
    5 	B 	a   3

I&#39;ve tried this so far:

    df[&#39;C&#39;]=df.groupby([&#39;A&#39;,&#39;B&#39;])[&#39;B&#39;].transform(&#39;rank&#39;)

...but it doesn&#39;t work!

Pandas number rows within group in increasing order

I have a python-pandas-DataFrame in which first column is `&quot;user_id&quot;` and rest of the columns are tags(`&quot;Tag_0&quot;` to `&quot;Tag_122&quot;`). 

I have the data in the following format:

    UserId	Tag_0	Tag_1
    7867688	0	5
    7867688	0	3
    7867688	3	0
    7867688	3.5	3.5
    7867688	4	4
    7867688	3.5	0

My aim is to achieve `Sum(Tag)/Count(NonZero(Tags))` for each user_id

`df.groupby(&#39;user_id&#39;).sum()`, gives me `sum(tag)`, however I am clueless about counting non zero values

Is it possible to achieve `Sum(Tag)/Count(NonZero(Tags))` in one command?

In MySQL I could achieve this as follows:-

    select user_id, sum(tag)/count(nullif(tag,0)) from table group by 1

Any help shall be appreciated.



Counting non zero values in each column of a DataFrame in python

I have a time series object `grouped` of the type `&lt;pandas.core.groupby.SeriesGroupBy object at 0x03F1A9F0&gt;`. `grouped.sum()` gives the desired result but I cannot get rolling_sum to work with the `groupby` object. Is there any way to apply rolling functions to `groupby` objects? For example:

    x = range(0, 6)
    id = [&#39;a&#39;, &#39;a&#39;, &#39;a&#39;, &#39;b&#39;, &#39;b&#39;, &#39;b&#39;]
    df = DataFrame(zip(id, x), columns = [&#39;id&#39;, &#39;x&#39;])
    df.groupby(&#39;id&#39;).sum()
    id    x
    a    3
    b   12

However, I would like to have something like:

      id  x
    0  a  0
    1  a  1
    2  a  3
    3  b  3
    4  b  7
    5  b  12

Python - rolling functions for GroupBy object

1. How can I perform aggregation with Pandas?
2. No DataFrame after aggregation! What happened?
3. How can I aggregate mainly strings columns (to `list`s, `tuple`s, `strings with separator`)?
4. How can I aggregate counts?
5. How can I create a new column filled by aggregated values?

I&#39;ve seen these recurring questions asking about various faces of the pandas aggregate functionality.
Most of the information regarding aggregation and its various use cases today is fragmented across dozens of badly worded, unsearchable posts.
The aim here is to collate some of the more important points for posterity.

This Q&amp;A is meant to be the next instalment in a series of helpful user-guides:

 - [How to pivot a dataframe][1],
 - [Pandas concat][2]
 - [How do I operate on a DataFrame with a Series for every column?][3]
 - [Pandas Merging 101][4]

Please note that this post is not meant to be a replacement for the [documentation about aggregation][5] and about [groupby][6], so please read that as well!

  [1]: https://stackoverflow.com/questions/47152691
  [2]: https://stackoverflow.com/questions/49620538
  [3]: https://stackoverflow.com/questions/53217607
  [4]: https://stackoverflow.com/questions/53645882
  [5]: http://pandas.pydata.org/pandas-docs/stable/groupby.html#aggregation
  [6]: http://pandas.pydata.org/pandas-docs/stable/groupby.html







Aggregation in Pandas

This would be useful so I know how many unique groups I have to perform calculations on.  Thank you.

Suppose groupby object is called `dfgroup`. 

How to get number of groups in a groupby object in pandas?

I want to split the following dataframe based on column ZZ

    df = 
            N0_YLDF  ZZ        MAT
        0  6.286333   2  11.669069
        1  6.317000   6  11.669069
        2  6.324889   6  11.516454
        3  6.320667   5  11.516454
        4  6.325556   5  11.516454
        5  6.359000   6  11.516454
        6  6.359000   6  11.516454
        7  6.361111   7  11.516454
        8  6.360778   7  11.516454
        9  6.361111   6  11.516454

As output, I want a new `DataFrame` with the `N0_YLDF` column split into 4, one new column for each unique value of `ZZ`. How do I go about this? I can do groupby, but do not know what to do with the grouped object.

Split pandas dataframe based on groupby

# Q1) I want to do a groupby, SQL-style aggregation and **rename the output column**:

Example dataset:

    &gt;&gt;&gt; df
        ID     Region  count
    0  100       Asia      2
    1  101     Europe      3
    2  102         US      1
    3  103     Africa      5
    4  100     Russia      5
    5  101  Australia      7
    6  102         US      8
    7  104       Asia     10
    8  105     Europe     11
    9  110     Africa     23

I want to group the observations of this dataset by `ID` and `Region` and summing the `count` for each group. So I used something like this...

    &gt;&gt;&gt; print(df.groupby([&#39;ID&#39;,&#39;Region&#39;],as_index=False).count().sum())

        ID     Region  count
    0  100       Asia      2
    1  100     Russia      5
    2  101  Australia      7
    3  101     Europe      3
    4  102         US      9
    5  103     Africa      5
    6  104       Asia     10
    7  105     Europe     11
    8  110     Africa     23

On using `as_index=False` I am able to get &quot;SQL-Like&quot; output. My problem is that I am unable to **rename the aggregate variable `count`** here. So in SQL if wanted to do the above thing I would do something like this:

    select ID, Region, sum(count) as Total_Numbers
    from df
    group by ID, Region
    order by ID, Region

As we see, it&#39;s very easy for me to **rename the aggregate variable `count`** to `Total_Numbers` in SQL. I wanted to do the same thing in Pandas but unable to find such an option in group-by function. Can somebody help?

The second question (more of an observation) is whether...

# Q2) Is it possible to directly use column names in Pandas dataframe functions without enclosing them in quotes?

I understand that the variable names are strings, so have to be inside quotes, but I see if use them outside dataframe function and as an attribute we don&#39;t require them to be inside quotes. Like `df.ID.sum()` etc. It&#39;s only when we use it in a DataFrame function like `df.sort()` or `df.groupby` we have to use it inside quotes. This is actually a bit of pain as in SQL or in SAS or other languages we simply use the variable name without quoting them. Any suggestion on this? 

Kindly reply to both questions (Q1 is the main, Q2 more of an opinion).

Renaming Column Names in Pandas Groupby function

what would be the most efficient way to use groupby and in parallel apply a filter in pandas?

Basically I am asking for the equivalent in SQL of

    select *
    ...
    group by col_name
    having condition

I think there are many uses cases ranging from conditional means, sums, conditional probabilities, etc. which would make such a command very powerful.

I need a very good performance, so ideally such a command would not be the result of several layered operations done in python.





What is the equivalent of SQL &quot;GROUP BY HAVING&quot; on Pandas?

I know this must have been answered some where but I just could not find it.

**Problem**: Sample each group after groupby operation.

&lt;!-- language: lang-py --&gt;

    import pandas as pd
    
    df = pd.DataFrame({&#39;a&#39;: [1,2,3,4,5,6,7],
                       &#39;b&#39;: [1,1,1,0,0,0,0]})
    
    grouped = df.groupby(&#39;b&#39;)

    # now sample from each group, e.g., I want 30% of each group


Sample each group after pandas groupby

I have the following pandas dataframe:

    token	 year	 uses  books
      386	xanthos	 1830	 3     3
      387	xanthos	 1840	 1     1
      388	xanthos	 1840	 2     2
      389	xanthos	 1868	 2     2
      390	xanthos	 1875	 1     1

I aggregate the rows with duplicate `token` and `years` like so:

    dfalph = dfalph[[&#39;token&#39;,&#39;year&#39;,&#39;uses&#39;,&#39;books&#39;]].groupby([&#39;token&#39;, &#39;year&#39;]).agg([np.sum])
    dfalph.columns = dfalph.columns.droplevel(1)

                   uses  books
    token    year		
    xanthos  1830	 3     3
             1840	 3     3
             1867	 2     2
             1868	 2     2
             1875	 1     1

Instead of having the &#39;token&#39; and &#39;year&#39; fields in the index, I would like to return them to columns and have an integer index. 

How to move pandas data from index to column after multiple groupby

I have a column `Date_Time` that I wish to groupby date time without creating a new column. Is this possible the current code I have does not work.

    df = pd.groupby(df,by=[df[&#39;Date_Time&#39;].date()])



Python Pandas Group by date using datetime data

    
I can&#39;t figure out the difference between Pandas `.aggregate` and `.apply` functions.    
Take the following as an example: I load a dataset, do a `groupby`, define a simple function,
and either user `.agg` or `.apply`.

As you may see, the printing statement within my function results in the same output
after using `.agg` and `.apply`. The result, on the other hand is different. Why is that?
    
    import pandas
    import pandas as pd
    iris = pd.read_csv(&#39;iris.csv&#39;)
    by_species = iris.groupby(&#39;Species&#39;)
    def f(x):
        ...:     print type(x)
        ...:     print x.head(3)
        ...:     return 1


Using `apply`:


    by_species.apply(f)
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #   Sepal.Length  Sepal.Width  Petal.Length  Petal.Width Species
    #0           5.1          3.5           1.4          0.2  setosa
    #1           4.9          3.0           1.4          0.2  setosa
    #2           4.7          3.2           1.3          0.2  setosa
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #   Sepal.Length  Sepal.Width  Petal.Length  Petal.Width Species
    #0           5.1          3.5           1.4          0.2  setosa
    #1           4.9          3.0           1.4          0.2  setosa
    #2           4.7          3.2           1.3          0.2  setosa
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #    Sepal.Length  Sepal.Width  Petal.Length  Petal.Width     Species
    #50           7.0          3.2           4.7          1.4  versicolor
    #51           6.4          3.2           4.5          1.5  versicolor
    #52           6.9          3.1           4.9          1.5  versicolor
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #     Sepal.Length  Sepal.Width  Petal.Length  Petal.Width    Species
    #100           6.3          3.3           6.0          2.5  virginica
    #101           5.8          2.7           5.1          1.9  virginica
    #102           7.1          3.0           5.9          2.1  virginica
    #Out[33]: 
    #Species
    #setosa        1
    #versicolor    1
    #virginica     1
    #dtype: int64

Using `agg`    
    
    by_species.agg(f)
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #   Sepal.Length  Sepal.Width  Petal.Length  Petal.Width Species
    #0           5.1          3.5           1.4          0.2  setosa
    #1           4.9          3.0           1.4          0.2  setosa
    #2           4.7          3.2           1.3          0.2  setosa
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #    Sepal.Length  Sepal.Width  Petal.Length  Petal.Width     Species
    #50           7.0          3.2           4.7          1.4  versicolor
    #51           6.4          3.2           4.5          1.5  versicolor
    #52           6.9          3.1           4.9          1.5  versicolor
    #&lt;class &#39;pandas.core.frame.DataFrame&#39;&gt;
    #     Sepal.Length  Sepal.Width  Petal.Length  Petal.Width    Species
    #100           6.3          3.3           6.0          2.5  virginica
    #101           5.8          2.7           5.1          1.9  virginica
    #102           7.1          3.0           5.9          2.1  virginica
    #Out[34]: 
    #           Sepal.Length  Sepal.Width  Petal.Length  Petal.Width
    #Species                                                         
    #setosa                 1            1             1            1
    #versicolor             1            1             1            1
    #virginica              1            1             1            1




What is the difference between pandas agg and apply function?

This should be an easy one, but somehow I couldn&#39;t find a solution that works.

I have a pandas dataframe which looks like this:

    index col1   col2   col3   col4   col5
    0     a      c      1      2      f 
    1     a      c      1      2      f
    2     a      d      1      2      f
    3     b      d      1      2      g
    4     b      e      1      2      g
    5     b      e      1      2      g

**I want to group by col1 and col2 and get the `sum()` of col3 and col4.** `col5` can be dropped since the data can not be aggregated.

Here is what the output should look like. I am interested in having both `col3` and `col4` in the resulting dataframe. It doesn&#39;t really matter if `col1` and `col2` are part of the index or not.

    index col1   col2   col3   col4   
    0     a      c      2      4          
    1     a      d      1      2      
    2     b      d      1      2      
    3     b      e      2      4      
      

Here is what I tried:

    df_new = df.groupby([&#39;col1&#39;, &#39;col2&#39;])[&#39;col3&#39;, &#39;col4&#39;].sum()

That however only returns the aggregated results of `col4`.

I am lost here. Every example I found only aggregates one column, where the issue obviously doesn&#39;t occur.



Pandas - dataframe groupby - how to get sum of multiple columns

I have a dataframe with panel data, let&#39;s say it&#39;s time series for 100 different objects:

    object  period  value 
    1       1       24
    1       2       67
    ...
    1       1000    56
    2       1       59
    2       2       46
    ...
    2       1000    64
    3       1       54
    ...
    100     1       451
    100     2       153
    ...
    100     1000    21

I want to add a new column `prev_value` that will store previous `value` for each object:

    object  period  value  prev_value
    1       1       24     nan
    1       2       67     24
    ...
    1       99      445    1243
    1       1000    56     445
    2       1       59     nan
    2       2       46     59
    ...
    2       1000    64     784
    3       1       54     nan
    ...
    100     1       451    nan
    100     2       153    451
    ...
    100     1000    21     1121

Can I use .shift() and .groupby() somehow to do that?

Use pandas.shift() within a group

I&#39;m having issues transitioning to pandas from R where `dplyr` package can easily group-by and perform multiple summarizations. 

Please help improve my existing Python pandas code for multiple aggregations:

    import pandas as pd
    data = pd.DataFrame(
        {&#39;col1&#39;:[1,1,1,1,1,2,2,2,2,2],
        &#39;col2&#39;:[1,2,3,4,5,6,7,8,9,0],
         &#39;col3&#39;:[-1,-2,-3,-4,-5,-6,-7,-8,-9,0]
        }
    )
    result = []
    for k,v in data.groupby(&#39;col1&#39;):
        result.append([k, max(v[&#39;col2&#39;]), min(v[&#39;col3&#39;])])
    print pd.DataFrame(result, columns=[&#39;col1&#39;, &#39;col2_agg&#39;, &#39;col3_agg&#39;])

Issues:

 - too verbose
 - probably can be optimized and efficient. (I rewrote a `for-loop groupby` implementation into `groupby.agg` and the performance enhancement was huge). 

In R the equivalent code would be:

    data %&gt;% groupby(col1) %&gt;% summarize(col2_agg=max(col2), col3_agg=min(col3))


-------------
UPDATE: @ayhan solved my question, here is a follow-up question that I will post here instead of as comment: 

Q2) What is the equivalent of `groupby().summarize(newcolumn=max(col2 * col3))` i.e. an aggregation/summarization where the function is a compound function of 2+ columns? 

What is the pandas equivalent of dplyr summarize/aggregate by multiple functions?

I have the following dataframe:

&lt;!-- begin snippet: js hide: false console: true babel: false --&gt;
&lt;!-- language: python --&gt;

       key1  key2
    0    a   one
    1    a   two
    2    b   one
    3    b   two
    4    a   one
    5    c   two
&lt;!-- end snippet --&gt;

Now, I want to group the dataframe by the `key1` and count the column `key2` with the value `&quot;one&quot;` to get this result:

&lt;!-- language: python --&gt;

       key1  
    0    a   2
    1    b   1
    2    c   0
&lt;!-- end snippet --&gt;

I just get the usual count with:

&lt;!-- begin snippet: js hide: false console: true babel: false --&gt;

&lt;!-- language: python --&gt;

    df.groupby([&#39;key1&#39;]).size()

&lt;!-- end snippet --&gt;

But I don&#39;t know how to insert the condition.

I tried things like this:

&lt;!-- begin snippet: js hide: false console: true babel: false --&gt;

&lt;!-- language: python --&gt;

    df.groupby([&#39;key1&#39;]).apply(df[df[&#39;key2&#39;] == &#39;one&#39;])

&lt;!-- end snippet --&gt;

But I can&#39;t get any further.  How can I do this?

All Pandas Groupby Solutions on Gang of Coders

Total of 37 Pandas Groupby Solutions