How to consolidate a table by summing values of one column when the rest of the row is duplicate

user_16253797 0
Viewed 38

I have this table below where some of the rows have the same values for every column except for the users column. I've been trying to aggregate the values in the $users column so to create a shorter table that doesn't have any repeated rows.

So far I've tried two different formulas, with different results:

(1)

basex2=aggregate(base3$users, by=subset(base3,select=-users),FUN=sum)

(2)

basex3 = base3 %>%
  group_by_all()%>%
  summarise(users=sum(users))

Both formulas however yielded different results. In (1), the number of rows was reduced to around 180,000 (the full frame has over 1M); In (2), it was reduced to 129,000.

By applying this code (3):

basex1 = base3 %>%
  subset(select=-users) %>%
  unique()

I was able to confirm that the actual number I should be reaching is around 130,000, which means (1) and (2) are inconsistent. Furthermore, I was able to easily find rows in (1) where everything but the users column was duplicate; and the total sum of users in (2) is slightly smaller than it is in the initial table.

My question here is why are those functions yielding different and incorrect results, and whether there's a better function that I could use to arrive at an optimal table.

# A tibble: 6 x 11
# Groups:   date, country, country_ip, country_campaign, device, source,
#   campaign, users, year, month [6]
  date       country country_ip country_campaign device  source     campaign users
  <date>     <chr>   <chr>      <chr>            <chr>   <chr>         <dbl> <dbl>
1 2021-03-01 AE      SA         WW               Android google un…  1.01e10     9
2 2021-03-01 AT      AT         WW               Android google un…  1.01e10    11
3 2021-03-01 AT      AT         WW               Android google un…  1.01e10    10
4 2021-03-01 AT      AT         WW               Android google un…  1.01e10     4
5 2021-03-01 AU      AU         WW               Android google un…  1.01e10    39
6 2021-03-01 AU      AU         WW               Android google un…  1.01e10     4
# … with 3 more variables: year <dbl>, month <chr>, game <chr>
dplyr dataframe r database
1 Answers

The actual answer to why the output are different is based on the actual data. But yes, the two code can generate different outputs.

Consider this example -

set.seed(123)
base3 <- data.frame(a = c(1, 1, 2, 2, 2, 3), 
                    b = c('a', 'a', 'b', 'b', 'b', NA), 
                    c = rnorm(6))
base3

#  a    b       c
#1 1    a -0.5605
#2 1    a -0.2302
#3 2    b  1.5587
#4 2    b  0.0705
#5 2    b  0.1293
#6 3 <NA>  1.7151

Note the presence of NA value in b.

When you use aggregate with default setting -

aggregate(base3$c, by=subset(base3,select=-c),FUN=sum)

#  a b      x
#1 1 a -0.791
#2 2 b  1.759

You loose the 3rd group where a = 3. Your original data can have multiple such NA values in any of the column which aggregate must be dropping.

As far as dplyr code is concerned it should not work anyway since you are using group_by_all which means you are also grouping the c column (i.e users column in your actual dataset).

library(dplyr) 
base3 %>% group_by_all()%>% summarise(c=sum(c))

#      a b         c
#  <dbl> <chr>   <dbl>
#1     1 a     -0.560 
#2     1 a     -0.230 
#3     2 b      0.0705
#4     2 b      0.129 
#5     2 b      1.56  
#6     3 NA     1.72

In the output we get same number of rows as the original data since every value in c column is unique. What you should do instead is group by every column except c which can be written as

base3 %>%  group_by(across(-c))%>%summarise(c=sum(c))

#      a b          c
#  <dbl> <chr>  <dbl>
#1     1 a     -0.791
#2     2 b      1.76 
#3     3 NA     1.72
user_39629140
Related