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
|