Merger and partial addition of rows without groups in R

Question

The following is a reprex of my problem written for dplyr:

library(tidyverse)

df <- tibble(State = c("A", "A", "A", "A", "A", "A", "B", "B", "B"),
             District_code = c(1:9),
                 District = c("North", "West", "North West", "South", "East", "South East", 
                              "XYZ", "ZYX", "AGS"), 
                 Population = c(1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 
                                7000000, 8000000, 9000000))

df
#> # A tibble: 9 x 4
#>   State District_code District   Population
#>   <chr>         <int> <chr>           <dbl>
#> 1 A                 1 North         1000000
#> 2 A                 2 West          2000000
#> 3 A                 3 North West    3000000
#> 4 A                 4 South         4000000
#> 5 A                 5 East          5000000
#> 6 A                 6 South East    6000000
#> 7 B                 7 XYZ           7000000
#> 8 B                 8 ZYX           8000000
#> 9 B                 9 AGS           9000000

For some States, I need to merge Districts using names into fewer geographical categories. In particular, State A should only have: "North - West - North West" and "South - East - South East". Some variables like Population must be added; but others like District_code should acquire NA. I have found this example of operations across rows but it's not quite the same. Grouping doesn't seem to apply.

The final result should be something like this:

new_df
#> # A tibble: 5 x 4
#>   State District_code District                  Population
#>   <chr>         <int> <chr>                          <dbl>
#> 1 A                NA North - West - North West    5000000
#> 2 A                NA South - East - South East   15000000
#> 3 B                 7 XYZ                          7000000
#> 4 B                 8 ZYX                          8000000
#> 5 B                 9 AGS                          9000000

In the real dataframe there are a number of variables like Population that must be added as well as a number of other variables like District_code, which will have to acquire NA values.

Thanks heaps for any help!

kath · Accepted Answer · 2018-10-09T14:42:08.840

You can use fct_collapse to specify the new factor levels and then use summarise on the new groups.

df %>%
  mutate(District = 
           fct_collapse(District, 
                        "North - West - North West" = c("North", "West", "North West"), 
                        "South - East - South East" = c("South", "East", "South East"))) %>% 
  group_by(State, District) %>% 
  summarise(Population = sum(Population), 
            District_code = ifelse(n() > 1, NA_real_, District_code))

# A tibble: 5 x 3
# Groups:   State [?]
#   State District                  Population
#   <chr> <fct>                          <dbl>
# 1 A     South - East - South East   15000000
# 2 A     North - West - North West    6000000
# 3 B     AGS                          9000000
# 4 B     XYZ                          7000000
# 5 B     ZYX                          8000000

If you want the change of the District only for some specific State you can add a case_when or if_else like this and also condition the summarising function on the type of the column (here double for Population as oppose to integer for the District)

df %>%
  mutate(District = 
           case_when(State == "A" ~ 
                       fct_collapse(District, 
                                    "North - West - North West" = c("North", "West", "North West"), 
                                    "South - East - South East" = c("South", "East", "South East")), 
                     TRUE ~ factor(District))) %>% 
  group_by(State, District) %>% 
  summarise_all(funs({if(is.double(.)) {
    sum(.) 
  } else {
    if (length(unique(.)) > 1) {
      NA
    } else {
      unique(.)
    }
  }}))

Thank you Kath! I think your second example makes it truly expandable. If I understand correctly only those columns that are dbls are summed; the rest are either turned into NA when two or more have been grouped or keep their value. Exactly what I need! I'm getting an odd error, though: Column `DISTRICT_CODE` can't promote group 138 to character. Something in the evaluation is not quite working? — Fons MA, Oct 10 '18 at 13:39
That's odd indead. Is the `DISTRICT_CODE` column an integer as in your example? — kath, Oct 10 '18 at 15:22
`DISTRICT_CODE` is actually character, which I hadn't realised would matter in your solution; changing it to int does make the whole thing run... but then another character variable `REGION_TYPE` ["Urban", "Rural"] is simply all set to NA? Not quite sure how either of those things is happening, since the code doesn't seem to distinguish between different types of variables... :-/ Thanks for any further clues! — Fons MA, Oct 11 '18 at 22:45

score 2 · Answer 2 · answered Oct 09 '18 at 14:31

For some States, I need to merge Districts using names into fewer geographical categories. In particular, State A should only have: "North - West - North West" and "South - East - South East".

You'll need to write down the grouping rules, eg...

merge_rules = list(
  list(State = "A", District = c("North", "West", "North West")),
  list(State = "A", District = c("South", "East", "South East"))
)

Some variables like Population must be added; but others like District_code should acquire NA.

I would do this by putting the merge rules in a table; doing calculations after the merge; and rbind-ing on the unmerged rows. Here's the data.table way...

library(data.table)
DT  = data.table(df)
mDT = rbindlist(lapply(merge_rules, as.data.table), id = "g")

gDT = DT[mDT, on=.(State, District)][, .(
  District_code = District_code[NA_integer_],
  District = paste(District, collapse = " - "),
  Population = sum(Population)
), by=.(g, State)]

rbind(
  DT[!mDT, on=.(State, District)],
  gDT[, !"g"]
)[order(State, District)]

   State District_code                  District Population
1:     A            NA North - West - North West    6.0e+06
2:     A            NA South - East - South East    1.5e+07
3:     B             9                       AGS    9.0e+06
4:     B             7                       XYZ    7.0e+06
5:     B             8                       ZYX    8.0e+06

And, I guess, the tidyverse way is similar:

mtib = bind_rows(lapply(merge_rules, as.tibble), .id = "g")

gtib = right_join(df, mtib, by=c("State", "District")) %>% 
  group_by(g, State) %>% summarise(
    District_code = District_code[NA_integer_],
    District = paste(District, collapse = " - "),
    Population = sum(Population)    
  )

bind_rows(
  anti_join(df, mtib, by=c("State", "District")),
  gtib %>% ungroup %>% select(-g)
) %>% arrange(State, District)

# A tibble: 5 x 4
  State District_code District                  Population
  <chr>         <int> <chr>                          <dbl>
1 A                NA North - West - North West    6000000
2 A                NA South - East - South East   15000000
3 B                 9 AGS                          9000000
4 B                 7 XYZ                          7000000
5 B                 8 ZYX                          8000000

score 0 · Answer 3 · answered Oct 09 '18 at 14:34

Here's one way to get the aggregated Populations for State A:

df %>% 
  filter(State == "A") %>%
  mutate(`North - West - North West` = (District == "North"|District == "West"|District == "North West"), 
         `South - East - South East` = (District == "South"|District == "East"|District == "South East")) %>% 
  gather(key = Districts, value = present, 5:6) %>% 
  filter(present != FALSE) %>% 
  group_by(Districts) %>% 
  summarise(Population = sum(Population))

Which gives the output:

  Districts          Population
  <chr>                   <dbl>
1 North - West - No…    6000000
2 South - East - So…   15000000

Someone should be able to help us put the above into the original df.

Merger and partial addition of rows without groups in R

3 Answers3