Adding rows with multiplication-CodePudding

I've got data as below

ID X1 X2 X3 X4
A  3  4  5  6 
B  5  7  9  2
C  8  5  2  6

I need to add additional rows, which contains multiplication of selected rows. Data should be like below.

ID X1 X2 X3 X4
A  3  4  5  6 
B  5  7  9  2
C  8  5  2  6
AB 15 28 45 12
BC 40 35 18 12

How can I convert it? Thanks, anba.

CodePudding user response：

Check this out: basic idea is that you perform the multiplications for subsets resulting in two temporary data frames and then bind the three data frames back together:

library(dplyr)

# first subset:
df1 <- df %>%
  filter(ID %in% c("A", "B")) %>%
  summarise(across(starts_with("X"), ~.x * lag(.x))) %>%
  mutate(ID = "AB")

# second subset:
df2 <- df %>%
  filter(ID %in% c("B", "C")) %>%
  summarise(across(starts_with("X"), ~.x * lag(.x))) %>%
  mutate(ID = "BC")

# bind original `df` together with `df1` and `df2`:
bind_rows(df, df1 %>% filter(complete.cases(.)), df2 %>% filter(complete.cases(.))
  ID X1 X2
1  A  3  4
2  B  5  7
3  C  7  5
4 AB 15 28
5 BC 35 35

Data:

df <- data.frame(
  ID = c("A", "B", "C"),
  X1 = c(3,5,7),
  X2 = c(4,7,5)
)