CODEWITHSUNDEEP
pythonpython-2.7pandas
Muhammad Waleed
Muhammad Waleed

Reputation: 92

How to add new column on the basis existing columns

I have dataframe like this

   id1         id2          Entry_Date
0  5344265358   55111095504 2018-07-13 00:00:00
1  5344265358   55111095504 2018-07-13 00:00:00
2  5344265358   55111095501 2018-08-13 00:00:00
3  5344265358   55111095502 2018-07-14 00:00:00
4  5441440119   55111366675 2018-08-13 00:00:00
5  5441440119   55111366676 2018-08-20 00:00:00
6  5441440119   55111366677 2018-09-21 00:00:00
7  5441440119   55111366677 2018-09-21 00:00:00

I want to add a new column based on existing column

I want to groupby by id1 and id2 then sort on the basis of date and then asign date of next group to previous group but as you can see data has repetitions

     id1           id2          Entry_Date          Next_Date
0  5344265358   55111095504 2018-07-13 00:00:00 2018-07-14 00:00:00
1   5344265358  55111095504 2018-07-13 00:00:00 2018-07-14 00:00:00
2  5344265358   55111095501 2018-08-13 00:00:00 NAN
3  5344265358   55111095502 2018-07-14 00:00:00 2018-08-13 00:00:00 
4  5441440119   55111366675 2018-08-13 00:00:00 2018-08-20 00:00:00
5  5441440119   55111366676 2018-08-20 00:00:00 2018-09-21 00:00:00
6  5441440119   55111366677 2018-09-21 00:00:00 NAN
7  5441440119   55111366677 2018-09-21 00:00:00 NAN

Upvotes: 1

Views: 63

Answers (1)

jezrael
jezrael

Reputation: 862661

I think need:

df['Entry_Date'] = pd.to_datetime(df['Entry_Date'])

df1 = (df.sort_values('Entry_Date')
         .drop_duplicates(['id1','id2'])
        .rename(columns={'Entry_Date':'Next_Date'}))
df1['Next_Date'] = df1['Next_Date'].shift(-1)
print (df1)
          id1          id2  Next_Date
0  5344265358  55111095504 2018-07-14
3  5344265358  55111095502 2018-08-13
2  5344265358  55111095501 2018-08-13
4  5441440119  55111366675 2018-08-20
5  5441440119  55111366676 2018-09-21
6  5441440119  55111366677        NaT

df = df.merge(df1, on=['id1','id2'], how='left')
print (df)
          id1          id2 Entry_Date  Next_Date
0  5344265358  55111095504 2018-07-13 2018-07-14
1  5344265358  55111095504 2018-07-13 2018-07-14
2  5344265358  55111095501 2018-08-13 2018-08-13
3  5344265358  55111095502 2018-07-14 2018-08-13
4  5441440119  55111366675 2018-08-13 2018-08-20
5  5441440119  55111366676 2018-08-20 2018-09-21
6  5441440119  55111366677 2018-09-21        NaT
7  5441440119  55111366677 2018-09-21        NaT

Upvotes: 1

Related Questions