R replace nested for loops with dplyr when column lookup is from a different data.frame

Question

The heart of this question involves using dplyr when the group_by information comes from a different data.frame than the units being summarized. Example: I have assigned locations to groups elsewhere. Each unique assignment of the set of locations to groups is one plan. There are thousands of plans. I am looking for summary statistics about each plan.

I am doing it in a slow nested for loop and would like to speed the process up as much as possible. I expect I can do this with group_by and summarise, but the syntax eludes me and the examples I am finding all have lookups from the same tibble or data.frame. Replicable example:

# locations (x,y), populations at those locations (popA, popB)   
df <- data.frame(x = rep(1:3, times = 3),
                 y = c(1,1,1,2,2,2,3,3,3),
                 popA = c(1,2,3,4,5,6,7,8,9),
                 popB = c(10,11,12,13,14,15,16,17,18))

# plans (Runs 1 through 3) each plan is a column in the data.frame and the
# value indicates the group to which each location was assigned in that plan  
result < -data.frame(Run1 = c(1,1,1,2,2,2,3,3,3),
                     Run2 = c(1,2,3,1,2,3,1,2,3),
                     Run3 = c(1,1,3,2,2,3,3,3,3))

#The data.frame where I will store my summary statistics.    
#Plan | District | Pop A | Pop B | Total
pop.by.dist <- data.frame(Plan = rep(NA,(max(result$Run1))*length(colnames(result))),
                          District = NA, PopA = NA, PopB = NA, Total = NA)

counter = 1
for(i in 1:length(colnames(result))){ #for every plan
  for(j in 1:max(result)){ #for every district
    tmp <- colSums(df[result[,i]==j,c("popA","popB")])
    pop.by.dist[counter,] <- c(colnames(result)[i],j,tmp,sum(tmp))
    counter <- counter+1
  }
}
pop.by.dist #output has one row per plan * district combination

    #> pop.by.dist
    #  Plan District PopA PopB Total
    #1 Run1        1    6   33    39
    #2 Run1        2   15   42    57
    #3 Run1        3   24   51    75
    #4 Run2        1   12   39    51
    #5 Run2        2   15   42    57
    #6 Run2        3   18   45    63
    #7 Run3        1    3   21    24
    #8 Run3        2    9   27    36
    #9 Run3        3   33   78   111

I know there are a huge number of related questions already here, but the specific need to have the lookup be from another data.frame has been hard for me to locate. I am not a new user and have spent some time looking for a response that I can get to work so before you flag me as a repeat, please just include the code to solve my problem. You might just help the next person.

broti broti · Accepted Answer · 2020-04-18T16:45:57

If there is no problem with just binding the two data frames, do this first:

new_df <- cbind(df, result)

Then you bring you data into long format, then group, then summarise:

new_df %>% pivot_longer(c(Run1, Run2, Run3), 
                        names_to = "Plan", 
                        values_to = "District") %>% 
  group_by(Plan, District) %>% 
  summarise_at(vars(popA, popB), sum) %>%
  mutate(Total = popA + popB)

# A tibble: 9 x 5
# Groups:   Plan [3]
  Plan  District  popA  popB Total
  <chr>    <dbl> <dbl> <dbl> <dbl>
1 Run1         1     6    33    39
2 Run1         2    15    42    57
3 Run1         3    24    51    75
4 Run2         1    12    39    51
5 Run2         2    15    42    57
6 Run2         3    18    45    63
7 Run3         1     3    21    24
8 Run3         2     9    27    36
9 Run3         3    33    78   111

R replace nested for loops with dplyr when column lookup is from a different data.frame

1 Answers