Вычисление значения для уникальных записей столбца с использованием dplyr
Я не смог найти аналогичный вопрос в SO. Я совсем новичок в dplyr в целом.
Рассмотрим игрушечный фрейм данных некоторых статистических данных о продажах.
df <- data.frame(
added = c("2020-10-05", "2020-10-30", "2020-11-04", "2020-12-10", "2020-12-14"),
closed = c("", "2020-11-05", "2020-12-10", "", ""),
value = c(100, 200, 300, 400, 500),
stage = c("Quote", "Won", "Lost", "Quote", "Quote")
)
Он содержит дату сделки added, когда она была closed, сделку value и текущую stage сделки. Если сделка не выиграна или не проиграна, у нее нет closed даты. Я хочу рассчитать значение конвейера, то есть стоимость сделок, которые еще не выиграны или не проиграны, для каждой уникальной записи в столбцах added и closed (поскольку это единственные моменты, когда значение конвейера может измениться).
Я заставил его работать через смесь base R и dplyr. Сначала я создаю фрейм данных дат:
date_df <- as.data.frame(seq(as.Date("2020-10-01"),as.Date(Sys.Date()),1))
colnames(date_df) <- c("date")
Мой выбор даты начала произволен, если она предшествует первой дате в столбце added. Затем я оцениваю это выражение для каждой записи в date_df через цикл:
library(tidyverse)
pipeline <- c()
for (i in 1:nrow(date_df)) {
pipeline <-
df %>%
filter(
(added <= date_df$date[i] & closed > date_df$date[i] & closed < Sys.Date()) |
(added <= date_df$date[i] & stage != "Won" & stage != "Lost")
) %>%
summarise(pipeline = sum(value))
date_df$pipeline[i] <- pipeline
}
Что правильно дает мне:
> date_df
date pipeline
1 2020-10-01 0
2 2020-10-02 0
3 2020-10-03 0
4 2020-10-04 0
5 2020-10-05 100
6 2020-10-06 100
7 2020-10-07 100
8 2020-10-08 100
9 2020-10-09 100
10 2020-10-10 100
11 2020-10-11 100
12 2020-10-12 100
13 2020-10-13 100
14 2020-10-14 100
15 2020-10-15 100
16 2020-10-16 100
17 2020-10-17 100
18 2020-10-18 100
19 2020-10-19 100
20 2020-10-20 100
21 2020-10-21 100
22 2020-10-22 100
23 2020-10-23 100
24 2020-10-24 100
25 2020-10-25 100
26 2020-10-26 100
27 2020-10-27 100
28 2020-10-28 100
29 2020-10-29 100
30 2020-10-30 300
31 2020-10-31 300
32 2020-11-01 300
33 2020-11-02 300
34 2020-11-03 300
35 2020-11-04 600
36 2020-11-05 400
37 2020-11-06 400
38 2020-11-07 400
39 2020-11-08 400
40 2020-11-09 400
41 2020-11-10 400
42 2020-11-11 400
43 2020-11-12 400
44 2020-11-13 400
45 2020-11-14 400
46 2020-11-15 400
47 2020-11-16 400
48 2020-11-17 400
49 2020-11-18 400
50 2020-11-19 400
51 2020-11-20 400
52 2020-11-21 400
53 2020-11-22 400
54 2020-11-23 400
55 2020-11-24 400
56 2020-11-25 400
57 2020-11-26 400
58 2020-11-27 400
59 2020-11-28 400
60 2020-11-29 400
61 2020-11-30 400
62 2020-12-01 400
63 2020-12-02 400
64 2020-12-03 400
65 2020-12-04 400
66 2020-12-05 400
67 2020-12-06 400
68 2020-12-07 400
69 2020-12-08 400
70 2020-12-09 400
71 2020-12-10 500
72 2020-12-11 500
73 2020-12-12 500
74 2020-12-13 500
75 2020-12-14 1000
76 2020-12-15 1000
77 2020-12-16 1000
78 2020-12-17 1000
79 2020-12-18 1000
80 2020-12-19 1000
81 2020-12-20 1000
82 2020-12-21 1000
По сути, я суммирую стоимость открытых сделок на каждую дату. Однако мне кажется, что это очень неэффективный подход. Реальный набор данных достаточно обширен, и для оценки на каждую дату требуется довольно много времени.
Меня действительно интересует только выражение, которое будет оцениваться для каждой уникальной записи в added и closed, как описано выше. Существуют ли какие-либо элегантные решения с использованием dplyr, которые достигают этого (предпочтительно) без создания нового объекта? Идея состоит в том, чтобы позже построить его и отправить в блестящее приложение, поэтому сокращение времени вычислений может иметь решающее значение.
Заранее спасибо!
РЕДАКТИРОВАТЬ
Чтобы показать пример для двух дат 2020-11-04 и 2020-11-05:
На 2020-11-04 три сделки (запись 1-3 в данных) не относятся ни к Lost, ни к Won, поэтому значение конвейера на эту дату равно 600.
На 2020-11-05 одна сделка теперь Lost (вторая запись в данных), а стоимость пайплайна падает до 400.
Таким образом, при закрытии сделки стоимость конвейера уменьшается, а при добавлении сделки стоимость конвейера увеличивается.
Значения для всех дат добавляются к исходному вопросу.
Ответы 2
Я не понимаю, почему вы используете closed < Sys.Date() есть ли у вас будущие закрытые сделки, если это так, этот код будет учитывать это:
df %>%
mutate(across(closed:added, lubridate::ymd), cl = closed < Sys.Date()) %>%
pivot_longer(c(closed, added), values_to = "date") %>%
filter(!is.na(date)) %>%
group_by(date) %>%
summarise(pipeline=sum((stage= = "Quote" | cl) *value)) %>%
mutate(pipeline=cumsum(pipeline))
# A tibble: 7 x 2
date summed
<date> <dbl>
1 2020-10-05 100
2 2020-11-05 300
3 2020-10-30 500
4 2020-12-10 1200
5 2020-11-04 1500
6 2020-12-10 2200
7 2020-12-14 2700
Пожалуйста, также смотрите мое редактирование с разъяснениями в исходном сообщении.
Такой подход также будет полезен
result <- dff %>% mutate(id = row_number(),
added = as.Date(added),
closed = as.Date(closed)) %>%
pivot_longer(cols = c("added", "closed"), names_to = "activity", values_to = "dates") %>%
mutate(activity = factor(activity, levels = c("added", "closed"), ordered = T)) %>%
arrange(dates, activity) %>%
mutate(val = cumsum(value*case_when(activity == "added" ~ 1,
activity == "closed" ~ -1,
TRUE ~ 0))) %>%
group_by(dates) %>% summarise(val = min(val))
# A tibble: 7 x 2
dates val
<date> <dbl>
1 2020-10-05 100
2 2020-10-30 300
3 2020-11-04 600
4 2020-11-05 400
5 2020-12-10 500
6 2020-12-14 1000
7 NA 0
Dput(dff) используется
> dput(dff)
structure(list(added = structure(c(18540, 18565, 18570, 18606,
18610), class = "Date"), closed = structure(c(NA, 18571, 18606,
NA, NA), class = "Date"), value = c(100, 200, 300, 400, 500),
stage = c("Quote", "Won", "Lost", "Quote", "Quote")), row.names = c(NA,
-5L), class = "data.frame")
Если вы хотите отобразить результаты, используйте complete и fill как
plot <- result %>% filter(!is.na(dates)) %>%
complete(dates = seq.Date(min(dates), max(dates), by = "day")) %>%
fill(val) %>%
ggplot() +
geom_line(aes(x = dates, y = val))
plot
Очень аккуратный! Как мне получить двойное значение для 2020-12-10 ? Конечным результатом будет 500, но ваше решение содержит две записи для этой даты. Кроме того, я был бы признателен, если бы вы добавили mutate(dates = as.Date(dates)) %>% после pivot_longer, чтобы можно было использовать исходный df.
Это выглядит хорошо! Для полноты картины я бы удалил select(dates, val) %>%, так как это лишнее, и заменил бы summarise(val = last(val)) на summarise(val = min(val)) для облегчения интерпретации. Отмечено как решение.
Это не было ясно, но будущие закрытые сделки действительно возможны, поэтому closed < Sys.Date(). Представленный здесь результат неверен. Например, значение для 2020-11-04 должно быть 600, а для 2020-11-05 должно быть 400.