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 this:
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
I tried following this post, but I would get duplicates.
Any help will be highly appreciated. Thanks.
You may try the following steps:
1. First, convert the date
column into a datetime()
index:
df.date = pd.to_datetime(df.date)
2. Next use min()
and list comprehension to create a new column containing your earliest (use max()
for latest) type a dates per user as follows:
df['a_type_date'] = [min(df[(df.user==i)&(df.type=='a')]['date']).date() for i in df.user]
The above will generate the desired output as stated in your original query.