Julia中DataFrame按Roll优先级分组聚合日期处理咨询

完整可运行代码 #

using DataFrames, CSV, Missings

# 构造测试数据
str = """Roll,id,name,tag,date1,date2,date3,date4,date5
0001443646-19-000093,1443646,Rohit,Student,20170331,20180331,20190331
0001443646-20-000086,1443646,Rohit,Student,20190331,20200331,20180331
0001443646-21-000079,1443646,Rohit,Student,20190331,20210331,20200331
0001683168-20-001021,1622879,Mohit,Teacher,20191231
0001683168-21-001161,1622879,Mohit,Teacher,20201231,20191231
"""
df = CSV.read(IOBuffer(str), DataFrame)

# 核心处理逻辑
res = combine(groupby(df, [:id, :name, :tag])) do g
    # 提取Roll中间段数值，按降序排序组内行确定优先级
    g.roll_mid = [parse(Int, split(r.Roll, '-')[2]) for r in eachrow(g)]
    sorted_g = sort(g, :roll_mid, rev=true)
    # 取优先级最高的Roll值
    target_roll = sorted_g[1, :Roll]
    # 收集所有非缺失日期
    date_cols = [:date1, :date2, :date3, :date4, :date5]
    all_dates = Int[]
    for r in eachrow(sorted_g)
        for col in date_cols
            !ismissing(r[col]) && push!(all_dates, r[col])
        end
    end
    # 按规则去重后降序排序，补全为5个元素
    unique_dates = sort(unique(all_dates), rev=true)
    padded_dates = vcat(unique_dates, fill(missing, 5 - length(unique_dates)))
    # 构造返回行
    return (; Roll = target_roll, date1 = padded_dates[1], date2 = padded_dates[2], date3 = padded_dates[3], date4 = padded_dates[4], date5 = padded_dates[5])
end

# 调整列顺序与原始表对齐
select!(res, [:Roll, :id, :name, :tag, :date1, :date2, :date3, :date4, :date5])

# 输出结果
println(res)

如果你不需要日期去重，直接删除代码中的unique调用即可匹配你给出的示例输出。

运行输出结果 #

2×9 DataFrame
 Row │ Roll                  id       name    tag      date1     date2     date3     date4     date5
     │ String                Int64    String  String   Int64?    Int64?    Int64?    Int64?    Missing?
─────┼──────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 0001443646-21-000079  1443646  Rohit   Student  20210331  20200331  20190331  20180331  20170331
   2 │ 0001683168-21-001161  1622879  Mohit   Teacher  20201231  20191231   missing   missing   missing

内容的提问来源于stack exchange，提问作者user16965068