Shifting Data from Columns to Rows with Two Factors

[charlieh108]

I have managed to delete a little bit of code that did the below task and can't for the life of me figure out how I did it before.

I want to widen the data that has two factors spread over 8 different 'waves'. There are four 'Paper' factors, each with the same four internal factors 'Response'. The output from a previously required function gives the following dataframe:

[

And I would like to make it look like this:

https://i.stack.imgur.com/ONuB2.png

The single column of the first tibble has become the single row of the second tibble.
As you can see, the second tibble has extra factors of Paper but these can just be joined row wise.

I really wasn't sure how to attack this, but thought it would be done using the pivot_wider function. When I tried

times_correct <- times_19 %>%
                  pivot_wider( id_cols = c('Stay/remain in the EU`', 'Leave the EU', 'I would/will not vote', 'Don\'t know'), names_from = eurrefcolnames)

I got the error that I can't subset columns that don't exist which makes sense: I need to manually add the correct 'Waves'. I think this is relatively simple, but can't for the life of me figure out how I did it!

Here is the dput of the various tibbles:

structure(list(resp = structure(c(3L, 2L, 4L, 1L, NA, NA, NA, 
NA), .Label = c("Don't Know", "Leave", "Remain", "Will Not Vote"
), class = "factor"), `Stay/remain in the EU` = c(316L, 290L, 
313L, 324L, 338L, 320L, 325L, 335L), `Leave the EU` = c(157L, 
123L, 159L, 154L, 134L, 189L, 187L, 181L), `I would/will not vote` = c(2L, 
3L, 3L, 3L, 2L, 2L, 2L, 0L), `Don't know` = c(56L, 51L, 55L, 
50L, 57L, 20L, 17L, 0L), Paper = structure(c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L), .Label = "Times", class = "factor")), row.names = c(NA, 
-8L), class = c("tbl_df", "tbl", "data.frame")) 

structure(list(resp = structure(c(3L, 2L, 4L, 1L, 3L, 2L, 4L, 
1L, 3L, 2L, 4L, 1L, 3L, 2L, 4L, 1L, 3L, 2L, 4L, 1L), .Label = c("Don't Know", 
"Leave", "Remain", "Will Not Vote"), class = "factor"), euRefVoteW1 = c(316L, 
157L, 2L, 56L, 190L, 339L, 4L, 70L, 819L, 79L, 9L, 71L, 1294L, 
1311L, 150L, 523L, 1715L, 2587L, 133L, 630L), euRefVoteW2 = c(290L, 
123L, 3L, 51L, 175L, 282L, 3L, 62L, 777L, 74L, 5L, 62L, 1091L, 
925L, 80L, 371L, 1528L, 2044L, 83L, 517L), euRefVoteW3 = c(313L, 
159L, 3L, 55L, 199L, 334L, 4L, 69L, 835L, 81L, 10L, 57L, 1348L, 
1289L, 139L, 508L, 1766L, 2563L, 156L, 586L), euRefVoteW4 = c(324L, 
154L, 3L, 50L, 215L, 328L, 2L, 61L, 848L, 70L, 10L, 55L, 1397L, 
1267L, 128L, 492L, 1853L, 2494L, 143L, 583L), euRefVoteW6 = c(338L, 
134L, 2L, 57L, 241L, 286L, 2L, 77L, 853L, 68L, 5L, 57L, 1519L, 
1133L, 112L, 520L, 2017L, 2284L, 106L, 667L), euRefVoteW7 = c(320L, 
189L, 2L, 20L, 186L, 384L, 2L, 34L, 832L, 109L, 8L, 34L, 1449L, 
1456L, 87L, 292L, 1906L, 2785L, 55L, 328L), euRefVoteW8 = c(325L, 
187L, 2L, 17L, 187L, 384L, 1L, 34L, 836L, 118L, 5L, 24L, 1462L, 
1522L, 72L, 228L, 1898L, 2852L, 56L, 268L), euRefVoteW9 = c(335L, 
181L, 0L, 0L, 206L, 385L, 0L, 6L, 844L, 102L, 0L, 4L, 1572L, 
1462L, 0L, 21L, 2018L, 2827L, 0L, 20L), Paper = structure(c(1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 5L, 
5L, 5L, 5L), .Label = c("Times", "Telegraph", "Control", "No_Paper", 
"Rest"), class = "factor")), row.names = c(NA, -20L), class = c("tbl_df", 
"tbl", "data.frame"))

