我有一个银行账户交易表,我想添加一列,标记任何退款交易(包括首次付款和退款(,但就我而言,我不知道如何匹配这些行。
样本数据:
transactions <- tibble(date = as.Date(c("2020-01-01", "2020-01-02", "2020-01-02", "2020-01-03", "2020-01-04")),
merchant = c("A", "B", "B", "C", "A"),
amount = c(10, 5, -5, 9, 10))
期望输出:
# A tibble: 5 x 4
date merchant amount refund
<date> <chr> <dbl> <lgl>
1 2020-01-01 A 10 FALSE
2 2020-01-02 B 5 TRUE
3 2020-01-02 B -5 TRUE
4 2020-01-03 C 9 FALSE
5 2020-01-04 A 10 FALSE
目前,我只想在退款当天匹配付款,但我可能会稍后更改,将付款后任何一天发生的退款包括在内。我的第一个想法是使用下面这样的东西,但它不能确保所有值都出现在同一行中。我更喜欢一个简洁的答案。
mutate(refund = if_else(date %in% date &
merchant %in% merchant &
(amount*-1) %in% amount,
T, F))
一种解决方案可以按标识匹配事务的列进行分组,在本例中为date
和merchant
。这将消除对if_else
中的x %in% y
语法的需要。
解决方案
使用case_when()
是因为它提供了细化逻辑的灵活性。if_else
在2种情况下是等效的(或更快?(。
transactions %>%
group_by(date, merchant) %>%
# This logic says:
# When the sum of the amounts DO NOT equal to 0, it is NOT a refund
mutate(refund = case_when(sum(amount) != 0 ~ F,
T ~ T))
输出
# A tibble: 5 x 4
# Groups: date, merchant [4]
date merchant amount refund
<date> <chr> <dbl> <lgl>
1 2020-01-01 A 10 FALSE
2 2020-01-02 B 5 TRUE
3 2020-01-02 B -5 TRUE
4 2020-01-03 C 9 FALSE
5 2020-01-04 A 10 FALSE
我在做其他事情时灵机一动,这是一个比我想象的更长的过程,但为了完整起见,我在这里发布了我的工作解决方案。
首先,我为每一行添加了一个唯一的ID(事实上,我的数据已经有了这个(
transactions <- tibble(date = as.Date(c("2020-01-01", "2020-01-02", "2020-01-02", "2020-01-03", "2020-01-04")),
merchant = c("A", "B", "B", "C", "A"),
amount = c(10, 5, -5, 9, 10)) %>%
mutate(id = seq_len(nrow(.)))
根据账户的资金流出(正值(还是流出(负值(,将数据帧一分为二。对于货币,我将金额列更改为正值,以便它们可以匹配。
transactions_out <- transactions %>%
filter(amount > 0)
transactions_in <- transactions %>%
filter(amount < 0) %>%
mutate(amount = amount*-1)
使用inner_join()
匹配两行中日期、商家和金额相同的行。然后使用pivot_longer()
和pull()
保存这些事务的ID。
在我的实际数据中,我在这里添加了额外的行来完善匹配,以减少误报。
refund_ids <- inner_join(transactions_out, transactions_in,
by = c("date", "merchant", "amount"),
suffix = c("_out", "_in")) %>%
pivot_longer(starts_with("id"),
names_to = "type",
values_to = "id") %>%
pull(id)
最后,我将新的退款列添加到refund_ids
:中ID的原始数据帧中
transactions <- transactions %>%
mutate(refund = if_else(id %in% refund_ids, T, F))
输出:
> transactions
# A tibble: 5 x 5
date merchant amount id refund
<date> <chr> <dbl> <int> <lgl>
1 2020-01-01 A 10 1 FALSE
2 2020-01-02 B 5 2 TRUE
3 2020-01-02 B -5 3 TRUE
4 2020-01-03 C 9 4 FALSE
5 2020-01-04 A 10 5 FALSE