0

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).

qwerty
  • 889
  • 6
  • 16
  • It's duplicate as https://stackoverflow.com/questions/15705630/get-the-rows-which-have-the-max-value-in-groups-using-groupby – caot Nov 02 '19 at 09:39
  • How did you expect `1 08/27/2019 1 123`? – caot Nov 02 '19 at 11:19
  • Yes, but I have two more issues: (1) regardless of 0 values, (2) taking the earlier value if there are two common values. – qwerty Nov 02 '19 at 11:29
  • How did you expect 1 08/27/2019 1 123?: 1 is the maximum value of Y for that (ID, date), and 123 is the common value of X for that (ID, date). – qwerty Nov 02 '19 at 11:30
  • if there aren't common values what do you want to get? – ansev Nov 02 '19 at 11:53
  • @ansev The earlier one (regardless of 0 values), like in that case: "3 08/27/2019 1 123". And if there are only zeros, so I want to get a 0 value, like in this case: "3 08/28/2019 0 0". – qwerty Nov 02 '19 at 12:03
  • The question doesn't make too much sense. Is it from real application or that you fake some data and raise a question? – caot Nov 02 '19 at 13:09
  • Real application. This data isn't real, but it seems like that. It's just illustration... – qwerty Nov 02 '19 at 13:23

0 Answers0