CODEWITHSUNDEEP
rdataframemelt
Masih
Masih

Reputation: 980

melting two unequal data frame into one in R

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

Upvotes: 0

Views: 249

Answers (2)

jazzurro
jazzurro

Reputation: 23574

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

Upvotes: 1

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193527

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

Upvotes: 1

Related Questions