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