How can I modify and transpose a messy dataset?

Question

I plan to transform my dataset from the one shown on left to right:

I am still learning how to use R for analysis, so would be really appreciated if anyone knows how to do that in R :)

Check `dplyr` package and `spread()` function in specific. Does exactly that. — denisafonin, Sep 22 '21 at 13:32
According to `spread()` help file it's been superseded by `pivot_wider()` — Harrison Jones, Sep 22 '21 at 13:38

score 0 · Answer 1 · answered Sep 22 '21 at 13:36

There is a good functionality available in data.table package in R

dcast(table, Test ~ Column1, value.var = "Preference")

You can check the functionality of the function here. There is also option to return back to the initial long version of the table using melt function.

score 0 · Accepted Answer · answered Sep 22 '21 at 13:37

pivot_wider from the tidyr package should do the trick

library(tidyverse)

foo <- tibble(
  ID = paste0('Female00', c(1, 1, 1, 2, 2, 3, 3, 3, 4, 5, 5, 5, 6)), 
  Test = c('Food', 'Beverage', 'Color', 'Beverage', 'Food', 'Color', 'Food', 'Beverage', 'Food', 'Beverage', 'Color', 'Food', 'Color'), 
  Preference = c('Strong', 'Strong', 'Medium', 'Low', 'Medium', 'Medium', 'Strong', 'Low', 'Low', 'Strong', 'Low', 'Medium', 'Medium')
)

foo %>%
  pivot_wider( 
    names_from = ID,
    values_from = Preference, 
    values_fill = NA
  )

score 0 · Answer 3 · answered Sep 22 '21 at 13:37

It is just a matter of transposing the data frame :

df
         Id     Test Preference
1 Female001     Food     Strong
2 Female001 Beverage     Strong
3 Female001    Color     Medium
4 Female002     Food     Medium
5 Female002 Beverage        Low
6 Female003    Color     Medium
7 Female003     Food     Strong
8 Female003 Beverage        Low
> library(tidyr)
> df %>% spread(Id,Preference)
      Test Female001 Female002 Female003
1 Beverage    Strong       Low       Low
2    Color    Medium      <NA>    Medium
3     Food    Strong    Medium    Strong

How can I modify and transpose a messy dataset?

3 Answers3