R how to convert from long to wide format

Question

I require a data frame df_wide with following columns:

userID   SAT   GRE   task_conf task_chall active_conf  active_chall  sleep_conf  sleep_chall morn_conf  morn_chall
30798    A     1400  2         3          5            2             6            1          4          2
30895    A     1200  6         2          5            3             5            2          5          3
32678    B     1000  5         3          6            3             6            2          5          2
34679    A     1300  4         3          4            2             6            1          6          3
35999    A     1400  2         2          2            2             2            2          2          2

Some information about the features:

The variables '_conf' and '_chall' contain integer values between 1 and 6
'userID's can be factors or integers but they are not continuous numbers
SAT represents the grade of that 'userID'
GRE represents the score of that 'userID'
SAT and GRE always stay the same for a given 'userID'

My original data df_long is currently in the following form :

userID SAT GRE  action ConfChall vals
30798  A   1400 task   conf      2
30798  A   1400 task   chall     3
30798  A   1400 active conf      5
30798  A   1400 active chall     2
30798  A   1400 sleep  conf      6
30798  A   1400 sleep  chall     1
30798  A   1400 morn   conf      4
30798  A   1400 morn   chall     2
30895  A   1200 task   conf      6
30895  A   1200 task   chall     2
30895  A   1200 active conf      5
30895  A   1200 active chall     3
30895  A   1200 sleep  conf      5
30895  A   1200 sleep  chall     2
30895  A   1200 morn   conf      5
30895  A   1200 morn   chall     3
32678  B   1000 task   conf      5
32678  B   1000 task   chall     3
32678  B   1000 active conf      6
32678  B   1000 active chall     3
32678  B   1000 sleep  conf      6
32678  B   1000 sleep  chall     2
32678  B   1000 morn   conf      5
32678  B   1000 morn   chall     2
34679  A   1300 task   conf      4
34679  A   1300 task   chall     3
34679  A   1300 active conf      4
34679  A   1300 active chall     2
34679  A   1300 sleep  conf      6
34679  A   1300 sleep  chall     1
34679  A   1300 morn   conf      6
34679  A   1300 morn   chall     3
35999  A   1400 task   conf      2
35999  A   1400 task   chall     2
35999  A   1400 active conf      2
35999  A   1400 active chall     2
35999  A   1400 sleep  conf      2
35999  A   1400 sleep  chall     2
35999  A   1400 morn   conf      2
35999  A   1400 morn   chall     2

I tried using the following codes, but the output is incorrect in both cases.

library(reshape2)
df_wide = recast(df_long, userID ~ c('action','confChall','vals'),
          id.var = c("userID", "SAT", "GRE"))

df_wide = dcast(df_long, userID + SAT + GRE ~ c(action + ConfChall), value.var = "vals")

I tried to follow the example codes from the following pages. But I am having difficulty in applying these to my problem. Any advice or suggestion on this would be greatly appreciated.

Reshape data from long to wide format - more than one variable

Reshape multiple values at once

eipi10 · Accepted Answer · 2019-10-31T23:21:16.030

You can reshape multiple category columns and multiple value columns with pivot_wider from the tidyr package (which is part of the tidyverse suite of packages):

library(tidyverse)

df_wide = df_long %>% 
  pivot_wider(names_from=c(action, ConfChall), values_from=vals)

  userID SAT  GRE task_conf task_chall active_conf active_chall sleep_conf sleep_chall morn_conf morn_chall
1  30798   A 1400         2          3           5            2          6           1         4          2
2  30895   A 1200         6          2           5            3          5           2         5          3
3  32678   B 1000         5          3           6            3          6           2         5          2
4  34679   A 1300         4          3           4            2          6           1         6          3

reshape2 is an old package that, as far as I know, is no longer under active development and has been supplanted by tidyverse packages.

To address the warning you mentioned in the comments: If there are any cells in the wide data frame that have more than one value, then you'll get the result you're getting. This will happen in your case when there is more than one row with the same userID, SAT, GRE, action, and ConfChall, or in general when their are combinations of the row and column categories that can appear in more than one row. That doesn't happen in your data sample, but it's happening in your real data.

So let's add a duplicated row to your data sample:

