Is there a way to get the number of occurrences of the last value in a groupby?

user_13204346 0
Viewed 112

Here is a sample dataframe -

df = pd.DataFrame({'ID': ['a1', 'a1', 'a1', 'a1', 'b2', 'b2', 'b2'],
                   'Price': [15, 12, 10, 10, 36, 34, 36]})

  ID  Price 
0 a1  15
1 a1  12
2 a1  10
3 a1  10
4 b2  36
5 b2  34
6 b2  36

Here is the expected output -

df.groupby('ID').agg({'Price': ['last', 'last_count']})
ID  Price_last Price_last_count
a1  10         2
b2  36         2

I need to be able to perform the 'last_count' operation in the agg.

pandas-groupby pandas python
3 Answers
df.groupby('ID')['Price'].agg(lastvalue = 'last', 
                              count = lambda x: sum(x==x.iloc[-1]) )


    lastvalue  count
ID                  
a1         10      2
b2         36      2

Edit to get OP exact format (by Scott Boston):

df.groupby('ID', as_index=False)\
  .agg(Price_last= ('Price' , 'last'), 
       Price_last_count=('Price' , lambda x: sum(x==x.iloc[-1])))

Output:

   ID  Price_last  Price_last_count
0  a1          10                 2
1  b2          36                 2
user_15689190

First find groupby ID and find the tail.

Do an inner merge with df

groupby ['ID','Price'] of the resultant frame and count the Prices

Code below:

  pd.merge(df.groupby('ID').tail(1),df, how='inner').groupby(['ID','Price'])['Price'].agg('count')

ID  Price
a1  10       2
b2  36       2
user_89869750

I wrote this answer to make the solution as didactic as possible. In summary, you get the last price of each group and count how many times the price column is equal to the last observed price for each group.

# Get the last value of each group
temp = df.groupby('ID').last()
# Merge df with this result
df = df.merge(temp, left_on='ID', right_index=True, how='inner')
# Declare column that evaluates if Price is equal to the last Price
df['count'] = df['Price_x'] == df['Price_y']
# Count times when Price equals Last price
df.groupby('ID')['count'].sum().to_frame()
user_97958170
Related