transform comma separated data into matrix using R

Question

My Data:

A/11:36/0,A/11:36/1,A/11:36/2,A/23:01/0,A/23:01/1,A/23:01/2,B/15:07/0,B/15:07/1,B/15:07/2
1,26,2,1,10,2,1,0,0

Output Expecting:

Name 0 1 2
A/11:36 1 26 2
A/23:01 1 10 2
B/15:07 1 0 0 

My Code

library(reshape)
library(library(splitstackshape))
input <- read.csv("D:/input.csv")
t_input <- t(input)
colnames(t_input)<- c("Name","Val")
data<-cSplit(t_input, 'V1', sep="/", type.convert=FALSE)
# here am going wrong, My script splitting the column1 into 3 columns. 
final_data <- cast(data, X1~X2)

I need help on spliting my column 1 into two as follows :

A/11:36 0
A/11:36 1
A/11:36 2
A/23:01 0
A/23:01 1
A/23:01 2
B/15:07 0
B/15:07 1
B/15:07 2

Can anybody help me to solve this ?

Would you please be able to rephrase your intentions more clearly? I don't quite understand. Please refer to [this post](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for guidance on how to include a reproducible example. — pfabri, Jun 20 '16 at 16:10
If the `cSplit` function accepts regular expressions as separators, then `sep = "/(?!.*/)"` should work. If not, see my answer for a `tidyr` solution. — Mikko Marttila, Jun 20 '16 at 17:17

Mikko Marttila · Accepted Answer · 2016-06-20T17:10:56.007

Here's a tidyr solution:

# read the sample data
data <- read.csv("input.csv", header = F)
tdata <- t(data)
colnames(tdata) <- c("name", "value")
df <- data.frame(tdata)

library(tidyr)

new_df <- df %>% 
    # extract the variables stored in 'name' to their own columns
    separate(name, c("group", "time", "x"), "/") %>%
    # transform to wide format
    spread(x, value, sep = "")

# final result
new_df
#       group  time x0 x1 x2
#     1     A 11:36  1 26  2
#     2     A 23:01  1 10  2
#     3     B 15:07  1  0  0

# if, for some reason, you really want the group and time columns together
new_df %>% unite(name, group, time, sep = "/")
#          name x0 x1 x2
#     1 A/11:36  1 26  2
#     2 A/23:01  1 10  2
#     3 B/15:07  1  0  0

# or if you want them together and skip the unite step, you can separate directly
# by splitting at a / that is not followed by another / anywhere in the string
df %>%
    separate(name, c("name", "x"), "/(?!.*/)") %>%
    spread(x, value, sep = "")
#          name x0 x1 x2
#     1 A/11:36  1 26  2
#     2 A/23:01  1 10  2
#     3 B/15:07  1  0  0

r - cast {reshape}: using variables instead of the columns name to group' , So I followed the tidyr library. Thank you — user2767090, Jun 21 '16 at 04:09

score 0 · Answer 2 · answered Jun 23 '16 at 06:13

    # read the sample data
    input <- read.csv("input.csv", header=FALSE)
    t_input <- t(input)
    colnames(t_input) <- c("name", "value")
    df <- data.frame(t_input)

    library(splitstackshape)

    new_df <- cSplit(t_input, 'name', sep="/", type.convert=FALSE)
    df1 <- reshape(new_df, timevar=c("name_3"), idvar = c("name_1",'name_2'), dir="wide")
    df2 <- within(df1, Name <- paste(name_1, name_2, sep='/'))
    df2[,c("name_1","name_2"):=NULL]
    Finaldf <- subset(df2, select=c(Name,value.0:value.2))
    write.csv(Finaldf, "output.csv", row.names = FALSE)

output

Name value.0 value.1 value.2
A/11:36       1      26       2
A/23:01       1      10       2
B/15:07       1       0       0

transform comma separated data into matrix using R

2 Answers2