Is there a way in R to sum all items in a column using as condition the values from another?

user_15806207 0
Viewed 50

I have two data frames containg string names with a specific condition and a numeric index value. What I want is to count how many names are there for a condition using an index value as reference.

The data frame is big so I'll just put and example. I want to summarize all values in NAME from a taking into account CONDITION between INDEX-MIN and INDEX-MAX from b. Here it its important to specify that not all names in ´a´ will be captured or summarize in the final result. The result should be as shown in c

a <- data.frame(c(1,1,2,3,3,3),c("A","B","C","D","E","F"),c(100,500,233,74,2750,10043))
colnames(a) <- c("CONDITION","NAME","INDEX")
b <- data.frame(c(1,2,3,3),c(1,75,2700,9872),c(600,245,3500,10500))
colnames(b) <- c("CONDITION","INDEX-MIN","INDEX-MAX")
c <- data.frame(c(1,2,3,3),c(1,75,2700,9872),c(600,245,3500,10500),c(2,1,1,1),c("A, B","C", "E", "F"))
colnames(c) <- c("CONDITION","INDEX-MIN","INDEX-MAX","NAME-COUNT","NAME")
dplyr dataframe r
2 Answers

We can do this with a non-equi join in data.table

library(data.table)
setDT(a)[b, .(NAME_COUNT = .N, NAME = toString(NAME)),
   on = .(CONDITION,  INDEX >=`INDEX-MIN`, INDEX < `INDEX-MAX`), by  = .EACHI]

-output

    CONDITION INDEX INDEX NAME_COUNT NAME
1:         1     1   600          2 A, B
2:         2    75   245          1    C
3:         3  2700  3500          1    E
4:         3  9872 10500          1    F
user_37322710

You can join the two dataframes with fuzzyjoin -

library(dplyr)

fuzzyjoin::fuzzy_inner_join(a, b, 
          by = c('CONDITION', 'INDEX' = 'INDEX-MIN', 'INDEX' = 'INDEX-MAX'), 
          match_fun = c(`==`, `>=`, `<=`)) %>%
  group_by(`INDEX-MIN`, `INDEX-MAX`) %>%
  summarise(CONDITION = sum(CONDITION.x), 
            `NAME-COUNT` = n(), 
            NAME = toString(NAME)) %>%
  ungroup

#  `INDEX-MIN` `INDEX-MAX` CONDITION `NAME-COUNT` NAME 
#        <dbl>       <dbl>     <dbl>        <int> <chr>
#1           1         600         2            2 A, B 
#2          75         245         2            1 C    
#3        2700        3500         3            1 E    
#4        9872       10500         3            1 F
user_39629140
Related