Transposing a group of variables

Slavek · November 17, 2021, 1:23pm

Hi,
I have received a df which has incorrect structure and needs transposing:

source <- data.frame(
             stringsAsFactors = FALSE,
                  check.names = FALSE,
                       UserNo = c(5, 6, 7, 9, 14, 16),
                      Started = c("2021-11-08 08:53:00","2021-11-08 13:26:00",
                                  "2021-11-08 23:59:00","2021-11-09 10:11:00",
                                  "2021-11-10 15:31:00","2021-11-15 09:25:00"),
               `Q2. Function` = c("NSC",
                                  "XYZ","NSC","Customer Excellence","XYZ",
                                  "NSC"),
                 `Q4. Market` = c("-", "adssad", "-", "-", "fghjfj", "-"),
                   `Q5. Team` = c("-", "-", "-", "bbb", "-", "-"),
              `Q6. How often` = c("Dayly", "Weekly", "-", "-", "Weekly", "-"),
     `Q7. How would you rate` = c("9", "10", "-", "-", "7", "-"),
  `Q21. How could we improve` = c("sdfgh s", "-", "-", "-", "-", "-"),
                  `Q22. Team` = c("-", "-", "fff", "-", "-", "-"),
             `Q23. How often` = c("-", "-", "Monthly", "-", "-", "-"),
    `Q24. How would you rate` = c("-", "-", "9", "-", "-", "-"),
  `Q38. How could we improve` = c("-", "-", "fgsd", "-", "-", "-"),
                  `Q73. Team` = c("-", "-", "-", "-", "-", "aaa"),
             `Q74. How often` = c("-","-",
                                  "-","-","-",
                                  "Fortnightly"),
    `Q75. How would you rate` = c("-", "-", "-", "-", "-", "9"),
  `Q89. How could we improve` = c("-", "-", "-", "-", "-", "xxx")
)

Q22 and Q73 are the same as Q5, Q23 and Q74 are the same as Q6, Q24 and Q75 are the same as Q7, Q38 and Q89 are the same as Q21 so they should be transposed and the result should look like that.

result <- data.frame(
        stringsAsFactors = FALSE,
             check.names = FALSE,
                  UserNo = c(5,6,7,9,14,16,
                             5,6,7,9,14,16,5,6,7,9,14,16),
                 Started = c("2021-11-08 08:53:00","2021-11-08 13:26:00","2021-11-08 23:59:00",
                             "2021-11-09 10:11:00","2021-11-10 15:31:00",
                             "2021-11-15 09:25:00","2021-11-08 08:53:00",
                             "2021-11-08 13:26:00","2021-11-08 23:59:00",
                             "2021-11-09 10:11:00","2021-11-10 15:31:00","2021-11-15 09:25:00",
                             "2021-11-08 08:53:00","2021-11-08 13:26:00",
                             "2021-11-08 23:59:00","2021-11-09 10:11:00",
                             "2021-11-10 15:31:00","2021-11-15 09:25:00"),
          `Q2. Function` = c("NSC","XYZ","NSC",
                             "Customer Excellence","XYZ","NSC","NSC","XYZ",
                             "NSC","Customer Excellence","XYZ","NSC","NSC",
                             "XYZ","NSC","Customer Excellence","XYZ","NSC"),
            `Q4. Market` = c("-","adssad","-",
                             "-","fghjfj","-","-","adssad","-","-",
                             "fghjfj","-","-","adssad","-","-","fghjfj","-"),
                    Team = c("-","-","-",
                             "bbb","-","-","-","-","fff","-","-","-","-",
                             "-","-","-","-","aaa"),
             `How often` = c("Dayly","Weekly",
                             "-","-","Weekly","-","-","-","Monthly","-",
                             "-","-","-","-","-","-","-","Fortnightly"),
    `How would you rate` = c("9","10","-","-",
                             "7","-","-","-","9","-","-","-","-","-",
                             "-","-","-","9"),
  `How could we improve` = c("sdfgh s","-","-",
                             "-","-","-","-","-","fgsd","-","-","-",
                             "-","-","-","-","-","xxx")
)

Is any clever way of transposing two sets of four questions below the first set?
In my real data I have more than these two sets in the example above...

nirgrahamuk · November 17, 2021, 2:51pm

library(tidyverse)
(a <- select(source,UserNo,contains("Team")))
(b <- select(source,UserNo,contains("How often")))
(c <- select(source,UserNo,contains("How would you rate")))
(d <- select(source,UserNo,contains("How could we improve")))
(original <- select(source,!(contains("Team") | contains("How often") | contains("How would you rate" ) | contains("How could we improve"))))

(pivot_results <- map(list(a,b,c,d),
    ~{
        step1 <- pivot_longer(data=.,
                  cols=-UserNo) %>% 
        mutate(cleannames = trimws(str_sub(name,start = (2+ str_locate(name,". ")[[1]]))))
        
        cleanname <- step1$cleannames[[1]]
        step2 <- step1 %>%     select(UserNo,value) %>% 
            rename(!!sym(cleanname) := value)
        }))


