在同一客户的R中传播和合并行记录 [英] Spread and merge row records in R for the same customer

问题描述

我具有下面的数据框，其中我试图将一个客户的多个交易合并到一个记录中.

I have the below data frame where I am trying to merge multiple transactions of the one customer into one single record.

输入:

ST_DATE ND_DATE LO_NO   ACTV_CODE   ACTV_AMT    AB_NO   FEATURE_CODE    L_NU    
7/27/16 7/27/16 265       O          15          1      INTEREST        855          
7/27/16 7/27/16 265       O          14          1      INSTALLMENT 855  

预期输出:

ST_DATE ND_DATE LO_NO   ACTV_CODE   ACTV_AMT    AB_NO   FEATURE_INTEREST FEATURE_INSTALLMENT     L_NU   
7/27/16 7/27/16 265      O           29          1             1            1                    855

尝试:

install1 <- install %>% 
  group_by(LO_NO,AB_NO,L_NU) %>%
  slice(which.min(as.Date(ST_DATE, '%Y/%m/%d'))) %>%
  slice(which.max(as.Date(ND_DATE, '%Y/%m/%d'))) %>%
  summarise(ACTV_AMT = sum(ACTV_AMT)) %>% 
  spread(FEATURE_CODE,fill = 0) %>%  # confused here on what to put key value pairs so that I can get the above output

任何人都可以帮助我实现代码.如果该功能代码与客户无关，则有许多feature_codes可用，如果应该fill 0，或者如果有2 same feature_codes，例如INTEREST在该月两次，则应该在FEATURE_INTEREST

Can anyone help me in implementing the code. There are many feature_codes are available if that feature code is not associated with the customer it should fill 0 or if there are 2 same feature_codes like INTEREST is twice for that month it should fill as 2 in FEATURE_INTEREST

推荐答案

您可以先使用group by，然后使用dcast散布汇总值-

You can first use group by and then use dcast to spread out the aggregated values-

install1 <- install %>% 
  group_by(LO_NO,AB_NO,L_NU,FEATURE_CODE) %>%
  summarise(count1= n())

library(reshape2)
final <- dcast(install1, formula = LO_NO + AB_NO + L_NU ~ FEATURE_CODE, fill = 0)

希望这会有所帮助

这篇关于在同一客户的R中传播和合并行记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！