CODEWITHSUNDEEP
rtime-seriesrowaggregate
user8435999
user8435999

Reputation:

How to sum time-series data rows by group?

My dataset looks like this:

block <- c(1,1,2,2,3,3,4,4)
treatment <- c(1,1,2,2,1,1,2,2)
type <- c("adult1","adult2","adult1","adult2","adult1","adult2","adult1","adult2")
t1 <- c(1,1,2,2,3,3,4,4)
t2 <- c(1,1,2,2,3,3,4,4)
t100 <- c(1,1,2,2,3,3,4,4)
df <- data.frame(block,treatment, type,t1,t2,t100)

I wish to sum the adults for each time point (t1,t2,t100) only with respect to the block. Here is what I want the final output to llok like

block <- c(1,2,3,4)
treatment <- c(1,2,1,2)
type <- c("adult","adult","adult","adult")
t1 <- c(2,4,6,8)
t2 <- c(2,4,6,8)
t100 <- c(2,4,6,8)
df <- data.frame(block,treatment,type,t1,t2,t100

)

Here is my attempt using aggregate function:

aggregate(df[,3:5], by = list(df$block), FUN = sum)

I get an error message saying that "arguments must be of the same length".

Upvotes: 0

Views: 2310

Answers (2)

acylam
acylam

Reputation: 18661

With aggregate you can use a formula to sum up t1:t100 and group by block and treatment:

df_final = aggregate(cbind(t1, t2, t100) ~ block + treatment, data = df, sum)
df_final$type1 = "adult"

Result:

  block treatment t1 t2 t100 type1
1     1         1  2  2    2 adult
2     3         1  6  6    6 adult
3     2         2  4  4    4 adult
4     4         2  8  8    8 adult

Or you can do this with dplyr:

library(dplyr)

df %>%
  group_by(block, treatment) %>%
  summarize_at(vars(t1:t100), sum) %>%
  mutate(type1 = "adult")

or 

df %>%
  group_by(block, treatment) %>%
  summarize_at(vars(2:4), sum) %>%
  mutate(type1 = "adult")

Result:

# A tibble: 4 x 6
# Groups:   block [4]
  block treatment    t1    t2  t100 type1
  <dbl>     <dbl> <dbl> <dbl> <dbl> <chr>
1     1         1     2     2     2 adult
2     2         2     4     4     4 adult
3     3         1     6     6     6 adult
4     4         2     8     8     8 adult

You can also use data.table, which supports column indexing:

library(data.table)

setDT(df)[, lapply(.SD, sum), by=.(block, treatment), .SDcols=4:6]

Result:

   block treatment t1 t2 t100
1:     1         1  2  2    2
2:     2         2  4  4    4
3:     3         1  6  6    6
4:     4         2  8  8    8

Upvotes: 1

Maurits Evers
Maurits Evers

Reputation: 50668

Solution in base R:

df <- cbind.data.frame(
    aggregate(cbind(t1, t2, t100) ~ block + treatment, data = df, FUN = sum),
    type = "adult");
#  block treatment t1 t2 t100  type
#1     1         1  2  2    2 adult
#2     3         1  6  6    6 adult
#3     2         2  4  4    4 adult
#4     4         2  8  8    8 adult

Note: They key is here to cbind relevant columns.

Benchmarking comparison

Below are the results from a microbenchmark of all three solutions (base R, dplyr, data.table).

# Sample dataframe with 1000 rows
df <- cbind.data.frame(
    block = rep(seq(1, 1000), each = 2),
    treatment = rep(c(1, 1, 2, 2), length.out = 250),
    type = rep(c("adult1", "adult2"), length.out = 500),
    t1 = rep(seq(1, 1000), each = 2),
    t2 = rep(seq(1, 1000), each = 2),
    t100 = rep(seq(1, 1000), each = 2));


# Benchmarking results
require(microbenchmark);
require(dplyr);
require(magrittr);
require(data.table);
microbenchmark(
    baseR = cbind.data.frame(
        aggregate(cbind(t1, t2, t100) ~ block + treatment, data = df, FUN = sum),
        type = "adult"),
    dplyr = df %>%
        group_by(block, treatment) %>%
        summarize_at(vars(t1:t100), sum) %>%
        mutate(type1 = "adult"),
    datatable = setDT(df)[, lapply(.SD, sum), by=.(block, treatment), .SDcols=4:6]
)
#Unit: microseconds
#      expr       min         lq       mean     median        uq       max neval
#     baseR 13817.627 14040.4835 14931.4202 14278.8220 15026.413 42347.511   100
#     dplyr  6698.983  7076.6360  8459.7861  7240.1680  7486.245 73401.747   100
# datatable   463.837   500.6555   663.5425   576.3075   597.443  9015.664   100

Upvotes: 0

Related Questions