R: Combine descriptive columns and related values into matrix form
I have a long dataset of operations (op #) and times {tm #) associated with various widgets. Unfortunately, the operations are not in a specific order, so the dyeing operation can occur in the first operation or in the 80s. Each operation has a corresponding time required to complete that operation in the column on the right. I would like to organize the data so that each column is a unique name for the operation, and the values in the column are the time it takes for the operation to complete.
# sample data frame
df = data.frame(widget = c("widget 1", "widget 2", "widget 3", "widget 4"),
op1 = c("paint", "weld", "frame", "weld"),
tm1 = c(20, 24, 14, 40),
op2 = c("weld", "coat", "weld", "paint"),
tm2 = c(10, 20, 50, 30))
print(df)
> part op1 tm1 op2 tm2
> 1 widget1 paint 20 weld 10
> 2 widget2 weld 24 coat 20
> 3 widget3 frame 14 weld 50
> 4 widget4 weld 40 paint 30
I am trying to refactor a dataframe as ...
> part paint weld coat frame
> 1 widget1 20 10 NULL NULL
> 2 widget2 NULL 24 20 NULL
> 3 widget3 NULL 50 NULL 14
> 4 widget4 30 40 NULL NULL
Any suggestions?
+3
source to share
1 answer
Try:
If `df1` is the dataset
names(df1)[grep("^op|^tm",names(df1))] <- gsub("([[:alpha:]]+)(\\d+)", "\\1_\\2", names(df1)[grep("^op|^tm", names(df1))])
df2 <- reshape(df1, idvar="widget", varying= grep("^op|^tm",names(df1)), sep="_", direction="long")
library(reshape2)
dcast(df2, widget~op, value.var="tm")[,c(1,3:5,2)]
# widget paint weld coat frame
#1 widget 1 20 10 NA NA
#2 widget 2 NA 24 20 NA
#3 widget 3 NA 50 NA 14 ##looks like you have 50 instead of 60 as shown in the expected
#4 widget 4 30 40 NA NA
- I used a combination of
grep
andgsub
to change the names of these columns (tm
,op
) so that there is a separation_
between the common characters and the corresponding numbers, making it easier to work withreshape
- After converting to a longer format, reformat it back to another format with
dcast
+2
source to share