Home > Net >  Efficient way to create a dataframe with multiple summary columns based on a grouped dataframe using
Efficient way to create a dataframe with multiple summary columns based on a grouped dataframe using

Time:09-21

I have a dataframe similar to this dummy:

dframe <- structure(list(id = c("294361-7349174-75411122", "294365-7645230-95464222", 
"291915-7345264-75464222", "291365-7345074-75164202", "594165-7345274-78444212", 
"234385-7335274-75464229", "734515-1345274-95464892", "201365-8345274-78464232", 
"294365-7315971-75464120", "591365-7345374-75464222", "394365-7345204-75411022", 
"494305-7345273-75464222", "291161-7345271-75461210", "294035-7345201-75464292", 
"298365-7345279-78864223", "294365-7345274-15964293", "294395-7345274-69464299", 
"899965-1345294-95464222", "194365-7145274-75464222", "194361-7349231-75464222", 
"294365-7345274-75464122", "191315-1345274-13464322", "794365-7349274-75464292", 
"214365-8318274-75464222", "394363-8341274-39494929"), gene = structure(c(3L, 
3L, 3L, 3L, 1L, 1L, 1L, 1L, 1L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("ABC_1", "C_1", "XYZ_123"
), class = "factor"), group = structure(c(2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L), .Label = c("KO", "WT"), class = "factor"), class_A = c(0, 
1, 0, 2, 1, 0, 0, 1, 0, 1, 0, 0, 0, 2, 2, 1, 0, 0, 0, 0, 1, 1, 
1, 0, 3), class_B = c(0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 
1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 1)), row.names = c(NA, -25L), class = "data.frame")

Based on this, I would like to produce the summary table (for data grouped per "group" and "gene"). For the purpose of the summary I refer to the non-zero occurrences as "counts" and to the sum of non-zero values as "hits". I do not feel very fluent in English, so without further ado - below is the desired output:

desired_dframe <- structure(list(group = structure(c(1L, 1L, 2L, 2L, 2L), .Label = c("KO", 
"WT"), class = "factor"), gene = structure(c(1L, 3L, 1L, 2L, 
3L), .Label = c("ABC_1", "C_1", "XYZ_123"), class = "factor"), 
    counts_total = c(8L, 7L, 5L, 1L, 4L), counts_AB = c(6L, 3L, 
    3L, 1L, 3L), hits_AB = c(9, 6, 3, 1, 5), counts_A = c(4L, 
    3L, 2L, 1L, 2L), hits_A = c(6, 5, 2, 1, 3), counts_B = c(3L, 
    1L, 1L, 0L, 2L), hits_B = c(3, 1, 1, 0, 2)), row.names = c(NA, 
-5L), class = "data.frame")

I can produce this with the following function:

summarize_mygene <- function(dframe){
  # count all ids per group and gene (counts)
  dframe1 <- dframe %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(counts_total = dplyr::n())

  # count all ocurrences of non-zero instances in both class_ columns (counts)
  dframe2 <- dframe %>% 
    rowwise() %>% 
    dplyr::mutate(counts_AB = sum(dplyr::c_across(class_A:class_B))) %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(dplyr::across(counts_AB, ~ sum(.x != 0))) 

  # sum up all non-zero instances in both class columns (hits)
  dframe3 <- dframe %>% 
    rowwise() %>% 
    dplyr::mutate(hits_AB = sum(dplyr::c_across(class_A:class_B))) %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(dplyr::across(hits_AB, ~ sum(.x)))

  # count non-zero ocurrences per group and gene in class_A column (counts)
  dframe4 <- dframe %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(dplyr::across(class_A, ~ sum(.x != 0))) %>% 
    dplyr::rename(counts_A = class_A)
  
  # sum up all non-zero instances in class_A column (hits)
  dframe5 <- dframe %>% 
    rowwise() %>% 
    dplyr::mutate(hits_A = class_A) %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(dplyr::across(hits_A, ~ sum(.x)))

  # count non-zero ocurrences per group and gene in class_A column (counts)
  dframe6 <- dframe %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(dplyr::across(class_B, ~ sum(.x != 0))) %>% 
    dplyr::rename(counts_B=class_B)

  # sum up all non-zero instances in class_B column (hits)
  dframe7 <- dframe %>% 
    rowwise() %>% 
    dplyr::mutate(hits_B = class_B) %>% 
    dplyr::group_by(group, gene) %>% 
    dplyr::summarise(dplyr::across(hits_B, ~ sum(.x)))

  # merge the outputs
  dframe_list <- list(dframe1, dframe2, dframe3, dframe4, dframe5, dframe6, dframe7)
  merged_dframe <- Reduce(function(x, y) merge(x, y, all=TRUE), dframe_list)
  
  
  return(merged_dframe)
}

However, as you can see, this is not the most elegant example of using dplyr. I wrote this function in a step-wise manner, because this was the only way I could avoid the errors. Initially, I tried to handle all operations at once, within single dplyr::mutate() call or within single dplyr::summarize() call. However, It threw multiple errors or resulted in a column(s) containing a tibble in each row. After a few attempts I gave up on writing concise code and landed with this summarize_mygene() abomination. However, I would appreciate assistance with improving this nightmare fuel.

CodePudding user response:

We could do this in a single group by operation

library(dplyr)
library(stringr)
dframe %>% 
   mutate(Sum_AB = rowSums(across(starts_with('class_')))) %>%
   group_by(group, gene) %>% 
   summarise(counts_total = n(),            
     across(c(Sum_AB, class_A, class_B), 
       list(counts = ~ sum(.x != 0), hits = ~ sum(.x))), .groups= 'drop')%>% 
  rename_with(~str_replace(.x, '^\\w _(\\w )_(\\w )', '\\2_\\1'), 3:last_col() )

-output

# A tibble: 5 × 9
  group gene    counts_total counts_AB hits_AB counts_A hits_A counts_B hits_B
  <fct> <fct>          <int>     <int>   <dbl>    <int>  <dbl>    <int>  <dbl>
1 KO    ABC_1              8         6       9        4      6        3      3
2 KO    XYZ_123            7         3       6        3      5        1      1
3 WT    ABC_1              5         3       3        2      2        1      1
4 WT    C_1                1         1       1        1      1        0      0
5 WT    XYZ_123            4         3       5        2      3        2      2

-OP's desired

> desired_dframe
  group    gene counts_total counts_AB hits_AB counts_A hits_A counts_B hits_B
1    KO   ABC_1            8         6       9        4      6        3      3
2    KO XYZ_123            7         3       6        3      5        1      1
3    WT   ABC_1            5         3       3        2      2        1      1
4    WT     C_1            1         1       1        1      1        0      0
5    WT XYZ_123            4         3       5        2      3        2      2

CodePudding user response:

Maybe you are looking for this

desired_dframe <- dframe %>% 
  group_by(group, gene) %>%
  summarise(counts_total = n(),
            counts_AB = sum(ifelse((class_A == 0 & class_B == 0), 0, 1)),
            hits_AB = sum(class_A, class_B),
            counts_A = sum(ifelse(class_A == 0, 0, 1)),
            hits_A = sum(class_A),
            counts_B = sum(ifelse(class_B == 0, 0, 1)),
            hits_B = sum(class_B)) 
  • Related