How to extract and sum distinct values in from a column and create a column with the sum

user_12197202 0
Viewed 391

I am trying to create a column which is the sum of the unique values of column SIZE

TYPE    SIZE
A   24522145.17
A   35359867.65
A   35359867.65
A   35359867.65
A   35359867.65
A   35359867.65
A   24522145.17
A   35359867.65
A   35359867.65
A   8527174.786

I don't want the count of unique values but the sum, that from the data above the sum will be adding (24522145.17 + 35359867.65 + 8527174.786). This after grouping by variable TYPE. Thanks!

dplyr r
4 Answers

We group by 'TYPE', get the unique 'SIZE' and return with the sum of those values in summarise

library(dplyr)
df1 %>%
    group_by(TYPE) %>%
     summarise(Sum = sum(unique(SIZE), na.rm = TRUE))

-output

# A tibble: 1 x 2
  TYPE        Sum
  <chr>     <dbl>
1 A     68409188.

data

df1 <- structure(list(TYPE = c("A", "A", "A", "A", "A", "A", "A", "A", 
"A", "A"), SIZE = c(24522145.17, 35359867.65, 35359867.65, 35359867.65, 
35359867.65, 35359867.65, 24522145.17, 35359867.65, 35359867.65, 
8527174.786)), class = "data.frame", row.names = c(NA, -10L))
user_37322710

We could also get the distinct combos and add those using count where the wt are the values we want to sum.

library(dplyr)
df1 %>%
  distinct(TYPE, SIZE) %>%
  count(TYPE, wt = SIZE)  # shortcut for group_by(TYPE) %>% summarize(n = sum(SIZE))

data

df1 <- data.frame(
  stringsAsFactors = FALSE,
              TYPE = c("A", "A", "A", "A", "A", "A", "A", "A", "A", "A"),
              SIZE = c(24522145.17,35359867.65,
                       35359867.65,35359867.65,35359867.65,35359867.65,
                       24522145.17,35359867.65,35359867.65,8527174.786)
)
user_68518250

With your properly formatted data

tibble::tribble(
  ~TYPE, ~SIZE,
  "A",   24522145.17,
  "A",   35359867.65,
  "A",   35359867.65,
  "A",   35359867.65,
  "A",   35359867.65,
  "A",   35359867.65,
  "A",   24522145.17,
  "A",   35359867.65,
  "A",   35359867.65,
  "A",   8527174.786
) -> df

you could do (h/t @akrun)

aggregate(SIZE ~ TYPE, unique(df), sum)

returning

  TYPE     SIZE
1    A 68409188
user_70100560

We could use filter and !duplicated():

df1 %>% 
  group_by(TYPE) %>% 
  filter(!duplicated(SIZE)) %>% 
  summarise(sum = sum(SIZE))

Output:

  TYPE        sum
  <chr>     <dbl>
1 A     68409188.
user_133216470
Related