0

I have the following data frame labelled df1:

df1 <- data.frame(ID = c("A", "A", "A", "B", "B", "B", "B"),
                  NO. = c(2,8,7,1,4,9,6),
                  RANK = c(1,2,3,1,2,3,4))

It looks like this:

| ID | NO. | RANK
------------------
| A  | 2   |  1
| A  | 8   |  2
| A  | 7   |  3
| B  | 1   |  1
| B  | 4   |  2
| B  | 9   |  3
| B  | 6   |  4

My data frame has many ID's and the 'RANK' column can go as high as 30 but this is just a snapshot.

I wish to transpose the NO. column based on the ID and the RANK like so:

| ID | NO._RANK_1 | NO._RANK_2  |  NO._RANK_3  |  NO._RANK_4
-------------------------------------------------------------
| A  | 2          |  8          |  7           |
| B  | 1          |  4          |  9           | 6

Ideally I would like this as a separate data frame (df2).

I tried:

library(dplyr)
library(tidyr)
df2 <- df1 %>%
  group_by(ID) %>%
  spread(RANK, NO.)

But this returns the numbers in separate columns AND rows like so:

| ID | 1  | 2  | 3  | 4
------------------
| A  | 2  | NA | NA | NA
| A  | NA | 8  | NA | NA
| A  | NA | NA | 7  | NA
| B  | 1  | NA | NA | NA
| B  | NA | 4  | NA | NA
| B  | NA | NA | 9  | NA
| B  | NA | NA | NA | 6
Michael
  • 221
  • 1
  • 8
  • Please share your data in a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) that we can copy/paste into R for testing. Look at the new `pivot_wider` verb in `tidyr` package. – MrFlick Jun 21 '21 at 03:39
  • thanks, I have edited my question to include the data for testing. – Michael Jun 21 '21 at 03:46
  • Your data is still not reproducible. Anyway, you can try `tidyr::pivot_wider(df1, names_from = RANK, values_from = NO., names_prefix = 'NO._RANK_')` – Ronak Shah Jun 21 '21 at 03:48
  • I do not get the output you do When I run with your code. I get the table with only two rows as desired. Note that `spread` is not in `dplyr`. Are you using `spread` from `tidyr`? What version of tidyr are you using? I tested with `tidyr_1.1.3` – MrFlick Jun 21 '21 at 03:49
  • ok I will read up on a reproducible format and amend accordingly. – Michael Jun 21 '21 at 03:50
  • I have amended the data to ensure it is reproduciable. Please let me know if it is not as this is the first time I have been asked to do it and I want to ensure it is correct. – Michael Jun 21 '21 at 03:52
  • Your data frame is now properly reproducible which is good. You still seems to have omitted the `library(tidyr)` which is required for `spread`. You should list all package required to run the code. But as I said. I don't get the same output when running the code. So either something has been left out, or we are using different versions of the package. It's useful to also include package versions from `sessionInfo()` when discrepancies arrive. – MrFlick Jun 21 '21 at 03:57
  • ok that is good news. I have added `library(tidyr)` and I will ensure in future I list all packages required. I am not sure why you don't get the same output but the `pivot_wider` version above gives me the required result. – Michael Jun 21 '21 at 04:01

0 Answers0