Using Pandas to conditionally remove row and fill value

user_9161607 0
Viewed 76

my df looks like this:

student_id   completed  start_enroll   end_enroll     completed_date
1001         N          2013-09-03     2017-02-27     NaT
1001         Y          NaT            NaT            2018-02-26
1002         N          2014-09-02     2020-05-30     NaT
1003         N          2008-08-25     2012-08-10     NaT
1003         Y          NaT            NaT            2012-08-10
.....

Please note:

  • some student_id are repeated:
    • when it is repeated, completed is either Y or N for each
    • in case of completed = Y, there is no value for start_enroll and end_enroll
    • in case of completed = N, there is no value for completed_date
    • there will always be only at most 2 rows with same student_id and each row will be Y and N respectively
    • for student_id with only one row, completed will always be N and we do not need to do anything

What I am looking for is this:

  • if student_id is repeated, then:
    • remove the completed = N row but copy the start_enroll and end_enroll from the completed = N row to the row where completed = Y

The new DF should look like this:

student_id   completed  start_enroll   end_enroll     completed_date
1001         Y          2013-09-03     2017-02-27     2018-02-26
1002         N          2014-09-02     2020-05-30     NaT
1003         Y          2008-08-25     2012-08-10     2012-08-10
.....
  • if student_id is not repeated and completed = N then we do not need to do anything

could someone please provide me some steps. Thank you!

pandas numpy
3 Answers

You can filter the df into 2 dfs, one with just the student ID and non-null completed dates, the other without those rows. Then merge on student ID.

df = pd.DataFrame({'student_id':[1001, 1001, 1002, 1003, 1003], 'completed':['N', 'Y', 'N', 'N', 'Y'],
                   'start_enroll':['2013-09-03', np.nan, '2014-09-02', '2008-08-25', np.nan],
                   'end_enroll':['2017-02-27', np.nan, '2020-05-30', '2012-08-10', np.nan],
                   'completed_date': [np.nan, '2018-02-26', np.nan, np.nan, '202-08-10']
                   })

df_comp = df[df['completed_date'].notnull()]
df_comp = df_comp[['student_id', 'completed_date']]  # keep only these 2 columns

df = df[df['completed_date'].isnull()]  # keep only the rows with null completed dates
del df['completed_date']        # delete col from original df, all non-null values are in df_comp


df = df.merge(df_comp, on='student_id', how='outer')
print(df)
#    student_id completed start_enroll  end_enroll completed_date
# 0        1001         N   2013-09-03  2017-02-27     2018-02-26
# 1        1002         N   2014-09-02  2020-05-30            NaN
# 2        1003         N   2008-08-25  2012-08-10      202-08-10
user_163178920

Try with

out = df.sort_values('completed').groupby('student_id',as_index=False).last()
   student_id completed start_enroll end_enroll completed_date
0        1001         Y   2013-09-03 2017-02-27     2018-02-26
1        1002         N   2014-09-02 2020-05-30            NaT
2        1003         Y   2008-08-25 2012-08-10     2012-08-10
user_79645270

Try this:

df.groupby('student_id').apply(lambda x: x.ffill()).drop_duplicates('student_id', keep='last')

Output:

   student_id completed start_enroll  end_enroll completed_date
1        1001         Y   2013-09-03  2017-02-27     2018-02-26
2        1002         N   2014-09-02  2020-05-30            NaN
4        1003         Y   2008-08-25  2012-08-10     2012-08-10
user_63615310
Related