CODEWITHSUNDEEP
rdplyrtidyrspread
William
William

Reputation: 164

How to convert multiple columns with various category levels to wide format?

I have the following set of data with multiple columns that correspond: prompt1 with value1, prompt2 with value2, promptn to valuen. Each prompt contains up to 3 different levels of categories.

df.1 <- data.frame(prompt1         = c('date', 'company', 'invoice'),
               value1              = c('2017-01-01', 'Tellabs', '23845'),
               prompt2             = c('code', 'city', 'item'),
               value2              = c('B34', 'Ilinois', 'SER0000066'),
               stringsAsFactors    = FALSE)

The desired dataframe would be:

df.2 <- data.frame(date              = '2017-01-010',
                   company           = 'Tellabs',
                   invoice           = '23845',
                   code              = 'B34',
                   city              = 'Ilinois',
                   item              = 'SER0000066',
                   stringsAsFactors  = FALSE)

I have tried spread from tidyr but without success

Upvotes: 1

Views: 55

Answers (1)

www
www

Reputation: 39154

A solution using functions from dplyr and tidyr.

library(dplyr)
library(tidyr)

df.2 <- df.1 %>%
  gather(Prompt, Column, contains("prompt")) %>%
  gather(Value, Content, contains("value")) %>%
  filter(sub("prompt", "", Prompt) == sub("value", "", Value)) %>%
  select(Column, Content) %>%
  spread(Column, Content) %>%
  select(date, company, invoice, code, city, item)

Upvotes: 1

Related Questions