panda aggregate by functions

Question

I have data like below:

id  movie   details value
5   cane1   good    6
5   wind2   ok  30.3
5   wind1   ok  18
5   cane1   good    2
5   cane22  ok  4
5   cane34  good    7
5   wind2   ok  2

I want the output with below criteria:

If movie name starts with 'cane' - sum the value

If movie name starts with 'wind' - count the occurrence.

So - the final output will be:

id  movie   value
5   cane1   8
5   cane22  4
5   cane34  7
5   wind1   1
5   wind2   2

I tried to use:

movie_df.groupby(['id']).apply(aggr)

def aggr(x):
    if x['movie'].str.startswith('cane'):
        y = x.groupby(['value']).sum()

    else:
         y = x.groupby(['movie']).count()

    return y

But It's not working. Can anyone please help?

I see value = 19 for `cane`, i.e. 6 + 2 + 4 + 7. – jpp Aug 24 '18 at 14:39 — jpp, Aug 24 '18 at 14:39

jpp · Accepted Answer · 2018-08-24T23:20:39.997

2

You should aim for vectorised operations where possible.

You can calculate 2 results and then concatenate them.

mask = df['movie'].str.startswith('cane')

df1 = df[mask].groupby('movie')['value'].sum()
df2 = df[~mask].groupby('movie').size()

res = pd.concat([df1, df2], ignore_index=0)\
        .rename('value').reset_index()

print(res)

    movie  value
0   cane1    8.0
1  cane22    4.0
2  cane34    7.0
3   wind1    1.0
4   wind2    2.0

edited Aug 24 '18 at 23:20

answered Aug 24 '18 at 14:38

jpp

159,742
34
281
339

Thank you for your reply! However- I realized in my original question I did not provide the output correctly. I have edited the question slightly. Can you please take a look? Basically-I need all of the movies in output(i.e- cane1, cane22, cane34, wind1,wind2 etc.) – Jadu Sen Aug 24 '18 at 23:05
Thank you! Now I just need the id column as I have to group by the id as well. Really appreciated! – Jadu Sen Aug 24 '18 at 23:36
You haven't identified the logic for `id` in your question. Either use `groupby(['id', 'movie'])` or use `first` as in @Retiefasaurus' answer. – jpp Aug 24 '18 at 23:45

score 2 · Answer 2 · answered Aug 24 '18 at 14:55

2

There might be multiple ways of doing this. One way would to filter by the start of movie name first and then aggregate and merge afterwards.

cane = movie_df[movie_df['movie'].str.startswith('cane1')]
wind = movie_df[movie_df['movie'].str.startswith('wind')]

cane_sum = cane.groupby(['id']).agg({'movie':'first', 'value':'sum'}).reset_index()
wind_count = wind.groupby(['id']).agg({'movie':'first', 'value':'count'}).reset_index()

pd.concat([cane_sum, wind_count])

answered Aug 24 '18 at 14:55

Retiefasaurus

96
3

Thank you for your reply! However- I realized in my original question I did not provide the output correctly. I have edited the question slightly. Can you please take a look? Basically-I need all of the movies in output(i.e- cane1, cane22, cane34, wind1,wind2 etc.) – Jadu Sen Aug 24 '18 at 23:07

JR ibkr · Answer 3 · 2018-08-24T14:32:12.177

0

First of all, you need to perform string operation. I guess in your case you don't want digits in a movie name. Use solution discussed at pandas applying regex to replace values. Then you call groupby() on new series.

FYI: Some movie names have digits only; in that case, you need to use update function. https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.update.html

edited Aug 24 '18 at 14:32

answered Aug 24 '18 at 14:26

JR ibkr

869
7
24

score 0 · Answer 4 · answered Aug 24 '18 at 15:13

I would start by creating a column which defines the required groups. For the example at hand this can be done with

df['group'] = df.movie.transform(lambda x : x[:4])

The next step would be to group by this column

df.groupby('group').apply(agg_fun)

using the following aggregation function

def agg_fun(grp):
    if grp.name == "cane":
        value=grp.value.sum()
    else:
        value=grp.value.count()
    return value

The output of this code is

group
cane    19.0
wind     3.0

panda aggregate by functions

4 Answers4