Group by column, summarise other columns, mean

Question

[bgraphit]

Hi I need some advise as to how to go from TEST_play data frame to a data frame that includes individual name, and the average of the counts.1 counts.2 for that Peak.

Example:

 >head(TEST_play)
     name counts.1 counts.2
1 Peak160       97      487
2 Peak160      425      371
3 Peak328        0      104
4 Peak328       13       20
5 Peak344        2       39
6 Peak344        7       63

Desired output

>head(average_TEST_play)
     name counts.1 counts.2
1 Peak160       261    429   
2 Peak328        6.5      62
etc,,,,

,

> sapply(TEST_play, class)
     name  counts.1  counts.2
 "factor" "numeric" "numeric"

[Ram]

What have you tried? Look at dplyr's group_by() and summarise() functions. There are ways to do it in base R too, but this might be easier.

Answer 1

[Ram]

Here's a base R way to do this:

dummy_df<-read.table(text='"name"   "counts.1"  "counts.2"
+ "Peak160" 97  487
+ "Peak160" 425 371
+ "Peak328" 0   104
+ "Peak328" 13  20
+ "Peak344" 2   39
+ "Peak344" 7   63', sep="\t", header=TRUE)

aggregate(cbind(counts.1, counts.2) ~ name, data=dummy_df, FUN = mean)
     name counts.1 counts.2
1 Peak160    261.0      429
2 Peak328      6.5       62
3 Peak344      4.5       51

Using dplyr, that'd be:

library(dplyr)
dummy_df %>% group_by(name) %>% summarise(counts.1 = mean(counts.1), counts2 = mean(counts.2))

# A tibble: 3 x 3
  name    counts.1 counts2
  <fct>      <dbl>   <dbl>
1 Peak160    261       429
2 Peak328      6.5      62
3 Peak344      4.5      51

[cpad0112]

> library(dplyr)
> test %>%
+   group_by(name) %>%
+   summarise_all(mean)
# A tibble: 3 x 3
  name    counts.1 counts.2
  <chr>      <dbl>    <dbl>
1 Peak160    261        429
2 Peak328      6.5       62
3 Peak344      4.5       51

[Ram]

Thank you, TIL summarise_all.

Answer 2

[cpad0112]

with package doBy:

> test=read.csv("test.txt", sep = "\t", stringsAsFactors = F, header = T)
> test
             name counts.1 counts.2
        1 Peak160       97      487
        2 Peak160      425      371
        3 Peak328        0      104
        4 Peak328       13       20
        5 Peak344        2       39
        6 Peak344        7       63
> library(doBy)
> summaryBy(test[,-1] ~ name, test, FUN = mean, keep.names = T)
     name counts.1 counts.2
1 Peak160    261.0      429
2 Peak328      6.5       62
3 Peak344      4.5       51