return highest date in column

Question

I have a column with Transmitter codes and corresponding dates. I want to make my dataset smaller by selecting all unique Transmitter codes with the latest dates and the rest of the columns. So for example if transmitter code: A69-1602-59778 has dates(2019-11-11; 2019-11-12; 2019-11-13) I want only 2019-11-13 with the corresponding transmitter code. How can I make this happen?

I should get a dataframe with 119 rows (119 unique transmitter codes) with the corresponding latest dates. Note: The Dput may not contain all transmitter codes because it is a large dataset.

My data:

structure(list(Date = structure(c(18211, 18211, 18211, 18211, 
18211, 18211, 18211, 18211, 18211, 18211, 18212, 18212, 18212, 
18212, 18212, 18212, 18212, 18212, 18212, 18212, 18213, 18213, 
18213, 18213, 18213, 18213, 18213, 18213, 18213, 18213, 18213, 
18213, 18214, 18214, 18214, 18214, 18214, 18214, 18214, 18214, 
18215, 18215, 18215, 18215, 18215, 18215, 18215, 18215, 18216, 
18216, 18216, 18216, 18216, 18216, 18216, 18216, 18216, 18216, 
18217, 18217, 18217, 18217, 18217, 18217, 18217, 18217, 18217, 
18217, 18217, 18217, 18217, 18218, 18218, 18218, 18218, 18218, 
18218, 18218, 18218, 18218, 18219, 18219, 18219, 18219, 18219, 
18219, 18219, 18219, 18219, 18219, 18220, 18220, 18220, 18220, 
18220, 18220, 18220, 18220, 18220, 18220), class = "Date"), Transmitter = c("A69-1602-59769", 
"A69-1602-59776", "A69-1602-59775", "A69-1602-59771", "A69-1602-59774", 
"A69-1602-59773", "A69-1602-59772", "A69-1602-59777", "A69-1602-59778", 
"A69-1602-59770", "A69-1602-59773", "A69-1602-59775", "A69-1602-59777", 
"A69-1602-59771", "A69-1602-59772", "A69-1602-59774", "A69-1602-59778", 
"A69-1602-59776", "A69-1602-59765", "A69-1602-59761", "A69-1602-59772", 
"A69-1602-59777", "A69-1602-59774", "A69-1602-59773", "A69-1602-59776", 
"A69-1602-59770", "A69-1602-59759", "A69-1602-59759", "A69-1602-59761", 
"A69-1602-59775", "A69-1602-59765", "A69-1602-59771", "A69-1602-59774", 
"A69-1602-59773", "A69-1602-59772", "A69-1602-59777", "A69-1602-59778", 
"A69-1602-59776", "A69-1602-59775", "A69-1602-59771", "A69-1602-59770", 
"A69-1602-59772", "A69-1602-59773", "A69-1602-59774", "A69-1602-59777", 
"A69-1602-59759", "A69-1602-59778", "A69-1602-59769", "A69-1602-59774", 
"A69-1602-59772", "A69-1602-59773", "A69-1602-59777", "A69-1602-59759", 
"A69-1602-59775", "A69-1602-59761", "A69-1602-59778", "A69-1602-59770", 
"A69-1602-59765", "A69-1602-59773", "A69-1602-59774", "A69-1602-59777", 
"A69-1602-59772", "A69-1602-59759", "A69-1602-59765", "A69-1602-59761", 
"A69-1602-59770", "A69-1602-59778", "A69-1602-59769", "A69-1602-59760", 
"A69-1602-59775", "A69-1602-59768", "A69-1602-59773", "A69-1602-59777", 
"A69-1602-59772", "A69-1602-59774", "A69-1602-59768", "A69-1602-59770", 
"A69-1602-59761", "A69-1602-59767", "A69-1602-59769", "A69-1602-59777", 
"A69-1602-59772", "A69-1602-59774", "A69-1602-59773", "A69-1602-59761", 
"A69-1602-59769", "A69-1602-59778", "A69-1602-59775", "A69-1602-59767", 
"A69-1602-59759", "A69-1602-59772", "A69-1602-59777", "A69-1602-59773", 
"A69-1602-59774", "A69-1602-59759", "A69-1602-59769", "A69-1602-59778", 
"A69-1602-59756", "A69-1602-59771", "A69-1602-59775"), Batch.location = c("Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Den Oever", "Medemblik", "Medemblik", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Medemblik", "Medemblik", "Medemblik", "Den Oever", "Medemblik", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Medemblik", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Medemblik", "Den Oever", "Medemblik", "Den Oever", 
"Den Oever", "Medemblik", "Den Oever", "Den Oever", "Den Oever", 
"Den Oever", "Medemblik", "Medemblik", "Medemblik", "Den Oever", 
"Den Oever", "Den Oever", "Medemblik", "Den Oever", "Medemblik", 
"Den Oever", "Den Oever", "Den Oever", "Den Oever", "Medemblik", 
"Den Oever", "Medemblik", "Medemblik", "Den Oever", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Medemblik", "Den Oever", 
"Den Oever", "Den Oever", "Medemblik", "Medemblik", "Den Oever", 
"Den Oever", "Den Oever", "Den Oever", "Medemblik", "Den Oever", 
"Den Oever", "Ketelhaven", "Den Oever", "Den Oever"), Location.Receiver = c("Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Medemblik Ijsselmeer, haven", 
"Medemblik Ijsselmeer, haven", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Stavoren Ijsselmeer", "Medemblik Ijsselmeer, haven", "Medemblik Ijsselmeer, gemaal", 
"Medemblik Ijsselmeer, haven", "Den Oever Ijsselmeer", "Medemblik Ijsselmeer, haven", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Stavoren Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Medemblik Ijsselmeer, haven", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Medemblik Ijsselmeer, haven", "Den Oever Ijsselmeer", "Medemblik Ijsselmeer, haven", 
"Den Oever Ijsselmeer", "Stavoren Ijsselmeer", "Medemblik Ijsselmeer, haven", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Medemblik Ijsselmeer, gemaal", "Medemblik Ijsselmeer, haven", 
"Medemblik Ijsselmeer, haven", "Stavoren Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Medemblik Ijsselmeer, haven", "Den Oever Ijsselmeer", 
"Medemblik Ijsselmeer, gemaal", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Medemblik Ijsselmeer, gemaal", 
"Stavoren Ijsselmeer", "Medemblik Ijsselmeer, haven", "Medemblik Ijsselmeer, gemaal", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Medemblik Ijsselmeer, haven", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Medemblik Ijsselmeer, gemaal", "Medemblik Ijsselmeer, gemaal", 
"Den Oever Ijsselmeer", "Den Oever Ijsselmeer", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Medemblik Ijsselmeer, gemaal", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer", "Ramspol 1 (zuid) ", "Den Oever Ijsselmeer", 
"Den Oever Ijsselmeer"), Length = c(63.1, 62.3, 67.9, 65, 68.1, 
65.7, 62.5, 65.7, 66.2, 63.1, 65.7, 67.9, 65.7, 65, 62.5, 68.1, 
66.2, 62.3, 61.4, 56, 62.5, 65.7, 68.1, 65.7, 62.3, 63.1, 56.5, 
56.5, 56, 67.9, 61.4, 65, 68.1, 65.7, 62.5, 65.7, 66.2, 62.3, 
67.9, 65, 63.1, 62.5, 65.7, 68.1, 65.7, 56.5, 66.2, 63.1, 68.1, 
62.5, 65.7, 65.7, 56.5, 67.9, 56, 66.2, 63.1, 61.4, 65.7, 68.1, 
65.7, 62.5, 56.5, 61.4, 56, 63.1, 66.2, 63.1, 70, 67.9, 62.8, 
65.7, 65.7, 62.5, 68.1, 62.8, 63.1, 56, 61.3, 63.1, 65.7, 62.5, 
68.1, 65.7, 56, 63.1, 66.2, 67.9, 61.3, 56.5, 62.5, 65.7, 65.7, 
68.1, 56.5, 63.1, 66.2, 48.2, 65, 67.9), Weight = c(3515, 2944, 
4553, 4112, 4868, 4105, 3404, 3998, 4029, 3535, 4105, 4553, 3998, 
4112, 3404, 4868, 4029, 2944, 3429, 2465, 3404, 3998, 4868, 4105, 
2944, 3535, 2565, 2565, 2465, 4553, 3429, 4112, 4868, 4105, 3404, 
3998, 4029, 2944, 4553, 4112, 3535, 3404, 4105, 4868, 3998, 2565, 
4029, 3515, 4868, 3404, 4105, 3998, 2565, 4553, 2465, 4029, 3535, 
3429, 4105, 4868, 3998, 3404, 2565, 3429, 2465, 3535, 4029, 3515, 
4402, 4553, 3466, 4105, 3998, 3404, 4868, 3466, 3535, 2465, 2846, 
3515, 3998, 3404, 4868, 4105, 2465, 3515, 4029, 4553, 2846, 2565, 
3404, 3998, 4105, 4868, 2565, 3515, 4029, 1421, 4112, 4553)), row.names = c(716209L, 
1073805L, 1019330L, 812249L, 987220L, 934268L, 840464L, 1156663L, 
1226152L, 795888L, 928943L, 1020561L, 1138203L, 807648L, 855925L, 
966957L, 1234618L, 1085184L, 626931L, 605844L, 846794L, 1129076L, 
982617L, 955912L, 1081788L, 799297L, 542223L, 525478L, 599496L, 
1009592L, 629158L, 814527L, 977839L, 875687L, 842029L, 1102446L, 
1229579L, 1085547L, 1000314L, 807813L, 801897L, 829339L, 885972L, 
984860L, 1121520L, 539123L, 1223695L, 706154L, 973168L, 857036L, 
922413L, 1131675L, 550919L, 1053493L, 589745L, 1230983L, 795265L, 
628214L, 947900L, 977889L, 1127631L, 834415L, 546178L, 629343L, 
594275L, 799017L, 1220587L, 722244L, 584846L, 1061698L, 684508L, 
911860L, 1157213L, 857431L, 989969L, 679834L, 803111L, 588690L, 
659389L, 724411L, 1168275L, 837874L, 982503L, 932847L, 586835L, 
769907L, 1232536L, 1032207L, 661232L, 555049L, 846636L, 1209851L, 
934974L, 984655L, 552925L, 746638L, 1229866L, 501088L, 815912L, 
1020860L), class = "data.frame")

