Hi,
I have this simple file:
data.frame(stringsAsFactors=FALSE,
URN = c("10BE022001884", "10BE022001936", "10BE022102325",
"10BE023230730", "10BE022102325", "10BE022102346",
"10BE022995949", "10BE023335219", "10BE022102340", "10BE022102346",
"10BE022104737"),
Category = c("Recommendation", "Recommendation", "Recommendation",
"Recommendation", "Staff", "Staff", "Staff", "Staff",
"Timeliness", "Timeliness", "Timeliness"),
Sent1 = c("+", "+", "-", "0", "-", "+", "0", "0", "+", "-", "-"),
Cat1 = c(29, 11, 7, 29, 16, 12, 24, 10, 18, 5, 11),
Sent2 = c("+", "+", "-", "0", "-", "+", "0", "0", "+", "-", "-"),
Cat2 = c(8, 7, 8, 7, 18, 7, 7, 29, 9, 9, 23),
Sent3 = c(NA, "+", "-", NA, "-", "-", NA, NA, NA, NA, "-"),
Cat3 = c(NA, 8, 9, NA, 18, 12, NA, NA, NA, NA, 32),
Sent4 = c(NA, NA, NA, NA, "-", "-", NA, NA, NA, NA, "-"),
Cat4 = c(NA, NA, NA, NA, 32, 32, NA, NA, NA, NA, 35),
Sent5 = c(NA, NA, NA, NA, "-", "-", NA, NA, NA, NA, NA),
Cat5 = c(NA, NA, NA, NA, 32, 9, NA, NA, NA, NA, NA)
)
My challenge is pivoting this table twice (or once if there is a clever way).
Step 1:
I need only one column called "Cat" (with numbers which are currently in "Cat1" to "Cat 5") and one column Called "Sent" (with +, - or 0). We should have 32 records with duplicated URNs as a result
Step 2:
I need to pivot the result from step one again having 3 separate data frames based on "Sent" (+, - and 0). In each of the data frames I need a table with 3 Categories (Recommendation, Staff, Timeliness) and "Cat" below each of them (numbers) for each URN. We should not have any duplicated URNs in each data frame.
I am sure there is an easy way of doing that but I'm lack of ideas...
Can you help?