Conditional sum on a non-equivalent data frame in R

user_13479861 0
Viewed 61

Yesterday I asked how to sum a column based on a condition in a different data.frame. This was a success in small subsets. However it took hours when using the full data. So I thought why not just force a join with the plyr rbind.fill function and then make the conditional sum. Then I realized I didn't know how, so I was hoping you could help me. This is head.

               a           b          c          d             
1        1010001 4507888.889         NA         NA               
2        1010011  843166.708         NA         NA              
3        1010021  612500.000         NA         NA               
4        1010031  740000.000         NA         NA               
5        1010041    4166.667         NA         NA               
6        1010051 3366666.667         NA         NA

This is tail.

                    a   b                 c          d            
689085             NA  NA             70.62    181.1278    
689086             NA  NA            106.30   2383.3616     
689087             NA  NA            768.80 248804.5507    
689088             NA  NA            512.30 189899.9227     
689089             NA  NA            144.70 176382.4634     
689090             NA  NA            340.90 264691.8022

What Im trying to do is taking each value of b and compare it to all values of d and then sum all values in c that fulfill the condition b(just one value)>=f(all values).I've tried with this.

df<-df%>%mutate(sumc=sum(df$g[b>=df$f]))

Which leads me to a column sumc full of 0. With the head and tail I'm showing the output I expect should look something like this. 

                    a         b        c          d               e          
1             1010001  4507888.889       NA         NA        1943.72      
2             1010011   843166.708       NA         NA        1943.72
3             1010021   612500.000       NA         NA        1943.72
4             1010031   740000.000       NA         NA        1943.72
5             1010041     4166.667       NA         NA         177.92
6             1010051  3366666.667       NA         NA        1943.72                                          
689085             NA        NA         70.62     181.1278       NA
689086             NA        NA        106.30    2383.3616       NA
689087             NA        NA        768.80  248804.5507       NA
689088             NA        NA        512.30  189899.9227       NA
689089             NA        NA        144.70  176382.4634       NA
689090             NA        NA        340.90  264691.8022       NA

Also, I tried using group_by(a) to have just the values for which sumc is taking a value but it doesn't work.

Thanks to everyone reading this! :)

data.table dplyr plyr r
2 Answers

Here is an option using rolling join in data.table:

DT[order(D), csc := cumsum(C)]

DT[, sumc := 
    DT[!is.na(D)][DT, on=.(D=B), roll=Inf, mult="last", csc]
]

output:

          A           B      C           D     csc    sumc
 1: 1010001 4507888.889     NA          NA      NA 1943.62
 2: 1010011  843166.708     NA          NA      NA 1943.62
 3: 1010021  612500.000     NA          NA      NA 1943.62
 4: 1010031  740000.000     NA          NA      NA 1943.62
 5: 1010041    4166.667     NA          NA      NA  176.92
 6: 1010051 3366666.667     NA          NA      NA 1943.62
 7:      NA          NA  70.62    181.1278   70.62      NA
 8:      NA          NA 106.30   2383.3616  176.92      NA
 9:      NA          NA 768.80 248804.5507 1602.72      NA
10:      NA          NA 512.30 189899.9227  833.92      NA
11:      NA          NA 144.70 176382.4634  321.62      NA
12:      NA          NA 340.90 264691.8022 1943.62      NA

data:

