Convert comma separated string to numeric columns

Question

I have a dataset with several columns, one of which is a column for reaction times. These reaction times are comma separated to denote the reaction times (of the same participant) for the different trials.

For example: row 1 (i.e.: the data from participant 1) has the following under the column "reaction times"

reaction_times
2000,1450,1800,2200

Hence these are the reaction times of participant 1 for trials 1,2,3,4.

I now want to create a new data set in which the reaction times for these trials all form individual columns. This way I can calculate the mean reaction time for each trial.

              trial 1  trial 2  trial 3  trial 4 
participant 1:   2000     1450     1800     2200

I tried the colsplit from the reshape2 package but that doesn't seem to split my data into new columns (perhaps because my data is all in 1 cell).

Any suggestions?

Paul Hiemstra · Accepted Answer · 2011-12-11T14:09:59.770

I think you are looking for the strsplit() function;

a = "2000,1450,1800,2200"
strsplit(a, ",")
[[1]]                                                                                                                                                       
[1] "2000" "1450" "1800" "2200"

Notice that strsplit returns a list, in this case with only one element. This is because strsplit takes vectors as input. Therefore, you can also put a long vector of your single cell characters into the function and get back a splitted list of that vector. In a more relevant example this look like:

# Create some example data
dat = data.frame(reaction_time = 
       apply(matrix(round(runif(100, 1, 2000)), 
                     25, 4), 1, paste, collapse = ","),
                     stringsAsFactors=FALSE)
splitdat = do.call("rbind", strsplit(dat$reaction_time, ","))
splitdat = data.frame(apply(splitdat, 2, as.numeric))
names(splitdat) = paste("trial", 1:4, sep = "")
head(splitdat)
  trial1 trial2 trial3 trial4
1    597   1071   1430    997
2    614    322   1242   1140
3   1522   1679     51   1120
4    225   1988   1938   1068
5    621    623   1174     55
6   1918   1828    136   1816

and finally, to calculate the mean per person:

apply(splitdat, 1, mean)
[1] 1187.50  361.25  963.75 1017.00  916.25 1409.50  730.00 1310.75 1133.75
[10]  851.25  914.75  881.25  889.00 1014.75  676.75  850.50  805.00 1460.00
[19]  901.00 1443.50  507.25  691.50 1090.00  833.25  669.25

Wow, great and quick response Paul, dankjewel! Works like a charm :) If I'm not mistaken you can also just use "colMeans" and "rowMeans" instead of 'apply(splitdat, 1, mean)'? PS. sorry I can't vote you up, apparently I need 15 reputation first?! — rvrvrv, Dec 11 '11 at 16:12
You are right about the colMeans ofcourse :). I think however that using apply is also nice because it is much more flexible. ps Are you also from the Netherlands? — Paul Hiemstra, Dec 11 '11 at 16:58

score 14 · Answer 2 · answered Dec 11 '11 at 14:53

14

A nifty, if rather heavy-handed, way is to use read.csv in conjunction with textConnection. Assuming your data is in a data frame, df:

x <- read.csv(textConnection(df[["reaction times"]]))

answered Dec 11 '11 at 14:53

Hong Ooi

56,353
13
134
187

3

Doesn't look heavy handed to me at all. Looks to be wielding R with a deft touch. – IRTFM Dec 11 '11 at 16:05
Elegant solution! Would be interesting to see if how our solutions compare in terms of speed for really large datasets. – Paul Hiemstra Dec 11 '11 at 16:57
Also works perfectly (can I actually approve both as solutions?) – rvrvrv Dec 11 '11 at 17:15

score 11 · Answer 3 · edited May 23 '17 at 12:24

Old question, but I came across it from another recent question (which seems unrelated).

Both existing answers are appropriate, but I wanted to share an answer related to a package I have created called "splitstackshape" that is fast and has straightforward syntax.

Here's some sample data:

set.seed(1)
dat = data.frame(
  reaction_time = apply(matrix(round(
    runif(24, 1, 2000)), 6, 4), 1, paste, collapse = ","))

This is the splitting:

library(splitstackshape)
cSplit(dat, "reaction_time", ",")
#    reaction_time_1 reaction_time_2 reaction_time_3 reaction_time_4
# 1:             532            1889            1374             761
# 2:             745            1322             769            1555
# 3:            1146            1259            1540            1869
# 4:            1817             125             996             425
# 5:             404             413            1436            1304
# 6:            1797             354            1984             252

And, optionally, if you need to take the rowMeans:

rowMeans(cSplit(dat, "reaction_time", ","))
# [1] 1139.00 1097.75 1453.50  840.75  889.25 1096.75

Excellent package – thanks for sharing, makes it much more straightforward! — rvrvrv, Nov 11 '14 at 14:35

sdevine188 · Answer 4 · 2017-08-11T18:40:55.510

Another option using dplyr and tidyr with Paul Hiemstra's example data is:

# create example data
data = data.frame(reaction_time = 
                     apply(matrix(round(runif(100, 1, 2000)), 
                                  25, 4), 1, paste, collapse = ","),
             stringsAsFactors=FALSE)
head(data)

# clean data
data2 <- data %>% mutate(split_reaction_time = str_split(as.character(reaction_time), ",")) %>% unnest(split_reaction_time) 
data2$col_names <- c("trial1", "trial2", "trial3", "trial4")
data2 <- data2 %>% spread(key = col_names, value = split_reaction_time) %>% select(-reaction_time)
head(data2)

Convert comma separated string to numeric columns

4 Answers4

Linked

Related