Is there a way to consolidate one hot encoded columns into one record by ID

Question

Lets say I have the following data:

quote_id = c(123,123,123,123,789,789,789,789,456,456,456,456)
Cake  = c( 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' )
Egg = c(1,0,0,0,0,0,0,0,0,0,1,0)
Regular_Milk = c(0,0,1,0,0,0,0,0,0,0,0,0)
Almond_Milk = c(0,0,0,0,1,0,0,0,0,0,0,0)
Coconut_Milk  = c(0,0,0,0,0,0,0,0,1,0,0,0)
Regular_Sugar = c(0,0,0,0,0,0,0,0,0,1,0,0)
Cane_Sugar = c(0,1,0,0,0,0,1,0,0,0,0,0)
Regular_Flour = c(0,0,0,1,0,0,0,0,0,0,0,0)
Oat_Flour = c(0,0,0,0,0,0,0,0,0,0,0,0)
Wheat_Flour = c(0,0,0,0,0,0,0,0,0,0,0,1)
Almond_Flour = c(0,0,0,0,0,0,0,1,0,0,0,0)

Old_Cake_Data = data.frame(quote_id, Cake , Egg, Regular_Milk, Almond_Milk, Coconut_Milk , Regular_Sugar, Cane_Sugar, Regular_Flour, Oat_Flour, Wheat_Flour, Almond_Flour)

Is there a way in SQL or R to get the following output:

quote_id = c(123,789,456)
Cake  = c( 'chocolate' , 'chocolate' ,'chocolate' )
Egg = c(1,0,1)
Regular_Milk = c(1,0,0)
Almond_Milk = c(0,1,0)
Coconut_Milk  = c(0,0,1)
Regular_Sugar = c(0,0,1)
Cane_Sugar = c(1,1,0)
Regular_Flour = c(1,0,0)
Oat_Flour = c(0,0,0)
Wheat_Flour = c(0,0,1)
Almond_Flour = c(0,1,0)

New_Cake_Data = data.frame(quote_id, Cake , Egg, Regular_Milk, Almond_Milk, Coconut_Milk , Regular_Sugar, Cane_Sugar, Regular_Flour, Oat_Flour, Wheat_Flour, Almond_Flour)

I thought about summing each column but the issue with that is that I need the output to be ordered by quote_id.

Essentially, I would like to have an output of one record per item id.

why is this tag sql and sql server? where you have that data? — Juan Carlos Oropeza, Jul 14 '19 at 19:40
Can you please describe the logical mapping between the input and output? For example, Cake appears to be the list of distinct items. However that is not the case for the rest. What is the expected conceptual logic so that we can help you with the code — camba1, Jul 14 '19 at 19:43
Possible duplicate: https://stackoverflow.com/questions/1660124/how-to-sum-a-variable-by-group This code would work for you although it re-orders based on the ```quote_id``` : ```aggregate(.~ quote_id + Cake, data = New_Cake_Data, FUN = max)``` — Cole, Jul 14 '19 at 20:38
Possible duplicate of [How to sum a variable by group](https://stackoverflow.com/questions/1660124/how-to-sum-a-variable-by-group) — Cole, Jul 14 '19 at 20:39

score 0 · Answer 1 · answered Jul 14 '19 at 22:28

In R you can try this:

quote_id = c(123,123,123,123,789,789,789,789,456,456,456,456)
Cake  = c( 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' , 'chocolate' )
Egg = c(1,0,0,0,0,0,0,0,0,0,1,0)
Regular_Milk = c(0,0,1,0,0,0,0,0,0,0,0,0)
Almond_Milk = c(0,0,0,0,1,0,0,0,0,0,0,0)
Coconut_Milk  = c(0,0,0,0,0,0,0,0,1,0,0,0)
Regular_Sugar = c(0,0,0,0,0,0,0,0,0,1,0,0)
Cane_Sugar = c(0,1,0,0,0,0,1,0,0,0,0,0)
Regular_Flour = c(0,0,0,1,0,0,0,0,0,0,0,0)
Oat_Flour = c(0,0,0,0,0,0,0,0,0,0,0,0)
Wheat_Flour = c(0,0,0,0,0,0,0,0,0,0,0,1)
Almond_Flour = c(0,0,0,0,0,0,0,1,0,0,0,0)

Old_Cake_Data = data.frame(quote_id, Cake , Egg, Regular_Milk, Almond_Milk, Coconut_Milk , Regular_Sugar, Cane_Sugar, Regular_Flour, Oat_Flour, Wheat_Flour, Almond_Flour)

# find quote_id's levels
lev<-levels(as.factor(Old_Cake_Data$quote_id))

# create a dataframe
New_Cake_Data <- Old_Cake_Data[1:length(lev),]
New_Cake_Data$quote_id<-lev

for( i in 1:length(lev)){
  d<-which(Old_Cake_Data$quote_id==lev[i])
  New_Cake_Data$Cake[i]<-Old_Cake_Data$Cake[d][1]
  New_Cake_Data$Egg [i]<-sum(Old_Cake_Data$Egg[d])
  New_Cake_Data$Regular_Milk [i]<-sum(Old_Cake_Data$Regular_Milk[d])
  New_Cake_Data$Almond_Milk [i]<-sum(Old_Cake_Data$Almond_Milk[d])
  New_Cake_Data$Coconut_Milk[i]<-sum(Old_Cake_Data$Coconut_Milk[d])
  New_Cake_Data$Regular_Sugar[i]<-sum(Old_Cake_Data$Regular_Sugar[d])
  New_Cake_Data$Cane_Sugar [i]<-sum(Old_Cake_Data$Cane_Sugar[d])
  New_Cake_Data$Regular_Flour[i]<-sum(Old_Cake_Data$Regular_Flour[d])
  New_Cake_Data$Oat_Flour [i]<-sum(Old_Cake_Data$Oat_Flour[d])
  New_Cake_Data$Wheat_Flour [i]<-sum(Old_Cake_Data$Wheat_Flour[d])
  New_Cake_Data$Almond_Flour [i]<-sum(Old_Cake_Data$Almond_Flour[d])
}


### final data
print(New_Cake_Data)

if you don't know the number of columns and the kind (numeric vs character)

# create a dataframe
New_Cake_Data <- Old_Cake_Data[1:length(lev),]
New_Cake_Data$quote_id<-lev

for( i in 1:length(lev)){
  d<-which(Old_Cake_Data$quote_id==lev[i])
  k<-i
  for(t in 2:ncol(New_Cake_Data)){
    if(is.numeric(Old_Cake_Data[,t])){
      New_Cake_Data[k,t]<-sum(Old_Cake_Data[d,t])
    }else{
      New_Cake_Data[k,t]<-Old_Cake_Data[d[1],t]
    }
  }
}

Is there a way to consolidate one hot encoded columns into one record by ID

1 Answers1