Dfyjkt

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))