How to transform my data frame to make rows columns?

Question

I have a data frame with two columns, "Type" and "Stats". I want to each type to have one row with all of the stats in a separate column. For example, my data frame looks something like this:

Column Type has values: A A A A B B B B
Column Stats has values:15 2 73 12 12 6 52 17  

And I want it to look like:

Column Type has values: A B
Column Stat1 has values: 15 12
Column Stat2 has values: 2 6
Column Stat3 has values: 73 52
Column Stat4 has values: 12 17

Not all types have the same number of stats, some types are missing a stat value and others have extra. I tried using t(), but ran into issues. I then tried to combine all the values of Stat into one column and separate with gsub() and csplit(), but I had issues combining all the Stat values for each type into one column. Any advice?

Answer 1

We can use pivot_wider after creating a sequence column grouped by 'Type'

library(dplyr)
library(tidyr)
df1 %>%
   group_by(Type) %>%
   mutate(rn = str_c('Stats_', row_number())) %>%
   ungroup %>%
   pivot_wider(names_from = rn, values_from = Stats)
# A tibble: 2 x 5
#  Type  Stats_1 Stats_2 Stats_3 Stats_4
#  <fct>   <dbl>   <dbl>   <dbl>   <dbl>
#1 A          15       2      73      12
#2 B          12       6      52      17

---

Or using dcast from data.table

library(data.table)
dcast(setDT(df1), Type ~ paste0("Stats_", rowid(Type)),  value.var = 'Stats')

---

Or as @Onyambu suggested in base R, it can be done with reshape

reshape(transform(df1, time = ave(Stats, Type,
           FUN = seq_along)), dir="wide", idvar = "Type", sep = "_")

data

df1 <- data.frame(Type = rep(c("A", "B"), each = 4), 
         Stats = c(15, 2, 73, 12, 12, 6, 52, 17))