Reshape by creating a new variable

Question

How do i transform the data by adding another variable called month to the below dataset

ID   Com    Lac   Nov(PM)    Nov(TM)  Dec(PM) Dec(TM)
1     A      E      12        11        28       29
2     B      F      2         13        26       44
3     C      G      7         15        18       37
4     D      H      4         10        22       22

Above is the data frame that needs to be transformed like below

ID   Com    Lac   Month    PM     TM
1    A       E     Nov      12    11
1    A       E     Dec      28    29
2    B       F     Nov      2     13
2    B       F     Dec      26    44
3    C       G     Nov      7     15
3    C       G     Dec      18    37
4    D       H     Nov      4     10
4    D       H     Dec      22    22

Data:

df1 <- structure(list(ID = 1:4, Com = c("A", "B", "C", "D"), Lac = c("E", 
 "F", "G", "H"), `Nov(PM)` = c(12L, 2L, 7L, 4L), `Nov(TM)` = c(11L, 
13L, 15L, 10L), `Dec(PM)` = c(28L, 26L, 18L, 22L), `Dec(TM)` = c(29L, 
44L, 37L, 22L)), class = "data.frame", row.names = c(NA, -4L))

Can you use dput() to provide a reporducible example? https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — william3031, Nov 16 '18 at 03:37

score 3 · Answer 1 · answered Nov 16 '18 at 03:38

Here is one option with tidyverse. We gather the columns 'Nov(PM)to 'Dec(TM) in to 'long' format, then using extract, split the 'key' column into 'Month' and another column, and finally spread it to 'wide' format

df1 %>%
   gather(key, val, `Nov(PM)`:`Dec(TM)`) %>% 
   extract(key, into = c('Month', 'key'), '^([^)]+)\\(([^)]+).*') %>% 
   spread(key, val)
#  ID Com Lac Month PM TM
#1  1   A   E   Dec 28 29
#2  1   A   E   Nov 12 11
#3  2   B   F   Dec 26 44
#4  2   B   F   Nov  2 13
#5  3   C   G   Dec 18 37
#6  3   C   G   Nov  7 15
#7  4   D   H   Dec 22 22
#8  4   D   H   Nov  4 10

data

df1 <- structure(list(ID = 1:4, Com = c("A", "B", "C", "D"), Lac = c("E", 
 "F", "G", "H"), `Nov(PM)` = c(12L, 2L, 7L, 4L), `Nov(TM)` = c(11L, 
13L, 15L, 10L), `Dec(PM)` = c(28L, 26L, 18L, 22L), `Dec(TM)` = c(29L, 
44L, 37L, 22L)), class = "data.frame", row.names = c(NA, -4L))

thelatemail · Answer 2 · 2018-11-16T04:02:23.800

The most evil and despised of all functions, reshape, handles this pretty well. So long as you spin the names around from "Time(Variable)" to "Variable_Time" it will process it all in one go:

names(df1) <- gsub("(.+?)\\((.+?)\\)", "\\2_\\1", names(df1))
reshape(df1, idvar=c("ID","Com","Lac"), varying=4:7,
        direction="long", sep="_", timevar="Month")

#          ID Com Lac Month PM TM
#1.A.E.Nov  1   A   E   Nov 12 11
#2.B.F.Nov  2   B   F   Nov  2 13
#3.C.G.Nov  3   C   G   Nov  7 15
#4.D.H.Nov  4   D   H   Nov  4 10
#1.A.E.Dec  1   A   E   Dec 28 29
#2.B.F.Dec  2   B   F   Dec 26 44
#3.C.G.Dec  3   C   G   Dec 18 37
#4.D.H.Dec  4   D   H   Dec 22 22

@akrun - i admit it is a bit tricky, but it is very flexible too! It doesn't have a lot of fans among R users so I like to throw in a tongue-in-cheek nod to its poor reputation. — thelatemail, Nov 16 '18 at 04:00

Reshape by creating a new variable

2 Answers2

data