I have a dataframe named emails_visits:
Rep Doctor Date type date_after
0 1 1 2021-01-25 email NaT
1 1 1 2021-05-29 email NaT
2 1 2 2021-03-15 email NaT
3 1 2 2021-04-02 email NaT
4 1 2 2021-04-29 email NaT
30 1 2 2021-06-01 visit 2021-06-01
5 1 3 2021-01-01 email NaT
6 1 3 2021-01-10 email NaT
7 1 3 2021-01-25 email NaT
8 1 3 2021-02-19 email NaT
9 1 4 2021-03-03 email NaT
I need to fill in column date_afterfrom bottom to top based on values in column Rep and Doctor.
if values row(x-1) Rep and Doctor ==values row(x) Rep and Doctor == then date_afterin row(x-1) should be == todate_afterin row(x)
example of desired result below
Rep Doctor Date type date_after
0 1 1 2021-01-25 email NaT
1 1 1 2021-05-29 email NaT
2 1 2 2021-03-15 email 2021-06-01
3 1 2 2021-04-02 email 2021-06-01
4 1 2 2021-04-29 email 2021-06-01
30 1 2 2021-06-01 visit 2021-06-01
5 1 3 2021-01-01 email NaT
6 1 3 2021-01-10 email NaT
7 1 3 2021-01-25 email NaT
8 1 3 2021-02-19 email NaT
9 1 4 2021-03-03 email NaT
>Solution :
Use groupby_bfill:
df['date_after'] = df.groupby(['Rep', 'Doctor'])['date_after'].bfill()
print(df)
# Output
Rep Doctor Date type date_after
0 1 1 2021-01-25 email NaT
1 1 1 2021-05-29 email NaT
2 1 2 2021-03-15 email 2021-06-01
3 1 2 2021-04-02 email 2021-06-01
4 1 2 2021-04-29 email 2021-06-01
30 1 2 2021-06-01 visit 2021-06-01
5 1 3 2021-01-01 email NaT
6 1 3 2021-01-10 email NaT
7 1 3 2021-01-25 email NaT
8 1 3 2021-02-19 email NaT
9 1 4 2021-03-03 email NaT