How to merge rows in a data frame when the values in a column are the same in R

Question

I have a number of observations from the same unit, and I need to merge the rows. So a data frame like

  data.frame(
  fir =c("001","006","001", "006", "062"),
  sec = c(10,5,6,7,8),
  thd = c(45,67,84,54,23))

fir sec thd
001  10  45
006  5   67
001  6   84
006  7   54
062  8   23

The first column has a 3 digit number representing a unit. I need to add the rows together to get a total for each unit. The other columns are numeric values that need adding together. So the dataframe would look like,

 fir sec thd
001  16  129
006  12  121
062  8   23

I need it to work for any unique number in the first column. Any ideas? Thank you for any help!

Answer 1

welcome this is a classic case of a group by operation, we can group your logic by group in this case we want the sum of the sec and thd columns.

library(tidyverse)

df <- data.frame(
  fir =c("001","006","001", "006", "062"),
  sec = c(10,5,6,7,8),
  thd = c(45,67,84,54,23))

df %>%
  group_by(fir) %>% 
  summarise(sec_sum = sum(sec),
            thd_sum = sum(thd))

Answer 2

We can do a group by 'sum'

library(dplyr)
df1 %>% 
  group_by(fir) %>% 
  summarise_all(sum) 
# A tibble: 3 x 3
#  fir     sec   thd
#  <fct> <dbl> <dbl>
#1 001      16   129
#2 006      12   121
#3 062       8    23

---

Or with aggregate from base R

aggregate(. ~ fir, df1, sum)

data

df1 <- data.frame(
  fir =c("001","006","001", "006", "062"),
  sec = c(10,5,6,7,8),
  thd = c(45,67,84,54,23))