Calculating weighted averages excluding some rows for each row using data.table in r

Question

I have been trying to obtain a column of weighted averages which excludes some rows for each row using data.table.

In the following example, FIPS is ID variable and STATE is group variable. I want to calculate weighted average of value excluding neighboring counties in a same state. as well as in other states.

I know how to implement it but I guess there is a more efficient way. I am not familiar with rowwise manipulation using data.table. Any ideas? Thank you in advance.

library(data.table)
rm(list=ls())

set.seed(920410)
DT <- data.table(FIPS =1:21, STATE = LETTERS[1:2], value=1:3, weight=2:7); DT
DT[, nbs := list(list(sample(1:21, 3))), by= names(DT)]

for(i in 1:nrow(DT)){
 DT$neighbor_sum_in_the_same_state[i] <- sum(DT[FIPS %in% unlist(DT$nbs[i]) & STATE == DT$STATE[i], value*weight])
 DT$neighbor_sum_in_other_states[i] <- sum(DT[FIPS %in% unlist(DT$nbs[i]) & STATE != DT$STATE[i], value*weight])
 }

score 0 · Answer 1 · answered Nov 12 '19 at 05:39

Maybe there is equivalent data.table, here is one way in tidyverse

library(dplyr)
library(purrr)

DT %>%
  group_by(STATE) %>%
  mutate(val1 = map_dbl(nbs, ~{inds <- FIPS %in% .x; 
                               sum(value[inds] * weight[inds])})) %>%
  ungroup() %>%
  mutate(val2 = map2_dbl(nbs, STATE, ~{inds <- FIPS %in% .x & STATE != .y; 
                                        sum(value[inds] * weight[inds])}))

# A tibble: 21 x 9
#    FIPS STATE value weight nbs       OP_val1 OP_val2  val1  val2
#   <int> <chr> <int>  <int> <list>      <int>   <int> <dbl> <dbl>
# 1     1 A         1      2 <int [3]>      14      21    14    21
# 2     2 B         2      3 <int [3]>      11      12    11    12
# 3     3 A         3      4 <int [3]>       0      17     0    17
# 4     4 B         1      5 <int [3]>       5      14     5    14
# 5     5 A         2      6 <int [3]>      16       0    16     0
# 6     6 B         3      7 <int [3]>      26      12    26    12
# 7     7 A         1      2 <int [3]>      14       5    14     5
# 8     8 B         2      3 <int [3]>      27       2    27     2
# 9     9 A         3      4 <int [3]>       2      42     2    42
#10    10 B         1      5 <int [3]>       6      14     6    14
# … with 11 more rows

where OP_val1 and OP_val2 is the output after running the for loop in OP.

data

set.seed(920410)
DT <- data.table(FIPS =1:21, STATE = LETTERS[1:2], value=1:3, weight=2:7)
DT[, nbs := list(list(sample(1:21, 3))), by= names(DT)]

for(i in 1:nrow(DT)){
   DT$OP_val1[i] <- sum(DT[FIPS %in% unlist(DT$nbs[i]) & STATE == DT$STATE[i], value*weight])
   DT$OP_val2[i] <- sum(DT[FIPS %in% unlist(DT$nbs[i]) & STATE != DT$STATE[i], value*weight])
}

score 0 · Answer 2 · answered Nov 12 '19 at 05:48

Here is an option in data.table by converting it into a long format before performing joins:

#convert into long format i.e. unlist the nbs column
nm <- c("FIPS","STATE","value", "weight")
DT_long <- DT[, .(nbs=unlist(nbs)), nm]

#look for neighbours in same STATE and FIPS
DT_long[, neighbor_sum_in_the_same_state := 
    .SD[.SD, on=.(FIPS=nbs, STATE), sum(x.value[1L] * x.weight[1L]), by=.EACHI]$V1]

#look for all in same FIPS but exclude those with same STATE
DT_long[, neighbor_sum_in_other_states := 
        .SD[.SD, on=.(FIPS=nbs), sum(x.value[x.STATE!=i.STATE][1L] * x.weight[x.STATE!=i.STATE][1L]), by=.EACHI]$V1]

#produce desired output
DT_long[, lapply(.SD, sum, na.rm=TRUE), nm, 
    .SDcols=c("neighbor_sum_in_the_same_state", "neighbor_sum_in_other_states")]

output:

    FIPS STATE value weight neighbor_sum_in_the_same_state neighbor_sum_in_other_states
 1:    1     A     1      2                             14                           21
 2:    2     B     2      3                             11                           12
 3:    3     A     3      4                              0                           17
 4:    4     B     1      5                              5                           14
 5:    5     A     2      6                             16                            0
 6:    6     B     3      7                             26                           12
 7:    7     A     1      2                             14                            5
 8:    8     B     2      3                             27                            2
 9:    9     A     3      4                              2                           42
10:   10     B     1      5                              6                           14
11:   11     A     2      6                             12                           26
12:   12     B     3      7                             11                            2
13:   13     A     1      2                             12                           11
14:   14     B     2      3                              5                           24
15:   15     A     3      4                             12                           26
16:   16     B     1      5                             21                           24
17:   17     A     2      6                              4                            5
18:   18     B     3      7                              6                           14
19:   19     A     1      2                             14                            5
20:   20     B     2      3                             11                           12
21:   21     A     3      4                             12                           27
    FIPS STATE value weight neighbor_sum_in_the_same_state neighbor_sum_in_other_states

score 0 · Answer 3 · answered Nov 13 '19 at 02:14

Thank you both :) Those help!

After trying various methods, I wrote the following code. The following code calculates weighted average of values excluding neighboring counties in the same state as well as in other states without using loop.


DT[, weighted_avg_nonneighboring_counties_in_same_state := 
  weighted.mean(
  DT[!FIPS == .BY[1] & !FIPS %in% unlist(nbs[.I]) & STATE == .BY[2], value],
  DT[!FIPS == .BY[1] & !FIPS %in% unlist(nbs[.I]) & STATE == .BY[2], weight],
  na.rm=TRUE),
  by=.(FIPS,STATE)][,
     weighted_avg_nonneighboring_counties_in_other_states := 
  weighted.mean(
  DT[!FIPS == .BY[1] & !FIPS %in% unlist(nbs[.I]) & STATE != .BY[2], value],
  DT[!FIPS == .BY[1] & !FIPS %in% unlist(nbs[.I]) & STATE != .BY[2], weight], 
  na.rm=TRUE),  
  by=.(FIPS,STATE)]

Calculating weighted averages excluding some rows for each row using data.table in r

3 Answers3