Extracting data frame from strings between values in R

user_9325413 0
Viewed 90

I am doing some coding in R and I am dealing with cases where some data frames are coded as individual strings, and it is necessary to be able to recover the data frames from the strings. The strings are in this form:

|Carrot^14|Cucumber^3|Potato^8|

The seperator | separates the rows of the data frame and the separator ^ separates the column with the character value from the column with the numeric value. (To simplify the problem, note that these symbols appear only as separators --- they never appear in the values in the data frame.) The data frame corresponding to this string would be:

 Vegetable  Quantity
  "Carrot"        14
"Cucumber"         3
  "Potato"         8

I would like to be able to decode a string to extract the data frame that it encodes. I know that this will require some kind of regexp query, but I'm not quite sure how to do it. I am also not sure if this kind of correspondence between a data frame and a single string has any particular name that would lead me to further information about the technique.

My Questions: Does this kind of coding from a data frame to a single string (and back) have a particular name? How can I decode the string in R?

r string
4 Answers

You can swap the vertical bar to a newline character then use read.table().

txt <- "|Carrot^14|Cucumber^3|Potato^8|"

read.table(text = gsub("\\|", "\n", txt), sep = "^", col.names = c("Vegetable", "Quantity"))

  Vegetable Quantity
1    Carrot       14
2  Cucumber        3
3    Potato        8
user_28352610

We can remove the separators at the start and end of the string with gsub, get the data in separate rows based on (|) separator and into separate columns based on (^) separator. 

library(dplyr)
library(tidyr)

df %>%
  mutate(col = gsub('^\\||\\|$', '' ,col)) %>%
  separate_rows(col, sep = "\\|") %>%
  separate(col, into = c('Vegetable', 'Quantity'), sep = "\\^", convert = TRUE)

#  Vegetable Quantity
#1    Carrot       14
#2  Cucumber        3
#3    Potato        8

data

df <- data.frame(col = "|Carrot^14|Cucumber^3|Potato^8|", stringsAsFactors = FALSE)
user_39629140

One way would be to directly extract vegetable names and numbers. For vegetable, I extracted characters which stay between | and ^. For numbers, I simply extracted numerical letters. I converted them to numeric.

library(stringi)

data.frame(vegetable = unlist(stri_extract_all_regex(str = foo$whatever,
                                      pattern = "(?<=\\|)[A-z]+(?=\\^)")),
           quantity = as.numeric(unlist(stri_extract_all_regex(str = foo$whatever, pattern = "[0-9]+")))
           )

  vegetable quantity
1    Carrot       14
2  Cucumber        3
3    Potato        8
4    Carrot       20
5  Cucumber        5
6    Potato       12

DATA

foo <- structure(list(whatever = c("|Carrot^14|Cucumber^3|Potato^8|", 
"|Carrot^20|Cucumber^5|Potato^12|")), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))
user_33044710

You can try the following base R solution

df <- setNames(data.frame(do.call(rbind,
                   strsplit(unlist(regmatches(s,gregexpr("(?<=\\|).*?(?=\\|)",s,perl = TRUE))),
                            "\\^"))),
                  c("Vegatable","Quantity"))
df$Quantity <- as.numeric(df$Quantity)

such that

> df
  Vegatable Quantity
1    Carrot        1
2  Cucumber        2
3    Potato        3

DATA

s <- "|Carrot^14|Cucumber^3|Potato^8|"
user_121587570
Related