pass other column inside spread with duplicate identifiers

Question

I have the below dataframe where I am trying to spread the feature_code by passing the actv_amt so that I get the corresponding actv_amt for the corresponding feature code. I am trying to pass as count_FEATURE = ACTV_AMT it is passing the value but not merging the data.

for reference, I asked a question earlier take unique count and sum each unique values in R

Input type: 1
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      INTEREST        855 

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

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

Input type 2:

Input
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    

Getting output:
ST_DATE ND_DATE LO_NO   ACTV_CODE   ACTV_AMT    AB_NO   INTEREST INSTALLMENT     L_NU   
7/Expected7/16 265          O           29           1      1          1           855 

Expected output:
ST_DATE ND_DATE LO_NO   ACTV_CODE   ACTV_AMT    AB_NO   INTEREST INSTALLMENT     L_NU   
7/27/16 7/27/16 265        O           29           1      15         14           855

Code implemented:

dt %>%
  group_by(AB_NO,LO_NO,L_NU)%>% 
  mutate(ACTV_AMT = sum(ACTV_AMT),
         ST_DATE = min(ST_DATE),
         ND_DATE = max(ND_DATE)) %>%
  ungroup() %>%
  mutate(id = row_number(),
         FEATURE_CODE = paste0("FTR_", FEATURE_CODE),
         ACTV_CODE = paste0("ACTV_", ACTV_CODE),
         count_FEATURE = 1,
         count_ACTV = 1) %>%
  spread(FEATURE_CODE, count_FEATURE) %>%
  spread(ACTV_CODE, count_ACTV) %>%
  select(-id) %>%
  group_by(ST_DATE, ND_DATE, LO_NO, ACTV_AMT, AB_NO, L_NU) %>%
  summarise_all(sum, na.rm=T) %>%
  ungroup()

Can anyone help me in getting the expected output.

@Hardikgupta I shared my data above or refer the link you will get it easily — vinay karagod, Nov 10 '17 at 05:01

score 1 · Accepted Answer · answered Nov 10 '17 at 05:26

You can try like this

library(reshape2)

df <- read.table(text = "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      INTEREST        855", header = T)

dcast(df, ST_DATE+ND_DATE+LO_NO+ACTV_CODE+AB_NO+L_NU~FEATURE_CODE, value.var = "ACTV_AMT", fun.aggregate = sum)

output:
-------
  ST_DATE ND_DATE LO_NO ACTV_CODE AB_NO L_NU INTEREST
1 7/27/16 7/27/16   265         O     1  855       29

input2:
-------
df <- read.table(text = "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", header = T)

dcast(df, ST_DATE+ND_DATE+LO_NO+ACTV_CODE+AB_NO+L_NU~FEATURE_CODE, value.var = "ACTV_AMT", fun.aggregate = sum)

output:
-------
  ST_DATE ND_DATE LO_NO ACTV_CODE AB_NO L_NU INSTALLMENT INTEREST
1 7/27/16 7/27/16   265         O     1  855          14       15

I am considering the min and max values of the dates how do I consider from the above code — vinay karagod, Nov 10 '17 at 13:14

pass other column inside spread with duplicate identifiers

1 Answers1