CODEWITHSUNDEEP
rdata.table
Gin_Salmon
Gin_Salmon

Reputation: 847

Sum by unique variable pairs

I'm looking for a way to cleanly sum the unique pairings of my two variables, preferably, in data.table.

Here's my data:

 > x_base
    State SA2 Population_SA2
 1:     A   x            304
 2:     A   x            304
 3:     A   y             79
 4:     A   y             79
 5:     A   y             79
 6:     B   z            961
 7:     B   z            961
 8:     B   z            961
 9:     B   z            961
10:     B   w             90
11:     B   w             90
12:     B   u            550
13:     C   v            113
14:     C   v            113
15:     C   t            425
16:     C   t            425
17:     C   t            425

What I want to do is to calculate the sum of Population_SA2 by State. However, I've got multiple entries for each State and SA2 pair, for instance I've got two pairs of State == A & SA2 == x, but when i sum over Population_SA2, it would be incorrect to count 304 twice, likewise it would be incorrect to count 79 three times. The population for State A should be 383 (304 + 79).

Here's my current solution:

1) Find the unique State and SA2 pairs:

> x_unique <- unique(x_base)
> x_unique
   State SA2 Population_SA2
1:     A   x            304
2:     A   y             79
3:     B   z            961
4:     B   w             90
5:     B   u            550
6:     C   v            113
7:     C   t            425

2) Sum Population_SA2 by State:

> x_unique <- x_unique[,.(sum_by_state = sum(Population_SA2)), by = State]
> x_unique
   State sum_by_state
1:     A          383
2:     B         1601
3:     C          538

3) Merge back onto the initial data.table:

> x_final <- merge(x_base, x_unique[,.(State,sum_by_state)], by = "State")
> x_final
    State SA2 Population_SA2 sum_by_state
 1:     A   x            304          383
 2:     A   x            304          383
 3:     A   y             79          383
 4:     A   y             79          383
 5:     A   y             79          383
 6:     B   z            961         1601
 7:     B   z            961         1601
 8:     B   z            961         1601
 9:     B   z            961         1601
10:     B   w             90         1601
11:     B   w             90         1601
12:     B   u            550         1601
13:     C   v            113          538
14:     C   v            113          538
15:     C   t            425          538
16:     C   t            425          538
17:     C   t            425          538

I'd be really keen to know if this can be done in a cleaner, more concise way, where I don't have to create any intermediate objects.

Much appreciated!

Not sure if necessary, but here's a dput()

structure(list(State = c("A", "A", "A", "A", "A", "B", "B", "B", 
"B", "B", "B", "B", "C", "C", "C", "C", "C"), SA2 = c("x", "x", 
"y", "y", "y", "z", "z", "z", "z", "w", "w", "u", "v", "v", "t", 
"t", "t"), Population_SA2 = c(304L, 304L, 79L, 79L, 79L, 961L, 
961L, 961L, 961L, 90L, 90L, 550L, 113L, 113L, 425L, 425L, 425L
)), row.names = c(NA, -17L), class = c("data.table", "data.frame"
), .internal.selfref = <pointer: 0x0000000000121ef0>)

Upvotes: 1

Views: 42

Answers (1)

kath
kath

Reputation: 7724

A data.table solution is 

x_base[, sum_by_state := sum(Population_SA2[!duplicated(SA2)]), by = State]
x_base
#     State SA2 Population_SA2 sum_by_state
#  1:     A   x            304          383
#  2:     A   x            304          383
#  3:     A   y             79          383
#  4:     A   y             79          383
#  5:     A   y             79          383
#  6:     B   z            961         1601
#  7:     B   z            961         1601
#  8:     B   z            961         1601
#  9:     B   z            961         1601
# 10:     B   w             90         1601
# 11:     B   w             90         1601
# 12:     B   u            550         1601
# 13:     C   v            113          538
# 14:     C   v            113          538
# 15:     C   t            425          538
# 16:     C   t            425          538
# 17:     C   t            425          538

A dplyr-solution would be: 

library(dplyr)

x_base %>% 
  group_by(State) %>% 
  mutate(sum_by_state = sum(Population_SA2[!duplicated(SA2)]))

# A tibble: 17 x 4
# Groups:   State [3]
#    State SA2   Population_SA2 sum_by_state
#    <chr> <chr>          <int>        <int>
# 1  A     x                304          383
# 2  A     x                304          383
# 3  A     y                 79          383
# 4  A     y                 79          383
# 5  A     y                 79          383
# 6  B     z                961         1601
# 7  B     z                961         1601
# 8  B     z                961         1601
# 9  B     z                961         1601
# 10 B     w                 90         1601
# 11 B     w                 90         1601
# 12 B     u                550         1601
# 13 C     v                113          538
# 14 C     v                113          538
# 15 C     t                425          538
# 16 C     t                425          538
# 17 C     t                425          538

Upvotes: 4

Related Questions