How to a create a new dataframe of consolidated values from multiple columns in R

user_16097358 0
Viewed 72

I have a dataframe, df1, that looks like the following:

sample 99_Ape_1 93_Cat_1 87_Ape_2 84_Cat_2 90_Dog_1 92_Dog_2
A 2 3 1 7 4 6
B 5 9 7 0 3 7
C 6 8 9 2 3 0
D 3 9 0 5 8 3

I want to consolidate the dataframe by summing the values based on animal present in the header row, i.e. by "Ape", "Cat", "Dog", and end up with the following dataframe:

sample Ape Cat Dog
A 3 10 10
B 12 9 10
C 15 10 3
D 3 14 11

I have created a list that represents all the animals called "animals_list"

I have then created a list of dataframes that subsets each animal into a separate dataframe with:

animals_extract <- c()

for (i in 1:length(animals_list)){
  species_extract[[i]] <- df1[, grep(animals_list[i], names(df1))]
}

I am then trying to sum each variable in the row by sample:

for (i in 1:length(species_extract)){
  species_extract[[i]]$total <- rowSums(species_extract[[i]])
}

and then create a dataframe 'animal_total' by binding all values in the new 'total' column.

animal_total <- NULL

for (i in 1:length(species_extract)){
  animal_total[i] <- cbind(species_extract[[i]]$total)
}

Unfortunately, this doesn't seem to work at all and I think I may have taken the wrong route. Any help would be really appreciated!

EDIT: my dataframe has over 300 animals, meaning incorporating use of my list of identifiers (animals_list) would be highly appreciated! I would also note that some column names do not follow the structure, "number_animal_number" and therefore I can't use a repetitive search (sorry!).

data-manipulation r for-loop sum
3 Answers

a data.table approach

library(data.table)
library(rlist)
#set data to data.table format
setDT(df1)
# split column 2:n by regex on column names
L <- split.default(df1[,-1], gsub(".*_(.*)_.*", "\\1", names(df1)[-1]))
# Bind together again
data.table(sample = df1$sample, 
           as.data.table(list.cbind(lapply(L, rowSums))))
#    sample Ape Cat Dog
# 1:      A   3  10  10
# 2:      B  12   9  10
# 3:      C  15  10   3
# 4:      D   3  14  11
user_63562780

Update: After clarification: This may work depending on the other names of your animals. but this is a start:

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(
    cols = -sample
  ) %>% 
  mutate(name1 = str_extract(name, '(?<=\\_)(.*?)(?=\\_)')) %>% 
  group_by(sample, name1) %>% 
  summarise(sum=sum(value)) %>% 
  pivot_wider(
    names_from = name1,
    values_from= sum
  )

Output:

  sample   Ape   Cat   Dog
  <chr>  <int> <int> <int>
1 A          3    10    10
2 B         12     9    10
3 C         15    10     3
4 D          3    14    11

First answer: Here is how we could do it with dplyr:

library(dplyr)

df %>% 
  mutate(Cat = rowSums(select(., contains("Cat"))),
         Ape = rowSums(select(., contains("Ape"))),
         Dog = rowSums(select(., contains("Dog")))) %>% 
  select(sample, Cat, Ape, Dog)

  sample   Ape   Cat   Dog
  <chr>  <int> <int> <int>
1 A          3    10    10
2 B         12     9    10
3 C         15    10     3
4 D          3    14    11
user_133216470

An alternative data.table solution

library(data.table)

# Construct data table 
dt <- as.data.table(list(sample = c("A", "B", "C", "D"), 
                         `99_Ape_1` = c(2, 5, 6, 3), 
                         `93_Cat_1` = c(3, 9, 8, 9), 
                         `87_Ape_2` = c(1, 7, 9, 0),
                         `84_Cat_2` = c(7, 0, 2, 5),
                         `90_Dog_1` = c(4, 3, 3, 8),
                         `92_Dog_2` = c(6, 7, 0, 3)))

# Alternatively convert existing dataframe
# dt <- setDT(df)

# Use Regex pattern to drop ids from column names
names(dt) <- gsub("((^[0-9_]{3})|(_[0-9]{1}$))", "", names(dt))

# Pivot long (columns to rows)
dt <- melt(dt, id.vars = "sample")

# Aggregate sample by variable
dt <- dt[, .(value=sum(value)), by=.(sample, variable)]

# Unpivot (rows to colums)
dcast(dt, sample ~ variable)

#     sample Ape Cat Dog
# 1:      A   3  10  10
# 2:      B  12   9  10
# 3:      C  15  10   3
# 4:      D   3  14  11

Alternatively, leaving the column names as is (after comment from OP to previous answer) and assuming that there are multiple observations of the same samples:

dt <- as.data.table(list(sample = c("A", "B", "C", "D", "A"), 
                         `99_Ape_1` = c(2, 5, 6, 3, 1), 
                         `93_Cat_1` = c(3, 9, 8, 9, 1), 
                         `87_Ape_2` = c(1, 7, 9, 0, 1),
                         `84_Cat_2` = c(7, 0, 2, 5, 1),
                         `90_Dog_1` = c(4, 3, 3, 8, 1),
                         `92_Dog_2` = c(6, 7, 0, 3, 1)))

dt

#     sample 99_Ape_1 93_Cat_1 87_Ape_2 84_Cat_2 90_Dog_1 92_Dog_2
# 1:      A        2        3        1        7        4        6
# 2:      B        5        9        7        0        3        7
# 3:      C        6        8        9        2        3        0
# 4:      D        3        9        0        5        8        3
# 5:      A        1        1        1        1        1        1

# Pivot long (columns to rows)
dt <- melt(dt, id.vars = "sample")

# Aggregate sample by variable
dt <- dt[, .(value=sum(value)), by=.(sample, variable)]

# Unpivot (rows to colums)
dcast(dt, sample ~ variable)

#     sample 99_Ape_1 93_Cat_1 87_Ape_2 84_Cat_2 90_Dog_1 92_Dog_2
# 1:      A        3        4        2        8        5        7
# 2:      B        5        9        7        0        3        7
# 3:      C        6        8        9        2        3        0
# 4:      D        3        9        0        5        8        3
user_92352200
Related