I have this dataset:
df=pd.DataFrame({'user':[1,1,2,2,2,3,3,3,3,3,4,4],
'date':['1995-09-01','1995-09-02','1995-10-03','1995-10-04','1995-10-05','1995-11-07','1995-11-08','1995-11-09','1995-11-10','1995-11-15','1995-12-18','1995-12-20'],
'type':['a','b','a','x','b','a','x','a','x','b','a','b']})
Which gives me:
user date type
1 1995-09-01 a
1 1995-09-02 b
2 1995-10-03 a
2 1995-10-04 x
2 1995-10-05 b
3 1995-11-07 a
3 1995-11-08 x
3 1995-11-09 a
3 1995-11-10 x
3 1995-11-15 b
4 1995-12-18 a
4 1995-12-20 b
and I want to get a new column, with dates based on a values. When a user has more than an value type a value , the date of the oldest a value of this user should be selected to show on the new column.
For example, user 3 has several a values on the type column. Thus, on the a_type_date column, the eldest date for the a value is chosen.
Here is the desired output:
user date type a_type_date
1 1995-09-01 a 1995-09-01
1 1995-09-02 b 1995-09-01
2 1995-10-03 a 1995-10-03
2 1995-10-04 x 1995-10-03
2 1995-10-05 b 1995-10-03
3 1995-11-07 a 1995-11-07
3 1995-11-08 x 1995-11-07
3 1995-11-09 a 1995-11-07
3 1995-11-10 x 1995-11-07
3 1995-11-15 b 1995-11-07
4 1995-12-18 a 1995-12-18
4 1995-12-20 b 1995-12-18
Any help will be highly appreciated.