Apply multiple functions to GroupBy object in a specific order

user_6224975 0
Viewed 89

I have a dataframe df with at column date which consists of date.

If I want to calculate the maximum difference between the dates within each group, is that doable (without having to re-group and without using .apply)? If I do


df

id | date |
---+-------
1  | 2020-01-20
1  | 2020-01-25
2  | 2020-02-03
2  | 2020-02-04

max_diff_for_each_id = df.groupby("id").diff(1).max()
max_diff_for_each_id 

id
--
1  5

that of course give the maximum difference between all groups, where I want

id 
--
1  5
2  1

I know I can just re-group max_diff_for_each_id but I think that

max_diff_for_each_id = df.groupby("id").diff(1).groupby("id").max()

is not really "pretty" and say you have multiple functions to apply, there is a ton of overhead by having to re-group all the time

pandas-groupby pandas python
3 Answers

is that doable (without having to re-group and without using .apply)

I think generally not, if only 2 values per groups or some another patterns of data there should be alternatives.

#if always 2 values per id in order
df1 = df.groupby("id")['date'].agg(['min','max'])
max_diff_for_each_id = df1['max'].sub(df1['min']).dt.days

Or:

#if always 2 values per id 
df2 = df.groupby("id")['date'].agg(['first','last'])

max_diff_for_each_id = df2['last'].sub(df2['first']).dt.days

One idea with convert id to index, but max(level=0) is only hidden .groupby(level=0).max(), so this should be trick solution (in my opinion)

max_diff_for_each_id = df.set_index('id').groupby("id")['date'].diff().max(level=0).dt.days

There is possible multiple groupby like:

max_diff_for_each_id = df.groupby("id")['date'].diff(1).groupby(df["id"]).max().dt.days

Or create custom functions like:

max_diff_for_each_id = df.groupby("id")['date'].apply(lambda x: x.diff().max()).dt.days

max_diff_for_each_id = df.groupby("id")['date'].agg(lambda x: x.diff().max()).dt.days

print (max_diff_for_each_id)
id
1    5
2    1
dtype: int64
user_29010020

One way using numpy.ptp:

df.groupby("id").apply(np.ptp)

Output:

id
1   5 days
2   1 days
dtype: timedelta64[ns]

Works fine with any number of values per each group

# print(df)
   id       date
0   1 2020-01-20
1   1 2020-01-25
2   1 2020-01-21
3   2 2020-02-03
4   2 2020-02-04
5   2 2020-02-06
6   2 2020-02-10

df.groupby("id").apply(np.ptp)

Output:

id
1   5 days
2   7 days
dtype: timedelta64[ns]
user_70937410

IIUC, you could do:

groups = df.groupby('id')
result = groups['date'].max() - groups['date'].min()
print(result)

Output

id
1   5 days
2   1 days
Name: date, dtype: timedelta64[ns]

An alternative version:

frame = df.groupby('id').agg({'date': ['max', 'min']})
result = frame[('date', 'max')] - frame[('date', 'min')]
print(result)

Output

id
1   5 days
2   1 days
dtype: timedelta64[ns]
user_40015920
Related