This is a test version of Biostars. For the public version, visit https://www.biostars.org.
How do you collapse multiple rows based on multiple columns in r?

So basically I have a sample dataframe that kinda looks like this:

Community Pop_Total Median_Age Under_5 5-9 10-14 15-19 20-24 

Akutan city   NA    NA         NA      NA  NA    NA    71
Alcan Border  NA    NA         2       NA  NA    NA    NA               
Alcan Border  NA    NA         NA      NA  NA    2     NA            
Alcan Border  NA    NA         NA      NA  5     NA    NA
Ambler City   224   NA         NA      NA  NA    NA    NA
Ambler City   NA    NA         NA      17  NA    NA    NA

But with gene names rather than numbers.

Is there a simple way to combine multiple rows based on multiple column data? I've seen a few scripts that say you can combine one duplicate variable in a column based on one or two data columns but I need to do it more large scale (I have ~400 rows with duplicates and ~30 columns (and each column has a large name). Ideally it would look like:

Community Pop_Total Median_Age Under_5 5-9 10-14 15-19 20-24 
Akutan city   NA    NA         NA      NA  NA    NA    71              
Alcan Border  NA    NA         2       NA  5     2     NA            
Ambler City   224   NA         NA      17  NA    NA    NA

AGAIN with gene names rather than numbers1

The following is my code:

df <- Good1_Poor3 %>% spread(key = Gene, value = consequences)

df <- df %>%
  group_by(sample_id) %>%
  summarise_if(
    is.character,
    sum,
    na.rm = TRUE
  )
r genomics
• 2,821 views • link
updated by Kevin Blighe Kevin Blighe • written by ngcatung0 • ngcatung0

ngcatung0 why did you create this post? The post you created earlier already had responses. Please do not do that, it is bad practice and disrespectful towards the users who already invested into the first thread.

Kevin Blighe would you mind moving your very good answer to the other thread so we can keep things concentrated?

• 0 views • link
written by ATpoint ATpoint

Thanks respected Sir Kevin :)

• 0 views • link
written by ATpoint ATpoint

1 answer

Hi, you can just use aggregate():

1, create random data

x <- matrix(rexp(200, rate=.1), ncol=20)
colnames(x) <- paste0('Sample', 1:ncol(x))
rownames(x) <- c(rep('Gene1', nrow(x)/2), rep('Gene2', nrow(x)/2))

x <- data.frame(rownames(x), x)

x[,1:5]

        rownames.x.    Sample1   Sample2   Sample3     Sample4
Gene1         Gene1 10.4025193 18.381352 8.6907223 25.79737747
Gene1.1       Gene1  2.9878178  4.787531 4.2150497  4.05715832
Gene1.2       Gene1  0.6686036  4.581924 2.9965842  2.62120831
Gene1.3       Gene1 12.5532843 21.639870 0.3838961 11.72538552
Gene1.4       Gene1 10.6173038  1.972496 4.9272924  2.54967894
Gene2         Gene2  1.3830771  5.305861 9.5365363  9.01649393
Gene2.1       Gene2  1.5165386  3.320702 0.1239832  3.99256675
Gene2.2       Gene2  3.5713496  5.706538 3.0282564  0.01418063
Gene2.3       Gene2  6.3914021  4.497280 8.0592531 15.96747167
Gene2.4       Gene2  1.5106198 10.709003 3.8570605 48.93099514

2, summarise by mean based on first column

aggregate(
  x[,2:ncol(x)],
  by = x[1],
  function(x) mean(x, na.rm = TRUE))[,-1]

   Sample1   Sample2  Sample3   Sample4  Sample5   Sample6  Sample7   Sample8
1 7.445906 10.272634 4.242709  9.350162 12.22026  8.319015 7.845908  4.310811
2 2.874597  5.907877 4.921018 15.584342 10.15808 10.553271 7.294825 12.786049
   Sample9 Sample10 Sample11 Sample12 Sample13  Sample14  Sample15  Sample16
1 13.62375 3.439321 8.638397 19.25654 2.957501  6.235269  9.059412  8.827654
2 11.48821 4.648529 4.513854 10.92598 8.997001 10.783194 12.008174 12.886687
   Sample17 Sample18 Sample19 Sample20
1 10.520657 13.88170 21.19594 9.318276
2  9.494722 13.22234 10.53380 5.118876
• 0 views • link
written by Kevin Blighe Kevin Blighe

Hi Kevin,

Thanks for your response. However, its not what im looking for. Im trying to create a dataframe similar to the dataframe output below. It shows the collapse of the same variable, 

Community Pop_Total Median_Age Under_5 5-9 10-14 15-19 20-24 
Akutan city   NA    NA         NA      NA  NA    NA    71              
Alcan Border  NA    NA         2       NA  5     2     NA            
Ambler City   224   NA         NA      17  NA    NA    NA
• 0 views • link
updated by Kevin Blighe Kevin Blighe • written by ngcatung0 • ngcatung0

Log in to answer this question.

More posts like this