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")
Please find below one possible solution using data.table and magrittr (for the pipes)
Reprex
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)
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)