Converting Multiple DPLYR Commands into a Single DPLYR Command

[omario]

I am working with the R programming language.

I have the following dataset ("my_data"):

structure(list(idd = 1:50, group_1 = c("B", "B", "A", "B", "B", 
"A", "A", "A", "B", "A", "A", "B", "B", "B", "A", "A", "A", "A", 
"B", "B", "A", "B", "A", "B", "A", "B", "B", "A", "B", "B", "B", 
"A", "B", "A", "B", "B", "A", "A", "A", "A", "A", "B", "B", "B", 
"A", "B", "B", "B", "B", "B"), v1 = c(15.7296737049317, -4.33377704672207, 
-0.551850185265, 2.66888122578048, 12.109072642513, 0.0107927293899017, 
20.7785032320562, -1.98974382507874, 12.1663703518471, 11.4308702978893, 
-0.657500910529805, 5.71376589298221, 3.43820523228653, 19.5939432685761, 
25.5605263610222, -0.407964337882465, 19.3057240854025, 9.24554068987809, 
-9.6719534905096, 2.44096357354807, 14.6114916050676, 11.4510663104787, 
-14.4231132108142, 15.8031868545157, 16.5505199848675, 6.95491162740581, 
2.92431767382703, 29.7157201447823, 9.10001319352251, 9.85982748068076, 
-1.23456937110154, -3.44130123376206, -5.23155771062088, 5.78031789617826, 
23.6092446408098, 27.5379484533487, 25.6836473435279, 22.9675556994775, 
7.62403748556388, -2.24150135680706, 6.72187319859928, -14.1245027627225, 
6.8620712655661, 26.5987870464572, 11.3095310060752, 20.9588868268958, 
14.8934095694391, 2.21089704551347, 27.4355935292935, 9.21612714668934
), group_2 = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 1L, 2L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 
8L, 9L, 10L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 1L, 2L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L)), row.names = c(NA, -50L), class = "data.frame")

head(my_data)

   idd group_1          v1 group_2
1    1       B 15.72967370       1
2    2       B -4.33377705       2
3    3       A -0.55185019       3
4    4       B  2.66888123       4
5    5       B 12.10907264       5
6    6       A  0.01079273       6
7    7       A 20.77850323       7
8    8       A -1.98974383       8
9    9       B 12.16637035       9
10  10       A 11.43087030      10
11  11       A -0.65750091       1
12  12       B  5.71376589       2

For this dataset, I want to perform the following steps in "dplyr":

• For each grouping of 10 rows, find the sum of "v1" for group_1 = "A" and group_2 = "B"
• For each of these groupings, create a new variable ("v2") that is : "A" if sum(group_1 = A) > sum(group_1 = B), "B" if sum(group_1 = A) < sum(group_1 = B) or "0" if sum(group_1 = A) = sum(group_1 = B)

I know how to do this manually in R:

#STEP 1: since my_data has 50 rows, break my_data into 5 groups of 10 rows

rows_1 = my_data[1:10,]
rows_2 = my_data[11:20,]
rows_3 = my_data[21:30,]
rows_4 = my_data[31:40,]
rows_5 = my_data[41:50,]

# STEP 2: find out values of "v2"

library(dplyr)

dplyr_row_1 = data.frame(rows_1 %>% group_by(group_1) %>% summarize(sum = sum(v1)))

dplyr_row_1$v2 = ifelse(dplyr_row_1[1,2] > dplyr_row_1[2,2], "A", ifelse(dplyr_row_1[1,2] < dplyr_row_1[2,2], "B", 0))

dplyr_row_2 = data.frame(rows_2 %>% group_by(group_1) %>% summarize(sum = sum(v1)))

dplyr_row_2$v2 = ifelse(dplyr_row_2[1,2] > dplyr_row_2[2,2], "A", ifelse(dplyr_row_2[1,2] < dplyr_row_2[2,2], "B", 0))

dplyr_row_3 = data.frame(rows_3 %>% group_by(group_1) %>% summarize(sum = sum(v1)))

dplyr_row_3$v2 = ifelse(dplyr_row_3[1,2] > dplyr_row_3[2,2], "A", ifelse(dplyr_row_3[1,2] < dplyr_row_3[2,2], "B", 0))

dplyr_row_4 = data.frame(rows_4 %>% group_by(group_1) %>% summarize(sum = sum(v1)))

