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.
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