Long to wide in multiple columns on data.table

Question

I have a question about data.table meltand dcastfor multiple columns. I looked at StackOverFlow, but many of these posts are not what I want. I will explain below.

Firstly, it dataconcerns the causes of problems and the sum of the values. Here is my part data:

ID   Type    Problem1    Value1     Problem2    Value2    Problem3    Value3
1    A       X           500        Y           1000      Z           400
2    A       X           600        Z           700       
3    B       Y           700        Z           100
4    B       W           200        V           200
5    C       Z           500        V           500       
6    C       X           1000       W           100       V           900

Secondly, IDis unique. Typecontains three ( A, Band C). There are problems 5 .

Take ID == 1as an example. This Type Acontains 3 tasks ( X, Yand Z). He Problem Xhas Value 500, Problem Yhas Value 1000, Problem Zhas Value 400. Take ID == 5as an example. It Type Ccontains two tasks ( Zand V). He Problem Zhas Value 500and Problem Vhas Value 500.

Third, column ID, Type, Problem1, Problem2and Problem3are character. Value1,, Value2and Value3- numeric.

As a result, I want:

Type    X     Y     Z     W     V
A       1100  1000  1100  0     0   
B       0     700   100   200   200
C       1000  0     500   100   1400  

, . Type, vlaue. , . . . , . ?

# data
dt <- fread("
ID   Type    Problem1    Value1     Problem2    Value2    Problem3    Value3
1    A       X           500        Y           1000      Z           400
2    A       X           600        Z           700       
3    B       Y           700        Z           100
4    B       W           200        V           200
5    C       Z           500        V           500       
6    C       X           1000       W           100       V           900", fill = T)    

Answer 1

melt, patterns measure 'long', dcast fun.aggregate sum

dcast(melt(dt, measure = patterns("^Value", "^Problem"), 
    value.name = c("Value", "Problem"))[Problem != ""
     ][, Problem := factor(Problem, levels = c("X", "Y", "Z", "W", "V"))], 
     Type ~Problem, value.var = "Value", sum, na.rm = TRUE)
#   Type    X    Y    Z   W    V
#1:    A 1100 1000 1100   0    0
#2:    B    0  700  100 200  200
#3:    C 1000    0  500 100 1400

melt from data.table patterns measure. , "^Value", , (^) "" "" "". "Value" "Problem" value.name. , , Problem != "". , . , "" factor levels . melt . "" dcast, , value.var fun.aggregate ( sum)

Answer 2

, (, - ).

library(magrittr)
rbind(
    dt[, .(Type, P = Problem1, V = Value1)],
    dt[, .(Type, P = Problem2, V = Value2)],
    dt[, .(Type, P = Problem3, V = Value3)]) %>%
    .[P != ""] %>%
    dcast(Type ~ P, value.var = "V", sum)

akrun ( dcast).

Answer 3

dplyr/tidyr:

library("dplyr")
library("tidyr")

# assume x is your dataframe
bind_rows(
  select(x, ID, Type, Problem = Problem1, Value = Value1),
  select(x, ID, Type, Problem = Problem2, Value = Value2),
  select(x, ID, Type, Problem = Problem3, Value = Value3)
  ) %>%
filter(!(is.na(Problem))) %>%
group_by(Type, Problem) %>%
summarise(Value = sum(Value)) %>%
spread(Problem, Value, fill = 0)

# A tibble: 3 x 6
# Groups:   Type [3]
   Type     V     W     X     Y     Z
* <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1     A     0     0  1100  1000  1100
2     B   200   200     0   700   100
3     C  1400   100  1000     0   500

V - Z , , select.

Answer 4

, melt, akrun, .

# melt and aggregate the data
temp <- melt(dt, measure = patterns("^Value", "^Problem"),
             value.name = c("Value", "Problem"))[
        !is.na(Value), .(Value=sum(Value)), by=.(Type, Problem)]

# set up the storage matrix
dimNames <- list(sort(unique(temp$Type)), unique(temp$Problem))
myMat <- matrix(0, length(dimNames[[1]]), length(dimNames[[2]]), dimnames=dimNames)

# fill in the matrix with the desired values
myMat[cbind(temp$Type, temp$Problem)] <- temp$Value

myMat
     X    Y   W    Z    V
A 1100 1000   0 1100    0
B    0  700 200  100  200
C 1000    0 100  500 1400

,

data.table(myMat, keep.rownames=TRUE)
   rn    X    Y   W    Z    V
1:  A 1100 1000   0 1100    0
2:  B    0  700 200  100  200
3:  C 1000    0 100  500 1400