CODEWITHSUNDEEP
rfilteringcalculated-columns
SPI_4324
SPI_4324

Reputation: 15

Calculate sum column filtering identical values on multiple columns

I have data for multiple columns (S1,S2,S3) and I'm trying to create a sum column (result). I want to sum values for each row that has identical values in S1, S2 and S3 columns. Here is a sample data and the result I'm looking for.

S1 <- c(1,1,1,0,1,0)
S2 <- c(1,1,1,0,1,0)
S3 <- c(1,0,0,0,0,0)
value <- c(9,5,3,2,4,1)
result <- c(9,12,12,3,12,3)
df <- data.frame(S1,S2,S3,value,result)
df

  S1 S2 S3 value result
1  1  1  1     9      9
2  1  1  0     5     12
3  1  1  0     3     12
4  0  0  0     2      3
5  1  1  0     4     12
6  0  0  0     1      3

I tried using rowwise() and sapply() functions but I couldn't get filtering to work or couldn't get values for each row. I'm looking for a solution that can be used for larger amount of rows and columns.

Upvotes: 0

Views: 35

Answers (2)

akrun
akrun

Reputation: 886928

Using data.table

library(data.table)
setDT(df)[, result := sum(value), by = .(S1, S2, S3)]

-output

> df
   S1 S2 S3 value result
1:  1  1  1     9      9
2:  1  1  0     5     12
3:  1  1  0     3     12
4:  0  0  0     2      3
5:  1  1  0     4     12
6:  0  0  0     1      3

Upvotes: 1

Ma&#235;l
Ma&#235;l

Reputation: 51894

You can group_by S1 to S3 and sum value:

library(dplyr)
df %>% 
  group_by(across(S1:S3)) %>% 
  mutate(result = sum(value)) %>% 
  ungroup()

     S1    S2    S3 value result
1     1     1     1     9      9
2     1     1     0     5     12
3     1     1     0     3     12
4     0     0     0     2      3
5     1     1     0     4     12
6     0     0     0     1      3

Or in base R:

df$result <- with(df, ave(value, paste0(S1, S2, S3), FUN = sum))

Upvotes: 1

Related Questions