Xtykutl

I am trying to convert a single-column data frame into separate columns — the main descriptor in the data is the "item number" and then includes information on the price, date, color, etc. I would just split the column depending on row number, but since each item has a different amount of information, that doesn't really work.

I've been playing around with this a bit but haven't found anything at all to come close, as I can't use regex to create a separate column (using str_which, for example) since the information differs so much item to item. How can I use regex to create intervals that I can then split the column into (so I need the information between each row containing "item" in a separate column). Sample data is below.

data



item 1

$600

red

item 2

$70

item 3

$430

orange

10/11/2017

Thank you!

Here is a function to reformat your data depending on how you want the final dataset to look like. For the function, you supply the dataframe DF, the variable var, and a vector of column names in the correct order colnames and byitem to choose the output format (default is TRUE, which outputs a dataframe with one row per item):

library(tidyverse)



df_transform = function(DF, var, colnames, byitem = TRUE){

  if(byitem){

    ID = sym("rowid")

  }else{

    ID = sym("id")

  }

  DF %>%

    group_by(id = paste0("item", cumsum(grepl("item", var)))) %>%

    mutate(rowid = replace(2:n(), 2:n(), setNames(colnames[1:(n()-1)], 2:n()))) %>%

    filter(!grepl("item", var)) %>%

    spread(!!ID, var)

}

Output:

> df_transform(df, var, c("price", "color", "date"))



# A tibble: 3 x 4

# Groups:   id [3]

  id    color  date       price

  <chr> <fct>  <fct>      <fct>

1 item1 red    <NA>       $600 

2 item2 <NA>   <NA>       $70  

3 item3 orange 10/11/2017 $430 





> df_transform(df, var, c("price", "color", "date"), byitem = FALSE)



# A tibble: 3 x 4

  rowid item1 item2 item3     

  <chr> <fct> <fct> <fct>     

1 color red   <NA>  orange    

2 date  <NA>  <NA>  10/11/2017

3 price $600  $70   $430  

Note that this would not work if you have missing values in the middle, since the column names are assigned by position.

Data:

df <- structure(list(var = structure(c(5L, 2L, 9L, 6L, 3L, 7L, 1L, 

8L, 4L), .Label = c("$430", "$600", "$70", "10/11/2017", "item_1", 

"item_2", "item_3", "orange", "red"), class = "factor")), .Names = "var", class = "data.frame", row.names = c(NA, 

-9L))