Complex conditional removal/extraction of rows

Question

I have a dataframe, and I wish to run some complex 'if' statements.

To give some background - this is data for different regions in the EU. I want to look at within country variation in 'results'. In the EU, we have different levels of administrative boundaries (called 'region' in the table) - 1 and 2 ('level' in the table). Preferably I want to explore variability at level '1', but some countries have smaller populations and therefore I will have to look at variability at level 2. I have created a shortened dataset to illustrate my point.

df >- structure(list(region = structure(c(2L, 4L, 6L, 8L, 9L, 13L, 
16L, 17L, 18L, 21L, 23L, 26L, 27L, 33L, 34L, 41L, 37L, 43L, 48L, 
50L, 56L, 54L, 59L, 3L, 1L, 7L, 5L, 10L, 11L, 12L, 14L, 15L, 
20L, 19L, 22L, 24L, 25L, 28L, 29L, 30L, 31L, 32L, 35L, 36L, 42L, 
38L, 40L, 39L, 45L, 44L, 46L, 49L, 51L, 47L, 52L, 53L, 57L, 58L, 
55L, 61L, 60L), .Label = c("AT13", "AT2", "AT22", "AT3", "BE10", 
"BE2", "BE21", "BE3", "CH0", "CH01", "CH02", "CH03", "CZ0", "CZ01", 
"CZ02", "DE4", "DEC", "DK0", "DK02", "DK04", "EE0", "EE00", "FI1", 
"FI19", "FI1D", "FI2", "FR1", "FR10", "FR21", "FR22", "FR23", 
"FR24", "FR8", "IE0", "IE01", "IE02", "NL1", "NL23", "NL31", 
"NL33", "NL4", "NL41", "NO0", "NO01", "NO03", "NO07", "PL11", 
"PL2", "PL21", "PL3", "PL41", "PL52", "PL62", "SE1", "SE11", 
"SE2", "SE22", "SE32", "SI0", "SI01", "SI02"), class = "factor"), 
    result = c(24.43, 20.37, 23.53, 25.51, 17.73, 30.61, 46.2, 
    43.75, 25.32, 53.32, 34.25, 46.15, 34.59, 38.06, 18.6, 32.29, 
    28.57, 22.36, 40.98, 34.53, 21.09, 23.89, 43.15, 25.73, 30.06, 
    26.64, 16.78, 18.75, 17.51, 19.58, 28.63, 25.44, 24.29, 30.73, 
    53.32, 37.31, 38.19, 34.59, 53.33, 39.02, 22.92, 35.44, 19.07, 
    18.38, 29.26, 38.6, 31.51, 21.54, 22.93, 17.86, 30.77, 39.87, 
    44.7, 36.8, 37.5, 33.33, 19.05, 30.77, 17.98, 39.71, 45.66
    ), level = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
    2L, 2L, 2L, 2L, 2L), country = structure(c(1L, 1L, 2L, 2L, 
    3L, 4L, 5L, 5L, 6L, 7L, 8L, 8L, 9L, 9L, 10L, 11L, 11L, 12L, 
    13L, 13L, 14L, 14L, 15L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 4L, 
    4L, 6L, 6L, 7L, 8L, 8L, 9L, 9L, 9L, 9L, 9L, 10L, 10L, 11L, 
    11L, 11L, 11L, 12L, 12L, 12L, 13L, 13L, 13L, 13L, 13L, 14L, 
    14L, 14L, 15L, 15L), .Label = c("AT", "BE", "CH", "CZ", "DE", 
    "DK", "EE", "FI", "FR", "IE", "NL", "NO", "PL", "SE", "SI"
    ), class = "factor")), .Names = c("region", "result", "level", 
"country"), class = "data.frame", row.names = c(NA, -61L))

So,

