-2

I have a data frame in a wide format (four variables that are rated from 1 to 7) which are repeated for 87 items. My data frame looks like this

Subject| Variable 1 for item 1| Variable 2 for item 1| Variable 3 for item 1| Variable 4 for item 1

till we got to Variable 1 for item 87| Variable 2 for item 87|Variable 3 for item 87|Variable 4 for item 87

The goal:

Subject Variable 1 Variable 2 Variable 3 Variable 3 Variable 4

1

1

1

1(for 87 items)

Appreciate any code example..

pogibas
  • 27,303
  • 19
  • 84
  • 117
user3783816
  • 17
  • 2

1 Answers1

0

This solution uses 'pivot_longer' and 'pivot_wider' from the tidyr package. If you've used tidyr or tidyverse previously I'd recommend checking that you have tidyr version 1.0.0, or just re-installing tidyr...

So first off I recommend taking a look at this article when you get the chance, it gives a lot of help: https://tidyr.tidyverse.org/articles/pivot.html

The workflow is pretty straightforward. First we need to make your very wide data long. Secondly we need to separate the variable and item columns Then lastly we can pivot wide again with only the variable column

# Create dummy data
x <- tibble(
        var = c('Variable 1 for item 1', 'Variable 2 for item 1', 
                'Variable 3 for item 1', 'Variable 4 for item 1', 
                'Variable 1 for item 2', 'Variable 2 for item 2', 
                'Variable 3 for item 2', 'Variable 4 for item 2'),
        results = c(runif(8))) %>% 
        mutate(subject = row_number())

# Pivot the table wider to replicate the description. 
# I could have written it this way, but I thought it might 
# take longer ... 
x_wide <- x %>% 
        pivot_wider(names_from = var, 
                    values_from = results)


# This is the actual part you care about
x_long <- x_wide %>%
# So first we need to make the table longer, so that 
# we can manipulate the column names into something workable
        pivot_longer(cols = starts_with('Variable'), 
                     names_to = 'var', 
                     values_to = 'val') %>%
# Next we need to separate the column name into variable and item
        separate(var, into = c('variable', 'item'), 
                 sep = 'for') %>% 
# This is just cleaning up the item and variable columns
        mutate(item = str_remove(item, 'item'), 
               item = str_trim(item, side = 'both'), 
               variable = str_trim(variable, side = 'both')
               ) %>% 
# lastly we pivot wide again but this time only on the variable
        pivot_wider(names_from = variable, 
                    values_from = val)

Hope this helps! I've had to make a lot of assumptions about what the data looks like. Happy to follow up if I've made a wrong guess.

JFlynn
  • 344
  • 2
  • 8