df_long = read.table(text="userID SAT GRE  action ConfChall vals
30798  A   1400 task   conf      2
30798  A   1400 task   chall     3
30798  A   1400 task   chall     4 # added row to create a duplicate
30798  A   1400 active conf      5
30798  A   1400 active chall     2
30798  A   1400 sleep  conf      6
30798  A   1400 sleep  chall     1
30798  A   1400 morn   conf      4
30798  A   1400 morn   chall     2
30895  A   1200 task   conf      6
30895  A   1200 task   chall     2
30895  A   1200 active conf      5
30895  A   1200 active chall     3
30895  A   1200 sleep  conf      5
30895  A   1200 sleep  chall     2
30895  A   1200 morn   conf      5
30895  A   1200 morn   chall     3
32678  B   1000 task   conf      5
32678  B   1000 task   chall     3
32678  B   1000 active conf      6
32678  B   1000 active chall     3
32678  B   1000 sleep  conf      6
32678  B   1000 sleep  chall     2
32678  B   1000 morn   conf      5
32678  B   1000 morn   chall     2
34679  A   1300 task   conf      4
34679  A   1300 task   chall     3
34679  A   1300 active conf      4
34679  A   1300 active chall     2
34679  A   1300 sleep  conf      6
34679  A   1300 sleep  chall     1
34679  A   1300 morn   conf      6
34679  A   1300 morn   chall     3", header=TRUE)

Now let's reshape to wide again. Note that we get the warning and that one of the list column cells has two values instead of one:

df_long %>% 
  pivot_wider(names_from=c(action, ConfChall), values_from=vals)

Warning message:
Values in `vals` are not uniquely identified; output will contain list-cols.
* Use `values_fn = list(vals = list)` to suppress this warning.
* Use `values_fn = list(vals = length)` to identify where the duplicates arise
* Use `values_fn = list(vals = summary_fun)` to summarise duplicates

  userID SAT     GRE   task_conf  task_chall active_conf active_chall  sleep_conf sleep_chall   morn_conf  morn_chall
   <int> <fct> <int> <list<int>> <list<int>> <list<int>>  <list<int>> <list<int>> <list<int>> <list<int>> <list<int>>
1  30798 A      1400         [1]         [2]         [1]          [1]         [1]         [1]         [1]         [1]
2  30895 A      1200         [1]         [1]         [1]          [1]         [1]         [1]         [1]         [1]
3  32678 B      1000         [1]         [1]         [1]          [1]         [1]         [1]         [1]         [1]
4  34679 A      1300         [1]         [1]         [1]          [1]         [1]         [1]         [1]         [1]

To get a regular data frame, you can use unnest(). Note that there are now five rows, with userID 30798 appearing twice:

df_long %>% 
  pivot_wider(names_from=c(action, ConfChall), values_from=vals) %>% 
  unnest()

  userID SAT     GRE task_conf task_chall active_conf active_chall sleep_conf sleep_chall morn_conf morn_chall
   <int> <fct> <int>     <int>      <int>       <int>        <int>      <int>       <int>     <int>      <int>
1  30798 A      1400         2          3           5            2          6           1         4          2
2  30798 A      1400         2          4           5            2          6           1         4          2
3  30895 A      1200         6          2           5            3          5           2         5          3
4  32678 B      1000         5          3           6            3          6           2         5          2
5  34679 A      1300         4          3           4            2          6           1         6          3

If you want the duplicate rows summarized in some way, so that you get just one row per combination of the row and column variables you can apply a summary function. Below, we take the mean of each cells, which in this case affects only the once cell with two rows of data:

df_long %>% 
  pivot_wider(names_from=c(action, ConfChall), values_from=vals,
              values_fn=list(vals=mean))

  userID SAT     GRE task_conf task_chall active_conf active_chall sleep_conf sleep_chall morn_conf morn_chall
   <int> <fct> <int>     <dbl>      <dbl>       <dbl>        <dbl>      <dbl>       <dbl>     <dbl>      <dbl>
1  30798 A      1400         2        3.5           5            2          6           1         4          2
2  30895 A      1200         6        2             5            3          5           2         5          3
3  32678 B      1000         5        3             6            3          6           2         5          2
4  34679 A      1300         4        3             4            2          6           1         6          3

Thanks @eipi10 I tried this, I am getting the following warning messages: Warning message: Values in `vals` are not uniquely identified; output will contain list-cols. * Use `values_fn = list(vals = list)` to suppress this warning. * Use `values_fn = list(vals = length)` to identify where the duplicates arise * Use `values_fn = list(vals = summary_fun)` to summarise duplicates Do I need to have action and ConfChall as factors? — Sandy, Oct 31 '19 at 22:42
In your real data, is there at least one combination of userID, SAT, and GRE that appears more than once? — eipi10, Oct 31 '19 at 22:43
YES, in my original `df_long` data most of the `userID`s are on more than one rows. — Sandy, Oct 31 '19 at 22:45
`SAT` and `GRE` stay the same for a given `userID`. I have updated my question, please have a look. — Sandy, Oct 31 '19 at 22:48
Sorry, what I said above was incorrect. Let me start over: If there are any cells in the wide data frame that have more than one value, then you'll get the result you're getting. This will happen in your case when there is more than one row with the same userID, SAT, GRE, action, and ConfChall. That doesn't happen in your data sample, but it's happening in your real data. — eipi10, Oct 31 '19 at 22:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/201689/discussion-between-sandy-and-eipi10). — Sandy, Oct 31 '19 at 22:55

R how to convert from long to wide format

1 Answers1

Linked