2

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)
Phoenix
  • 359
  • 7
  • 15

2 Answers2

1

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
mozway
  • 194,879
  • 13
  • 39
  • 75
1
pd.melt(df, id_vars=['time', 'asset_id'], value_vars=['sensor_01', 'sensor_02'], var_name='sensor_ID', value_name='sensor_value')
durjoy
  • 1,709
  • 1
  • 14
  • 25