library(data.table)
DT <- fread("A           B          C          D             
1010001 4507888.889         NA         NA               
1010011  843166.708         NA         NA              
1010021  612500.000         NA         NA               
1010031  740000.000         NA         NA               
1010041    4166.667         NA         NA               
1010051 3366666.667         NA         NA 
NA  NA             70.62    181.1278    
NA  NA            106.30   2383.3616     
NA  NA            768.80 248804.5507    
NA  NA            512.30 189899.9227     
NA  NA            144.70 176382.4634     
NA  NA            340.90 264691.8022")
user_19894800

Here's a possible solution, I'm not sure how long it will take to run.

Updated: to provide a value for C instead of a sum for C.

library(tidyverse)

df <- readr::read_table(
"               a           b          c          d             
1        1010001 4507888.889         NA         NA               
2        1010011  843166.708         NA         NA              
3        1010021  612500.000         NA         NA               
4        1010031  740000.000         NA         NA               
5        1010041    4166.667         NA         NA               
6        1010051 3366666.667         NA         NA"
) %>%  bind_rows(
readr::read_table(
  "                    a   b                 c          d            
689085             NA  NA             70.62    181.1278    
689086             NA  NA            106.30   2383.3616     
689087             NA  NA            768.80 248804.5507    
689088             NA  NA            512.30 189899.9227     
689089             NA  NA            144.70 176382.4634     
689090             NA  NA            340.90 264691.8022"
)
)
#> Warning: Missing column names filled in: 'X1' [1]
#> Warning: Missing column names filled in: 'X1' [1]
df
#> # A tibble: 12 x 5
#>        X1       a        b     c       d
#>     <dbl>   <dbl>    <dbl> <dbl>   <dbl>
#>  1      1 1010001 4507889.  NA       NA 
#>  2      2 1010011  843167.  NA       NA 
#>  3      3 1010021  612500   NA       NA 
#>  4      4 1010031  740000   NA       NA 
#>  5      5 1010041    4167.  NA       NA 
#>  6      6 1010051 3366667.  NA       NA 
#>  7 689085      NA      NA   70.6    181.
#>  8 689086      NA      NA  106.    2383.
#>  9 689087      NA      NA  769.  248805.
#> 10 689088      NA      NA  512.  189900.
#> 11 689089      NA      NA  145.  176382.
#> 12 689090      NA      NA  341.  264692.

# What Im trying to do is taking each value of b and compare it to all values
# of d and then sum all values in c that fulfill the condition
# b(just one value)>=f(all values)

df %>% 
  mutate(b_higher = if_else(b > max(d, na.rm = TRUE), "yes", "no", "missing")) %>% 
  mutate(c_sum = if_else(b_higher == "yes", c, NULL,NULL))
#> # A tibble: 12 x 7
#>        X1       a        b     c       d b_higher c_sum
#>     <dbl>   <dbl>    <dbl> <dbl>   <dbl> <chr>    <dbl>
#>  1      1 1010001 4507889.  NA       NA  yes         NA
#>  2      2 1010011  843167.  NA       NA  yes         NA
#>  3      3 1010021  612500   NA       NA  yes         NA
#>  4      4 1010031  740000   NA       NA  yes         NA
#>  5      5 1010041    4167.  NA       NA  no          NA
#>  6      6 1010051 3366667.  NA       NA  yes         NA
#>  7 689085      NA      NA   70.6    181. missing     NA
#>  8 689086      NA      NA  106.    2383. missing     NA
#>  9 689087      NA      NA  769.  248805. missing     NA
#> 10 689088      NA      NA  512.  189900. missing     NA
#> 11 689089      NA      NA  145.  176382. missing     NA
#> 12 689090      NA      NA  341.  264692. missing     NA

df %>% 
  add_row(X1 = 7, a = 1010061, b = 843167, c = 10, d = 181) %>% 
  mutate(b_higher = if_else(b > max(d, na.rm = TRUE), "yes", "no", "missing")) %>% 
  mutate(c_sum = if_else(b_higher == "yes", c, NULL,NULL))
#> # A tibble: 13 x 7
#>        X1       a        b     c       d b_higher c_sum
#>     <dbl>   <dbl>    <dbl> <dbl>   <dbl> <chr>    <dbl>
#>  1      1 1010001 4507889.  NA       NA  yes         NA
#>  2      2 1010011  843167.  NA       NA  yes         NA
#>  3      3 1010021  612500   NA       NA  yes         NA
#>  4      4 1010031  740000   NA       NA  yes         NA
#>  5      5 1010041    4167.  NA       NA  no          NA
#>  6      6 1010051 3366667.  NA       NA  yes         NA
#>  7 689085      NA      NA   70.6    181. missing     NA
#>  8 689086      NA      NA  106.    2383. missing     NA
#>  9 689087      NA      NA  769.  248805. missing     NA
#> 10 689088      NA      NA  512.  189900. missing     NA
#> 11 689089      NA      NA  145.  176382. missing     NA
#> 12 689090      NA      NA  341.  264692. missing     NA
#> 13      7 1010061  843167   10      181  yes         10

Created on 2020-05-07 by the reprex package (v0.3.0)

user_64971370
Related