pivot_wider() generates new dataframe filled with NULL values and other misprinted values

Question

I am using pivot_wider() in an attempt to transform this dataframe.

subject_id  test_name  test_result test_unit  
12          Spanish    100         print
12          English    99          online
13          Spanish    98          print
13          English    91          print

Into:

subject_id  spanish_test  english_test  
12          100           99
13          98            91

I used pivot_wider with the following code:

test %>%
  pivot_wider(id_cols = subject_id,
  names_from = Test_Name,
  values_from = Test_Unit)

And I got the individual test columns generated, however, they were filled with the units or NULL values. Here is the dataframe for reference:

subject_id <- c(12, 12, 13, 13)
test_name <- c("Spanish", "English", "Spanish", "English")
test_result <- c(100, 99, 98, 91)
test_unit <- c("print", "online", "print", "print")
df <- data.frame(subject_id, test_name, test_result, test_unit)

If you use lowercase `name` and `value` entries (e.g. `test_name`) and use `df` instead of `test`, your code will produce the result you want, minus the `_test` column suffix. for the suffix, see `names_glue` in @RonakShah's answer. — andrew_reece, Jul 19 '21 at 03:23

score 3 · Answer 1 · answered Jul 19 '21 at 03:18

3

You can use pivot_wider as -

tidyr::pivot_wider(df, 
                   id_cols = subject_id,
                   names_from = test_name,
                   values_from = test_result, 
                   names_glue = '{test_name}_test')

#  subject_id Spanish_test English_test
#       <dbl>        <dbl>        <dbl>
#1         12          100           99
#2         13           98           91

answered Jul 19 '21 at 03:18

Ronak Shah

377,200
20
156
213

wrapping `test_name` with `tolower()` would produce OP's expected output exactly. `names_glue = '{tolower(test_name)}_test'` – andrew_reece Jul 19 '21 at 03:24

AndrewGB · Answer 2 · 2021-07-19T05:47:26.483

An alternative using reshape2 along with dplyr to rename the columns.

library(reshape2)
library(dplyr)

reshape2::dcast(df, subject_id ~ test_name,
                value.var = "test_result") %>%
  dplyr::rename_at(vars(Spanish:English), list( ~ paste0(., "_test"))) %>%
  dplyr::rename_all(tolower) %>%
  dplyr::select(subject_id, spanish_test, english_test)

Output

  subject_id spanish_test english_test
1         12          100           99
2         13           98           91

pivot_wider() generates new dataframe filled with NULL values and other misprinted values

2 Answers2