rtidyversepivot-table

Tidyverse pivot_wider specify data which is retained as rows

I have a recurring problem which I have been addressing using a messy approach of multiple passes of pivot_wider and rbinding the results together. This seems inelegant, does anyone have a single step

My data looks like this ...

a <- c("Jan 2024", "212", "21000")
b <- c("Feb 2024", 188, "22000")
c <- c("Mar 2024", "166", "19000")

df <- as.data.frame(rbind(a,b,c))
colnames(df) <- c("MthYr", "count", "MedianTAT")

starting data

What I want to achieve is this ...

desired outcome

I have tried various variations on :-

Summary <- pivot_wider(df, names_from = MthYr, values_from = c(count, MedianTAT))

which produces ...

not correct

and

Summary <- pivot_wider(df, names_from = c(MthYr,count), values_from = c(MedianTAT))

which gives this -

not correct

but never get any workable outcomes - does anyone know how to achieve this ??

PC

Solution

  • Your desired result has no column names, which can't be done in a tibble, as far as I know. Also, do you really need the row names a, b, and c or will this do?

    library(tidyverse) 
a <- c("Jan 2024", "212", "21000") 
b <- c("Feb 2024", 188, "22000") 
c <- c("Mar 2024", "166", "19000")  
df <- as.data.frame(rbind(a,b,c)) 
colnames(df) <- c("MthYr", "count", "MedianTAT") 
df 
#>      MthYr count MedianTAT 
#> a Jan 2024   212     21000 
#> b Feb 2024   188     22000 
#> c Mar 2024   166     19000 

df |> pivot_longer(cols=count:MedianTAT, names_to = "Quant") |>
    pivot_wider(names_from = MthYr, values_from = "value") 

#> # A tibble: 2 × 4 
#>   Quant     `Jan 2024` `Feb 2024` `Mar 2024` 
#>   <chr>     <chr>      <chr>      <chr>      
#> 1 count     212        188        166        
#> 2 MedianTAT 21000      22000      19000