CODEWITHSUNDEEP
r
BillyJean
BillyJean

Reputation: 1587

calculate conditional average of a dataframe-vector

My dataset has the following form

Name                 year                          val    
”a”                     1                           25    
”a”                     1                           75    
”a”                     2                           20    
”a”                     2                           40    
”a”                     2                           60    
”a”                     3                           50    

”b”                     1                           20    
”b”                     2                           10    
”b”                     2                           20    
”b”                     2                           30
”b”                     3                           40
”b”                     3                           60

So it consists of a name, year and a value. I would like to find the average of the values (val) for each year and grouped by name. I.e., I am interested in getting

Name                  year         average                terms in the average
”a”                     1            50                       2
”a”                     2            40                       3
”a”                     3            NA                       1

”b”                     1            NA                       1
”b”                     2            20                       3
”b”                     3            50                       2

In the average-column it says NA whenever there are less than 2 terms and I have furthermore added a fourth column that contains the number of terms used in the average.

This is quite a few operations and they can naturally be done in a for-loop. But what is the professional R-way to solve such a problem?

The data

df = structure(list(name = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L, 2L, 2L), .Label = c("a", "b"), class = "factor"), 
    year = c(1, 1, 2, 2, 2, 3, 1, 2, 2, 2, 3, 3), val = c(25, 
    75, 20, 40, 60, 50, 20, 10, 20, 30, 40, 60)), .Names = c("name", 
"year", "val"), row.names = c(NA, -12L), class = "data.frame")

Upvotes: 0

Views: 418

Answers (3)

d.b
d.b

Reputation: 32548

aggregate(df$val, by = list(df$name, df$year), function(x)
                        c(mean = mean(x)*NA^(!length(x)>1), n = length(x)))
#  Group.1 Group.2 x.mean x.n
#1       a       1     50   2
#2       b       1     NA   1
#3       a       2     40   3
#4       b       2     20   3
#5       a       3     NA   1
#6       b       3     50   2

Upvotes: 1

LyzandeR
LyzandeR

Reputation: 37879

One way with data.table:

library(data.table)
#group by name and year and calculate average
df2 <- setDT(df)[, list(average = mean(val), terms = .N), by = c('Name', 'year')]
#NA if terms less than 2
df2[terms < 2, average := NA]

Output:

   Name year average terms
1:    a    1      50     2
2:    a    2      40     3
3:    a    3      NA     1
4:    b    1      NA     1
5:    b    2      20     3
6:    b    3      50     2

Upvotes: 2

yeedle
yeedle

Reputation: 5008

The dplyr way: 


library(dplyr)

df %>%
  group_by(name, year) %>% 
  summarize(average = ifelse(n() < 2, NA, mean(val)), 
            `terms in the average` = n())
#> Source: local data frame [6 x 4]
#> Groups: name [?]
#> 
#>     Name  year average `terms in the average`
#>   <fctr> <int>   <dbl>                  <int>
#> 1    ”a”     1      50                      2
#> 2    ”a”     2      40                      3
#> 3    ”a”     3      NA                      1
#> 4    ”b”     1      NA                      1
#> 5    ”b”     2      20                      3
#> 6    ”b”     3      50                      2

Upvotes: 2

Related Questions