Take Sum of a Variable if Combination of Values in Two Other Columns are Unique [duplicate]

by

We could either use the base R method by first sorting the first two columns by row. We use apply with MARGIN=1 to do that, transpose the output, convert to ‘data.frame’ to create ‘df1’, use the formula method of aggregate to get the sum of ‘num_email’ grouped by the first two columns of the transformed dataset.

df1 <- data.frame(t(apply(df[1:2], 1, sort)), df[3])
aggregate(num_email~., df1, FUN=sum)

#      X1    X2 num_email
# 1  Beth Mable         2
# 2  Beth Susan         3
# 3 Mable Susan         1

Or using data.table, we convert the first two columns to character class, unname to change the column names of the first two columns to the default ‘V1’, ‘V2’, and convert to ‘data.table’. Using the lexicographic ordering of character columns, we create the logical index for i (V1 > V2), assign (:=) the columns that meet the condition by reversing the order of columns (.(V2, V1)), and get the sum of ‘num_email’ grouped by ‘V1’, ‘V2’.

library(data.table)
dt = do.call(data.table, c(lapply(unname(df[1:2]), as.character), df[3]))
dt[V1 > V2, c("V1", "V2") := .(V2, V1)]
dt[, .(num_email = sum(num_email)), by= .(V1, V2)]

#       V1    V2 num_email
# 1:  Beth Mable         2
# 2:  Beth Susan         3
# 3: Mable Susan         1

Or using dplyr, we use mutate_each to convert the columns to character class, then reverse the order with pmin and pmax, group by ‘V1’, ‘V2’ and get the sum of ‘num_email’.

library(dplyr)
df %>%
  mutate_each(funs(as.character), senders, receivers) %>%
  mutate( V1 = pmin(senders, receivers), 
          V2 = pmax(senders, receivers) ) %>%
  group_by(V1, V2) %>%
  summarise(num_email=sum(num_email))

#      V1    V2 num_email
#   (chr) (chr)     (dbl)
# 1  Beth Mable         2
# 2  Beth Susan         3
# 3 Mable Susan         1

NOTE: The data.table solution was updated by @Frank.

More Related Contents:

  1. How to reproduce a table of student? [closed]
  2. Faster ways to calculate frequencies and cast from long to wide
  3. How can I remove all duplicates so that NONE are left in a data frame?
  4. strptime, as.POSIXct and as.Date return unexpected NA
  5. Why is it not advisable to use attach() in R, and what should I use instead?
  6. How to delete a row by reference in data.table?
  7. Case Statement Equivalent in R
  8. How to replace NA with mean by group / subset?
  9. R – Concatenate two dataframes?
  10. Controlling ggplot2 legend display order
  11. Error in plot.new() : figure margins too large in R
  12. Generate a repeating sequence based on vector
  13. How to sort a character vector where elements contain letters and numbers?
  14. anova test fails on lme fits created with pasted formula
  15. Why is apply() method slower than a for loop in R?
  16. How do I import a CSV file in R? [closed]
  17. How to get a reversed, log10 scale in ggplot2?
  18. How can we make xkcd style graphs?
  19. First entry from string split
  20. World map with ggmap
  21. Saving a graph with ggsave after using ggplot_build and ggplot_gtable
  22. How to convert integer into categorical data in R?
  23. set ggplot plots to have same x-axis width and same space between dot plot rows
  24. Split the title onto multiple lines?
  25. lapply-ing with the “$” function
  26. How to replace NA (missing values) in a data frame with neighbouring values
  27. Trying to merge multiple csv files in R
  28. Reshape a dataframe to long format with multiple sets of measure columns [duplicate]
  29. Rtools is required to build R packages but is not currently installed
  30. Data.frame Merge and Selection of values which are common in 2 Data.frames

Leave a Comment