CODEWITHSUNDEEP
rdplyrtidyr
Doug Fir
Doug Fir

Reputation: 21302

Straight forward pivot, pivot_wider giving unexpexted results

Here's some example data:

    exdata <- structure(list(date = structure(c(18780, 18784, 18785, 18786, 
18787, 18789, 18779, 18781, 18782, 18783, 18788, 18790, 18791, 
18792, 18793, 18794, 18795), class = "Date"), foo = c(1L, 1L, 
3L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), 
    bar = c(2986L, 2443L, 3601L, 3184L, 3164L, 3230L, 3343L, 
    3313L, 2631L, 2054L, 3386L, 2686L, 3127L, 4072L, 4057L, 3723L, 
    3912L), blah = c(21L, 36L, 44L, 32L, 28L, 13L, 31L, 12L, 
    27L, 24L, 22L, 23L, 25L, 17L, 24L, 24L, 18L)), row.names = c(NA, 
-17L), class = c("tbl_df", "tbl", "data.frame"))

That looks like this:

exdata
# A tibble: 17 x 4
   date         foo   bar  blah
   <date>     <int> <int> <int>
 1 2021-06-02     1  2986    21
 2 2021-06-06     1  2443    36
 3 2021-06-07     3  3601    44
 4 2021-06-08     1  3184    32
 5 2021-06-09     1  3164    28
 6 2021-06-11     1  3230    13
 7 2021-06-01     0  3343    31
 8 2021-06-03     0  3313    12
 9 2021-06-04     0  2631    27
10 2021-06-05     0  2054    24
11 2021-06-10     0  3386    22
12 2021-06-12     0  2686    23
13 2021-06-13     0  3127    25
14 2021-06-14     0  4072    17
15 2021-06-15     0  4057    24
16 2021-06-16     0  3723    24
17 2021-06-17     0  3912    18

I'd like to pivot, where I have date running across the top and then 3 rows underneath, one row for each of foo, bar and bla.

Tried:

exdata %>% pivot_wider(names_from = date, values_from = foo:bar)

Which gave:

# A tibble: 15 x 35
    blah `foo_2021-06-02` `foo_2021-06-06` `foo_2021-06-07` `foo_2021-06-08` `foo_2021-06-09`
   <int>            <int>            <int>            <int>            <int>            <int>
 1    21                1               NA               NA               NA               NA
 2    36               NA                1               NA               NA               NA
 3    44               NA               NA                3               NA               NA
 4    32               NA               NA               NA                1               NA
 5    28               NA               NA               NA               NA                1
 6    13               NA               NA               NA               NA               NA
 7    31               NA               NA               NA               NA               NA
 8    12               NA               NA               NA               NA               NA
 9    27               NA               NA               NA               NA               NA
10    24               NA               NA               NA               NA               NA
11    22               NA               NA               NA               NA               NA
12    23               NA               NA               NA               NA               NA
13    25               NA               NA               NA               NA               NA
14    17               NA               NA               NA               NA               NA
15    18               NA               NA               NA               NA               NA
# … with 29 more variables: `foo_2021-06-11` <int>, `foo_2021-06-01` <int>, `foo_2021-06-03` <int>,
#   `foo_2021-06-04` <int>, `foo_2021-06-05` <int>, `foo_2021-06-10` <int>, `foo_2021-06-12` <int>,
#   `foo_2021-06-13` <int>, `foo_2021-06-14` <int>, `foo_2021-06-15` <int>, `foo_2021-06-16` <int>,
#   `foo_2021-06-17` <int>, `bar_2021-06-02` <int>, `bar_2021-06-06` <int>, `bar_2021-06-07` <int>,
#   `bar_2021-06-08` <int>, `bar_2021-06-09` <int>, `bar_2021-06-11` <int>, `bar_2021-06-01` <int>,
#   `bar_2021-06-03` <int>, `bar_2021-06-04` <int>, `bar_2021-06-05` <int>, `bar_2021-06-10` <int>,
#   `bar_2021-06-12` <int>, `bar_2021-06-13` <int>, `bar_2021-06-14` <int>, `bar_2021-06-15` <int>,
#   `bar_2021-06-16` <int>, `bar_2021-06-17` <int>

Maybe pivot_wider() is overkill for my needs here, I 'feel' like I need a more simple operation.

How can I transpose exdata so that I have dates along the top as columns and then a row for each of the metrics?

Upvotes: 0

Views: 38

Answers (1)

csgroen
csgroen

Reputation: 2551

Not really a tidy solution, but the data that you want is also not tidy (at least in this example):

library(tidyverse)
exdata %>%
    column_to_rownames("date") %>%
    t() %>%
    as.data.frame()
#>      2021-06-02 2021-06-06 2021-06-07 2021-06-08 2021-06-09 2021-06-11
#> foo           1          1          3          1          1          1
#> bar        2986       2443       3601       3184       3164       3230
#> blah         21         36         44         32         28         13
#>      2021-06-01 2021-06-03 2021-06-04 2021-06-05 2021-06-10 2021-06-12
#> foo           0          0          0          0          0          0
#> bar        3343       3313       2631       2054       3386       2686
#> blah         31         12         27         24         22         23
#>      2021-06-13 2021-06-14 2021-06-15 2021-06-16 2021-06-17
#> foo           0          0          0          0          0
#> bar        3127       4072       4057       3723       3912
#> blah         25         17         24         24         18

Upvotes: 1

Related Questions