Does this answer your question: [Getting the top values by group](https://stackoverflow.com/questions/27766054/getting-the-top-values-by-group) — stefan, Dec 16 '21 at 13:52

lovalery · Accepted Answer · 2021-12-16T14:15:54.723

Please find below one possible solution using data.table and magrittr (for the pipes)

Reprex

Code

library(data.table)
library(magrittr)

results <- setDT(df)[, Date := as.Date(Date)][] %>% 
  setorder(., -Date) %>% 
  unique(., by ="Transmitter")

OR (probably more elegant solution)

results <- setDT(df)[, Date := as.Date(Date)][] %>% 
  unique(., by ="Transmitter", fromLast = TRUE)

Output

results
#>           Date    Transmitter Batch.location            Location.Receiver
#>  1: 2019-11-20 A69-1602-59772      Den Oever         Den Oever Ijsselmeer
#>  2: 2019-11-20 A69-1602-59777      Den Oever         Den Oever Ijsselmeer
#>  3: 2019-11-20 A69-1602-59773      Den Oever         Den Oever Ijsselmeer
#>  4: 2019-11-20 A69-1602-59774      Den Oever         Den Oever Ijsselmeer
#>  5: 2019-11-20 A69-1602-59759      Medemblik Medemblik Ijsselmeer, gemaal
#>  6: 2019-11-20 A69-1602-59769      Den Oever         Den Oever Ijsselmeer
#>  7: 2019-11-20 A69-1602-59778      Den Oever         Den Oever Ijsselmeer
#>  8: 2019-11-20 A69-1602-59756     Ketelhaven            Ramspol 1 (zuid) 
#>  9: 2019-11-20 A69-1602-59771      Den Oever         Den Oever Ijsselmeer
#> 10: 2019-11-20 A69-1602-59775      Den Oever         Den Oever Ijsselmeer
#> 11: 2019-11-19 A69-1602-59761      Medemblik  Medemblik Ijsselmeer, haven
#> 12: 2019-11-19 A69-1602-59767      Medemblik Medemblik Ijsselmeer, gemaal
#> 13: 2019-11-18 A69-1602-59768      Medemblik Medemblik Ijsselmeer, gemaal
#> 14: 2019-11-18 A69-1602-59770      Den Oever          Stavoren Ijsselmeer
#> 15: 2019-11-17 A69-1602-59765      Medemblik  Medemblik Ijsselmeer, haven
#> 16: 2019-11-17 A69-1602-59760      Medemblik  Medemblik Ijsselmeer, haven
#> 17: 2019-11-14 A69-1602-59776      Den Oever         Den Oever Ijsselmeer
#>     Length Weight
#>  1:   62.5   3404
#>  2:   65.7   3998
#>  3:   65.7   4105
#>  4:   68.1   4868
#>  5:   56.5   2565
#>  6:   63.1   3515
#>  7:   66.2   4029
#>  8:   48.2   1421
#>  9:   65.0   4112
#> 10:   67.9   4553
#> 11:   56.0   2465
#> 12:   61.3   2846
#> 13:   62.8   3466
#> 14:   63.1   3535
#> 15:   61.4   3429
#> 16:   70.0   4402
#> 17:   62.3   2944

^{Created on 2021-12-16 by the reprex package (v2.0.1)}

score 1 · Answer 2 · answered Dec 16 '21 at 15:35

Using ave.

dat[with(dat, as.logical(ave(as.numeric(Date), Transmitter, FUN=\(x) x == max(x)))), ]
#               Date    Transmitter Batch.location            Location.Receiver Length Weight
# 1085547 2019-11-14 A69-1602-59776      Den Oever         Den Oever Ijsselmeer   62.3   2944
# 629343  2019-11-17 A69-1602-59765      Medemblik  Medemblik Ijsselmeer, haven   61.4   3429
# 584846  2019-11-17 A69-1602-59760      Medemblik  Medemblik Ijsselmeer, haven   70.0   4402
# 679834  2019-11-18 A69-1602-59768      Medemblik Medemblik Ijsselmeer, gemaal   62.8   3466
# 803111  2019-11-18 A69-1602-59770      Den Oever          Stavoren Ijsselmeer   63.1   3535
# 586835  2019-11-19 A69-1602-59761      Medemblik  Medemblik Ijsselmeer, haven   56.0   2465
# 661232  2019-11-19 A69-1602-59767      Medemblik Medemblik Ijsselmeer, gemaal   61.3   2846
# 846636  2019-11-20 A69-1602-59772      Den Oever         Den Oever Ijsselmeer   62.5   3404
# 1209851 2019-11-20 A69-1602-59777      Den Oever         Den Oever Ijsselmeer   65.7   3998
# 934974  2019-11-20 A69-1602-59773      Den Oever         Den Oever Ijsselmeer   65.7   4105
# 984655  2019-11-20 A69-1602-59774      Den Oever         Den Oever Ijsselmeer   68.1   4868
# 552925  2019-11-20 A69-1602-59759      Medemblik Medemblik Ijsselmeer, gemaal   56.5   2565
# 746638  2019-11-20 A69-1602-59769      Den Oever         Den Oever Ijsselmeer   63.1   3515
# 1229866 2019-11-20 A69-1602-59778      Den Oever         Den Oever Ijsselmeer   66.2   4029
# 501088  2019-11-20 A69-1602-59756     Ketelhaven            Ramspol 1 (zuid)    48.2   1421
# 815912  2019-11-20 A69-1602-59771      Den Oever         Den Oever Ijsselmeer   65.0   4112
# 1020860 2019-11-20 A69-1602-59775      Den Oever         Den Oever Ijsselmeer   67.9   4553

return highest date in column

2 Answers2