Convert a string to data frame, including column names

Question

I have a string whose structure and length can keep varying, that is

Input:

X <- ("A=12&B=15&C=15")
Y <- ("A=12&B=15&C=15&D=32&E=53")

What I was looking for this string to convert to data frame

Output Expected:

Dataframe X

 A  B  C
 12 15 15

and Dataframe Y

 A  B  C  D  E
 12 15 15 32 53

What I tired was this:

X <- as.data.frame(strsplit(X, split="&"))

But this didn't work for me, as it created only one column and column name was messed up.

P.S: I cannot hard code the column names because they can vary, and at any given time a string will contain only one row

eval(parse(text=paste("data.frame(",gsub("&", ",", X),")"))) I feel bad for suggesting it, but maybe it isn't all that horrible in the current context. — thelatemail
– thelatemail, Commented Nov 9, 2016 at 5:59

akrun · Accepted Answer · 2016-11-09 06:05:12Z

5

One option is to extract the numeric part from the string, and read it with read.table. The pattern [^0-9]+ indicates one or more characters that are not a number and replace it with a space in the first gsub, read that using read.table, and specify the column names in the col.names argument with the values got by removing all characters that are not an upper case letter (second gsub)

f1 <- function(str1){
read.table(text=gsub("[^0-9]+", " ", str1), 
         col.names = scan(text=trimws(gsub("[^A-Z]+", " ", str1)), 
             what = "", sep=" ", quiet=TRUE))
 }

f1(X)
#   A  B  C
#1 12 15 15
f1(Y)
#   A  B  C  D  E
#1 12 15 15 32 53

edited Nov 9, 2016 at 6:05

answered Nov 9, 2016 at 5:54

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anubhav Dikshit Over a year ago

Works beautifully, will wait for two minutes before accepting yours!

Anubhav Dikshit Over a year ago

If its not too much to ask, would you mind explaining how your code works, if not for me, others will find it helpful ( one up vote from me for sure )

akrun Over a year ago

@USER3875610 Sure, I was going to update it when my laptop gets so slow.

Sandipan Dey · Accepted Answer · 2016-11-09 06:02:40Z

3

You can try this too:

library(stringr)
res <- str_match_all(X, "([A-Z]+)=([0-9]+)")[[1]]
df <- as.data.frame(matrix(as.integer(res[,3]), nrow=1))
names(df) <- res[,2]

df
   A  B  C
1 12 15 15

answered Nov 9, 2016 at 6:02

Sandipan Dey

23.4k4 gold badges59 silver badges72 bronze badges

Collectives™ on Stack Overflow

Convert a string to data frame, including column names

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related