How to collapse rows in a dataframe?

[Rigel]

Hi!

Was wondering if anybody knew how to do this. I have a dataframe with 2 columns and 20 rows and would like to add the values of both columns together from some rows. Say, for example, that df[1,1] = 3, df[2,1] = 2, df[1,2] = 4 and df[2,2] = 5, then I would like to collapse row 1 and 2 to get only 1 row where df[1,1] = 5 and df[1,2] = 9.
Is there a smart way to do this?

[Rigel]

Thank you. No, I'm trying to modify the existing dataframe and combine row 1 and 2 into a single row with each of the values in the columns from row 1 and 2 added together. I don't know if this is possible?

[Yarnabrina]

Ok, so you've more than two rows? Then, I think it does what you're trying to do.

df <- data.frame(a = 1:5,
                 b = 11:15,
                 c = 21:25)

df
#>   a  b  c
#> 1 1 11 21
#> 2 2 12 22
#> 3 3 13 23
#> 4 4 14 24
#> 5 5 15 25

df[1,] <- (df[1,] + df[2,])

df <- df[-2,]

df
#>   a  b  c
#> 1 3 23 43
#> 3 3 13 23
#> 4 4 14 24
#> 5 5 15 25

^{Created on 2019-03-20 by the reprex package (v0.2.1)}

But most probably not what you're looking for. It's anything but smart.

You can check this SO post:

How to combine two rows in R?

For your future posts, please ask with a reproducible example.

[Rigel]

Thanks, but when I try to do that, I get the error:

Error in dimnames(x) <- dnx : 'dimnames' applied to non-array

[Rigel]

Oh nvm I found out why. Do you know how I can extract the values in the second row in the table here:

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
0 13 15 16 7 10 4 2 1 2 1 1 0 0 1 0 2 0 0 0 1

I want to extract these numbers and add them as a column in my dataframe, and i did it before just with data.frame(numbers = tablename), but this won't allow me to collapse the rows, then, because the data types are mixed up.

[Rigel]

Nevermind, got it to work! Thanks!

[andresrcs]

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …

[Rigel]

Sure, but is there an easier way to combine 8 rows, for example, instead of writing + each time?

[Yarnabrina]

Are you looking for something like this?

df <- data.frame(a = 1:15,
                 b = 11:25,
                 c = 21:35)

df
#>     a  b  c
#> 1   1 11 21
#> 2   2 12 22
#> 3   3 13 23
#> 4   4 14 24
#> 5   5 15 25
#> 6   6 16 26
#> 7   7 17 27
#> 8   8 18 28
#> 9   9 19 29
#> 10 10 20 30
#> 11 11 21 31
#> 12 12 22 32
#> 13 13 23 33
#> 14 14 24 34
#> 15 15 25 35

indices_of_rows_to_merge <- c(1, 4, 9, 3, 2, 8, 6, 7)

index_of_row_where_to_merge <- 2

df[index_of_row_where_to_merge,] <- colSums(x = df[indices_of_rows_to_merge,])

df <- df[- indices_of_rows_to_merge[!(indices_of_rows_to_merge %in% index_of_row_where_to_merge)],]

df
#>     a   b   c
#> 2  40 120 200
#> 5   5  15  25
#> 10 10  20  30
#> 11 11  21  31
#> 12 12  22  32
#> 13 13  23  33
#> 14 14  24  34
#> 15 15  25  35

^{Created on 2019-03-20 by the reprex package (v0.2.1)}

[andresrcs]

I had the same indexing idea, here is an example implemented with dplyr

df <- data.frame(a = 1:20,
                 b = 21:40,
                 c = 41:60)
library(dplyr)
index <- rep(1:ceiling(dim(df)[1]/8), each = 8)[1:dim(df)[1]]
df %>% 
    cbind(index) %>% 
    group_by(index) %>%
    summarise_all(sum) %>% 
    select(-index)
#> # A tibble: 3 x 3
#>       a     b     c
#>   <int> <int> <int>
#> 1    36   196   356
#> 2   100   260   420
#> 3    74   154   234

^{Created on 2019-03-20 by the reprex package (v0.2.1)}

[Rigel]

Thank you, it works!