Pandas dataframe to convert the unique value as column name

Question

I have pandas dataframe in the below mentioned format,

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

I need to convert the same like below one,

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

@jezrael, The link which you have provided contains unique row values like row0,row2,row4 whereas in my case, all the values under "mac" column is same. — Mahamutha M, Feb 25 '19 at 10:00

score 3 · Accepted Answer · answered Feb 25 '19 at 10:06

Use cumcount for new counter column with set_index, unstack and 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

If order of columns is important:

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

Pandas dataframe to convert the unique value as column name

1 Answers1

Linked