Geek Answers Handbook

Convert multiple lines of text to data frame

I am trying to find a way to convert multiple lines of text into a data frame. I'm not sure if there is a way you can use read.delim() to read in multiple lines of text and create the next data frame with something like rehape()?

The data is structured as follows:

A: 1
B: 2
C: 10
A: 34
B: 20
C: 6.7
A: 2
B: 78
C: 35

I would like to convert this data into something similar to the following data frame:

A             B             C
1             2             10
34            20            6.7
2             78            35

Sorry if there is an obvious way to do this!

+5
source share
4 answers

What about:

s<-"A: 1
B: 2
C: 10
A: 34
B: 20
C: 6.7
A: 2
B: 78
C: 35
"
d<-read.delim(textConnection(s),header=FALSE,sep=":",strip.white=TRUE)
cols<-levels(d[,'V1'])
d<-data.frame(sapply(cols,function(x) {d['V2'][d['V1']==x]}, USE.NAMES=TRUE))

which gives:

   A  B    C
1  1  2 10.0
2 34 20  6.7
3  2 78 35.0
+9
source

Here's how to do it with the plyr package:

require("plyr")
my.data <- "A: 1
            B: 2
            C: 10
            A: 34
            B: 20
            C: 6.7
            A: 2
            B: 78
            C: 35"   
df <- read.delim(textConnection(my.data),header=FALSE,sep=":",strip.white=TRUE)

as.data.frame(dlply(df,.(V1),function(x) x[[2]]))

You get

   A  B    C
1  1  2 10.0
2 34 20  6.7
3  2 78 35.0

You can see what the magic plyr does by playing with dlply(df,.(V1))ordlply(df,.(V1),function(x) x)

+4

R-help, , unstack.

This is a modification of Lev Alekseev’s answer

my.data <- "A: 1
            B: 2
            C: 10
            A: 34
            B: 20
            C: 6.7
            A: 2
            B: 78
            C: 35"   
df <- read.delim(textConnection(my.data),header=FALSE,sep=":",strip.white=TRUE)
unstack(df, V2 ~ V1)

It leads to:

   A  B    C
1  1  2 10.0
2 34 20  6.7
3  2 78 35.0

Some advantages of this approach compared to other thoughtful answers are that you do not need to specify the number of columns ahead of time. It also does not require additional packages.

+2
source

Here is one solution using reshape

s<-"A: 1
B: 2
C: 10
A: 34
B: 20
C: 6.7
A: 2
B: 78
C: 35
"
d<-d<-read.delim(textConnection(s),header=FALSE,sep=":",strip.white=TRUE)
N<-nrow(d)%/%3
d$id<-rep(1:N,each=3)
reshape(d,dir="wide",timevar="V1",idvar="id")

What produces

  id V2.A V2.B V2.C
1  1    1    2 10.0
4  2   34   20  6.7
7  3    2   78 35.0
0
source

More articles:


All Articles