eurrefcolnames = c('euRefVoteW1','euRefVoteW2', 'euRefVoteW3', 'euRefVoteW4', 'euRefVoteW6',' euRefVoteW7', 'euRefVoteW8',  'euRefVoteW9')

https://i.stack.imgur.com/ONuB2.png

[mara]

I'm not sure how the first data frame is transforming into the latter, given you have, say 338 aligned with NA in the resp column in the first data frame, but then it has Remain as its response in the latter.

What's the relationship between the various tibbles? If the second one is from a transformation of the first, then I'm not sure where the data is coming from.

Also including a reprex below (the two tibbles are at the bottom), since it's easier to view than bouncing out to images:

suppressPackageStartupMessages(library(tidyverse))
df1 <- structure(list(resp = structure(c(
  3L, 2L, 4L, 1L, NA, NA, NA,
  NA
), .Label = c("Don't Know", "Leave", "Remain", "Will Not Vote"), class = "factor"), `Stay/remain in the EU` = c(
  316L, 290L,
  313L, 324L, 338L, 320L, 325L, 335L
), `Leave the EU` = c(
  157L,
  123L, 159L, 154L, 134L, 189L, 187L, 181L
), `I would/will not vote` = c(
  2L,
  3L, 3L, 3L, 2L, 2L, 2L, 0L
), `Don't know` = c(
  56L, 51L, 55L,
  50L, 57L, 20L, 17L, 0L
), Paper = structure(c(
  1L, 1L, 1L, 1L,
  1L, 1L, 1L, 1L
), .Label = "Times", class = "factor")), row.names = c(
  NA,
  -8L
), class = c("tbl_df", "tbl", "data.frame"))
df2 <- structure(list(resp = structure(c(
  3L, 2L, 4L, 1L, 3L, 2L, 4L,
  1L, 3L, 2L, 4L, 1L, 3L, 2L, 4L, 1L, 3L, 2L, 4L, 1L
), .Label = c(
  "Don't Know",
  "Leave", "Remain", "Will Not Vote"
), class = "factor"), euRefVoteW1 = c(
  316L,
  157L, 2L, 56L, 190L, 339L, 4L, 70L, 819L, 79L, 9L, 71L, 1294L,
  1311L, 150L, 523L, 1715L, 2587L, 133L, 630L
), euRefVoteW2 = c(
  290L,
  123L, 3L, 51L, 175L, 282L, 3L, 62L, 777L, 74L, 5L, 62L, 1091L,
  925L, 80L, 371L, 1528L, 2044L, 83L, 517L
), euRefVoteW3 = c(
  313L,
  159L, 3L, 55L, 199L, 334L, 4L, 69L, 835L, 81L, 10L, 57L, 1348L,
  1289L, 139L, 508L, 1766L, 2563L, 156L, 586L
), euRefVoteW4 = c(
  324L,
  154L, 3L, 50L, 215L, 328L, 2L, 61L, 848L, 70L, 10L, 55L, 1397L,
  1267L, 128L, 492L, 1853L, 2494L, 143L, 583L
), euRefVoteW6 = c(
  338L,
  134L, 2L, 57L, 241L, 286L, 2L, 77L, 853L, 68L, 5L, 57L, 1519L,
  1133L, 112L, 520L, 2017L, 2284L, 106L, 667L
), euRefVoteW7 = c(
  320L,
  189L, 2L, 20L, 186L, 384L, 2L, 34L, 832L, 109L, 8L, 34L, 1449L,
  1456L, 87L, 292L, 1906L, 2785L, 55L, 328L
), euRefVoteW8 = c(
  325L,
  187L, 2L, 17L, 187L, 384L, 1L, 34L, 836L, 118L, 5L, 24L, 1462L,
  1522L, 72L, 228L, 1898L, 2852L, 56L, 268L
), euRefVoteW9 = c(
  335L,
  181L, 0L, 0L, 206L, 385L, 0L, 6L, 844L, 102L, 0L, 4L, 1572L,
  1462L, 0L, 21L, 2018L, 2827L, 0L, 20L
), Paper = structure(c(
  1L,
  1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 5L,
  5L, 5L, 5L
), .Label = c(
  "Times", "Telegraph", "Control", "No_Paper",
  "Rest"
), class = "factor")), row.names = c(NA, -20L), class = c(
  "tbl_df",
  "tbl", "data.frame"
))
eurrefcolnames <- c("euRefVoteW1", "euRefVoteW2", "euRefVoteW3", "euRefVoteW4", "euRefVoteW6", " euRefVoteW7", "euRefVoteW8", "euRefVoteW9")