I want an if statement to remove rows IF level = 1 AND there is only one record for a country at level = 1 (i.e. it would remove countries CH, CZ, DK, EE, IE, NO and SI). (so rows 5, 6, 9, 10, 15, 18 and 23 would be removed) Then:
I want a second if statement to remove rows IF level == 2 AND the country IS represented in the data frame at level 1 (having applied statement 1).

I can do simple conditioning if statements:

df<-nuts1_2[!(df$level==1),]

... but am struggling on the second part (i.e. nuts1_2$level==1 & df$country 'only appears once'??)

I should be left with a dataframe, as detailed below:

       region result level country
1     AT2  24.43     1      AT
2     AT3  20.37     1      AT
3     BE2  23.53     1      BE
4     BE3  25.51     1      BE
5     DE4  46.20     1      DE
6     DEC  43.75     1      DE
7     FI1  34.25     1      FI
8     FI2  46.15     1      FI
9     FR1  34.59     1      FR
10    FR8  38.06     1      FR
11    NL4  32.29     1      NL
12    NL1  28.57     1      NL
13    PL2  40.98     1      PL
14    PL3  34.53     1      PL
15    SE2  21.09     1      SE
16    SE1  23.89     1      SE
17   CH01  18.75     2      CH
18   CH02  17.51     2      CH
19   CH03  19.58     2      CH
20   CZ01  28.63     2      CZ
21   CZ02  25.44     2      CZ
22   DK04  24.29     2      DK
23   DK02  30.73     2      DK
24   EE00  53.32     2      EE
25   IE01  19.07     2      IE
26   IE02  18.38     2      IE
27   NO03  22.93     2      NO
28   NO01  17.86     2      NO
29   NO07  30.77     2      NO
30   SI02  39.71     2      SI
31   SI01  45.66     2      SI

In an ideal world, if these rows could be subset into another dataframe called 'deleted' - this would help my record keeping.

Any help on the above would be gratefully appreciated.

Jaap · Accepted Answer

With the data.table package you could do it as follows:

library(data.table)
setDT(df1)[, if(!(level==1 & .N==1)) .SD, by = .(country,level)
           ][, unlev := uniqueN(level), by = country
             ][!(unlev==2 & level==2)][,unlev:=NULL][]

which gives:

    country level region result
 1:      AT     1    AT2  24.43
 2:      AT     1    AT3  20.37
 3:      BE     1    BE2  23.53
 4:      BE     1    BE3  25.51
 5:      DE     1    DE4  46.20
 6:      DE     1    DEC  43.75
 7:      FI     1    FI1  34.25
 8:      FI     1    FI2  46.15
 9:      FR     1    FR1  34.59
10:      FR     1    FR8  38.06
11:      NL     1    NL4  32.29
12:      NL     1    NL1  28.57
13:      PL     1    PL2  40.98
14:      PL     1    PL3  34.53
15:      SE     1    SE2  21.09
16:      SE     1    SE1  23.89
17:      CH     2   CH01  18.75
18:      CH     2   CH02  17.51
19:      CH     2   CH03  19.58
20:      CZ     2   CZ01  28.63
21:      CZ     2   CZ02  25.44
22:      DK     2   DK04  24.29
23:      DK     2   DK02  30.73
24:      EE     2   EE00  53.32
25:      IE     2   IE01  19.07
26:      IE     2   IE02  18.38
27:      NO     2   NO03  22.93
28:      NO     2   NO01  17.86
29:      NO     2   NO07  30.77
30:      SI     2   SI02  39.71
31:      SI     2   SI01  45.66

You can apply the same logic with dplyr:

library(dplyr)
df1 %>% 
  group_by(country,level) %>% 
  mutate(n = n()) %>% 
  filter(!(level==1 & n==1)) %>%
  group_by(country) %>%
  mutate(unlev = length(unique(level))) %>%
  filter(!(unlev==2 & level==2)) %>%
  select(-n, -unlev)

Complex conditional removal/extraction of rows

Answers (2)

Related Questions