Wide To Long Data Assigning Values Based on Columns

Question

I am trying to convert a wide dataset to a long dataset (I believe this is the correct way to talk about it) which should be rather straight forward. However, I am trying to sort and assign values to the numbers in the columns. It is much easier to understand with a basic example below:

df <- data.frame(respondent = c( "One", "Two", "Three"),
                 minus.four = c(20, 21, 22),
                 minus.three = c(21, 20, 20),
                 minus.two = c(22, 22, 21 ))
df

respondent minus.four minus.three minus.two
1        One         20          21        22
2        Two         21          20        22
3      Three         22          20        21

I am hoping to change it from wide to long (I think that this is correct) and I want it to look like this:

  value One Two Three
1    20  -4  -3    -3
2    21  -3  -4    -2
3    22  -2  -2    -4

The values of 20-22 are based on the column that they were originally in and should be further sorted by the respondent column.

Hopefully this make sense and thank you for any guidance.

Answer 1

Your intended output is still "wide" data, but performing the transformation you're interested in is easier to accomplish if the data are temporarily reshaped to long. Once your column names are placed in the long "name" column, it's relatively easy to substitute the numeric values with case_when, then reshape to wide format.

library(tidyverse)

df %>% 
  pivot_longer(-respondent) %>% 
  mutate(value_numeric = case_when(
    name == 'minus.four' ~ -4,
    name == 'minus.three' ~ -3,
    name == 'minus.two' ~ -2
  )) %>% 
  select(-name) %>% 
  pivot_wider(values_from = value_numeric, names_from = respondent)

  value   One   Two Three
  <dbl> <dbl> <dbl> <dbl>
1    20    -4    -3    -3
2    21    -3    -4    -2
3    22    -2    -2    -4