df1
#> # A tibble: 8 x 6
#>   resp    `Stay/remain in t… `Leave the EU` `I would/will no… `Don't know` Paper
#>   <fct>                <int>          <int>             <int>        <int> <fct>
#> 1 Remain                 316            157                 2           56 Times
#> 2 Leave                  290            123                 3           51 Times
#> 3 Will N…                313            159                 3           55 Times
#> 4 Don't …                324            154                 3           50 Times
#> 5 <NA>                   338            134                 2           57 Times
#> 6 <NA>                   320            189                 2           20 Times
#> 7 <NA>                   325            187                 2           17 Times
#> 8 <NA>                   335            181                 0            0 Times

df2
#> # A tibble: 20 x 10
#>    resp  euRefVoteW1 euRefVoteW2 euRefVoteW3 euRefVoteW4 euRefVoteW6 euRefVoteW7
#>    <fct>       <int>       <int>       <int>       <int>       <int>       <int>
#>  1 Rema…         316         290         313         324         338         320
#>  2 Leave         157         123         159         154         134         189
#>  3 Will…           2           3           3           3           2           2
#>  4 Don'…          56          51          55          50          57          20
#>  5 Rema…         190         175         199         215         241         186
#>  6 Leave         339         282         334         328         286         384
#>  7 Will…           4           3           4           2           2           2
#>  8 Don'…          70          62          69          61          77          34
#>  9 Rema…         819         777         835         848         853         832
#> 10 Leave          79          74          81          70          68         109
#> 11 Will…           9           5          10          10           5           8
#> 12 Don'…          71          62          57          55          57          34
#> 13 Rema…        1294        1091        1348        1397        1519        1449
#> 14 Leave        1311         925        1289        1267        1133        1456
#> 15 Will…         150          80         139         128         112          87
#> 16 Don'…         523         371         508         492         520         292
#> 17 Rema…        1715        1528        1766        1853        2017        1906
#> 18 Leave        2587        2044        2563        2494        2284        2785
#> 19 Will…         133          83         156         143         106          55
#> 20 Don'…         630         517         586         583         667         328
#> # … with 3 more variables: euRefVoteW8 <int>, euRefVoteW9 <int>, Paper <fct>

^{Created on 2021-01-05 by the reprex package (v0.3.0.9001)}

[charlieh108]

Hi mara,

First of all, thanks for correctly formatting the dput(). The data in the first tibble is an incorrect output from a function which tallies the number of responses in each of four categories across eight waves for one 'Paper'. (There are multiple Papers, as you can see in the second tibble, but these can just be joined row-wise) The four columns of data are correct but each row actually represents each wave. I am trying to, in effect, rotate these columns with the first column going to the first row 'Remain, the second column to the second row 'Leave 'etc.. and replace the current column labels with the eurrefcolnames which is the correct label.

[mara]

I don't know why I'm having so much trouble getting my head around this one (hopefully someone else won't), but have you taken a look at some of the more involved examples in the tidyr Pivoting vignette?

Pivoting

I think you'll need to perform a join to use the waves in the first tibble…but, again, I think there's something I'm not quite groking here. Sorry