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!

score 4 · Accepted Answer · answered May 22 '23 at 08:25

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

score 2 · Answer 2 · answered May 22 '23 at 08:19

Two places to correct:

The right-hand side of the formula should be Con, rather than Gr
"Freq" instead of "Frequency" should be used in value.var argument

> dcast(setDT(dt), Bro + Gr ~ Con, value.var = c("N", "Freq"))
   Bro Gr N_Am N_Jp Freq_Am Freq_Jp
1:  CF ii  160  578    60.1    54.3
2:  CF ir  106  423    39.9    45.7
3:  CY im  730 1835    20.2    69.4
4:  CY nm 2901  808    79.8    30.6

long to wide table on multiple columns in r

2 Answers2