Transpose part of dataframe Python

Question

I have a dataset as below:

>>>df = pd.DataFrame(
    [
        ["site1", "2020-12-05T15:50:00", "0", "0"],
        ["site1", "2020-12-05T15:55:00", "0.5", "0"],
        ["site2", "2020-12-05T15:50:00", "0.5", "0"],
        ["site2", "2020-12-05T15:55:00", "1", "0"],
    ],
    columns=["code", "site_time", "r1", "r2"],
)
>>>df
    code            site_time   r1 r2   
0  site1  2020-12-05T15:50:00    0  0  
1  site1  2020-12-05T15:55:00  0.5  0   
2  site2  2020-12-05T15:50:00  0.5  0   
3  site2  2020-12-05T15:55:00    1  0

Then I would like to transpose it to the table as below:

code  site_time           trace value
site1 2020-12-05T15:50:00 r1    0
site1 2020-12-05T15:50:00 r2    0
site1 2020-12-05T15:55:00 r1    0.5
site1 2020-12-05T15:55:00 r2    0
site2 2020-12-05T15:50:00 r1    0.5
site2 2020-12-05T15:50:00 r2    0
site2 2020-12-05T15:55:00 r1    1
site2 2020-12-05T15:55:00 r2    0

Could I ask how i accomplish this?

adhg · Accepted Answer · 2020-12-06 02:09:10Z

2

use melt:

df.melt(id_vars=['code','site_time']).rename(columns={'variable':'trace'}).sort_values(by=['code','trace',])

desired result:

     code   site_time         trace value
0   site1   2020-12-05T15:50:00 r1  0
1   site1   2020-12-05T15:55:00 r1  0.5
4   site1   2020-12-05T15:50:00 r2  0
5   site1   2020-12-05T15:55:00 r2  0
2   site2   2020-12-05T15:50:00 r1  0.5
3   site2   2020-12-05T15:55:00 r1  1
6   site2   2020-12-05T15:50:00 r2  0
7   site2   2020-12-05T15:55:00 r2  0

edited Dec 6, 2020 at 2:09

answered Dec 6, 2020 at 1:41

adhg

11k13 gold badges63 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transpose part of dataframe Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related