Eliminating rows by group, for which certain factor values do not exist

Question

I have a table that looks as follows (data posted below):

I would like to exclude all iso3c (the three letter codes) for which var has not at least three values (i.e 0,1,2,3). As example, for AGO the only value is 0. So this one needs to go. The next one ALB can stay, because the value goes up to 3. I want to do something like, setDT(DT)[max(as.numeric(as.character(var))) <3, iso3c:=NA, by="iso3c"]. But that does not do anything to the data.

Any ideas?

table(DT)

var AGO ALB ARG ARM AZE BDI BEN BFA BGR BiH BLR BOL BRA BWA CHL CHN CMR COD COL CPV CRI Czech DEU DOM ECU ESP EST GEO GIN GMB GRC GTM GUY HND HRV HUN IDN IND IRL JAM JOR
  0  10   4  10   8   7   9   3   9   2   5   7   9   3   8   9   1  10  10  10   9   5     3   7   3   8   6   6   2  10  10   5   9   6   8   6   8   6   7   6   1  10
  1   0   3   0   2   3   1   2   1   2   3   3   1   1   2   0   3   0   0   0   1   1     3   2   0   0   0   2   2   0   0   5   0   0   1   3   1   1   2   2   0   0
  2   0   1   0   0   0   0   2   0   3   2   0   0   2   0   1   4   0   0   0   0   2     3   0   1   0   3   2   2   0   0   0   0   2   0   0   0   0   0   0   4   0
  3   0   2   0   0   0   0   2   0   2   0   0   0   2   0   0   2   0   0   0   0   0     1   1   5   0   1   0   4   0   0   0   0   2   0   1   1   2   0   2   2   0
  4   0   0   0   0   0   0   1   0   1   0   0   0   2   0   0   0   0   0   0   0   2     0   0   1   2   0   0   0   0   0   0   1   0   1   0   0   1   1   0   3   0
   iso3c
var KAZ KEN KGZ KHM KOR LAO LBN LKA LSO LTU LVA MAR MDA MDG MEX MKD MLI MNE MNG MRT MUS MWI NAM NIC PAN PER PHL POL PRT PRY PSE ROU RUS SEN Serbia&Montenegro SLV SRB SVK
  0  10   1   2   2   8   2   4   5   0   3   4   7   4   1   9   3   4   2   4   9   5   0   5  10  10   7   7   6   4   9  10   4   2   4                 3   8   3   7
  1   0   0   4   2   2   4   2   3   1   4   1   0   3   2   1   3   1   1   2   1   1   0   5   0   0   2   0   3   2   1   0   3   3   5                 4   2   3   2
  2   0   1   1   2   0   3   1   1   3   3   4   1   3   5   0   2   2   4   1   0   1   1   0   0   0   0   1   0   2   0   0   2   5   1                 1   0   2   0
  3   0   4   3   3   0   1   2   1   3   0   1   0   0   2   0   2   1   2   2   0   3   2   0   0   0   1   2   1   2   0   0   1   0   0                 2   0   1   1
  4   0   4   0   1   0   0   1   0   3   0   0   2   0   0   0   0   2   1   1   0   0   7   0   0   0   0   0   0   0   0   0   0   0   0                 0   0   1   0
   iso3c
var SVN SWZ TJK TUR TZA UGA UKR URY UZB VNM ZAF ZMB
  0   7  10   6   2   7   9   5  10   7   6   2   0
  1   0   0   3   3   2   1   1   0   3   3   2   4
  2   2   0   1   3   0   0   3   0   0   1   2   1
  3   1   0   0   0   1   0   1   0   0   0   1   3
  4   0   0   0   2   0   0   0   0   0   0   3   2

DATA

