0

I'm trying to convert the following data with two string columns to expand from long to wide. What is the most efficient method in R to achieve the following:

Sample data:

data_sample <- data.frame(code=c(1,1,2,3,4,2,4,3),name=c("bill","bob","rob","max","mitch","john","bart","joe"),numberdata=c(100,400,300,-200,300,-500,100,-400))

Desired function to result in the following dataset:

data_desired <- data.frame(code=c(1,2,3,4),name1=c("bill","rob","max","mitch"),name2=c("bob","john","joe","bart"),numberdata1=c(100,300,-200,300),numberdata2=c(400,-500,-400,100))

I'm using big data (the real code is 1-100,000), is there an efficient data.table method to accomplish this? Thanks!

Neal Barsch
  • 2,810
  • 2
  • 13
  • 39

2 Answers2

3

You may use dcast -

library(data.table)

setDT(data_sample)
dcast(data_sample, code~rowid(code), value.var = c('name', 'numberdata'))

#   code name_1 name_2 numberdata_1 numberdata_2
#1:    1   bill    bob          100          400
#2:    2    rob   john          300         -500
#3:    3    max    joe         -200         -400
#4:    4  mitch   bart          300          100
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Just to note ```reshape2``` (which I tried to use) has to be detached (un-libraried) for this to work, but works great even on big data – Neal Barsch Dec 28 '21 at 12:05
0

If the naming doesnt matter (eg. name1, name2 etc) you could

  1. Create a new grouping variable running over sequential observations in each code
  2. split according to this group
  3. make a primary key to use for merging on all resultings data.tables
  4. finally perform keyed merges across the results
data_sample <- data.frame(code=c(1,1,2,3,4,2,4,3),name=c("bill","bob","rob","max","mitch","john","bart","joe"),numberdata=c(100,400,300,-200,300,-500,100,-400))
setDT(data_sample)
# Create new group indicating the variable "*group*"
data_sample[, `*group*` := seq.int(.N), by = code]
# Split the data.table according to the new group
groups <- split(data_sample, by = '*group*')
# Set key on each group to the "code" variable
lapply(groups, \(dt){
  setkey(dt, code)
  # Remove group
  dt[, `*group*` := NULL]
  })
# Merge the result (using Reduce here 
res <- Reduce(\(dt1, dt2)merge(dt1, dt2, all = TRUE), groups)
# Reorder columns
setcolorder(res, sort(names(res)))
res
   code name.x name.y numberdata.x numberdata.y
1:    1   bill    bob          100          400
2:    2    rob   john          300         -500
3:    3    max    joe         -200         -400
4:    4  mitch   bart          300          100

The benefit of this is of course that this works in cases where you have more than 2 entries for each code.

Oliver
  • 8,169
  • 3
  • 15
  • 37