Is there a way to create a range of sum of multiple columns matching a condition in R?

user_15806207 0
Viewed 47

I have a data frame which contains an experimental CONDITION which has an determined INDEX. Each experiment has a NAME-A associated and a NAME_B corresponding to a specific NAME_A.

My main objective is to summarize total of NAME-A and NAME-B by CONDITION by creating a range considering the difference of INDEX value between two consecutives NAME_A , i.e. the difference between indexes for the same CONDITION should not be grater than 400 (INDEX[i+1] - INDEX[i] < 400).

Possible escenarios are NAME_A can be duplicated, but not NAME_B. NAME_A can have no NAME_B associated and so count can differ between columns.

Here I leave an example in a is the input data I have and b should be the output.

a <- data.frame(c(1,2,2,2,2,3),c(1,1,50,400,900,1),c("A","B","B","C","D","E"),
                c("X1","X2","X3","X4","X5",NA))
colnames(a) <- c("CONDITION","INDEX","NAME_A","NAME_B")

Data

  CONDITION INDEX NAME_A NAME_B
1         1     1      A     X1
2         2     1      B     X2
3         2    50      B     X3
4         2   400      C     X4
5         2   900      D     X5
6         3     1      E   <NA>

Desire Output

b <- data.frame(c(1,2,2,3),c(1,1,900,1),c(1,400,900,1),c("A","B, C","D","E"),c(1,2,1,1),
                c("X1","X2, X3, X4","X5",NA),c(1,3,1,0))
colnames(b) <- c("CONDITION","INDEX_MIN","INDEX_MAX",
                 "NAME_A","COUNT_A","NAME_B","COUNT_B")

  CONDITION INDEX_MIN INDEX_MAX NAME_A COUNT_A     NAME_B COUNT_B
1         1         1         1      A       1         X1       1
2         2         1       400   B, C       2 X2, X3, X4       3
3         2       900       900      D       1         X5       1
4         3         1         1      E       1       <NA>       0

My problem is that I've made this separately for NAME-A and NAME-B but as shown in example, sometimes NAME-A has no NAME-B associated so the result range windows are not equally distributed between data frames, and so further manual edition is required.

summarize dplyr dataframe r
1 Answers

With dplyr, you can group_by CONDITION, then use summarise to determine the other columns. Of note, I have used underscores instead of hyphens column names (otherwise if using non-standard names, wrap them in backticks).

To address the difference of no greater than 400 in INDEX between rows, you can assign a GROUP number with cumsum that will increment within a given CONDITION when the difference in INDEX exceeds 400. Then you can use this in your group_by statement before summarising.

Note that the n_distinct(NAME_B, na.rm = TRUE) can be substituted with sum(!is.na(NAME_B)) as NAME_B cannot be duplicated.

library(dplyr)

a %>%
  group_by(CONDITION) %>%
  group_by(GROUP = cumsum(c(1, diff(INDEX) > 400)), .add = TRUE) %>%
  summarise(
    INDEX_MIN = min(INDEX),
    INDEX_MAX = max(INDEX),
    COUNT_A = n_distinct(NAME_A),
    NAME_A = toString(unique(NAME_A)), 
    COUNT_B = n_distinct(NAME_B, na.rm = TRUE),
    NAME_B = toString(NAME_B)
  )

Output

  CONDITION GROUP INDEX_MIN INDEX_MAX COUNT_A NAME_A COUNT_B NAME_B    
      <dbl> <dbl>     <dbl>     <dbl>   <int> <chr>    <int> <chr>     
1         1     1         1         1       1 A            1 X1        
2         2     1         1       400       2 B, C         3 X2, X3, X4
3         2     2       900       900       1 D            1 X5        
4         3     2         1         1       1 E            0 NA

Data

a <- structure(list(CONDITION = c(1, 2, 2, 2, 2, 3), INDEX = c(1, 
1, 50, 400, 900, 1), NAME_A = c("A", "B", "B", "C", "D", "E"), 
    NAME_B = c("X1", "X2", "X3", "X4", "X5", NA)), class = "data.frame", row.names = c(NA, 
-6L))
user_34606700
Related