DT <- structure(list(var= structure(c(2, 0, 0, 3, 4, 
4, 1, 0, 2, 3, 1, 2, 3, 4, 2, 3, 3, 0, 0, 1, 3, 2, 3, 2, 2, 0, 
2, 1, 1, 1, 4, 0, 0, 0, 0, 0, 0, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 
0, 4, 0, 0, 1, 0, 0, 0, 0, 0, 0, 4, 0, 0, 0, 0, 1, 0, 3, 4, 3, 
0, 0, 3, 4, 3, 4, 0, 4, 4, 3, 3, 2, 0, 2, 0, 2, 3, 2, 3, 1, 2, 
4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 3, 0, 
0, 0, 3, 0, 0, 0, 2, 3, 0, 0, 1, 2, 0, 4, 2, 1, 0, 1, 3, 0, 0, 
1, 1, 0, 2, 0, 1, 0, 3, 0, 0, 0, 3, 0, 1, 0, 0, 1, 0, 0, 3, 1, 
1, 4, 1, 2, 3, 1, 4, 3, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 4, 2, 
1, 1, 4, 3, 2, 0, 4, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 3, 2, 0, 
0, 0, 2, 0, 0, 3, 1, 0, 2, 1, 1, 0, 1, 1, 0, 0, 0, 4, 0, 3, 0, 
1, 2, 4, 2, 0, 1, 0, 0, 2, 0, 0, 0, 0, 1, 1, 3, 0, 2, 3, 0, 0, 
3, 1, 0, 0, 2, 4, 0, 4, 0, 0, 0, 0, 0, 0, 2, 2, 2, 0, 2, 3, 3, 
1, 1, 2, 3, 3, 3, 1, 1, 1, 1, 0, 0, 2, 4, 4, 3, 4, 3, 2, 4, 4, 
4, 4, 3, 3, 1, 4, 2, 0, 2, 1, 0, 0, 1, 0, 2, 0, 1, 0, 0, 2, 3, 
3, 0, 1, 1, 1, 1, 0, 0, 0, 1, 0, 3, 0, 0, 1, 3, 0, 1, 4, 2, 0, 
0, 0, 1, 4, 0, 0, 0, 2, 2, 4, 4, 0, 3, 1, 1, 0, 3, 0, 0, 2, 1, 
3, 4, 3, 2, 2, 4, 4, 3, 2, 3, 4, 3, 4, 0, 4, 2, 2, 2, 2, 0, 1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 
0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 4, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 3, 0, 
0, 0, 3, 3, 2, 3, 4, 3, 1, 1, 0, 2, 0, 3, 1, 0, 2, 0, 0, 3, 1, 
2, 1, 1, 3, 0, 0, 0, 1, 0, 0, 1, 1, 3, 0, 0, 0, 2, 1, 0, 1, 2, 
0, 0, 1, 0, 0, 0, 0, 2, 0, 0, 0, 0, 2, 3, 0, 1, 0, 1, 3, 1, 3, 
1, 0, 2, 0, 1, 0, 2, 1, 4, 4, 1, 2, 0, 2, 0, 1, 0, 0, 0, 0, 0, 
1, 0, 1, 0, 1, 1, 0, 1, 0, 0, 0, 0, 3, 0, 3, 0, 0, 1, 0, 0, 0, 
0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 3, 0, 3, 0, 0, 2, 2, 1, 0, 1, 1, 
2, 1, 0, 0, 2, 1, 2, 1, 2, 0, 0, 0, 1, 2, 0, 2, 1, 3, 1, 0, 0, 
2, 1, 3, 1, 0, 2, 2, 0, 4, 3, 3, 0, 2, 2, 0, 1, 2, 0, 0, 2, 0, 
0, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 1, 0, 
2, 0, 0, 0, 2, 1, 0, 0, 3, 3, 0, 3, 2, 0, 1, 1, 2, 3, 2, 1, 1, 
0, 2, 1, 0, 1, 0, 2, 2, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 2, 1, 0, 1, 2, 
1, 0, 2, 2, 2, 0, 0, 2, 2, 1, 0, 2, 0, 0, 3, 2, 2, 0, 0, 0, 0, 
2, 3, 0, 0, 1, 0, 0, 1, 0, 3, 3, 0, 0, 0, 0, 0, 3, 0, 0, 1, 0, 
0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 2, 0, 1, 2, 1, 3, 
0, 2), label = "label", format.stata = "%20.0f", class = c("haven_labelled", 
"vctrs_vctr", "double"), labels = c(`No obstacle` = 0, `Minor obstacle` = 1, 
`Moderate obstacle` = 2, `Major obstacle` = 3, `Very Severe Obstacle` = 4
)), iso3c = structure(c(13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 
13L, 13L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 16L, 
16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 16L, 25L, 25L, 25L, 25L, 
25L, 25L, 25L, 25L, 25L, 25L, 32L, 32L, 32L, 32L, 32L, 32L, 32L, 
32L, 32L, 32L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 34L, 
37L, 37L, 37L, 37L, 37L, 37L, 37L, 37L, 37L, 37L, 43L, 43L, 43L, 
43L, 43L, 43L, 43L, 43L, 43L, 43L, 59L, 59L, 59L, 59L, 59L, 59L, 
59L, 59L, 59L, 59L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 65L, 
65L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 68L, 68L, 
68L, 68L, 68L, 68L, 68L, 68L, 68L, 68L, 78L, 78L, 78L, 78L, 78L, 
78L, 78L, 78L, 78L, 78L, 49L, 49L, 49L, 49L, 49L, 49L, 49L, 49L, 
49L, 49L, 84L, 84L, 84L, 84L, 84L, 84L, 84L, 84L, 84L, 84L, 91L, 
91L, 91L, 91L, 91L, 91L, 91L, 91L, 91L, 91L, 77L, 77L, 77L, 77L, 
77L, 77L, 77L, 77L, 77L, 77L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 
90L, 90L, 90L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 15L, 
33L, 33L, 33L, 33L, 33L, 33L, 33L, 33L, 33L, 33L, 75L, 75L, 75L, 
75L, 75L, 75L, 75L, 75L, 75L, 75L, 58L, 58L, 58L, 58L, 58L, 58L, 
58L, 58L, 58L, 58L, 89L, 89L, 89L, 89L, 89L, 89L, 89L, 89L, 89L, 
89L, 62L, 62L, 62L, 62L, 62L, 62L, 62L, 62L, 62L, 62L, 53L, 53L, 
53L, 53L, 53L, 53L, 53L, 53L, 53L, 53L, 55L, 55L, 55L, 55L, 55L, 
55L, 55L, 55L, 55L, 55L, 44L, 44L, 44L, 44L, 44L, 44L, 44L, 44L, 
44L, 44L, 63L, 63L, 63L, 63L, 63L, 63L, 63L, 63L, 63L, 63L, 7L, 
7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 70L, 70L, 70L, 70L, 70L, 
70L, 70L, 70L, 70L, 70L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 31L, 
31L, 31L, 48L, 48L, 48L, 48L, 48L, 48L, 48L, 48L, 48L, 48L, 21L, 
21L, 21L, 21L, 21L, 21L, 21L, 21L, 21L, 21L, 60L, 60L, 60L, 60L, 
60L, 60L, 60L, 60L, 60L, 60L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 
50L, 50L, 50L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 40L, 
8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 17L, 17L, 17L, 17L, 17L, 
17L, 17L, 17L, 17L, 17L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 20L, 
20L, 20L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 6L, 6L, 6L, 
6L, 6L, 6L, 6L, 6L, 6L, 6L, 18L, 18L, 18L, 18L, 18L, 18L, 18L, 
18L, 18L, 18L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 30L, 
61L, 61L, 61L, 61L, 61L, 61L, 61L, 61L, 61L, 61L, 85L, 85L, 85L, 
85L, 85L, 85L, 85L, 85L, 85L, 85L, 14L, 14L, 14L, 14L, 14L, 14L, 
14L, 14L, 14L, 14L, 64L, 64L, 64L, 64L, 64L, 64L, 64L, 64L, 64L, 
64L, 81L, 81L, 81L, 81L, 81L, 81L, 81L, 81L, 81L, 81L, 29L, 29L, 
29L, 29L, 29L, 29L, 29L, 29L, 29L, 29L, 3L, 3L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 
12L, 19L, 19L, 19L, 19L, 19L, 19L, 19L, 19L, 19L, 19L, 56L, 56L, 
56L, 56L, 56L, 56L, 56L, 56L, 56L, 56L, 66L, 66L, 66L, 66L, 66L, 
66L, 66L, 66L, 66L, 66L, 87L, 87L, 87L, 87L, 87L, 87L, 87L, 87L, 
87L, 87L, 71L, 71L, 71L, 71L, 71L, 71L, 71L, 71L, 71L, 71L, 38L, 
38L, 38L, 38L, 38L, 38L, 38L, 38L, 38L, 38L, 72L, 72L, 72L, 72L, 
72L, 72L, 72L, 72L, 72L, 72L, 41L, 41L, 41L, 41L, 41L, 41L, 41L, 
41L, 41L, 41L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 
57L, 57L, 57L, 57L, 57L, 57L, 57L, 57L, 57L, 57L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 
35L, 35L, 35L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 10L, 
80L, 80L, 80L, 80L, 80L, 80L, 80L, 80L, 80L, 80L, 76L, 76L, 76L, 
76L, 76L, 76L, 76L, 76L, 76L, 76L, 83L, 83L, 83L, 83L, 83L, 83L, 
83L, 83L, 83L, 83L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 11L, 
11L, 69L, 69L, 69L, 69L, 69L, 69L, 69L, 69L, 69L, 69L, 36L, 36L, 
36L, 36L, 36L, 36L, 36L, 36L, 36L, 36L, 79L, 79L, 79L, 79L, 79L, 
79L, 79L, 79L, 79L, 79L, 22L, 22L, 22L, 22L, 22L, 22L, 22L, 22L, 
22L, 22L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 73L, 
73L, 73L, 73L, 73L, 73L, 73L, 73L, 73L, 73L, 9L, 9L, 9L, 9L, 
9L, 9L, 9L, 9L, 9L, 9L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 52L, 
52L, 52L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 27L, 27L, 27L, 27L, 27L, 27L, 27L, 
27L, 27L, 27L, 28L, 28L, 28L, 28L, 28L, 28L, 28L, 28L, 28L, 28L, 
51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 51L, 82L, 82L, 82L, 
82L, 82L, 82L, 82L, 82L, 82L, 82L, 42L, 42L, 42L, 42L, 42L, 42L, 
42L, 42L, 42L, 42L, 88L, 88L, 88L, 88L, 88L, 88L, 88L, 88L, 88L, 
88L, 74L, 74L, 74L, 74L, 74L, 74L, 74L, 74L, 74L, 74L, 86L, 86L, 
86L, 86L, 86L, 86L, 86L, 86L, 86L, 86L, 26L, 26L, 26L, 26L, 26L, 
26L, 26L, 26L, 26L, 26L, 39L, 39L, 39L, 39L, 39L, 39L, 39L, 39L, 
39L, 39L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 46L, 
46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 47L, 47L, 47L, 47L, 
47L, 47L, 47L, 47L, 47L, 47L), .Label = c("AGO", "ALB", "ARG", 
"ARM", "AZE", "BDI", "BEN", "BFA", "BGR", "BiH", "BLR", "BOL", 
"BRA", "BWA", "CHL", "CHN", "CMR", "COD", "COL", "CPV", "CRI", 
"Czech", "DEU", "DOM", "ECU", "ESP", "EST", "GEO", "GIN", "GMB", 
"GRC", "GTM", "GUY", "HND", "HRV", "HUN", "IDN", "IND", "IRL", 
"JAM", "JOR", "KAZ", "KEN", "KGZ", "KHM", "KOR", "LAO", "LBN", 
"LKA", "LSO", "LTU", "LVA", "MAR", "MDA", "MDG", "MEX", "MKD", 
"MLI", "MNE", "MNG", "MRT", "MUS", "MWI", "NAM", "NIC", "PAN", 
"PER", "PHL", "POL", "PRT", "PRY", "PSE", "ROU", "RUS", "SEN", 
"Serbia&Montenegro", "SLV", "SRB", "SVK", "SVN", "SWZ", "TJK", 
"TUR", "TZA", "UGA", "UKR", "URY", "UZB", "VNM", "ZAF", "ZMB"
), class = "factor")), row.names = c(NA, -910L), class = c("data.table", 
"data.frame"))

score 3 · Accepted Answer · answered Mar 24 '21 at 16:47

3

Grouped by 'iso3c', get the count of unique elements in 'var', create a logical vector out of it, get the row index (.I) and subset based on that column

DT1 <- droplevels(DT[DT[, .I[uniqueN(var) >=3],iso3c]$V1])

answered Mar 24 '21 at 16:47

akrun

874,273
37
540
662

1

Another option: `DT[,len_unique := length(unique(var)),iso3c][len_unique>0]` – bouncyball Mar 24 '21 at 16:49

score 1 · Answer 2 · answered Mar 24 '21 at 16:53

1

You can first create a column that counts the number of unique elements per isoc3 group, then only keep those with more than 3 unique var values:

DT[,unique_var := uniqueN(var), by =iso3c]
DT <- DT[unique_var >= 3]

answered Mar 24 '21 at 16:53

Gabe Solomon

365
3
12

Eliminating rows by group, for which certain factor values do not exist

DATA

2 Answers2