Pandas group-by and sum

Question

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   Bob Apples 16   for example     I tried grouping by Name and Fruit but how do I get the total number of fruit

User · Answer

Both the other answers accomplish what you want.

You can use the pivot functionality to arrange the data in a nice table

df.groupby(['Fruit','Name'],as_index = False).sum().pivot('Fruit','Name').fillna(0)



Name    Bob     Mike    Steve   Tom    Tony
Fruit                   
Apples  16.0    9.0     10.0    0.0     0.0
Grapes  35.0    0.0     0.0     87.0    15.0
Oranges 67.0    57.0    0.0     15.0    1.0

User · Answer

If you want to keep the original columns Fruit and Name  use reset index    Otherwise Fruit and Name will become part of the index    df groupby   Fruit   Name     Number   sum   reset index    Fruit   Name       Number Apples  Bob        16 Apples  Mike        9 Apples  Steve      10 Grapes  Bob        35 Grapes  Tom        87 Grapes  Tony       15 Oranges Bob        67 Oranges Mike       57 Oranges Tom        15 Oranges Tony        1   As seen in the other answers   df groupby   Fruit   Name     Number   sum                   Number Fruit   Name          Apples  Bob        16         Mike        9         Steve      10 Grapes  Bob        35         Tom        87         Tony       15 Oranges Bob        67         Mike       57         Tom        15         Tony        1

User · Answer

Use GroupBy sum   df groupby   Fruit   Name    sum    Out 31                   Number Fruit   Name          Apples  Bob        16         Mike        9         Steve      10 Grapes  Bob        35         Tom        87         Tony       15 Oranges Bob        67         Mike       57         Tom        15         Tony        1

User · Answer

You can set the groupby column to  index  then using sum with level   df set index   Fruit   Name    sum level  0 1   Out 175                   Number Fruit   Name          Apples  Bob        16         Mike        9         Steve      10 Oranges Bob        67         Tom        15         Mike       57         Tony        1 Grapes  Bob        35         Tom        87         Tony       15

User · Answer

Also you can use agg function   df groupby   Name    Fruit     Number   agg  sum

User · Answer

df groupby   Fruit   Name     Number   sum     You can select different columns to sum numbers

User · Answer

A variation on the  agg   function  provides the ability to  1  persist type DataFrame   2  apply averages  counts  summations  etc  and  3  enables groupby on multiple columns while maintaining legibility    df groupby   att1    att2    agg   att1    count    att3    sum   att4    mean      using your values      df groupby   Name    Fruit    agg   Number    sum

[python] Pandas group-by and sum

Examples related to python

Examples related to pandas

Examples related to dataframe

Examples related to group-by

Examples related to aggregate