Condense matrix of values into one column?

I have a dataset where I have unnecessarily duplicated column variables that I want to condense down. I wish the output wasn't so clumsy and I've already had to do some work to transform it and make it easier to manage. I'm familiar with basic R stuff but not an expert by any means so please be patient!

Each R(1-10) corresponds to a response rating for the question (q1-10). The questions are randomised in order for each trial for each participant, so the qnum doesn't refer to the same question every time. I want to condense the matrix of response ratings down so that there is a single column called 'response' which reflects the rating given by the participant for each qnum for each trialNumber, so I don't have unnecessarily repeated tokens! Essentially going down the diagonal and reshaping it into one column. How can I do this?

Topic transformation reshape r

Category Data Science

1
answered at

You can use quite easily using the dplyr and tidyr packages as follows:

df %>%
    # convert the columns to rows
    gather(key="response", value="value", R1, R2, R3, R4, R5, R6, R7, R8, R9, R10) %>%
    # select only rows where the reponse corresponds to the question
    filter(qnum == substr(response, 2, numchar(response)))

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.