Re: [R] merge multiple data frames

Tue, 31 Jan 2012 00:24:07 -0800 

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.

Reply via email to