(p2 <- bind_cols(pivot_results,.name_repair="minimal"))
(p3 <- select(p2,all_of(unique(names(p2)))))

(res2 <- full_join(original,p3))

Slavek · November 17, 2021, 4:22pm

Perfect!!! Thank you

Slavek · November 18, 2021, 10:41pm

I know the issue is resolved but my real examples have more than 3 repeated questions (17).

source <- data.frame(
                                                                                                                                           stringsAsFactors = FALSE,
                                                                               NA,
                                                                                                                                                check.names = FALSE,
                                                                               NA,
                                                                      UserID = c(178524794,
                                                                                 178525070),
                                                                                                                                                       UserNo = c(1,2,
                                                                                 NA),
                                                                                                                                                         Name = c(NA,NA,
                                                                                 NA),
                                                                                                                                                        Email = c("aaa","bbb",
                                                                                 NA),
                                                                                                                                                 `IP Address` = c("fsdf","sdfs",
                                                                                 NA),
                                                                                                                                                  `Unique ID` = c(NA,NA,
                                                                                 NA),
                                                                     Started = c("2021-11-08 08:02:00",
                                                                                 "2021-11-08 08:08:00"),
                                                                       Ended = c("2021-11-08 08:10:00",
                                                                                 "2021-11-08 08:32:00"),
                                          `Q1. Please select  your job role` = c("Other",
                                                                                 "Manager"),
              `Q2. Which of the following most closely matches the function` = c("DDCE",
                                                                                 "Customer Excellence Department"),
                                                                                                                                   `Q3. Please select market` = c(NA,NA,
                                                                                 NA),
                                                                                                                                   `Q4. Please select market` = c("dfdsf",NA,
                                                                                 NA),
                                                                                                     `Please select Account Team Member you wish to surveyQ5` = c(NA,"sdfs",
                                                                                 NA),
                                                                                                                                      `How often are you  Q6` = c("Every Couple of Days",NA,
                                                                                 NA),
                                                                                                              `How would you rate your overall experience Q7` = c(10,NA,
                                                                                 NA),
                                                                                                           `have built a good working relationship with meQ8` = c(10,NA,
                                                                                 NA),
                                                                                                          `I feel that the team are helpful and supportiveQ9` = c(10,NA,
                                                                                 NA),
                                                                                    `I trust the team to keep their promises and deliver what they agreedQ10` = c(10,NA,
                                                                                 NA),
                                                                                       `are knowledgeable and can answer my questions about the ProgrammeQ11` = c(10,NA,
                                                                                 NA),
                                                                                                     `The team have a good understanding of local marketsQ12` = c(10,NA,
                                                                                 NA),
                                                                                                              `respond quickly to my requests and queriesQ13` = c(10,NA,
                                                                                 NA),
                                                                                                                                     `Proactive informingQ14` = c(10,NA,
                                                                                 NA),
                                                                                        `are well prepared for our regular PMOs or Programme update callsQ15` = c(10,NA,
                                                                                 NA),
                                                                                                    `Communication from the team is effective and conciseQ16` = c(10,NA,
                                                                                 NA),
                                                                                                                                        `something urgentQ17` = c(10,NA,
                                                                                 NA),
                                                                                                                                                      JIRAQ18 = c(10,NA,
                                                                                 NA),
                                                                                                                              `without me having to chaseQ19` = c(10,NA,
                                                                                 NA),
                                                                                        `Please take this opportunity to give us any additional feedback:Q20` = c("sfs",NA,
                                                                                 NA),
                                                                                                     `How could we improve the service we provide to you?Q21` = c("sadfad",NA,
                                                                                 NA),
                                                                                                    `Please select Account Team Member you wish to surveyQ22` = c(NA,NA,
                                                                                 NA),
                                                                                                                                     `How often are you  Q23` = c(NA,NA,
                                                                                 NA),
                                                                                                             `How would you rate your overall experience Q24` = c(NA,NA,
                                                                                 NA),
                                                                                                          `have built a good working relationship with meQ25` = c(NA,NA,
                                                                                 NA),
                                                                                                         `I feel that the team are helpful and supportiveQ26` = c(NA,NA,
                                                                                 NA),
                                                                                    `I trust the team to keep their promises and deliver what they agreedQ27` = c(NA,NA,
                                                                                 NA),
                                                                                       `are knowledgeable and can answer my questions about the ProgrammeQ28` = c(NA,NA,
                                                                                 NA),
                                                                                                     `The team have a good understanding of local marketsQ29` = c(NA,NA,
                                                                                 NA),
                                                                                                              `respond quickly to my requests and queriesQ30` = c(NA,NA,
                                                                                 NA),
                                                                                                                                     `Proactive informingQ31` = c(NA,NA,
                                                                                 NA),
                                                                                        `are well prepared for our regular PMOs or Programme update callsQ32` = c(NA,NA,
                                                                                 NA),
                                                                                                    `Communication from the team is effective and conciseQ33` = c(NA,NA,
                                                                                 NA),
                                                                                                                                        `something urgentQ34` = c(NA,NA,
                                                                                 NA),
                                                                                                                                                      JIRAQ35 = c(NA,NA,
                                                                                 NA),
                                                                                                                              `without me having to chaseQ36` = c(NA,NA,
                                                                                 NA),
                                                                                        `Please take this opportunity to give us any additional feedback:Q37` = c(NA,NA,
                                                                                 NA),
                                                                                                     `How could we improve the service we provide to you?Q38` = c(NA,NA,
                                                                                 NA),
                                                                                                   `Please select Account Team Member you wish to surveyQ871` = c(NA,"sss",
                                                                                 NA),
                                                                                                                                     `How often are you Q872` = c(NA,"Less Often",
                                                                                 NA),
                                                                                                            `How would you rate your overall experience Q873` = c(NA,8,
                                                                                 NA),
                                                                                                         `have built a good working relationship with meQ874` = c(NA,8,
                                                                                 NA),
                                                                                                        `I feel that the team are helpful and supportiveQ875` = c(NA,8,
                                                                                 NA),
                                                                                   `I trust the team to keep their promises and deliver what they agreedQ876` = c(NA,8,
                                                                                 NA),
                                                                                      `are knowledgeable and can answer my questions about the ProgrammeQ877` = c(NA,8,
                                                                                 NA),
                                                                                                    `The team have a good understanding of local marketsQ878` = c(NA,8,
                                                                                 NA),
                                                                                                             `respond quickly to my requests and queriesQ879` = c(NA,8,
                                                                                 NA),
                                                                                                                                    `Proactive informingQ880` = c(NA,8,
                                                                                 NA),
                                                                                       `are well prepared for our regular PMOs or Programme update callsQ881` = c(NA,8,
                                                                                 NA),
                                                                                                   `Communication from the team is effective and conciseQ882` = c(NA,8,
                                                                                 NA),
                                                                                                                                       `something urgentQ883` = c(NA,8,
                                                                                 NA),
                                                                                                                                                     JIRAQ884 = c(NA,8,
                                                                                 NA),
                                                                                                                             `without me having to chaseQ885` = c(NA,8,
                                                                                 NA),
                                                                                       `Please take this opportunity to give us any additional feedback:Q886` = c(NA,NA,
                                                                                 NA),
                                                                                                    `How could we improve the service we provide to you?Q887` = c(NA,"faa",
                                                                                 NA)
)

