Re: [R] merge multiple data frames

[David M. Schruth]

Sorry this is so late:

But you could try a "nerge" (from the 'caroline' package)
nerge(list(a,b,c))

Just have to make sure that the rows for each dataframe are renamed with 
the date columns.


On 1/30/2012 11:44 PM, Massimo Bressan wrote:
thanks don
I have here enough to study for a while....
thank you for your help
max
----- Original Message ----- From: "MacQueen, Don" <macque...@llnl.gov>
To: "Massimo Bressan" <mbres...@arpa.veneto.it>; <r-help@r-project.org>
Sent: Monday, January 30, 2012 4:47 PM
Subject: Re: [R] merge multiple data frames


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" <mbres...@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" <macque...@llnl.gov>
To: "maxbre" <mbres...@arpa.veneto.it>; <r-help@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" <mbres...@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@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.




______________________________________________
R-help@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.

______________________________________________
R-help@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.