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.

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