Reshape table in tidyverse, problems with pivot_wider

user_5436714 0
Viewed 45

I have a table looking like:

library(tidyverse)

df_initial <- tibble::tribble(
        ~year_month, ~n_unique, ~new_n, ~old_n,
          "2021-01",       168,    159,      9,
          "2021-02",       249,    244,      5,
          "2021-03",       197,    186,     11
        )

aka:

# A tibble: 3 × 4
  year_month n_unique new_n old_n
  <chr>         <dbl> <dbl> <dbl>
1 2021-01         168   159     9
2 2021-02         249   244     5
3 2021-03         197   186    11

And I would like to reshape column and rows. I try via pivot_wider but I don't have any luck - especially because it doesn't find the right character view.

df_reshaped <- tibble::tribble(
                 ~year_month, ~`2021-01`, ~`2021-02`, ~`2021-03`,
                  "n_unique",      168,      249,      197,
                     "new_n",      159,      244,      186,
                     "old_n",        9,        5,       11
                 )

Finally looking like this:

# A tibble: 3 × 4
  year_month `2021-01` `2021-02` `2021-03`
  <chr>          <dbl>     <dbl>     <dbl>
1 n_unique         168       249       197
2 new_n            159       244       186
3 old_n              9         5        11

I am quite confused in how to use the function and any help would be greatly appreciated!

dplyr tidyverse r
2 Answers

Swapping rows and columns can be archived with the transpose function:

t(df_initial)

This is a more or less equivalent to pivoting everythong longer and than wider again with swapped columns:

df_initial %>%
  mutate(across(everything(), as.character)) %>% 
  pivot_longer(-year_month) %>%
  pivot_wider(names_from = year_month, values_from = value) %>%
  type_convert()


# A tibble: 3 × 4
  name     `2021-01` `2021-02` `2021-03`
  <chr>        <dbl>     <dbl>     <dbl>
1 n_unique       168       249       197
2 new_n          159       244       186
3 old_n            9         5        11
user_168531140

You can use some tibble functions:

df_initial %>%
  column_to_rownames("year_month") %>%
  t() %>%
  as_tibble(rownames = "year_month")

# # A tibble: 3 x 4
#   year_month `2021-01` `2021-02` `2021-03`
#   <chr>          <dbl>     <dbl>     <dbl>
# 1 n_unique         168       249       197
# 2 new_n            159       244       186
# 3 old_n              9         5        11

Its base equivalent:

setNames(data.frame(t(df_initial[-1])), df_initial[[1]])
user_100689850
Related