Summarise data over several rows in pandas data frame

Question

I have a data frame that takes this form:

import pandas as pd
dict = {'id':["1001", "1001", "1001", "1002", "1002", "1002", "1003", "1003", "1003"], 
    'food': ["apple", "ham", "egg", "apple", "pear", "cherry", "cheese", "milk", "cereal"], 
    'fruit':[1, 0, 0, 1, 1, 1, 0, 0, 0],
    'score':[1, 3, 1, 1, 1, 1, 2, 2, 3]} 
df = pd.DataFrame(dict) 

    id      food    fruit   score
0   1001    apple   1       1
1   1001    ham     0       0
2   1001    egg     0       0
3   1002    apple   1       1
4   1002    pear    1       2
5   1002    cherry  1       3
6   1003    cheese  0       0
7   1003    cherry  1       3
8   1003    cheese  0       0

I would like to create a new data frame that has one row for a single participant (i.e., same id) and then columns for custom summaries of the data, e.g.:

number of unique foods
number of total fruits
total score
etc.

Example output:

      id    unique  fruits  score
0   1001    3       1       1
1   1002    3       3       6
2   1003    2       1       3

I could create a new empty data frame and then iterate over the unique id's in the old data frame, using logical indexing to fill the columns. But my data frame has about 50x10^6 rows and ~200,000 unique id's so this would take extremely long. I have read that iterating over the rows of a data frame is inefficient, but I don't know how to apply alternative solutions to my dataset.

Thanks.

`df.groupby('id').agg({'fruit':'sum','score':'sum','food':'nunique'}) ` — BENY, Oct 29 '19 at 15:45

score 2 · Answer 1 · answered Oct 29 '19 at 15:45

How about groupby().agg():

df.groupby('id', as_index=False).agg({'food':'nunique',
                      'fruit':'sum',
                     'score':'sum'})

Output:

     id  food  fruit  score
0  1001     3      1      1
1  1002     3      3      6
2  1003     2      1      3

score 0 · Accepted Answer · answered Oct 29 '19 at 16:23

Since pandas >= 0.25.0 we having named aggregations for this, where we can aggregate and at the same time give our columns a more informative name, since we aggregate:

So in this example we can make the column unique in one go.

df.groupby('id').agg(
    unique=('food', 'nunique'),
    fruits=('fruit', 'sum'),
    score=('score', 'sum')
).reset_index()

     id  unique  fruits  score
0  1001       3       1      1
1  1002       3       3      6
2  1003       2       1      3

Summarise data over several rows in pandas data frame

2 Answers2