How to find the row value of other column(column2), if my row value isin same column(column1) in other row

Question

I have DataFrame like this:

month       CPT
5/1/2017    aa
5/1/2017    bb
5/1/2017    cc
6/1/2017    aa
6/1/2017    cc
6/1/2017    dd
7/1/2017    aa
7/1/2017    bb
7/1/2017    cc

I want new column(old_bill) where in I need column(month) values after having row value of column(CPT) in early occurrence in the same column(CPT). Expected dataframe is:

period      CPT old_bill
5/1/2017    aa  nan
5/1/2017    bb  nan
5/1/2017    cc  nan
6/1/2017    aa  5/1/2017
6/1/2017    cc  5/1/2017
6/1/2017    dd  nan
7/1/2017    aa  6/1/2017
7/1/2017    bb  5/1/2017
7/1/2017    cc  6/1/2017

I have tried with .isin method. But I didn't get show-able results.

Answer 1

I believe you need DataFrameGroupBy.shift - only necessary consecutive datetimes per groups:

df['old_bill'] = df.groupby('CPT')['month'].shift()
print (df)
      month CPT  old_bill
0  5/1/2017  aa       NaN
1  5/1/2017  bb       NaN
2  5/1/2017  cc       NaN
3  6/1/2017  aa  5/1/2017
4  6/1/2017  cc  5/1/2017
5  6/1/2017  dd       NaN
6  7/1/2017  aa  6/1/2017
7  7/1/2017  bb  5/1/2017
8  7/1/2017  cc  6/1/2017