CODEWITHSUNDEEP
pythonpandasmerge
AB Code
AB Code

Reputation: 15

Dataframe merge two consecutive rows based on condition

This is my DataFrame

         id   group  sensor        sdate   stime   status
0   US-2222  BUTTON  LA-1212  2022-06-21  11:00:00     ON
1   US-2222  BUTTON  LA-1212  2202-06-21  11:30:00    OFF
2   US-6666  BUTTON  LA-4545  2022-06-21  06:00:00     ON
3   US-6666  BUTTON  LA-4545  2022-06-21  06:30:00    OFF
4   US-6666  MOTION  LA-4545  2022-06-21  09:00:00     ON
5   US-6666  MOTION  LA-4545  2022-06-21  09:20:00    OFF
6   US-6666  MOTION  LA-4545  2022-06-21  18:00:00     ON

I need to merge consecutive rows for each "Sensor" based on "status". The output should look like this

         id  group   sensor     sdate        stime  status       stime2     status2
0   US-2222  BUTTON  LA-1212  2022-06-21  11:00:00     ON       11:30:00        OFF
2   US-6666  BUTTON  LA-4545  2022-06-21  06:00:00     ON       06:30:00        OFF
4   US-6666  MOTION  LA-4545  2022-06-21  09:00:00     ON       09:20:00        OFF

Thanks

Upvotes: 0

Views: 52

Answers (2)

AB Code
AB Code

Reputation: 15

Thanks ! I found a crude but simpler light weight solution.

respStr = "["
sList = mClass_sLog_japiapp.objects.all()
t = len(sList)-1
for i in range (0, t, 2):
    R = sList[i]
    respStr += "{"
    s = "\"{}\" : \"{}\",".format("GROUP",R.group)
    respStr += s
    s = "\"{}\" : \"{}\",".format("SENSOR",R.plate)
    respStr += s
    s = "\"{}\" : \"{}\",".format("DATE",R.sdate)
    respStr += s
    s = "\"{}\" : \"{}\",".format("FTIME",R.stime)
    respStr += s
    # s = "\"{}\" : \"{}\",".format("FSTATUS",R.status)
    # respStr += s
    s = "\"{}\" : \"{}\"".format("TOTIME",sList[i+1].stime)
    respStr += s
    # s = "\"{}\" : \"{}\"".format("TSTATUS",sList[i+1].status)
    # respStr += s
    respStr += "},"
respStr += "]"
respStr = respStr.replace("},]", "}]")

Upvotes: 0

mozway
mozway

Reputation: 260790

You can use a custom pivot:

cols = ['id', 'group', 'sensor', 'sdate']
out = (df
 .assign(idx=df.groupby(cols).cumcount().floordiv(2),
         col=lambda d: d.groupby(cols+['idx']).cumcount().astype(str)
        )
 .pivot(index=cols+['idx'], columns='col', values=['stime', 'status'])
 .sort_index(level=1, axis=1) 
 .pipe(lambda d: d.set_axis(d.columns.map('_'.join), axis=1))
 .dropna()
 .reset_index()
)

output:

        id   group   sensor       sdate  idx status_0   stime_0 status_1   stime_1
0  US-2222  BUTTON  LA-1212  2022-06-21    0       ON  11:00:00      OFF  11:30:00
1  US-6666  BUTTON  LA-4545  2022-06-21    0       ON  06:00:00      OFF  06:30:00
2  US-6666  MOTION  LA-4545  2022-06-21    0       ON  09:00:00      OFF  09:20:00

Upvotes: 1

Related Questions