Sum column based on conditions in another column in a data frame

Question

I have a data frame, which contains two columns, Time and Response

df = cbind.data.frame(
                     Time = c(1, 1.2, 1.9, 2.2, 2.9, 3.1, 3.2, 3.2, 3.2, 3.6, 3.9, 4, 5.1, 5.99), 
                     Response = c(1, 1, 1, 2, 3, 3, 3, 4, 3.5, 3.6, 3.3, 6, 11, 13)
                     )

I want to transform it by summing the Response within the same minute (Time) . [1-2), [2-3), [3-4), [4-5), and [5 and above].

The expected data frame will be

dfe = cbind.data.frame(
                      time.range = c(1, 2, 3, 4, 5), 
                      Response = c(3, 5, 19.4, 6, 24)
                      )

Ronak Shah · Accepted Answer · 2019-03-28 10:42:06Z

6

We can use floor to group it for every minute

library(dplyr)

df %>%
  group_by(minute = floor(Time)) %>%
  summarise(Response = sum(Response))

#   minute Response
#    <dbl>    <dbl>
#1      1      3  
#2      2      5  
#3      3     20.4
#4      4      6  
#5      5     24

Using aggregate in base R

aggregate(Response~floor(Time), df, sum)

Also with tapply

tapply(df$Response, floor(df$Time), sum)

And for completion data.table option

library(data.table)
setDT(df)[,sum(Response), by = floor(Time)]

edited Mar 28, 2019 at 10:42

answered Mar 28, 2019 at 10:32

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

akrun · Accepted Answer · 2019-03-28 17:02:03Z

1

We can use rowsum from base R

rowsum(df$Response, as.integer(df$Time))
#   [,1]
#1  3.0
#2  5.0
#3 20.4
#4  6.0
#5 24.0

answered Mar 28, 2019 at 17:02

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Sum column based on conditions in another column in a data frame

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related