long to wide table on multiple columns in r

Question

I'm having trouble in transform my table from long to wide. Table like :

Con Bro  Gr       N     Freq
 Jp  CF  ii     578     54.3
 Jp  CF  ir     423     45.7
 Jp  CY  im    1835     69.4
 Jp  CY  nm     808     30.6
 Am  CF  ii     160     60.1
 Am  CF  ir     106     39.9
 Am  CY  im     730     20.2
 Am  CY  nm    2901     79.8

Desired output

Bro  Gr    Jp_N  Jp_Freq    Am_N  Am_Freq
 CF  ii     578     54.3     160     60.1
 CF  ir     423     45.7     106     39.9
 CY  im    1835     69.4     730     20.2
 CY  nm     808     30.6    2901     79.8

Here's what I've tried.

dcast(setDT(dt), Con ~ Gr, value.var = c("N", "Frequency"))

But got Aggregate function missing, defaulting to 'length', Any help would be greatly appreciated! Thank you!

Answer 1

Using pivot_wider(),

tidyr::pivot_wider(dt, id_cols = c(Bro, Gr), names_from = Con, values_from = c(N, Freq))

Note, the column name order is the other way around.

# A tibble: 4 × 6
  Bro   Gr     N_Jp  N_Am Freq_Jp Freq_Am
  <chr> <chr> <int> <int>   <dbl>   <dbl>
1 CF    ii      578   160    54.3    60.1
2 CF    ir      423   106    45.7    39.9
3 CY    im     1835   730    69.4    20.2
4 CY    nm      808  2901    30.6    79.8