How to transform and include a dataframe in another dataframe

Question

I have another tricky task that I cannot master at the moment. It deals with dataframes in R.

Say I have a data frame looking like:

original = data.frame(Male = c(rep(1,3),rep(2,4),rep(3,2)),
                  SongNumber = c(1,2,3,1,2,3,4,1,2),
                  SongType = c("16a","16b","17a","24a","24b","25d","24f","5e","5e"),
                  Start = c(0.5,16.1,24.2,0.9,10.1,18.9,0.7,0.6,12.2),
                  RecordFile = c(rep("A1",3),rep("B1",3),"B2",rep("C1",2)))
original

and another data frame containing the particular syllable order of each song type:

additional = data.frame(SongType = c("16a","16b","17a","24a"),
                    Syll1 = c(4,4,3,16),
                    Syll2 = c(4,4,3,16),
                    Syll3 = c(84,84,3,3),
                    Syll4 = c(3,3,3,16),
                    Syll5 = c(16,16,3,3),
                    Syll6 = c(16,16,NA,4),
                    Syll7 = c(NA,16,NA,NA),
                    Syll8 = c(NA,16,NA,NA),
                    Syll9 = c(NA,3,NA,NA),
                    Syll10 = c(NA,1,NA,NA))
additional

what I would like is to now insert the syllable order as a column in the previous data frame. The final result should look like this:

aim = data.frame(Male = c(rep(1,21),rep(2,9),rep(3,2)),
             SongNumber = c(rep(1,6),rep(2,10),rep(3,5),rep(1,6),2,3,4,1,2),
             SongType = c(rep("16a",6),rep("16b",10),rep("17a",5),rep("24a",6),"24b","25d",
                          "24f","5e","5e"),
             Start = c(rep(0.5,6),rep(16.1,10),rep(24.2,5),rep(0.9,6),10.1,18.9,0.7,0.6,
                       12.2),
             RecordFile = c(rep("A1",21),rep("B1",8),"B2",rep("C1",2)),
             SyllOrder = c(4,4,84,3,16,16,4,4,84,3,16,16,16,16,3,1,3,3,3,3,3,16,16,3,16,3,4,
                           NA,NA,NA,NA,NA))
aim

So far, I do not see how functions such as merge can help: merge only add columns of dataframe2 to dataframe1 based on a common column between two data frames. It does not force dataframe1 to add rows accordingly!

well, the answer provided by @Thierry that first transform `additional` into a long format could not be found in the question [How to join (merge) data frames (inner, outer, left, right)?] — KrisAnathema, Oct 21 '15 at 12:03

Jaap · Accepted Answer · 2015-10-21T12:27:03.967

To get to the desired output, you can do:

library(data.table)
additional2 <- melt(setDT(additional), id="SongType", na.rm=TRUE)[, .(SyllOrder = toString(value)), by = SongType]

aim2 <- setDT(original)[additional2, SyllOrder := i.SyllOrder, on="SongType"
                        ][, lapply(.SD, function(x) unlist(tstrsplit(x, ",", fixed=TRUE))),
                          by=setdiff(names(original),"SyllOrder")]

As an alternative to the last step, you can also use:

aim2 <- additional2[original, on="SongType"
                    ][, lapply(.SD, function(x) unlist(tstrsplit(x, ",", fixed=TRUE))),
                      by=setdiff(names(original),"SyllOrder")]

Both result in:

> aim3
    Male SongNumber SongType Start RecordFile SyllOrder
 1:    1          1      16a   0.5         A1         4
 2:    1          1      16a   0.5         A1         4
 3:    1          1      16a   0.5         A1        84
 4:    1          1      16a   0.5         A1         3
 5:    1          1      16a   0.5         A1        16
 6:    1          1      16a   0.5         A1        16
 7:    1          2      16b  16.1         A1         4
 8:    1          2      16b  16.1         A1         4
 9:    1          2      16b  16.1         A1        84
10:    1          2      16b  16.1         A1         3
11:    1          2      16b  16.1         A1        16
12:    1          2      16b  16.1         A1        16
13:    1          2      16b  16.1         A1        16
14:    1          2      16b  16.1         A1        16
15:    1          2      16b  16.1         A1         3
16:    1          2      16b  16.1         A1         1
17:    1          3      17a  24.2         A1         3
18:    1          3      17a  24.2         A1         3
19:    1          3      17a  24.2         A1         3
20:    1          3      17a  24.2         A1         3
21:    1          3      17a  24.2         A1         3
22:    2          1      24a   0.9         B1        16
23:    2          1      24a   0.9         B1        16
24:    2          1      24a   0.9         B1         3
25:    2          1      24a   0.9         B1        16
26:    2          1      24a   0.9         B1         3
27:    2          1      24a   0.9         B1         4
28:    2          2      24b  10.1         B1        NA
29:    2          3      25d  18.9         B1        NA
30:    2          4      24f   0.7         B2        NA
31:    3          1       5e   0.6         C1        NA
32:    3          2       5e  12.2         C1        NA

Thanks! It looks clever and great, but I get an error message when I run it in R. Here is the message: "Error in `[.data.table`(setDT(original), additional2, `:=`(SyllOrder, : unused argument (on = "SongType")" — KrisAnathema, Oct 21 '15 at 12:24
Which version of `data.table` do you have? You need the latest one from CRAN (i.e. *v1.9.6*) — Jaap, Oct 21 '15 at 12:25
I had *v1.9.4*! I updated it and it works perfectly now. Thanks a lot! — KrisAnathema, Oct 21 '15 at 12:33

score 1 · Answer 2 · answered Oct 21 '15 at 09:59

1

You need to transform additional into a long format. Then you can merge them.

library(dplyr)
library(tidyr)
additional %>% 
  gather("Syllable", "SyllOrder", -SongType) %>% 
  inner_join(original, by = "SongType")

answered Oct 21 '15 at 09:59

Thierry

18,049
5
48
66

Thanks @Thierry! Perfect, exactly what I needed! – KrisAnathema Oct 21 '15 at 11:55
@KrisAnathema This is not giving the output which you specified in the `aim` dataset. – Jaap Oct 21 '15 at 11:58
Yes, it nearly does so. It just adds a column Syllable, and SyllOrder with NA. But I can remove them afterwards! – KrisAnathema Oct 21 '15 at 12:06
@KrisAnathema No, check the last 5 rows of your desired output. They are not present in the output of this answer. – Jaap Oct 21 '15 at 12:24
You are right, but with my actual dataset, this would not happen as I have syllable order for each song type! – KrisAnathema Oct 21 '15 at 12:35
Note that you can use `full_join` instead of `inner_join` if you wan to keep all rows from both dataframes – Thierry Oct 21 '15 at 13:05

How to transform and include a dataframe in another dataframe

2 Answers2