converting columns to rows in paython

Question

I have a dataframe like this,

df1=
        time   asset_id sensor_01   sensor_02
0   2019-08-01  120         23        54
1   2019-08-02  125         45        38
2   2019-08-03  120         25        49

since number of sensors are variable, I decided to write them in rows like,

df2=
      time  asset_id    sensor_ID   sensor_value
0   2019-08-01  120     sensor_01      23
1   2019-08-02  125     sensor_01      45
2   2019-08-03  120     sensor_01      25
3   2019-08-01  120     sensor_02      54
4   2019-08-02  125     sensor_02      38
5   2019-08-03  120     sensor_02      49

I am wondering if there is any convenient way to convert df1 to df2?

Appendix: df1 code for convenience,

import pandas as pd
dictionary = {'time': ['2019-08-01', '2019-08-02', '2019-08-03'],
              'asset_id': ['120', '125', '120'],
              'sensor_01': [23, 45, 25],
              'sensor_02': [54, 38, 49]
    
}
df1 = pd.DataFrame(dictionary)

score 1 · Answer 1 · answered Aug 22 '21 at 12:55

Use pandas.melt:

pd.melt(df1,
        id_vars=['time', 'asset_id'], # variables to keep as columns
        var_name='sensor_ID',         # column name for the variable
        value_name='sensor_value'     # column name for the values
       )

output:

         time asset_id  sensor_ID  sensor_value
0  2019-08-01      120  sensor_01            23
1  2019-08-02      125  sensor_01            45
2  2019-08-03      120  sensor_01            25
3  2019-08-01      120  sensor_02            54
4  2019-08-02      125  sensor_02            38
5  2019-08-03      120  sensor_02            49

durjoy · Answer 2 · 2021-08-22T13:11:31.490

1

pd.melt(df, id_vars=['time', 'asset_id'], value_vars=['sensor_01', 'sensor_02'], var_name='sensor_ID', value_name='sensor_value')

edited Aug 22 '21 at 13:11

answered Aug 22 '21 at 12:55

durjoy

1,709
1
14
25

converting columns to rows in paython

2 Answers2