Convert rows to one based on a common name

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

score 5 · Answer 1 · answered Jun 21 '15 at 18:32

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

as I can understand d$t is the 4 values and 2 are the two rows in the result I have. As I mention this is an example in the dataset I don't know the specific numbe of the new rows and columns. — Ster32, Jun 21 '15 at 19:23

score 4 · Accepted Answer · edited Jun 21 '15 at 21:30

4

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

edited Jun 21 '15 at 21:30

Arun

116,683
26
284
387

answered Jun 21 '15 at 18:25

akrun

874,273
37
540
662

Unfortunately until now I can install the version #v1.9.5+ maybe I have to install first the Github? – Ster32 Jun 21 '15 at 19:21
@Stef32 You need `library(devtools)` installed first – akrun Jun 21 '15 at 19:30
Yes I made it. It has installed. It only gives a warning to install R tools but I didn't make it. – Ster32 Jun 21 '15 at 19:32
@Ster32 Does it work now? – akrun Jun 21 '15 at 19:34
@Ster32 Which version of R you have? I am using `R 3.2.0` in `linux` and the newest is `R 3.2.1`. As mentioned in the link, the windows users should install `Rtools` – akrun Jun 21 '15 at 19:36
@Ster32 Another option is `library(reshape2);df2 <- transform(df1, indx=ave(stockname, stockname, FUN=seq_along)); recast(df2, id.var=c('stockname', 'indx'),stockname~variable+indx, value.var='value')` – akrun Jun 21 '15 at 19:54
@Ster32, as mentioned in the webpage akrun linked to, you've to have R tools installed first.. – Arun Jun 21 '15 at 21:31

Convert rows to one based on a common name

2 Answers2