CODEWITHSUNDEEP
r
Rod Michael Coronel
Rod Michael Coronel

Reputation: 592

How to compute aggregate and append result as new column in R

Given the following data:

data
  a b c
1 x m 1
2 x n 2
3 x m 3
4 y n 4
5 y m 5
6 y n 6

I want to compute an aggregate min of c grouped by a,b:

setNames(aggregate(data$c, by=list(data$a, data$b), min), c("a", "b", "min_c"))

  a b min_c
1 x m     1
2 y m     5
3 x n     2
4 y n     4

However, I want to append "min_c" to the original data, like below:

  a b c min_c
1 x m 1 1
2 x n 2 2
3 x m 3 1
4 y n 4 4
5 y m 5 5
6 y n 6 4

What is the simplest way to do it in R?

Upvotes: 2

Views: 297

Answers (6)

harihara sudan. s
harihara sudan. s

Reputation: 185

ddply(Data, .(a, b), transform, 
+       min_c=min(c))
  a b c min_c
1 x m 1     1
2 x m 3     1
3 x n 2     2
4 y m 5     5
5 y n 4     4
6 y n 6     4

Upvotes: 1

rnso
rnso

Reputation: 24623

Try following: 

merge(data,data2)

Full output:

> data
  a b c
1 x m 1
2 x n 2
3 x m 3
4 y n 4
5 y m 5
6 y n 6
> 
> data2 = setNames(aggregate(data$c, by=list(data$a, data$b), min), c("a", "b", "min_c"))
> data2
  a b min_c
1 x m     1
2 y m     5
3 x n     2
4 y n     4
> 
> merge(data,data2)
  a b c min_c
1 x m 1     1
2 x m 3     1
3 x n 2     2
4 y m 5     5
5 y n 4     4
6 y n 6     4

Upvotes: 0

KFB
KFB

Reputation: 3501

data.table approach

data

library(data.table)
dt = data.table(a = rep(c("x", "y"), each=3),
                b = rep(c("m","n"), 3),
                c = 1:6)

calculate and append

dt[, cmin := min(c), by = "a,b"][]
#    a b c cmin
# 1: x m 1    1
# 2: x n 2    2
# 3: x m 3    1
# 4: y n 4    4
# 5: y m 5    5
# 6: y n 6    4

:= means to assign, [] is to print the result

If the original data is a data frame, like below:

df = data.frame(a = rep(c("x", "y"), each=3),
                b = rep(c("m","n"), 3),
                c = 1:6)

you could use setDT to convert it to data table.

setDT(df)

Upvotes: 3

David Arenburg
David Arenburg

Reputation: 92300

Base R approach

transform(data, minc = ave(c, list(a, b), FUN = min))
#   a b c minc
# 1 x m 1    1
# 2 x n 2    2
# 3 x m 3    1
# 4 y n 4    4
# 5 y m 5    5
# 6 y n 6    4

Upvotes: 2

Prasanna Nandakumar
Prasanna Nandakumar

Reputation: 4335

> zz <- " a b c
+ 1 x m 1
+ 2 x n 2
+ 3 x m 3
+ 4 y n 4
+ 5 y m 5
+ 6 y n 6"
> 
> Data <- read.table(text=zz, header = TRUE)
> dt <- as.data.table(Data)
> 
> dt[, min_c := min(c), by = list(a,b)]
> dt
   a b c min_c
1: x m 1     1
2: x n 2     2
3: x m 3     1
4: y n 4     4
5: y m 5     5
6: y n 6     4

Upvotes: 2

jazzurro
jazzurro

Reputation: 23574

An approach with dplyr would be:

library(dplyr)

mydf %>%
    group_by(a,b) %>%
    mutate(cmin = min(c))

#  a b c cmin
#1 x m 1    1
#2 x n 2    2
#3 x m 3    1
#4 y n 4    4
#5 y m 5    5
#6 y n 6    4

Upvotes: 4

Related Questions