pandas dataframe, move rows values to new columns in the same row

Question

I have the follow dataframe:

acount    document     type    sum_old     sum_new
001         12345        C        10          20
001         12345        D        20          50
555         00770        C        31          44

And I would like this output:

acount    document    C_sum_old    D_sum_old    C_sum_new    D_sum_new
001        12345          10          20            20           50
001        00770          31                        44

I had tried transpose method but not work

What can I do?

Answer 1

You can do this by pivoting your data, and then massaging the column labels a little:

pivotted_df = df.pivot(["acount", "document"], "type")
pivotted_df.columns = pivotted_df.columns.swaplevel().map("_".join)

print(pivotted_df)
                 C_sum_old  D_sum_old  C_sum_new  D_sum_new
acount document
1      12345          10.0       20.0       20.0       50.0
555    770            31.0        NaN       44.0        NaN

Or, for you method chaining addicts:

out = (
    df.pivot(["acount", "document"], "type")
    .pipe(lambda d: 
        d.set_axis(d.columns.swaplevel().map("_".join), axis=1)
    )
)

print(out)
                 C_sum_old  D_sum_old  C_sum_new  D_sum_new
acount document
1      12345          10.0       20.0       20.0       50.0
555    770            31.0        NaN       44.0        NaN