Using tidyr to combine multiple columns

Question

The data I am working at looks like this:

Cust no. Chg1 Chg2 Chg3 Chg4 Ctot1 Ctot2 Ctot3 Ctot4 Size Name
61        A    B     C   D     2     4     5    6     2    XA

The desired output is:

Cust no. Chg Ctot Size Name
61       A     2   2    XA
61       B     3   2    XA
61       C     4   2    XA
61       D     5   2    XA

Using gather(key,value,Ctot1:Ctot4), the output is

Cust no. Chg   Key  Value Size Name
61       A     Ctot1   2   2   XA
61       A     Ctot2   3   2   XA
61       A     Ctot3   4   2   XA
61       A     Ctot4   5   2   XA

Some advise on this will be really helpful.

Answer 1

We can use melt from data.table for this purpose as it can take multiple measure patterns

library(data.table)
melt(setDT(df1), measure = patterns("^Chg", "^Ctot"),
          value.name = c("Chg", "Ctot"))[, variable := NULL][]
#   Custno. Size Name Chg Ctot
#1:      61    2   XA   A    2
#2:      61    2   XA   B    4
#3:      61    2   XA   C    5
#4:      61    2   XA   D    6