How to collapse rows in a dataframe?

#1

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?

0 Likes

#2

Welcome to the community!

Are you trying to find the sum of the column elements? You can check the colSums function.

0 Likes

#3

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?

0 Likes

#4

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:

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

1 Like

#5

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

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

0 Likes

#6

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.

0 Likes

#7

Nevermind, got it to work! Thanks!

0 Likes

#8

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:

0 Likes

#9

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

0 Likes

#10

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)

0 Likes

#11

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)

1 Like

#12

Thank you, it works!

0 Likes

closed #13

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes