ycw ycw -4 years ago 76
R Question

Calculate the total value of a group while keep information from the other column in a data frame

I have a data frame as the following structure.

# Create example data
ex_df <- data.frame(
Date = as.Date(c("2000-01-01", "2000-01-02", "2000-01-03", "2000-01-03",
"2000-01-04", "2000-01-04", "2000-01-05", "2000-01-05",
"2000-01-05")),
Value = c(1, 3, 1, 2, 5, 2, 1, 3, 1),
Label = c("A", "B", "A", "A",
"B", "A", "A", "A", "B"),
stringsAsFactors = FALSE)

ex_df
# Date Value Label
# 1 2000-01-01 1 A
# 2 2000-01-02 3 B
# 3 2000-01-03 1 A
# 4 2000-01-03 2 A
# 5 2000-01-04 5 B
# 6 2000-01-04 2 A
# 7 2000-01-05 1 A
# 8 2000-01-05 3 A
# 9 2000-01-05 1 B


I would like to calculate the total
Value
of each
Date
, while keeping the information in the
Label
column. The desired output would be the following.

# Date Value Label
#1 2000-01-01 1 A
#2 2000-01-02 3 B
#3 2000-01-03 3 A
#4 2000-01-04 7 B
#5 2000-01-05 5 B


In this data frame, I want
Label
to be
A
or
B
if all the rows from the same
Date
have the same label, such as the case in
2000-01-03
. However, if rows from the same
Date
have different labels, I want
Label
to be
B
, such as the case in
2000-01-04
and
2000-01-05
.

I know I can calculate the group total value using the
dplyr
package as follows.

library(dplyr)
ex_df %>%
group_by(Date) %>%
summarise(Value = sum(Value))


But how can I keep the information in the
Label
column? I would welcome solutions not only using
dplyr
, but also
data.table
, base R, or other packages.

Thank you.

Answer Source

You can check if B is in Label for each group, if yes, return B in the summary else return A:

library(dplyr)
ex_df %>% group_by(Date) %>% 
    summarise(Value = sum(Value), Label = if("B" %in% Label) "B" else "A")

# A tibble: 5 × 3
#        Date Value Label
#      <date> <dbl> <chr>
#1 2000-01-01     1     A
#2 2000-01-02     3     B
#3 2000-01-03     3     A
#4 2000-01-04     7     B
#5 2000-01-05     5     B
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download