CODEWITHSUNDEEP
r
suny
suny

Reputation: 119

Calculate summation of multiple values in row

Hi i have a i/p data frame as below 

df = data.frame('value' = c("(1_00),(0_04),(1_08),(0_12),(1_14)","(1_15),(0_22),(1_29)",
                            "(0_30),(1_38),(0_40),(1_44)","(0_45),(1_57),(0_59)",
                            "(0_15),(1_21),(0_26),(0_29)"),stringsAsFactors = F)

From the above data frame i am unable to find out the column "output" which comes from column "sample_calc" The "sample_calc" is calculated as below 

for row:1-  i.e `(1_00),(0_04),(1_08),(0_12),(1_14)` calculated as second set  
 of character in second block after "_" (04) minus second set (00) in the first 
 block multply by first set in   first block  (i.e: (4-0)*1 )    
                  similarly from 3rd block to 2nd block .finally we need need to   add all blocks.`(4-0)*1 + (8-4)*0 +(12-8)*1 + (14-12)*0`

o/p df1

df1 = data.frame('value' = c("(1_00),(0_04),(1_08),(0_12),(1_14)",
                             "(1_15),(0_22),(1_29)",
                             "(0_30),(1_38),(0_40),(1_44)",
                             "(0_45),(1_57),(0_59)","(1_00),(0_07),(1_14)",
                             "(0_15),(1_21),(0_26),(0_29)"),
                              "sample_calc"=c("(4-0)*1 + (8-4)*0 +(12-8)*1 + (14-12)*0",
                                              "(22-15)*1 + (29-22)*0",
                                              "(38-30)*0 + (40-38)*1 + (44-40)*0",
                                              "(57-45)*0 + (59-57)*1",
                                              "(7-0)*1 + (14-7)*0",
                                              "(21-15)*0  + (26-21)*1 + (29-26)*0"),
                            "output"=c(8,7,2,2,7,5),stringsAsFactors = F)

Upvotes: 1

Views: 86

Answers (3)

moodymudskipper
moodymudskipper

Reputation: 47350

Here's a base R solution:

df$output <-
  sapply(strsplit(gsub('[()]','',df$value),'[_,]'),
       function(x) {
         x<-as.numeric(x)
         sum(-x[!seq_along(x)%%2]*diff(c(0,head(x[!!seq_along(x)%%2],-1),0)))})
# df
#                                value output
# 1 (1_00),(0_04),(1_08),(0_12),(1_14)      8
# 2               (1_15),(0_22),(1_29)      7
# 3        (0_30),(1_38),(0_40),(1_44)      2
# 4               (0_45),(1_57),(0_59)      2
# 5        (0_15),(1_21),(0_26),(0_29)      5

The tidyverse equivalent:

library(tidyverse)

df %>%
  rowid_to_column() %>%
  separate_rows(value,sep=",") %>%
  mutate_at("value",~gsub('[()]','',.x)) %>%
  separate(value,c("a","b"),convert = T) %>%
  group_by(rowid) %>%
  mutate_at("a",~-diff(c(0,.x[-length(.x)],0))) %>%
  summarize(output = sum(a*b)) %>%
  select(-rowid) %>%
  cbind(df,.)

#                                value output
# 1 (1_00),(0_04),(1_08),(0_12),(1_14)      8
# 2               (1_15),(0_22),(1_29)      7
# 3        (0_30),(1_38),(0_40),(1_44)      2
# 4               (0_45),(1_57),(0_59)      2
# 5        (0_15),(1_21),(0_26),(0_29)      5

Upvotes: 0

Gwang-Jin Kim
Gwang-Jin Kim

Reputation: 10163

process.df.column <- function(df, col) {
  blocks <- function(s) unlist(strsplit(s, ","))
  unblock <- Vectorize(function(bs) gsub("\\)", "", gsub("\\(", "", bs)))
  values.l <- lapply(lapply(t(df[, "value", drop = FALSE]), blocks), unblock)
  get.2nd <- function(bs) as.numeric(unlist(strsplit(bs, "_"))[2])
  get.1st <- function(bs) as.numeric(unlist(strsplit(bs, "_"))[1])
  process.row <- function(bs.vec) {
    l <- length(bs.vec)
    second.seconds <- sapply(bs.vec, get.2nd)[2:l]
    first.seconds  <- sapply(bs.vec, get.2nd)[1:(l - 1)]
    first.firsts   <- sapply(bs.vec, get.1st)[1:(l - 1)]
    (second.seconds - first.seconds) * first.firsts
  }
  sapply(lapply(values.l, process.row), sum)
}

process.df.column(df1, "value")
[1] 8 7 2 2 7 5

Upvotes: 0

pogibas
pogibas

Reputation: 28379

First I would turn character string into numeric string with following code:

foo <- lapply(strsplit(gsub("\\(|\\)", "", df$value), ","),
              function(x) as.numeric(unlist(strsplit(x, "_"))))

foo contains same information but in numbers:

foo[1:2]    

[[1]]
 [1]  1  0  0  4  1  8  0 12  1 14

[[2]]
[1]  1 15  0 22  1 29

Next we just need to apply your calculation on sublists of foo (we get "IDs" (y) of elements in "second set" and apply wanted logic: sum((x[y] - x[y - 2]) * x[y - 3]))

sapply(foo, function(x) {y <- seq(4, length(x), 2); 
                         sum((x[y] - x[y - 2]) * x[y - 3])})

[1] 8 7 2 2 7 5

Upvotes: 1

Related Questions