r - Pivot_longer to manipulate table

Question

I would like to pivot variables nclaims, npatients, nproviders to show up underneath groups.

I believe I should be using pivot_longer but it doesn't work.

library(tidyr)

ptype <- c(0,1,2,0,1)
groups <- c(rep(1,3), rep(2,2))
nclaims <- c(10,23,32,12,8)
nproviders <- c(2,4,5,1,1)
npatients <- c(8, 20, 29, 9, 6)


dta <- data.frame(ptype=ptype, groups=groups, nclaims=nclaims, nproviders=nproviders, npatients=npatients)

table <- pivot_longer(everything(dta), names_to = "groups", values_to=c("nclaims", "npatients", "nproviders"))
      

            
                      

Desired output:

question from:https://stackoverflow.com/questions/65939521/pivot-longer-to-manipulate-table

Answer 1

We need to use pivot_longer, then pivot_wider:

dta %>%
    pivot_longer(nclaims:npatients) %>%
    # values_fill = 0 changes NA values to 0, as in your desired result
    pivot_wider(names_from = ptype, values_from = value,
                values_fill = 0)

  groups name         `0`   `1`   `2`
   <dbl> <chr>      <dbl> <dbl> <dbl>
1      1 nclaims       10    23    32
2      1 nproviders     2     4     5
3      1 npatients      8    20    29
4      2 nclaims       12     8     0
5      2 nproviders     1     1     0
6      2 npatients      9     6     0