Shawn Mehan Shawn Mehan - 3 months ago 14
R Question

Reshape levels of a column (long) into new columns (wide)

I want to take the levels of a column in one DF and add each level as a new column in a new DF. Here is a toy dataset showing the source and ideal target DFs.

Source DF

person hour ride
Bill 1 A
Sue 2 B
Bob 1 C
Jill 3 B
Dan 3 A
Tina 3 A


Mapped DF

hour A B C Saturation
1 1 0 1 .66
2 0 1 0 .33
3 1 1 0 .66


Here is a test data set:

test_data <- cbind.data.frame(person = c('Bill', 'Sue', 'Bob', 'Jill', 'Dan', 'Tina'),
hour = factor(c(1, 2, 1, 3, 3, 3)),
ride = c('A', 'B', 'C', 'B', 'A', 'A'))

test_data$person <- as.character(test_data$person)


See how each ride in
Source
turns into a new column in
Mapped
. I can get levels and use them to create a mapped DF via

new_data <- cbind.data.frame(hour = levels(test_data$hour))


but it all fails when I try to iterate through levels to add new columns. I see the levels.

unlist(lapply(levels(test_data$ride), function(x) paste(x)))


yields

[1] "A" "B" "C"


So how to go through the levels in
$ride
and add a column in the mapped DF?

Bonus: I am going to run through each of the rows in
test_data
and
ifelse()
a
1
in the column that corresponds to that ride to show it had a rider, and a
0
otherwise, but someone must see how to do this more elegantly? As it stands, I would need an
ifelse
for every column extracted from the levels in
$ride
which I know has to be more verbose than required.

Answer
require(reshape2)

mydat <- recast(test_data,hour~ride)
mydat
  hour A B C
1    1 1 0 1
2    2 0 1 0
3    3 2 1 0
# 2nd part
for(i in 2:ncol(mydat)){
  for(ii in 1:nrow(mydat)){
    if(mydat[ii,i] > 0) {mydat[ii,i] <- 1}
  }
}
  hour A B C
1    1 1 0 1
2    2 0 1 0
3    3 1 1 0
Comments