Creating a new column based on conditions of first and last value of groupby Python

user_11918314 0
Viewed 57

I have a pandas dataframe and would like to create a new column with values based on conditions from the first and last row of a groupby. The conditions required are

mgr to mgr = hired as mgr

emp to mgr = promoted to mgr

emp to emp = hired as emp

mgr to emp = status change

date        email          level 
01/01/2000  john@abc.com   mgr
05/06/2000  john@abc.com   mgr     
10/01/2001  john@abc.com   mgr     
14/02/2000  kimdo@abc.com  emp     
19/10/2001  kimdo@abc.com  mgr     
12/05/2000  waint@abc.com  emp  
08/08/2000  waint@abc.com  emp  
14/04/2001  waint@abc.com  emp     
22/05/2000  neds@abc.com   mgr
08/11/2000  neds@abc.com   mgr     
12/06/2001  neds@abc.com   emp

Would like to achieve the results below

date        email          level   status
01/01/2000  john@abc.com   mgr     hired as mgr
10/01/2001  john@abc.com   mgr     hired as mgr
14/02/2000  kimdo@abc.com  emp     promoted to mgr
19/10/2001  kimdo@abc.com  mgr     promoted to mgr
12/05/2000  waint@abc.com  emp     hired as emp
14/04/2001  waint@abc.com  emp     hired as emp
22/05/2000  neds@abc.com   mgr     status change
12/06/2001  neds@abc.com   emp     status change

So far, am able to select the first and last rows of the dataframe based on a groupyby, but am not entirely sure how to apply the conditions to get the new 'status' column. Appreciate any form of help, thank you.

df2 = df.groupby('email', as_index=False).nth([0,-1])
pandas conditional-statements dataframe group-by python
2 Answers
df2 = df.groupby('email', as_index=False).nth([0,-1])

You can try:

d={'mgr:mgr':'hired as mgr','emp:mgr':'promoted to mgr','emp:emp':'hired as emp','mgr:emp':'status change'}
#created a dict for mapping

Finally:

df2.loc[:,'status']=df2.groupby('email')['level'].transform(':'.join).map(d)

output of df2:

    date        email           level   status
0   01/01/2000  john@abc.com    mgr     hired as mgr
2   10/01/2001  john@abc.com    mgr     hired as mgr
3   14/02/2000  kimdo@abc.com   emp     promoted to mgr
4   19/10/2001  kimdo@abc.com   mgr     promoted to mgr
5   12/05/2000  waint@abc.com   emp     hired as emp
7   14/04/2001  waint@abc.com   emp     hired as emp
8   22/05/2000  neds@abc.com    mgr     status change
10  12/06/2001  neds@abc.com    emp     status change
user_142898920

try creating a map dictionary to map the status.

fl = lambda s: s.iloc[[0,-1]]
d = {'mgr-mgr': 'hired as mgr', 'emp-mgr': 'promoted to mgr', 'emp-emp': 'hired as emp', 'mgr-emp': 'status change'}
res = df.groupby('email', as_index=False)['level'].apply(lambda x: (fl(x).shift(1) + "-" + (fl(x))).bfill()).map(d)
res.index= res.index.droplevel()
df['status'] = res
df.dropna(inplace=True)
date email level status
0 01/01/2000 john@abc.com mgr hired as mgr
2 10/01/2001 john@abc.com mgr hired as mgr
3 14/02/2000 kimdo@abc.com emp promoted to mgr
4 19/10/2001 kimdo@abc.com mgr promoted to mgr
5 12/05/2000 waint@abc.com emp hired as emp
7 14/04/2001 waint@abc.com emp hired as emp
8 22/05/2000 neds@abc.com mgr status change
10 12/06/2001 neds@abc.com emp status change
user_66603730
Related