CODEWITHSUNDEEP
pythonpandasdatedataframe
lpt
lpt

Reputation: 975

Adding groupby min / max / count aggregations

I have following pandas dataframe. 

 ex_one  ex_two weight  fake_date
0  228055  231908      1 2004-12-17
1  228056  228899      1 2000-02-26
2  228050  230029      1 2003-01-27
3  228055  230564      1 2001-07-25
4  228059  230548      1 2002-05-04

Here is what I want:

from column ex_one take for an example 228055 and then count number of occurrences based on fake_date (max) and fake_date(min) value for 228055

 ex_one  ex_two weight  fake_date      max_date     min_date   frequency             
0  228055  231908      1 2004-12-17   2004-12-17   2001-07-25  2
1  228056  228899      1 2000-02-26
2  228050  230029      1 2003-01-27
3  228055  230564      1 2001-07-25
4  228059  230548      1 2002-05-04

Upvotes: 3

Views: 3606

Answers (1)

jpp
jpp

Reputation: 164843

This is one way.

df['fake_date'] = pd.to_datetime(df['fake_date'])

g = df.groupby('ex_one')['fake_date'].agg(['min', 'max', 'count']).reset_index()

res = df.merge(g, how='left')

Result

   ex_one  ex_two  weight  fake_date        min        max  count
0  228055  231908       1 2004-12-17 2004-12-17 2004-12-17      1
1  228056  228899       1 2000-02-26 2000-02-26 2000-02-26      1
2  228050  230029       1 2003-01-27 2003-01-27 2003-01-27      1
3  228059  230564       1 2001-07-25 2001-07-25 2002-05-04      2
4  228059  230548       1 2002-05-04 2001-07-25 2002-05-04      2

Explanation

  • Caste fake_date column as datetime type if not already done.
  • Create a groupby dataframe with your 3 aggregations.
  • Left merge with original dataframe.

Upvotes: 7

Related Questions