I have changed your code to:


library(tidyverse)
(a <- select(source,UserNo,contains("Account Team")))
(b <- select(source,UserNo,contains("How often")))
(c <- select(source,UserNo,contains("How would you rate")))
(d <- select(source,UserNo,contains("relationship")))
(e <- select(source,UserNo,contains("supportive")))
(f <- select(source,UserNo,contains("promises")))
(g <- select(source,UserNo,contains("knowledgeable")))
(h <- select(source,UserNo,contains("markets")))
(i <- select(source,UserNo,contains("respond")))
(j <- select(source,UserNo,contains("proactive")))
(k <- select(source,UserNo,contains("prepared")))
(l <- select(source,UserNo,contains("Communication")))
(m <- select(source,UserNo,contains("urgent")))
(n <- select(source,UserNo,contains("JIRA")))
(o <- select(source,UserNo,contains("chase")))
(p <- select(source,UserNo,contains("additional feedback")))
(q <- select(source,UserNo,contains("How could we improve")))
(original <- select(source,!(contains("Account Team") | contains("How often") | contains("How would you rate" ) | contains("relationship")
                             | contains("supportive")| contains("promises")| contains("knowledgeable") | contains("markets")| contains("respond")| contains("proactive")
                             | contains("prepared")| contains("Communication")| contains("urgent")| contains("JIRA")| contains("chase")| contains("additional feedback")
                             | contains("How could we improve")
                             )))

(pivot_results <- map(list(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q),
                      ~{
                        step1 <- pivot_longer(data=.,
                                              cols=-UserNo) %>% 
                          mutate(cleannames = trimws(str_sub(name,start = (2+ str_locate(name,". ")[[1]]))))
                        
                        cleanname <- step1$cleannames[[1]]
                        step2 <- step1 %>%     select(UserNo,value) %>% 
                          rename(!!sym(cleanname) := value)
                      }))


(p2 <- bind_cols(pivot_results,.name_repair="minimal"))
(p3 <- select(p2,all_of(unique(names(p2)))))

but I can see:

> (p2 <- bind_cols(pivot_results,.name_repair="minimal"))
Error: Can't recycle `..1` (size 1690) to match `..2` (size 1716).

What am I doing wrong?

nirgrahamuk · November 19, 2021, 9:55am

Could you take a second look at the source definition you provided.
I get

Error in data.frame(stringsAsFactors = FALSE, NA, check.names = FALSE,  : 
  arguments imply differing number of rows: 1, 2, 3

Slavek · November 19, 2021, 10:37am

This must be the issue with

datapasta::df_paste

as I import it from excel and then paste using datapasta.

I know this is not our practice but I can give you access to my little file in excel : 20680012.xlsx - Google Sheets

Would it be easier?

Slavek · November 24, 2021, 9:37am

I looked at your solution step by step and I resolved the problem myself.
Thank you master!
People like you make R perfect!

system · December 1, 2021, 9:38am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.