CODEWITHSUNDEEP
rgroup-bysummarization
Adam_S
Adam_S

Reputation: 770

Add row to dataframe with sum of within group data

I have an example dataframe below. 

eg_data <- data.frame(
time = c("1", "1", "2","2"), 
type = c("long", "short","long", "short"), 
size=c(200,50, 500, 150 ))

I need to create rows which total the values of size, for each time period. I have looked at combinations of aggregate and by, but I cannot get it to work correctly.

An example of what I've tried:

rbind(eg_data, data.frame(time="1 + 2", type="long", size=by(eg_data$size, 
eg_data$time=="long", sum)))

An example of what I want the final dataframe to look like:

eg_data <- data.frame(
time = c("1", "1", "2","2", "1 + 2", "1 + 2"), 
type = c("long", "short","long", "short", "long", "short"), 
size=c(200, 50, 500, 150, 700, 200))

Any help is appreciated, a solution with base R would be really appreciated.

Upvotes: 3

Views: 1586

Answers (2)

Adam_S
Adam_S

Reputation: 770

AntoniosK and Hugo had good answers via dplyr, I also found this one using [] and base R

eg_data <- rbind(eg_data, data.frame(time="1 + 2", type="long", 
size=sum(eg_data[which(eg_data[,2]=="long"),3])))

eg_data <- rbind(eg_data, (data.frame(time="1 + 2", type="short", 
size=sum(eg_data[which(eg_data[,2]=="short"),3]))))

It takes two lines, not as parsimonious, but it adds the sum rows to the dataframe, and does not change any other variable data.

Upvotes: 0

AntoniosK
AntoniosK

Reputation: 16121

eg_data <- data.frame(
  time = c("1", "1", "2","2"), 
  type = c("long", "short","long", "short"), 
  size=c(200,50, 500, 150 ))

library(dplyr)

eg_data %>%
  group_by(type) %>%                               # for each type
  summarise(time = paste(time, collapse = " + "),  # combine times
            size = sum(size)) %>%                  # get sum of sizes
  bind_rows(eg_data, .)                            # add everything after your original dataset (rows)

#    time  type size
# 1     1  long  200
# 2     1 short   50
# 3     2  long  500
# 4     2 short  150
# 5 1 + 2  long  700
# 6 1 + 2 short  200

Upvotes: 6

Related Questions