Удаление всех транзакций после обнаружения плохого кредита

#sql #r #spss

Вопрос:

Ниже приведен небольшой набор данных записей транзакций с ИДЕНТИФИКАТОРОМ, ДАТОЙ месяца, фиктивной переменной Bad_Credit или нет. Я хотел бы удалить все транзакции после начала плохого кредита. Столбец ВЫВОДА указывает правильный результат, который является строкой 1,2,3,5,6,8,10.

Это всего лишь пример, может быть тысячи строк. SQL, R, SPSS будут работать. Спасибо.

Дата	ID	Bad_CREDIT	ВЫВОД
12	A	1	1
15	A	1	1
18	A	0	1
2	B	0	0
10	B	1	1
20	B	0	1
5	C	0	0
15	C	1	1
1	D	0	0
9	E	1	1

1. Кстати, плохой кредит не обязательно должен быть из второй транзакции, он может быть третьим или более поздним…

2. Кто-нибудь знает, как использовать SPSS для этого?

Ответ №1:

Вы можете arrange использовать данные по ID и DATE и для каждого ID присваивать 0, если первое значение Bad_CREDIT равно 0.

 library(dplyr)

df %>%
  arrange(ID, DATE) %>%
  group_by(ID) %>%
  mutate(OUTPUT = as.integer(!(first(Bad_CREDIT) == 0 amp; row_number() == 1)))

#    DATE ID    Bad_CREDIT OUTPUT
#   <int> <chr>      <int>  <int>
# 1    12 A              1      1
# 2    15 A              1      1
# 3    18 A              0      1
# 4     2 B              0      0
# 5    10 B              1      1
# 6    20 B              0      1
# 7     5 C              0      0
# 8    15 C              1      1
# 9     1 D              0      0
#10     9 E              1      1

данные

 df <- structure(list(DATE = c(12L, 15L, 18L, 2L, 10L, 20L, 5L, 15L, 
1L, 9L), ID = c("A", "A", "A", "B", "B", "B", "C", "C", "D", 
"E"), Bad_CREDIT = c(1L, 1L, 0L, 0L, 1L, 0L, 0L, 1L, 0L, 1L)), 
row.names = c(NA, -10L), class = "data.frame")

Ответ №2:

Если я правильно понимаю, вы можете использовать оконные функции:

 select t.*
from (select t.*,
             min(case when bad_credit = 1 then date end) over (partition by id) as min_bd_date
      from t
     ) t
where date >= min_bd_date;

Вы также можете сделать это с помощью коррелированного подзапроса:

 select t.*
from t
where t.date >= (select min(t2.date)
                 from t t2
                 where t2.id = t.id and
                       t2.bad_credit = 1
                );

Ответ №3:

Если это находится в базе данных, то я думаю, что SQL, вероятно, лучшее место для решения этой проблемы. Однако, если у вас уже есть это в R, тогда …

Вот метод R, использующий dplyr :

 library(dplyr)
dat %>%
  group_by(ID) %>%
  mutate(OUTPUT2 =  cumany(Bad_CREDIT)) %>%
  ungroup()
# # A tibble: 10 x 5
#     DATE ID    Bad_CREDIT OUTPUT OUTPUT2
#    <int> <chr>      <int>  <int>   <int>
#  1    12 A              1      1       1
#  2    15 A              1      1       1
#  3    18 A              0      1       1
#  4     2 B              0      0       0
#  5    10 B              1      1       1
#  6    20 B              0      1       1
#  7     5 C              0      0       0
#  8    15 C              1      1       1
#  9     1 D              0      0       0
# 10     9 E              1      1       1

Поскольку это фактически простая операция группировки, тогда базовый R и data.table решения такие же простые.

  ave(dat$Bad_CREDIT, dat$ID, FUN=cumany)
#  [1] 1 1 1 0 1 1 0 1 0 1

library(data.table)
datDT <- as.data.table(dat)
datDT[, OUTPUT2 :=  cumany(Bad_CREDIT), by = .(ID)]

Ответ №4:

Вы можете использовать EXISTS следующее:

 select t.* from your_table t
where exists
     (select 1
        from your_table tt
       where t.id = tt.id
         and t.date >= tt.date 
         and tt.bad_credit = 1);

Ответ №5:

Это для SPSS:

 sort cases by ID date.
compute PullOut=Bad_CREDIT.
if $casenum>1 and ID=lag(ID) and lag(PullOut)=1 PullOut=1.
exe.