melting two unequal data frame into one in R

Question

I have two excel files like this that i import as data frame

x   title1   title2                 x  title3
1    x          y                   1    j
2    a          b                   2    m
3    i          j                   3    y
4    m          n

i want to melt these data frames into one like this

1   title1  x           
2   title1  a           
3   title1  i           
4   title1  m
1   title2  y
2   title2  b
3   title2  j
4   title2  n
1   title3  j
2   title3  m
3   title3  y

i should draw a plot of the final data frame using ggplot, i know how to work with ggplot but im a little confused how to melt two unequal data frames into one i appreciate any help

Can't you melt each seperately and then rbind the melted dfs? — erc, Sep 19 '14 at 08:56
@beetroot, `melt` also works on `list`s, so they can just create a `list` of the `data.frame`s and `melt` that. — A5C1D2H2I1M1N2O1R2T1, Sep 19 '14 at 11:08

jazzurro · Answer 1 · 2014-09-19T13:28:47.577

1

One approach here.

library(reshape2)
library(dplyr) 

id <- 1:4
t1 <- c("x","a","i","m")
t2 <- c("y", "b", "j", "n")
foo <- data.frame(id, t1, t2, stringsAsFactors = FALSE)

id <- 1:3
t3 <- c("j","m","y")
foo2 <- data.frame(id, t3, stringsAsFactors = FALSE)

foo %>%
    merge(., foo2, by = "id", all = TRUE) %>%
    melt(., id.vars = "id") %>%
    filter(!value %in% NA)

   id variable value
1   1       t1     x
2   2       t1     a
3   3       t1     i
4   4       t1     m
5   1       t2     y
6   2       t2     b
7   3       t2     j
8   4       t2     n
9   1       t3     j
10  2       t3     m
11  3       t3     y

edited Sep 19 '14 at 13:28

answered Sep 19 '14 at 09:14

jazzurro

23,179
35
66
76

+1, Perhaps you could use `left_join`, `gather` etc unique to `dplyr/tidyr` – akrun Sep 19 '14 at 09:22
thanks for reply, but my Rstudio gives the error, Error: could not find function "%>%", does it need some other package to be imported?! – Masih Sep 19 '14 at 09:33
@akrun thanks. The functions were in my mind. Since I posted things related to `left_join` the other day, I thought it would be good to choose `merge` to avoid potential errors. But, it would be good to use `dplyr/tidyr` functions here too. – jazzurro Sep 19 '14 at 10:12
@user3015703 You need to install `reshape2`, `dplyr`. %>% is an operator you need. Once you have `dplyr` uploaded, you will be fine. – jazzurro Sep 19 '14 at 10:13

score 1 · Answer 2 · answered Sep 19 '14 at 11:07

Put your data.frames into a list and melt them. Using @jazzurro's sample data, try:

melt(list(foo, foo2), id.vars = "id")
#    id variable value L1
# 1   1       t1     x  1
# 2   2       t1     a  1
# 3   3       t1     i  1
# 4   4       t1     m  1
# 5   1       t2     y  1
# 6   2       t2     b  1
# 7   3       t2     j  1
# 8   4       t2     n  1
# 9   1       t3     j  2
# 10  2       t3     m  2
# 11  3       t3     y  2

Even cooler, because it gives you the original data.frame names in another column, is to use mget in combination with ls with the above approach:

melt(mget(ls(pattern = "foo")), id.vars = "id")
#    id variable value   L1
# 1   1       t1     x  foo
# 2   2       t1     a  foo
# 3   3       t1     i  foo
# 4   4       t1     m  foo
# 5   1       t2     y  foo
# 6   2       t2     b  foo
# 7   3       t2     j  foo
# 8   4       t2     n  foo
# 9   1       t3     j foo2
# 10  2       t3     m foo2
# 11  3       t3     y foo2

melting two unequal data frame into one in R

2 Answers2