dplyr_row_4$v2 = ifelse(dplyr_row_4[1,2] > dplyr_row_4[2,2], "A", ifelse(dplyr_row_4[1,2] < dplyr_row_4[2,2], "B", 0))

dplyr_row_5 = data.frame(rows_5 %>% group_by(group_1) %>% summarize(sum = sum(v1)))

dplyr_row_5$v2 = ifelse(dplyr_row_5[1,2] > dplyr_row_5[2,2], "A", ifelse(dplyr_row_5[1,2] < dplyr_row_5[2,2], "B", 0))

# STEP 3: append "v2" to first 5 files:

rows_1$v2 = dplyr_row_1$v2
rows_2$v2 = dplyr_row_2$v2
rows_3$v2 = dplyr_row_3$v2
rows_4$v2 = dplyr_row_4$v2
rows_5$v2 = dplyr_row_5$v2

# STEP 4: create final file:

final_file = rbind(rows_1,rows_2, rows_3, rows_4, rows_5)

As a result, the final file looks something like this:

  idd group_1          v1 group_2 v2
1    1       B 15.72967370       1  B
2    2       B -4.33377705       2  B
3    3       A -0.55185019       3  B
4    4       B  2.66888123       4  B
5    5       B 12.10907264       5  B
6    6       A  0.01079273       6  B
7    7       A 20.77850323       7  B
8    8       A -1.98974383       8  B
9    9       B 12.16637035       9  B
10  10       A 11.43087030      10  B
11  11       A -0.65750091       1  A

My Question: Can someone please show me how to perform Steps 1 to Step 4 in a single "dplyr" command?

Thanks!

[ibertchen]

I can't make it within one pipe. But this is fun so I tried.

Definitely not a good solution.

# use the same data.frame from the origin post. 

library(dplyr)
library(tidyr)

my_data_copy <- my_data %>% 
  mutate(group3 = case_when(
    idd < 11 ~ 1,
    idd < 21 ~ 2, 
    idd < 31 ~ 3,
    idd < 41 ~4,
    TRUE ~ 5
  ))
  
final_file <-  my_data_copy %>% 
  left_join(my_data_copy %>% 
              group_by(group3, group_1) %>% 
              mutate(sum = sum(v1)) %>% 
              select(group_1, group3, sum) %>% 
              distinct() %>% 
              pivot_wider(names_from = group_1, values_from = sum) %>% 
              mutate(v2 = case_when(
                A > B ~ "A",
                B > A ~ "B",
                TRUE ~ "0"
                )), by="group3") %>% 
  select(-group3:-A)


head(final_file, 11)
#>    idd group_1          v1 group_2 v2
#> 1    1       B 15.72967370       1  B
#> 2    2       B -4.33377705       2  B
#> 3    3       A -0.55185019       3  B
#> 4    4       B  2.66888123       4  B
#> 5    5       B 12.10907264       5  B
#> 6    6       A  0.01079273       6  B
#> 7    7       A 20.77850323       7  B
#> 8    8       A -1.98974383       8  B
#> 9    9       B 12.16637035       9  B
#> 10  10       A 11.43087030      10  B
#> 11  11       A -0.65750091       1  A

^{Created on 2022-03-05 by the reprex package (v2.0.1)}

[olyerickson]

1. I don't think there is a simpler and more "literate" way to do this than ibertchen 's suggestion. It makes sense to create a new dataframe with the mutated tag, esp. if it might be used elsewhere in the code.

2. Why omario did you use such a strange syntax for building the original dataframe? Why not just data.frame() the vectors together?

[nirgrahamuk]

This is not pure dplyr as I use tidyr pivot_wider and purrr map_dfc, but they are all tidyverse libs

final_file <- my_data %>%
  mutate(splitg = (idd - 1) %/% 10) %>%
  group_by(splitg) %>%
  group_split() %>%
  map_dfr(
    ~ {
      expand_grid(.x, {
        .x %>%
          group_by(group_1) %>%
          summarize(sum = sum(v1)) %>%
          pivot_wider(
            names_from = "group_1",
            values_from = "sum"
          ) %>%
          mutate(v2 = case_when(
            A > B ~ "A",
            A < B ~ "B",
            TRUE ~ "0"
          )) %>%
          select(v2)
      })
    }
  ) %>%
  select(-splitg)