[R] merge multiple data frames

Mon Jan 30 16:47:57 CET 2012

Does this example help? It doesn't handle the problem of common field
names, but see below for another example.

df1 <- data.frame(jn=1:4, a1=letters[1:4], a2=LETTERS[1:4])
df2 <- data.frame(jn=2:6, b1=month.abb[2:6])
df3 <- data.frame(jn=3:7, x=rnorm(5), y=13:17)

dfn <- sqldf('select * from df1 left join df2 using (jn) left join df3
using (jn)')

In this example, you automatically get all fields from all three data
frames, without having to name them in the SQL statement -- but you should
not have common names.

To deal with common names, I myself would probably rename the variables in
the data frames before trying to merge.

A general method would be something like:
  nms1 <- names(df1)
  nms1[nms1 != 'date'] <- paste(nms1[nms1 != 'date'],'.1',sep='')
  names(df1) <- nms1
Of course it has to be done for every data frame, but this can be put in a
loop, if necessary.

However, here is an example where I have changed df1 and df2; they both
have a field named 'aa', in addition to the matching field.

df1 <- data.frame(jn=1:4, aa=letters[1:4], a2=LETTERS[1:4])
df2 <- data.frame(jn=2:6, aa=month.abb[2:6])
df3 <- data.frame(jn=3:7, x=rnorm(5), y=13:17)

dfn <- sqldf('select jn, df1.aa aa1, df2.aa aa2,
              a2, x, y
   from df1 left join df2 using (jn) left join df3 using (jn)')

By the way, you can still select *, even with common names:

  dfx <- sqldf('select *   from df1 left join df2 using (jn) left join df3
using (jn)')but you might not like the result. Try it and see!

It's my understanding that in the current SQL definition 'as' is no longer
required when changing field names (though it is also still allowed in the
databases I work with, Oracle and MySQL). Perhaps sqldf does not allow it.
I don't know.

Hope this helps.

-Don

-- 
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062

On 1/30/12 4:40 AM, "Massimo Bressan" <mbressan at arpa.veneto.it> wrote:

>hi don
>
>I followed your advice about using sqldf package but the problem of
>labelling the fields persists;
>for some reasons I can not properly handle the sql 'as' statement....
>
>a_b<-sqldf("select a.*, b.* from a left join b on a.date=b.date")
>a_b_c<-sqldf("select a_b.*, c.* from a_b left join c on a_b.date=c.date")
>
>bye
>
>max
>
>
>
>
>
>----- Original Message -----
>From: "MacQueen, Don" <macqueen1 at llnl.gov>
>To: "maxbre" <mbressan at arpa.veneto.it>; <r-help at r-project.org>
>Sent: Saturday, January 28, 2012 12:24 AM
>Subject: Re: [R] merge multiple data frames
>
>
>Not tested, but this might be a case for the sqldf package.
>
>-Don
>
>-- 
>Don MacQueen
>
>Lawrence Livermore National Laboratory
>7000 East Ave., L-627
>Livermore, CA 94550
>925-423-1062
>
>
>
>
>
>On 1/26/12 9:29 AM, "maxbre" <mbressan at arpa.veneto.it> wrote:
>
>>This is my reproducible example (three data frames: a, b, c)
>>
>>a<-structure(list(date = structure(1:6, .Label = c("2012-01-03",
>>"2012-01-04", "2012-01-05", "2012-01-06", "2012-01-07", "2012-01-08",
>>"2012-01-09", "2012-01-10", "2012-01-11", "2012-01-12", "2012-01-13",
>>"2012-01-14", "2012-01-15", "2012-01-16", "2012-01-17", "2012-01-18",
>>"2012-01-19", "2012-01-20", "2012-01-21", "2012-01-22", "2012-01-23"
>>), class = "factor"), so2 = c(0.799401398190476, 0, 0,
>>0.0100453950434783,
>>0.200154920565217, 0.473866969181818), nox = c(111.716109973913,
>>178.077239330435, 191.257829021739, 50.6799951473913, 115.284643540435,
>>110.425185027727), no = c(48.8543691516522, 88.7197448817391,
>>93.9931932472609, 13.9759949817391, 43.1395266865217, 41.7280296016364
>>), no2 = c(36.8673432865217, 42.37150668, 47.53311701, 29.3026882474783,
>>49.2986070321739, 46.5978461731818), co = c(0.618856168125,
>>0.996593475083333,
>>0.666987416083333, 0.383437311166667, 0.281604928875, 0.155383408913043
>>), o3 = c(12.1393100029167, 12.3522739816522, 10.9908791203043,
>>26.9122200013043, 13.8421695947826, 12.3788847045455), ipa =
>>c(167.541954974667,
>>252.7196257875, 231.802370709167, 83.4850259595833, 174.394613581667,
>>173.868599272609), ws = c(1.47191016429167, 0.765781205208333,
>>0.937053086791667, 1.581022406625, 0.909756802125, 0.959252831695652
>>), wd = c(45.2650019737732, 28.2493544114369, 171.049080544214,
>>319.753674830936, 33.8713897347193, 228.368119533759), temp =
>>c(7.91972825883333,
>>3.79434291520833, 2.1287644735, 6.733854600625, 3.136579722,
>>3.09864120704348), umr = c(86.11566638875, 94.5034087491667,
>>94.14451249375, 53.1016709004167, 65.63420423, 74.955669236087
>>)), .Names = c("date", "so2", "nox", "no", "no2", "co", "o3",
>>"ipa", "ws", "wd", "temp", "umr"), row.names = c(NA, 6L), class =
>>"data.frame")
>>
>>
>>b<-structure(list(date = structure(1:6, .Label = c("2012-01-03",
>>"2012-01-04", "2012-01-05", "2012-01-06", "2012-01-07", "2012-01-08",
>>"2012-01-09", "2012-01-10", "2012-01-11", "2012-01-12", "2012-01-13",
>>"2012-01-14", "2012-01-15", "2012-01-16", "2012-01-17", "2012-01-18",
>>"2012-01-19", "2012-01-20", "2012-01-21", "2012-01-22", "2012-01-23"
>>), class = "factor"), so2 = c(0, 0, 0, 0, 0, 0), nox = c(13.74758511,
>>105.8060582, 61.22720599, 11.45280354, 56.86804174, 39.17917222
>>), no = c(0.882593766, 48.97037506, 9.732937217, 1.794549972,
>>16.32300019, 8.883637786), no2 = c(11.80447753, 25.35235381,
>>28.72990261, 8.590004034, 31.9003796, 25.50512403), co = c(0.113954917,
>>0.305985964, 0.064001839, 0, 1.86e-05, 0), o3 = c(5.570499897,
>>9.802379608, 5.729360104, 11.91304016, 12.13407993, 10.00961971
>>), ipa = c(6.065110207, 116.9079971, 93.21240234, 10.5777998,
>>66.40740204, 34.47359848), ws = c(0.122115001, 0.367668003, 0.494913995,
>>0.627124012, 0.473895013, 0.593913019), wd = c(238.485119317031,
>>221.645073036776, 220.372076815032, 237.868340917096, 209.532933617465,
>>215.752030286564), temp = c(4.044159889, 1.176810026, 0.142934993,
>>0.184606999, -0.935989976, -2.015399933), umr = c(72.29229736,
>>88.69879913, 87.49530029, 24.00079918, 44.8852005, 49.47729874
>>)), .Names = c("date", "so2", "nox", "no", "no2", "co", "o3",
>>"ipa", "ws", "wd", "temp", "umr"), row.names = c(NA, 6L), class =
>>"data.frame")
>>
>>
>>c<-structure(list(date = structure(1:6, .Label = c("2012-01-03",
>>"2012-01-04", "2012-01-05", "2012-01-06", "2012-01-07", "2012-01-08",
>>"2012-01-09", "2012-01-10", "2012-01-11", "2012-01-12", "2012-01-13",
>>"2012-01-14", "2012-01-15", "2012-01-16", "2012-01-17", "2012-01-18",
>>"2012-01-19", "2012-01-20", "2012-01-21", "2012-01-22", "2012-01-23"
>>), class = "factor"), so2 = c(2.617839247, 0, 0, 0.231044086,
>>0.944608887, 2.12400444), nox = c(308.9046313, 275.6778849, 390.0824142,
>>178.7429364, 238.655832, 251.892601), no = c(156.0262489, 151.4412498,
>>221.0725021, 65.96049786, 106.541748, 119.3471241), no2 = c(74.80145447,
>>59.29991481, 66.5897975, 77.84267978, 75.68422569, 85.43044816
>>), co = c(1.628431197, 1.716231492, 1.264678366, 1.693460745,
>>0.780637084, 0.892724398), o3 = c(26.1473999, 15.91584015, 22.46199989,
>>37.39400101, 15.63426018, 17.51494026), ipa = c(538.414978, 406.4620056,
>>432.6459961, 275.2820129, 435.7909851, 436.8039856), ws = c(4.995530128,
>>1.355309963, 1.708899975, 3.131690025, 1.546270013, 1.571320057
>>), wd = c(58.15639877, 64.5657153143848, 39.9754269501381,
>>24.0739884380921,
>>55.9453098437477, 56.7648829092446), temp = c(10.24740028, 7.052690029,
>>4.33258009, 13.91609955, 8.762220383, 11.04300022), umr = c(97.60900116,
>>96.91899872, 96.20649719, 94.74620056, 82.04550171, 89.41320038
>>)), .Names = c("date", "so2", "nox", "no", "no2", "co", "o3",
>>"ipa", "ws", "wd", "temp", "umr"), row.names = c(NA, 6L), class =
>>"data.frame")
>>
>>
>>Given the three data frames ³a², ³b² and ³c², I need to merge them all by
>>the common field ³date².
>>The following attempt is working fine butŠ
>>
>># code start
>>all<-merge(a,b,by="date",suffixes=c(".a",".b"),all=T)
>>all<-merge(all,c,by="date",all=T)
>># code end
>>
>>ŠI would like to possibly do it in just ³one shot² (a generalisation of
>>the
>>code for a more complex case of handling many data frames) also by
>>assigning
>>proper suffixes to each variable (not possible with the previous code
>>snippet)
>>
>>Then I also try a different approach with the use of the library reshape
>>and
>>the function ³merge_all² butŠ
>>
>># code start
>>library("reshape")
>>all.new<-merge_all(a, b, c, by="date", all=T, suffixes=c(".a",".b",".c"))
>># code end
>>
>>ŠI got the following error message:
>>error in merge in merge.data.frame(as.data.frame(x), as.data.frame(y),
>>...)
>>:
>>  formal argument "all" associated to different argument passed
>>(a free translation from italian)
>>
>>My question is:
>>how to accomplish the merging of multiple data frames with all the same
>>variable names and by a single id field with the need of ³keeping track²
>>of
>>the original data frame by means of the use of suffixes appended to new
>>variables?
>>
>>Any help much appreciated
>>
>>Thank you
>>
>>
>>
>>
>>--
>>View this message in context:
>>http://r.789695.n4.nabble.com/merge-multiple-data-frames-tp4331089p433108
>>9
>>.html
>>Sent from the R help mailing list archive at Nabble.com.
>>
>>______________________________________________
>>R-help at r-project.org mailing list
>>https://stat.ethz.ch/mailman/listinfo/r-help
>>PLEASE do read the posting guide
>>http://www.R-project.org/posting-guide.html
>>and provide commented, minimal, self-contained, reproducible code.
>