I have a R df with the current structure, where there are +200 bins:
| GBO | Group | Index | bin1 | bin2 | … |
|---|---|---|---|---|---|
| loc | 1 | DP | HoS | HeS | … |
| loc | 1 | DPI | 0.1 | 0.02 | … |
| loc | 1 | DPP | 0.3 | 0.1 | … |
| loc | 2 | DP | DR | HoS | … |
| loc | 2 | DPI | 0.0 | 0.2 | … |
| loc | 2 | DPP | 0.2 | 0.01 | … |
And I’m trying really hard to re-structure the df to look like this:
| GBO | Group | Bin | DP | DPI | DPP |
|---|---|---|---|---|---|
| loc | 1 | bin1 | HoS | 0.1 | 0.3 |
| loc | 1 | bin2 | HeS | 0.02 | 0.1 |
| loc | 2 | bin1 | DR | 0.0 | 0.2 |
| loc | 2 | bin2 | HoS | 0.2 | 0.01 |
My brain is melting and I’d really like some help!
>Solution :
You can try dcast + melt from package data.table
library(data.table)
dcast(
melt(setDT(df), id = c("GBO", "Group", "Index"), variable.name = "Bin"),
GBO + Group + Bin ~ Index
)
which gives
GBO Group Bin DP DPI DPP
1: loc 1 bin1 HoS 0.1 0.3
2: loc 1 bin2 HeS 0.02 0.1
3: loc 2 bin1 DR 0.0 0.2
4: loc 2 bin2 HoS 0.2 0.01
Or, you can use recast from package reshape2
> library(reshape2)
> recast(df, GBO + Group + variable ~ Index, id.var = c("GBO", "Group", "Index"))
GBO Group variable DP DPI DPP
1 loc 1 bin1 HoS 0.1 0.3
2 loc 1 bin2 HeS 0.02 0.1
3 loc 2 bin1 DR 0.0 0.2
4 loc 2 bin2 HoS 0.2 0.01
data
df <- structure(list(GBO = c("loc", "loc", "loc", "loc", "loc", "loc"
), Group = c(1L, 1L, 1L, 2L, 2L, 2L), Index = c("DP", "DPI",
"DPP", "DP", "DPI", "DPP"), bin1 = c("HoS", "0.1", "0.3", "DR",
"0.0", "0.2"), bin2 = c("HeS", "0.02", "0.1", "HoS", "0.2", "0.01"
)), class = "data.frame", row.names = c(NA, -6L))