R Split dataframe by new line in column

Question

I'm trying to split strings within a column by new-line " ". Here is a dataframe sample_data:

 test_data <- data.frame(ID=c('john@xxx.com', 'sally@xxx.com'),
                  Changes=c('3 max cost changes
  productxyz > pb100  > a : Max cost decreased from $0.98 to $0.83
  productxyz > pb2  > a : Max cost decreased from $1.07 to $0.91
  productxyz > pb2  > b : Max cost decreased from $0.65 to $0.55', 
                            '2 max cost changes
  productabc > pb1000  > d : Max cost decreased from $1.07 to $0.91
  productabc > pb1000  > x : Max cost decreased from $1.44 to $1.22'), stringsAsFactors=FALSE)

My goal is to extract the prices into columns and obtain a result set like this:

ID              Prev_Price    New_Price
john@xxx.com     $0.98            $0.83
john@xxx.com     $1.07            $0.91
john@xxx.com     $0.65            $0.55
sally@xxx.com    $1.07            $0.91
sally@xxx.com    $1.44            $1.22

I've tried using tidyr package but my result is full of N/A.

vars <- c("Prev_Price","New_Price")
seperate(sample_data, Changes, into = vars, sep = "[A-Za-z]+from", extra= "drop")

Any help would be much appreciated.

Thanks!

akrun · Accepted Answer

Try

df1$ID <- df1$ID[df1$ID!=''][cumsum(df1$ID!='')]
library(stringi)
setNames(data.frame(df1$ID, do.call(rbind,stri_extract_all(df1$Changes, 
       regex='\$\d*'))), c('ID', 'Prev_Price', 'New_Price'))
 #   ID Prev_Price New_Price
 #1  A        $20       $10
 #2  A        $11       $10
 #3  B        $13       $12
 #4  B        $15       $12

Or

library(tidyr)
extract(df1, Changes, into=c('Prev_Price', 'New_Price'), 
          '[^$]*(\$\d*)[^$]*(\$\d*)')
#   ID Prev_Price New_Price
#1  A        $20       $10
#2  A        $11       $10
#3  B        $13       $12
#4  B        $15       $12

Or

library(data.table)#v1.9.5+
setDT(df1)[, c('Prev_Price', 'New_Price') := tstrsplit(Changes, 
                                 '[A-Za-z ]+')[-1]][]
#   ID              Changes Prev_Price New_Price
#1:  A down from $20 to $10        $20       $10
#2:  A down from $11 to $10        $11       $10
#3:  B down from $13 to $12        $13       $12
#4:  B down from $15 to $12        $15       $12

NOTE: The "Changes" can be removed

Or using only base R methods

data.frame(ID=df1$ID, read.table(text=gsub('[^$]*(\$\d+)', ' \1 ', 
   df1$Changes),col.names=c('Prev_Price', 'New_Price'), 
                    stringsAsFactors=FALSE))
 #   ID Prev_Price New_Price
 #1  A        $20       $10
 #2  A        $11       $10
 #3  B        $13       $12
 #4  B        $15       $12

Update

If the elements reside in the same cell, one option would be using the devel version of data.table ie. v1.9.5+. It can be installed from here

Here, we use the same code to split the 'Changes' (tstrsplit(Changes,..)), then melt the output to long form by specifying the measure.vars as a list, and if needed order by 'ID' and remove the unwanted columns ('variable').

 melt(
   setDT(df2)[, paste0('V',1:4) := tstrsplit(Changes,
           '[A-Za-z ]+')[-1]][,-2, with=FALSE],
      id.var='ID', measure=list(c('V1', 'V3'), c('V2', 'V4')), 
        value.name=c('Prev_Price', 'New_Price'))[order(ID)][, variable:=NULL]
  #    ID Prev_Price New_Price
  #1:  A        $20       $10
  #2:  A        $11       $10
  #3:  B        $13       $12
  #4:  B        $15       $12

Or we can use gsub as before and then convert to long format with reshape from base R

 d1 <- data.frame(ID=df2$ID,read.table(text=gsub('[^$]*(\$\d+)',
                 ' \1 ', df2$Changes)))

colnames(d1)[-1] <- paste0(c('Prev_Price.', 'New_Price.'), 
                          rep(1:2,each=2))
reshape(d1, idvar='ID', varying=2:ncol(d1), sep=".", direction='long')
#    ID time Prev_Price New_Price
#A.1  A    1        $20       $10
#B.1  B    1        $13       $12
#A.2  A    2        $11       $10
#B.2  B    2        $15       $12

Update2

For the new dataset ("df3"), we can use stri_extract_all_regex to extract the $ followed by numbers including the decimals ('\$[0-9.]+') for the "Changes" column, use Map to combine the first column with the list output we got from the stri_extract_all_regex after changing the output to a matrix (as we need the alternating elements to be in different columns), and then rbind (do.call(rbind,).

library(stringi)
res <- do.call(rbind,
       Map(function(x,y) data.frame(x,matrix(y, ncol=2, byrow=TRUE, 
           dimnames=list(NULL, c("Prev_Price", "New_Price")))),
        df3$ID, stri_extract_all_regex(df3$Changes, '\$[0-9.]+')))
row.names(res) <- NULL
res
#              x Prev_Price New_Price
#1  john@xxx.com      $0.98     $0.83
#2  john@xxx.com      $1.07     $0.91
#3  john@xxx.com      $0.65     $0.55
#4 sally@xxx.com      $1.07     $0.91
#5 sally@xxx.com      $1.44     $1.22

data

df1 <- structure(list(ID = c("A", "", "B", ""), 
 Changes = c("down from $20 to $10", 
"down from $11 to $10", "down from $13 to $12", "down from $15 to $12"
)), .Names = c("ID", "Changes"), class = "data.frame", 
row.names = c(NA, -4L))

df2 <- data.frame(ID=c('A', 'B'),
   Changes=c('down from $20 to $10 down from $11 to $10', 
  'down from $13 to $12 down from $15 to $12'), stringsAsFactors=FALSE)

R Split dataframe by new line in column

Answers (2)

Update

Update2

data

Related Questions