Convert columns to rows repeat common values

Question

good people, I have a data frame that I want to transform as below from dataframe1 to dataframe2:

dataframe1 <- data.frame(Company_Name = c("KFC", "McD"), 
                 Company_ID = c(1,  2),
                 Company_Phone = c("237389", "237002"),
                 Employee = c("John", "Joshua" ),
                 ID = c(1001, 2001),
                 Employee = c("Mary", "Anne"),
                 ID = c(1002, 2002),
                 Employee = c("Jane", ""),
                ID = c(1003, ""))

dataframe2 <- data.frame(Company_Name = c("KFC", "KFC", "KFC", "McD", "McD"), 
                      Company_ID = c(1, 1, 1, 2, 2),
                      Company_Phone = c("237389", "237389", "237389", "237002", "237002"),
                      Employee = c("John", "Mary", "Jane", "Joshua", 
                                        "Anne"),
                     ID = c(1001, 1002, 1003, 2001, 2002))

What I tried so far :

   cbind(dataframe1[1], stack(dataframe1[-1]))

and

  stack(dataframe2)

Without getting the required output. Any help or insight will be appreciated.

`library(tidyverse); dataframe1 %>% gather(var, val, -contains("Company")) %>% separate(var, c('var', 'i'), fill = 'right') %>% spread(var, val) %>% select(-i) %>% filter(Employee != "")` — alistaire, Sep 13 '18 at 08:37

score 2 · Accepted Answer · answered Sep 13 '18 at 09:03

using melt from data.table, with the patterns function

library(data.table)
melt(setDT(dataframe1),measure = patterns("^Employee","^ID"),value.name = c("Employee","Employee_ID"))[Employee != ""]


   Company_Name Company_ID Company_Phone variable Employee Employee_ID
1:          KFC          1        237389        1     John        1001
2:          McD          2        237002        1   Joshua        2001
3:          KFC          1        237389        2     Mary        1002
4:          McD          2        237002        2     Anne        2002
5:          KFC          1        237389        3     Jane        1003

Convert columns to rows repeat common values

1 Answers1