Summation of money amounts in character format by group

user_13275613 0
Viewed 122

I have a data frame that contains the monetary transactions among individuals. The transactions can be two-way, i.e. A can transfer money to B and B can also transfer money to A. The structure of the data frame looks like below:

From  To  Amount
A     B   $100
A     C   $40
A     D   $30
B     A   $25
B     C   $70
C     A   $190
C     D   $110

I want to summarize the total amount of transactions among each pair of individuals who have transactions with each other and the results should be something like:

Individual_1  Individual_2    Sum
A             B               $125
A             C               $230
A             D               $30
B             C               $70
C             D               $110

I tried to utilize the grouping feature of the package dplyr but I think it does not apply to my case.

dataframe aggregate r
3 Answers

You can use pmin/pmax to sort From and To columns and sum the Amount value.

library(dplyr)

df %>%
  group_by(col1 = pmin(From, To), 
           col2 = pmax(From, To)) %>%
  summarise(Amount = sum(readr::parse_number(Amount)))

#  col1  col2  Amount
#  <chr> <chr>  <dbl>
#1 A     B        125
#2 A     C        230
#3 A     D         30
#4 B     C         70
#5 C     D        110

Using the same logic in base R you can do :

aggregate(Amount~col1 + col2, 
      transform(df, col1 = pmin(From, To), col2 = pmax(From, To), 
                Amount = as.numeric(sub('$', '', Amount, fixed = TRUE))), sum)

data

df <- structure(list(From = c("A", "A", "A", "B", "B", "C", "C"), To = c("B", 
"C", "D", "A", "C", "A", "D"), Amount = c("$100", "$40", "$30", 
"$25", "$70", "$190", "$110")), class = "data.frame", row.names = c(NA, -7L))
user_39629140

A solution using the tidyverse package. You need to find a way to create a common grouping column with the right order of the individuals. dat2 is the final output.

library(tidyverse)

dat2 <- dat %>%
  mutate(Amount = as.numeric(str_remove(Amount, "\\$"))) %>%
  mutate(Group = map2_chr(From, To, ~str_c(sort(c(.x, .y)), collapse = "_"))) %>%
  group_by(Group) %>%
  summarize(Sum = sum(Amount, na.rm = TRUE)) %>%
  separate(Group, into = c("Individual_1", "Individual_2"), sep = "_") %>%
  mutate(Sum = str_c("$", Sum))
print(dat2)
# # A tibble: 5 x 3
#   Individual_1 Individual_2 Sum  
#   <chr>        <chr>        <chr>
# 1 A            B            $125 
# 2 A            C            $230 
# 3 A            D            $30  
# 4 B            C            $70  
# 5 C            D            $110

Data

dat <- read.table(text = "From  To  Amount
A     B   $100
A     C   $40
A     D   $30
B     A   $25
B     C   $70
C     A   $190
C     D   $110",
                header = TRUE)
user_76698090

A complete solution without packages, based on @RonakShah's great pmin/pmax approach, using list notation in aggregate (in contrast to formula notation) which allows name assignment.

with(
  transform(d, a=as.numeric(gsub("\\D", "", Amount)), b=pmin(From, To), c=pmax(From, To)),
  aggregate(list(Sum=a), list(Individual_1=b, Individual_2=c), function(x) 
    paste0("$", sum(x))))
#   Individual_1 Individual_2  Sum
# 1            A            B $125
# 2            A            C $230
# 3            B            C  $70
# 4            A            D  $30
# 5            C            D $110

Data:

d <- structure(list(From = c("A", "A", "A", "B", "B", "C", "C"), To = c("B", 
"C", "D", "A", "C", "A", "D"), Amount = c("$100", "$40", "$30", 
"$25", "$70", "$190", "$110")), class = "data.frame", row.names = c(NA, 
-7L))
user_65740380
Related