CODEWITHSUNDEEP
rreshape
Sameer
Sameer

Reputation: 289

Reshape data from long to wide with multiple explained variables

I'm trying to convert my data frame format using R. I want unique company name as it has multiple observations for each company. My data looks like

company name    Values  Year
    A              1    2010
    A              2    2011
    B              4    2010
    B              6    2012
    C              8    2011

I want below format

 company name   first_value First_year  second_values second_year
     A              1          2010          2           2011
     B              4          2010          6           2012  
     C              8          2011          NA           NA

I have tried this code but it is not giving result what I am expecting

library(plyr)
extract.hashtags <- function(x) {
x <- subset(x,select=c(-Company.Name))
mat <- as.matrix(x)
dim(mat) <- c(1,length(mat))
as.data.frame(mat)
}

df1 = ddply(data, .(Company.Name), extract.hashtags )

Upvotes: 2

Views: 502

Answers (2)

David Arenburg
David Arenburg

Reputation: 92292

A similar solution using the devel version of data.table (v 1.9.5+)

library(data.table) ## v 1.9.5+
dcast(setDT(df)[, indx := 1:.N, by = company_name], 
      company_name ~ indx, value.var = c("Values", "Year"))

# c   ompany_name Values_1 Values_2 Year_1 Year_2
# 1:            A        1        2   2010   2011
# 2:            B        4        6   2010   2012
# 3:            C        8       NA   2011     NA

The idea is to add a counter per group and then reshape from long to wide according to that counter while specifying two variables as the explained vars simultaneously (currently available only in the devel version).

Upvotes: 6

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193517

You can use reshape in base R after you add a "time" variable, which can be done with getanID from my "splitstackshape" package:

reshape(getanID(mydf, "companyname"), idvar = "companyname", 
          timevar = ".id", direction = "wide")
#    companyname Values.1 Year.1 Values.2 Year.2
# 1:           A        1   2010        2   2011
# 2:           B        4   2010        6   2012
# 3:           C        8   2011       NA     NA

Upvotes: 6

Related Questions