Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 864
LeCoconutWhisperer Avatar asked Feb 25 '21 02:02

LeCoconutWhisperer


2 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
like image 189
jf328 Avatar answered Oct 09 '22 06:10

jf328


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
like image 39
wwnde Avatar answered Oct 09 '22 06:10

wwnde