return first date in pandas groupby

user_13735281 0
Viewed 332

I try to create a new feature that shows the minimum (lowest) data of a given group.

My data looks as follows:

animal_id cycle_nr feed_date start_date_cycle
1 A 2021-02-06 2020-09-20
1 A 2021-02-10 2020-09-20
1 B 2021-02-14 2020-09-20
2 A 2021-02-20 2020-09-30
2 A 2021-02-21 2020-09-30

For every animal_id and cycle_nr, I want to return the first feed_date.

My desired df would look like this

animal_id cycle_nr feed_date start_date_cycle first_feed_date
1 A 2021-02-06 2020-09-20 2021-02-06
1 A 2021-02-10 2020-09-20 2021-02-06
1 B 2021-02-14 2020-09-20 2021-02-14
2 A 2021-02-20 2020-09-30 2021-02-20
2 A 2021-02-21 2020-09-30 2021-02-20

I have tried multiple things, but none of these options return the correct value:

df['first_feed_date'] = df.groupby(['animal_id', 'cycle_nr']).agg({'feed_date' : np.min})
df['first_feed_date'] = df.loc[df.groupby(['animal_id', 'cycle_nr'])['feed_date'].idxmin()]
df['first_feed_date']= df.groupby(["animal_id", "cycle_nr"])["feed_date"].transform(min)
df['first_feed_date'] = df.groupby(["animal_id", "cycle_nr"])["feed_date"](['first'])

Who can help me out?

pandas-groupby pandas python
4 Answers

Try:

Firstly convert 'feed_date' and 'start_date_cycle' columns to datetime:

df['feed_date']=pd.to_datetime(df['feed_date'])
df['start_date_cycle']=pd.to_datetime(df['start_date_cycle'])

Finally use groupby() and transform():

df['first_feed_date']=df.groupby(["animal_id", "cycle_nr"])['feed_date'].transform('first')

output of df:

  animal_id     cycle_nr        feed_date   start_date_cycle    first_feed_date
0   1           A               2021-02-06  2020-09-20          2021-02-06
1   1           A               2021-02-10  2020-09-20          2021-02-06
2   1           B               2021-02-14  2020-09-20          2021-02-14
3   2           A               2021-02-20  2020-09-30          2021-02-20
4   2           A               2021-02-21  2020-09-30          2021-02-20
user_142898920

I usually using drop_duplicates then merge

out = df.merge(df.sort_values('feed_date').drop_duplicates(['animal_id','cycle_nr']).drop('start_date_cycle',1).rename(columns={'feed_date':'1st_feed_date'}))
Out[33]: 
   animal_id cycle_nr  feed_date start_date_cycle 1st_feed_date
0          1        A 2021-02-06       2020-09-20    2021-02-06
1          1        A 2021-02-10       2020-09-20    2021-02-06
2          1        B 2021-02-14       2020-09-20    2021-02-14
3          2        A 2021-02-20       2020-09-30    2021-02-20
4          2        A 2021-02-21       2020-09-30    2021-02-20
user_79645270

Try something like this:

df1.groupby(['State'])['Sales'].min()
user_164411690
user_118784720
Related