Reorganising data


I am working with questionnaire data. I have a data list (an Excel file that I have imported in RStudio with function "read_excel") where columns are the questions and each individual's answer makes one row. One of the questions is "diagnosis" (options a, b and c), and another "treatment" (options x, y, z and w). I would like to create a new table that represents distribution of the treatment option based on diagnosis (where each diagnosis group makes one row, and the column names are x, y, z and w, and the table values indicate how many in the diagnosis group have received a specific treatment). Could someone please help me how to do that in RStudio?


To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

Here is an example of what I understand you want to do. I strongly recommend you read the link @adresrcs provided so you can ask efficient questions in the future.
In my code, I first invent some data, which you will not need to do since you have already imported a data set.

#invent some data
DF <- data.frame(id = 1:10, diag = sample(letters[1:3],10, replace = TRUE),
+                  treat = sample(letters[23:26], 10, replace = TRUE))
   id diag treat
1   1    a     z
2   2    a     w
3   3    c     z
4   4    b     y
5   5    a     x
6   6    a     x
7   7    c     z
8   8    a     y
9   9    b     z
10 10    c     x

#Make a table from two columns
CrossTable <- table(DF$diag, DF$treat)
    w x y z
  a 1 2 1 1
  b 0 0 1 1
  c 0 1 0 2

That would work. Another way would be to use the xtabs function:

CrossTable <- xtabs(~diag + treat, data = DF)

Yes, that's exactly what I was looking for! Thank you very much for your help and advice!

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.