Convert rows to one based on a common name [duplicate]

Question

I have many stock data but in a strange way which I couldn't find an example how to convert them to the output which could helpful. Here is a snippet of data

stockname,value,prediction
Yahoo,32,12
Yahoo,21,12
Yahoo,30,10
Yahoo,56,19
Google,32,10
Google,22,30

Is it possible to convert this dataframe to something easier like

stockname value1 value2 value3 value4 prediction1 prediction2 prediction3 prediction4
Yahoo       32     21     30     56     12            12           10         19
Google      32     22                   10            10

Answer 1

This is a simple problem of reshaping data from "long" to "wide". Here's a base R solution using reshape:

# load data
d <- read.csv(text="stockname,value,prediction
Yahoo,32,12
Yahoo,21,12
Yahoo,30,10
Yahoo,56,19
Google,32,10
Google,22,30", header = TRUE)

# create a time indicator variable
d$t <- c(1:4, 1:2)
# reshape
reshape(d, idvar="stockname", direction="wide", timevar = "t")
#   stockname value.1 prediction.1 value.2 prediction.2 value.3 prediction.3 value.4 prediction.4
# 1     Yahoo      32           12      21           12      30           10      56           19
# 5    Google      32           10      22           30      NA           NA      NA           NA

Answer 2

You can try dcast from the devel version of data.table ie. v1.9.5+, which can take multiple value.var columns. Instructions to install the devel version are here

library(data.table)#v1.9.5+
setDT(df1)[, indx:=1:.N, stockname]#create a sequence index by stockname
dcast(df1, stockname~indx, value.var=c('value', 'prediction'))
#  stockname value_1 value_2 value_3 value_4 prediction_1 prediction_2
#1:    Google      32      22      NA      NA           10           30
#2:     Yahoo      32      21      30      56           12           12
#   prediction_3 prediction_4
#1:           NA           NA
#2:           10           19