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?
9 Answers
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 Also you can use agg function,
df.groupby(['Name', 'Fruit'])['Number'].agg('sum') 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 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 df.groupby(['Fruit','Name'])['Number'].sum() You can select different columns to sum numbers.
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"}) 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 You could also use transform() on column Number after group by. This operation will calculate the total number in one group with function sum, the result is a series with the same index as original dataframe.
df['Number'] = df.groupby(['Fruit', 'Name'])['Number'].transform('sum') df = df.drop_duplicates(subset=['Fruit', 'Name']).drop('Date', 1) Then, you can drop the duplicate rows on column Fruit and Name. Moreover, you can drop the column Date by specifying axis 1 (0 for rows and 1 for columns).
# print(df) Fruit Name Number 0 Apples Bob 16 2 Apples Mike 9 3 Apples Steve 10 5 Oranges Bob 67 6 Oranges Tom 15 7 Oranges Mike 57 9 Oranges Tony 1 10 Grapes Bob 35 11 Grapes Tom 87 14 Grapes Tony 15 # You could achieve the same result with functions discussed by others: # print(df.groupby(['Fruit', 'Name'], as_index=False)['Number'].sum()) # print(df.groupby(['Fruit', 'Name'], as_index=False)['Number'].agg('sum')) There is an official tutorial Group by: split-apply-combine talking about what you can do after group by.
1You can use dfsql
for your problem, it will look something like:
df.sql('SELECT fruit, sum(number) GROUP BY fruit') here is an article about it:
1
