CODEWITHSUNDEEP
rtransform
Yilun Zhang
Yilun Zhang

Reputation: 9018

R: transform data (factor value as column name)

I have a dataset that I want to transform in to another format:

The data looks like this (for privacy issue, I cannot put the original data):

ID1 ID2 Month   Value
1   A   Jan-03  10
2   B   Jan-03  11
1   A   Feb-03  12
2   B   Feb-03  13
1   A   Mar-03  14
2   B   Mar-03  15

I want the Month column to be as the column name, and the format looks like this:

ID1 ID2 Jan-03  Feb-03  Mar-03
1   A   10  12  14
2   B   11  13  16

Thanks!

Upvotes: 3

Views: 650

Answers (1)

akrun
akrun

Reputation: 887213

You could try

 df$Month <- factor(df$Month, levels=unique(df$Month))
 reshape(df, idvar=c('ID1', 'ID2'), timevar='Month', direction='wide')
 #  ID1 ID2 Value.Jan-03 Value.Feb-03 Value.Mar-03
 #1   1   A           10           12           14
 #2   2   B           11           13           15

Or

library(reshape2)
dcast(df, ID1+ID2~Month, value.var='Value')
#   ID1 ID2 Jan-03 Feb-03 Mar-03
#1   1   A     10     12     14
#2   2   B     11     13     15

Or

library(tidyr)
spread(df, Month, Value)
#   ID1 ID2 Jan-03 Feb-03 Mar-03
#1   1   A     10     12     14
#2   2   B     11     13     15

data

df <- structure(list(ID1 = c(1L, 2L, 1L, 2L, 1L, 2L), ID2 = c("A", 
"B", "A", "B", "A", "B"), Month = c("Jan-03", "Jan-03", "Feb-03", 
"Feb-03", "Mar-03", "Mar-03"), Value = 10:15), .Names = c("ID1", 
"ID2", "Month", "Value"), class = "data.frame", row.names = c(NA, 
-6L))

Upvotes: 5

Related Questions