CODEWITHSUNDEEP
pythonpandasjupyter-notebook
Alex5207
Alex5207

Reputation: 484

Python pandas last and first date of specific key in dataframe

I have a dataframe with two columns: An object column with duplicate records and a date column. What I need is a new dataframe containing the unique objects with the first and last date from the first dataframe

Is there a neat way to filter the original dataframe, create a new one and then do lookup or how would one approach this?

Best regards, Alex

EDIT:

Very easily solves with a pivot table with the object as index, date as values and aggfunc=max

Upvotes: 0

Views: 2408

Answers (1)

jezrael
jezrael

Reputation: 862781

It seems you need groupby with aggregate by DataFrameGroupBy.agg with GroupBy.first and GroupBy.last:

rng = pd.date_range('2017-04-03', periods=10)
df = pd.DataFrame({'Date': rng, 'a': list('aaabbbcccc')})  
print (df)
        Date  a
0 2017-04-03  a
1 2017-04-04  a
2 2017-04-05  a
3 2017-04-06  b
4 2017-04-07  b
5 2017-04-08  b
6 2017-04-09  c
7 2017-04-10  c
8 2017-04-11  c
9 2017-04-12  c

df1 = df.groupby('a')['Date'].agg(['first','last']).reset_index()
print (df1)
   a      first       last
0  a 2017-04-03 2017-04-05
1  b 2017-04-06 2017-04-08
2  c 2017-04-09 2017-04-12

Upvotes: 1

Related Questions