Groupby + Conditional From Another Column To Create New One

I am trying to capture the date of the “visit_num==2” of “users” in a new column ('2nd_visit_date') Here's the code (including the new column I want to create) df=pd.DataF

Solution 1:

Let say this is your original df:

dfuserdatevisit_num011995-09-01  1111995-09-02  2221995-10-03  1321995-10-04  2421995-10-05  3531995-11-07  1631995-11-08  2731995-11-09  3831995-11-10  4931995-11-15  51041995-12-18  11141995-12-20  2

You can first create a dataframe for second visits (and change column name):

df_2 = df[df.visit_num==2][['user', 'date']]
df_2.columns = ['user', '2nd_visit_date']
df_2


   user 2nd_visit_date
111995-09-02321995-10-04631995-11-081141995-12-20

And merge it with your original df

pd.merge(df,df_2,on='user',how='left')userdatevisit_num2nd_visit_date011995-09-01      11995-09-02111995-09-02      21995-09-02221995-10-03      11995-10-04321995-10-04      21995-10-04421995-10-05      31995-10-04531995-11-07      11995-11-08631995-11-08      21995-11-08731995-11-09      31995-11-08831995-11-10      41995-11-08931995-11-15      51995-11-081041995-12-18      11995-12-201141995-12-20      21995-12-20