Python基于groupby分组首尾行条件为DataFrame新增列的问题求助
解法代码
import pandas as pd # 加载原始数据,实际使用时替换为你的数据源读取逻辑 data = [ ["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"] ] df = pd.DataFrame(data, columns=["date", "email", "level"]) # 按邮箱和日期升序排序,保证取到的首尾行是时间最早、最晚的有效记录 df["date"] = pd.to_datetime(df["date"], dayfirst=True) df = df.sort_values(["email", "date"]).reset_index(drop=True) # 取出每个邮箱分组的首尾行,和你现有操作一致 df2 = df.groupby("email", as_index=False).nth([0, -1]) # 定义状态映射规则 status_rule = { ("mgr", "mgr"): "hired as mgr", ("emp", "mgr"): "promoted to mgr", ("emp", "emp"): "hired as emp", ("mgr", "emp"): "status change" } # 按邮箱分组计算状态,自动将结果广播到该分组的所有行 df2["status"] = df2.groupby("email")["level"].transform(lambda x: status_rule[(x.iloc[0], x.iloc[-1])]) # 可选:将日期格式转回示例中的字符串格式 df2["date"] = df2["date"].dt.strftime("%d/%m/%Y")
逻辑说明
- 排序步骤不可省略,避免原始数据时序混乱导致取到错误的首尾记录
transform方法可以把分组计算出的单个status值自动填充到该分组的所有行,刚好匹配同一个用户两条记录显示相同status的需求- 用元组作为key的字典映射规则,比嵌套if判断逻辑更清晰,后续修改规则时直接调整字典即可
内容的提问来源于stack exchange,提问作者wjie08
相关产品推荐
相关产品推荐

