CODEWITHSUNDEEP
pythonpandaspandas-groupby
LeCoconutWhisperer
LeCoconutWhisperer

Reputation: 319

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

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.

Upvotes: 5

Views: 153

Answers (3)

jf328
jf328

Reputation: 7351

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

Upvotes: 7

Arturo Sbr
Arturo Sbr

Reputation: 6333

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()

Upvotes: 0

wwnde
wwnde

Reputation: 26676

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

Upvotes: 3

Related Questions