1

Is there any function in R which can reshape the data in stacked format by merging two dataframes?

Table1

Vegetables|Onion|Potato|Tomato|
Cheap     |  20 |  30  |  40  |

Table2

Vegetables|Cabbage|Carrot|Cauliflower|Eggplant
Mid       |  20   |  30  |     40    |   30

Required Output

   Vegetables |  Type     |Quantity|
    Cheap     |  Onion    |   20   |
    Cheap     |  Potato   |   30   |
    Cheap     |  Tomato   |   40   |
    Mid       |  Cabbage  |   20   |
    Mid       |  Carrot   |   30   |
    Mid       |Cauliflower|   40   |
    Mid       |  Eggplant |   30   |

Thanks in advance for your help! you are awesome.

ayush varshney
  • 517
  • 7
  • 20
  • You're not really merging here. You are really just combining them or concatenating them. Look into "melt"-ing the data from to turn your columns into rows. – MrFlick Mar 23 '17 at 19:08
  • yup, i've made few changes. i tried using melt too. can you please give me an example like how to achieve it. – ayush varshney Mar 23 '17 at 19:11
  • It would be better if you showed what you attempted and describe how it failed. It's easier to help if you provide data in a [reproducible format](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) to make it easier to copy/paste into R would make it easier to help. – MrFlick Mar 23 '17 at 19:17
  • Thanks for the suggestion, i've tried melt(Table1, id = "Vegetables") which serve the purpose however i've arround 100 data frame like these in my R environment ls(). Can you please let me know how can i apply the same for all the dataframe and combine them in one table. – ayush varshney Mar 23 '17 at 19:24
  • varlist <- ls() # "Table_1" "Table_2" for (i in varlist){ varlist <- melt(varlist, id = "Vegetables") } # Error: id variables not found in data: Vegetables – ayush varshney Mar 23 '17 at 19:30

1 Answers1

1

The name of the parameter you're trying to use is id.vars instead of id. Try melt(Table1,id.vars="Vegetable")

library(reshape2)
df1 <- data.frame(Vegetables="Cheap", Onion=20,Potato=30,Tomato=40)
df2 <- data.frame(Vegetables="Mid", Cabbage=20, Carrot=30,Cauliflower=40, Eggplant=30)
do.call("rbind", lapply(list(df1,df2),function(d){melt(d,id.vars="Vegetables", variable.name="Type", value.name="Quantity")}))

You can then insert all your tables into a list and simply use lapply. (The do.call then combines melted datasets into one)

Frank
  • 98
  • 5