Bdtjtk

Updated with dummy data and dummycode - apologies, I assumed my question was simple and you could advice the best way without a reproducible example.

dummy<-data.frame(prodA=c(0,0,0,1,1,0,0,1),

              prodB=c(0,0,1,1,0,1,1,0),

              prodC=c(1,1,1,0,0,0,0,1))



dummy[,4:6]<-dummy[,1:3]



for (j in (1:nrow(dummy))){

    for (i in 4:6){

            dummy[j,i]<-ifelse(dummy[j,i]==1,colnames(dummy[i]),"")}

}

dummy2<-dummy[,4:6]

dummy$NewProds<-apply(dummy2,1,paste,collapse="") 

dummy$NewProds<-gsub(".1","//",dummy$NewProds)

My second attempt is as:

prods<-dummy[,1:3]

prods[,4:6]<-dummy[,1:3]

for (i in 4:6){

    prods[,i]<-colnames(prods[i-3])

}



prods[,7:9]<-prods[,4:6]

#works, but I will need multiple ifs for this to work, suggesting this

#won't be very efficient

prods[,10]<-ifelse(prods[,1]==1,prods[,4],"")

Original Post Follows:
I am playing with the Santander Product recommendation dataset from Kaggle. I have identified which products have been purchased from one month to another. This means I have 23 columns of 1's ( when a new product is added) and 0's (when not).
I created the following code to return the column name when a product has been purchased. It works great on a sample of 6 lines, but it runs forever when I try this on the 48k customers who changed, let alone the million in the dataset.

Is there another way to do this?

df2[,99:122]<-df2[,72:95]



for (j in (1:nrow(df2))){

    for (i in 99:122){

            df2[j,i]<-ifelse(df2[j,i]==1,colnames(df2[i]),"")}

}

df22<-df2[,99:122]

df2$NewProds<-apply(df22,1,paste,collapse="") 

df2$NewProds<-gsub("change.1","//",df2$NewProds)

I figured the challenge was that I am looking at every variable and so started with another approach whereby I would take a couple of versions of the data, and then do an if variable is 1 then take the name. However I couldn't get this to work, and I think I come to the same issue.

#copy a bunch of 1's and 0's

prods<-df2[,72:95]

#repeat and overwrite with colnames

prods[,25:48]<-df2[,72:95]

for (i in 25:48){

    prods[,i]<-colnames(prods[i-24])

}

prods[,49:72]<-prods[,25:48]

#attempt to only populate colnames if it was originally a 1 - doesn't work

prod[,49]<-ifelse(prod[,1]==1,prod[,25],"")

I haven't provided any data but I hope you can see what I am tring to do and can advise on efficient ways of doing this.
Thanks in advance,
J

Using apply as @AndersEllernBilgrau illustrated is one obvious way to do it, but it will be slow for data sets with many rows.

dummy[["NewProds"]] <- do.call(

    paste,

    c(mapply(ifelse,

             dummy,

             names(dummy),

             MoreArgs = list(no = ""),

             SIMPLIFY = FALSE),

      sep = "//"))

is a bit harder to follow, but it will be much faster:

library(microbenchmark)



n <- 10000

dummy <- data.frame(prodA = rep(c(0,0,0,1,1,0,0,1), n),

                    prodB = rep(c(0,0,1,1,0,1,1,0), n),

                    prodC = rep(c(1,1,1,0,0,0,0,1), n))



microbenchmark(

    do.call = do.call(

        paste,

        c(mapply(ifelse,

                 dummy,

                 names(dummy),

                 MoreArgs = list(no = ""),

                 SIMPLIFY = FALSE),

          sep = "//")),

    apply = apply(

        dummy == 1,

        1,

        function(x) paste0(names(which(x)), collapse = "//")

    ))

## Unit: milliseconds

##     expr       min        lq      mean   median       uq      max neval cld

##  do.call  63.92695  65.44777  72.07261  67.8667  73.3850 184.5151   100  a 

##    apply 296.81323 364.31947 404.71894 397.0927 443.7223 683.3892   100   b

Without data, I have a hard time understanding precisely what you want to do.
A couple of things are (almost) certain however:

• You probably do not need for loops.


• You should used R's vectorized functions, the dataset is not that big

Using some toy data, does the following do what you want?

d <- 23

n <- 46e3



# Simulate some toy data

df <- data.frame(matrix(rbinom(d*n, 1, 0.1), n, d),

                 row.names = paste0("row", 1:n))

head(df)

      X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 X16 X17 X18 X19 X20 X21 X22 X23

row1  0  0  0  0  0  0  0  1  0   0   0   0   0   0   0   1   0   0   0   0   0   0   0

row2  1  0  0  0  0  0  0  0  0   0   0   0   0   0   0   0   0   0   0   0   0   0   0

row3  0  0  0  0  0  0  0  1  0   0   0   0   0   0   0   0   0   0   0   1   0   0   0

row4  0  0  0  1  0  0  0  0  0   0   1   0   0   0   0   0   0   0   0   1   0   0   0

row5  0  0  0  0  0  0  1  0  0   0   0   0   0   0   1   0   0   0   0   0   0   0   0

row6  0  0  0  1  0  0  0  0  0   0   0   0   0   0   0   0   0   1   0   0   1   0   0







# Paste together the colnames of all non-zero rows

res <- apply(df == 1, 1, function(x) paste0(names(which(x)), collapse = "-"))

head(res)

#    row1         row2         row3         row4         row5         row6 

#"X8-X16"         "X1"     "X8-X20" "X4-X11-X20"     "X7-X15" "X4-X18-X21" 

I.e. res is here a character vector of length n with the colnames of each row the corresponding to 1 entries pasted together (with separator -). This it at least what it appears to me what your code is doing conceptually.