library(tidyverse)
library(magrittr)
library(googlesheets4)
library(kableExtra)

Table of expenses

df_raw <- read_sheet('https://docs.google.com/spreadsheets/d/1HHpHJ6ovtPFheNsbxkp1uJmM6SrvgYLPYDdhrK2zSx4/edit#gid=701905824')

df <- df_raw %>% select(-1, -6) %>% set_colnames(c("payer", "payer_included", "type", "amount", "to_pay"))


df %>%
  kable() %>%
  kable_styling(bootstrap_options = c("hover", "condensed", "responsive"))
payer payer_included type amount to_pay
Sergiu Yes train to puglia 164.85 Austeja, Patricia
Sergiu Yes monopoly stay 428.00 Austeja, Alina, Dan, Patricia
Sergiu Yes stay galipoli 726.79 Austeja, Alina, Dan, Patricia
Sergiu Yes car 362.00 Austeja, Alina, Dan, Patricia
Austeja No train for Patricia and Sergiu return 75.00 Patricia, Sergiu
Austeja No train for Dan and Alina return 129.80 Alina, Dan

Balance by expense

df %<>% mutate(id = row_number())

df_bal <- df %>% pivot_longer(cols = c(payer, to_pay), names_to = "roles", values_to = "names") %>%
  mutate(names = strsplit(names, ", ")) %>%
  unnest(names) %>% 
  group_by(id) %>% mutate(n = n()) %>%
  mutate(balance = case_when(
    roles == "payer" &  payer_included == "Yes" ~ ((n - 1)/ n) * amount,
    roles == "payer" &  payer_included == "No" ~ amount,
    roles == "to_pay" &  payer_included == "Yes" ~ - amount/n,
    roles == "to_pay" &  payer_included == "No" ~ -amount/(n - 1),
    TRUE ~ NA_real_
  ))

df_bal %>%
  kable() %>%
  kable_styling(bootstrap_options = c("hover", "condensed", "responsive"))
payer_included type amount id roles names n balance
Yes train to puglia 164.85 1 payer Sergiu 3 109.900
Yes train to puglia 164.85 1 to_pay Austeja 3 -54.950
Yes train to puglia 164.85 1 to_pay Patricia 3 -54.950
Yes monopoly stay 428.00 2 payer Sergiu 5 342.400
Yes monopoly stay 428.00 2 to_pay Austeja 5 -85.600
Yes monopoly stay 428.00 2 to_pay Alina 5 -85.600
Yes monopoly stay 428.00 2 to_pay Dan 5 -85.600
Yes monopoly stay 428.00 2 to_pay Patricia 5 -85.600
Yes stay galipoli 726.79 3 payer Sergiu 5 581.432
Yes stay galipoli 726.79 3 to_pay Austeja 5 -145.358
Yes stay galipoli 726.79 3 to_pay Alina 5 -145.358
Yes stay galipoli 726.79 3 to_pay Dan 5 -145.358
Yes stay galipoli 726.79 3 to_pay Patricia 5 -145.358
Yes car 362.00 4 payer Sergiu 5 289.600
Yes car 362.00 4 to_pay Austeja 5 -72.400
Yes car 362.00 4 to_pay Alina 5 -72.400
Yes car 362.00 4 to_pay Dan 5 -72.400
Yes car 362.00 4 to_pay Patricia 5 -72.400
No train for Patricia and Sergiu return 75.00 5 payer Austeja 3 75.000
No train for Patricia and Sergiu return 75.00 5 to_pay Patricia 3 -37.500
No train for Patricia and Sergiu return 75.00 5 to_pay Sergiu 3 -37.500
No train for Dan and Alina return 129.80 6 payer Austeja 3 129.800
No train for Dan and Alina return 129.80 6 to_pay Alina 3 -64.900
No train for Dan and Alina return 129.80 6 to_pay Dan 3 -64.900

Final balance

df_bal %>% group_by(names) %>%
  summarise(balance = sum(balance)) %>%
  kable() %>%
  kable_styling(bootstrap_options = c("hover", "condensed", "responsive"))
names balance
Alina -368.258
Austeja -153.508
Dan -368.258
Patricia -395.808
Sergiu 1285.832