将唯一值转换为列名的PANAS数据框

问题描述

我有下面提到格式的 pandas 数据帧

input_df :
    gw_mac        mac          val    status 
0   AC233FC01403  AC233F264A4C -21    Outwards
1   AC233FC015F6  AC233F264A4C -37    Outwards
2   AC233FC01403  AC233F264A4C -20    Outwards
3   AC233FC015F6  AC233F264A4C -37    Outwards
4   AC233FC01403  AC233F264A4C -29    Outwards
5   AC233FC015F6  AC233F264A4C -39    Outwards
6   AC233FC01403  AC233F264A4C -37    Outwards
7   AC233FC015F6  AC233F264A4C -37    Outwards
8   AC233FC01403  AC233F264A4C -22    Outwards
9   AC233FC015F6  AC233F264A4C -37    Outwards
10  AC233FC015F6  AC233F264A4C -37    Outwards

我需要转换与下面相同的，

output_df:
    AC233FC01403  AC233FC015F6  mac            status    
1   -21           -37           AC233F264A4C   Outwards  
2   -20           -37           AC233F264A4C   Outwards
3   -29           -39           AC233F264A4C   Outwards
4   -37           -37           AC233F264A4C   Outwards
5   -22           -37           AC233F264A4C   Outwards
6    0            -37           AC233F264A4C   Outwards

---

解决方案

对新的counter列使用cumcount，set_index，unstack和reset_index：

g = df.groupby(['gw_mac','mac','status']).cumcount()

df = (df.set_index([g, 'mac','status','gw_mac'])['val']
        .unstack(fill_value=0)
        .reset_index(level=[1,2])
        .rename_axis(None, axis=1))
print (df)
            mac    status  AC233FC01403  AC233FC015F6
0  AC233F264A4C  Outwards           -21           -37
1  AC233F264A4C  Outwards           -20           -37
2  AC233F264A4C  Outwards           -29           -39
3  AC233F264A4C  Outwards           -37           -37
4  AC233F264A4C  Outwards           -22           -37
5  AC233F264A4C  Outwards             0           -37

如果列顺序很重要：

df = df[df.columns[2:].tolist() + df.columns[:2].tolist()]
print (df)
   AC233FC01403  AC233FC015F6           mac    status
0           -21           -37  AC233F264A4C  Outwards
1           -20           -37  AC233F264A4C  Outwards
2           -29           -39  AC233F264A4C  Outwards
3           -37           -37  AC233F264A4C  Outwards
4           -22           -37  AC233F264A4C  Outwards
5             0           -37  AC233F264A4C  Outwards