This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Group by column, summarise other columns, mean

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"
r
• 1,604 views • link
updated by zx8754 zx8754 • written by bgraphit • bgraphit

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.

• 0 views • link
written by Ram Ram

3 answers

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
• 0 views • link
written by Ram Ram 
> 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
• 0 views • link
written by cpad0112 cpad0112

Thank you, TIL summarise_all.

• 0 views • link
written by Ram Ram

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
• 0 views • link
written by cpad0112 cpad0112

You can use the "apply" function instead of sapply, to calculate an average across rows:

name        counts.1 counts.2
1 Peak160       10        0
2 Peak160       10        0
3 Peak328       10        0
4 Peak328       10        0
5 Peak344       10        0
6 Peak344       10        0



df$average<-apply(df[,2:3], 1, mean)

In this line of code, df[,2:3] specifies the two numeric columns in the data frame. The next argument "1" tells apply to calculate across rows. 

     name     counts.1 counts.2 average
1 Peak160       10        0       5
2 Peak160       10        0       5
3 Peak328       10        0       5
4 Peak328       10        0       5
5 Peak344       10        0       5
6 Peak344       10        0       5
• 0 views • link
written by adam.faranda adam.faranda

How does this address the aggregation part of the question? The second df still has multiple rows per unique value of name

• 0 views • link
written by Ram Ram

Log in to answer this question.

More posts like this