I've got data frame with IDs, dates per ID and times per date. Also, a binary variable (Y) that gets 1 or 0, and another nominal variable (X). I want to merge rows by ID and date, and take the maximum Y value per (ID, date), and take the common X value per (ID, date), but regardless of 0 values level.
Note: ID, Date and Time are indices, and they are sorted.
Data illustration:
ID Date Time Y X
1 08/27/2019 18:00 0 123
19:00 0 123
20:00 1 456
2 08/27/2019 18:00 0 0
19:00 0 0
20:00 1 456
3 08/27/2019 18:00 1 123
19:00 0 0
20:00 1 456
3 08/28/2019 18:00 0 0
19:00 0 0
20:00 0 0
Expected result:
ID Date Y X
1 08/27/2019 1 123
2 08/27/2019 1 456
3 08/27/2019 1 123
3 08/28/2019 0 0
Note: for the first observation of ID #3 (on the 08/27/2019), 123 was taken because it's earlier (in terms of time).