基于groupby Python的第一个和最后一个值的条件创建新列

Question

我有一个pandas dataframe，希望根据groupby第一行和最后一行的条件创建一个新列，其中包含值。所需条件如下：

经理对经理=受雇为经理

emp转经理=晋升为经理

emp到emp=受雇为emp

经理到环境管理计划=状态更改

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

希望实现以下结果

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

到目前为止，我能够基于groupyby选择dataframe的第一行和最后一行，但不完全确定如何应用这些条件来获取新的“status”列。感谢任何形式的帮助，谢谢

df2 = df.groupby('email', as_index=False).nth([0,-1])

Answer 1

尝试创建一个map{}来映射状态

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)

---

^{tb1}$

Answer 2

df2 = df.groupby('email', as_index=False).nth([0,-1])

您可以尝试：

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

最后：

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

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