So, I sent a tweet to the #rstats followers with a link to where I posted the problem on this blog to see if I could get a hint. (twitter has been a very valuable networking tool for me, I've learned a lot about data mining, machine learning, and R from the tweet-stream. Tweets and blog posts from people like Hadley Wickham, Drew Conway, and J.D. Long have been tremendously helpful to me as I've taken up R.
Back to the topic at hand, below is my new code, based on suggestions from Hadley Wickham and a comment (from Harlan) that lead me to some answers to a similar question on stack overflow. The code below requires he reshape library as well as plyr, which I should mention appears to have been created by Hadley Wickham himself.
# read all Y2000 csv files filenames <- list.files(path="/Users/wkuuser/Desktop/R Data Sets/TRADE_DATA/TempData00", full.names=TRUE) import.list <- llply(filenames, read.csv) # left join all Y2000 csv files AllData00 <- Reduce(function(x, y) merge(x, y, all=FALSE,by.x="Reporting.Countries.Partner.Countries",by.y="Reporting.Countries.Partner.Countries",all.x =TRUE, all.y =FALSE),import.list,accumulate=F) dim(AllData00) # n = 211 211 # rename common key variable to something less awkward and change World to World00 AllData00 <- rename(AllData00, c(Reporting.Countries.Partner.Countries="Partner", World = "World00")) names(AllData00) # list all variable names # keep only the partner name variable and total world trade AllData00 <-AllData00[c("Partner","World00")] dim(AllData00) # data dimensions names(AllData00) # variable names fix(AllData00) # view in data editor
That pretty well gives me the data set I need, for year 2000 data. I repeated the process for 2004 and 2008 data sets I had and then merged them with left joins to get the final data set. All I am after at this point is the total world trade for each of the countries/groups listed. This could probably be made even more efficient, but is is a lot less coding than what I initially used for the project. (see below- and this doesn't even include some of the renaming and sub-setting functions I performed above) And, this process would have to be repeated 2 more times for 2004 and 2008 data. To say that the above code is much more efficient is an understatement. (note the code below actually contains some mistakes as Hadley Wickham pointed out. For instance, in the merge statement, I have by.a and by.b, or by.'dataset', while in every case it should be by.x and by.y. I guess x and y are alias's for the data sets being merged, sort of like a and b would be in SQL, if you were to say:
create table newdataset as
select *
from dat1 a left join dat2 b
on a.partner=b.partner
So, I'm not sure why my code even worked to begin with. I do realize that instead of the merge statement in R I could have used the sqldf package in R, but I have had issues with my mac crashing when I try to load the library. Still, I don't think SQL would have made things any better, as I would still be doing a series of left joins vs. the more compact code using the reduce function in R. I've used sqldf in a windows environment before and it worked great by the way.
The code below first reads in each data file individually, and then executes the endless number of left joins to give me the same data set I got above with a fraction of the amount of required code.
# a a <- read.csv("X_A.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(a) a <- rename(a, c(Reporting.Countries.Partner.Countries="Partner")) names(a) dim(a) # b b <- read.csv("X_B.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(b) b <- rename(b, c(Reporting.Countries.Partner.Countries="Partner")) names(b) dim(b) # c c <- read.csv("X_C.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(c) c <- rename(c, c(Reporting.Countries.Partner.Countries="Partner")) names(c) dim(c) # de de <- read.csv("X_DE.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(de) de <- rename(de, c(Reporting.Countries.Partner.Countries="Partner")) names(de) dim(de) # fgh fgh <- read.csv("X_FGH.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(fgh) fgh <- rename(fgh, c(Reporting.Countries.Partner.Countries="Partner")) names(fgh) dim(fgh) # ijk ijk <- read.csv("X_IJK.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(c) ijk <- rename(ijk, c(Reporting.Countries.Partner.Countries="Partner")) names(ijk) dim(ijk) # lm lm <- read.csv("X_LM.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(lm) lm <- rename(lm, c(Reporting.Countries.Partner.Countries="Partner")) names(lm) dim(lm) # nop nop <- read.csv("X_NOP.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(nop) nop <- rename(nop, c(Reporting.Countries.Partner.Countries="Partner")) names(nop) dim(nop) # qr qr <- read.csv("X_QR.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(qr) qr <- rename(qr, c(Reporting.Countries.Partner.Countries="Partner")) names(qr) dim(qr) # s odd name changed to 'SaloTomaPrincip' manaully in excel s <- read.csv("X_S.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(s) s <- rename(s, c(Reporting.Countries.Partner.Countries="Partner")) names(s) dim(s) # tuv tuv <- read.csv("EX_TUV.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(tuv) tuv <- rename(tuv, c(Reporting.Countries.Partner.Countries="Partner")) names(tuv) dim(tuv) # wxyz wxyz <- read.csv("X_WXYZ.csv", na.strings=c(".", "NA", "", "?"), encoding="UTF-8") names(wxyz) wxyz <- rename(wxyz, c(Reporting.Countries.Partner.Countries="Partner")) names(wxyz) dim(wxyz) # ------------------------------------------------------------------ # sequentially left join data sets # ------------------------------------------------------------------ # a & b ab <- merge(a,b, by.a = Partner, by.b =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(ab) names(ab) 14 + 18 - 1 # r = 211 c = 31 # abc abc <- merge(ab,c, by.ab = Partner, by.c =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(abc) names(abc) 31 + 24 -1 # n = 54 # a_e a_e <- merge(abc,de, by.abc = Partner, by.de =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_e) names(a_e) 54 + 18 -1 # n = 71 # a_h a_h <- merge(a_e,fgh, by.a_e = Partner, by.fgh =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_h) names(a_h) 71 + 23 -1 # n = 93 # a_k a_k <- merge(a_h,ijk, by.a_h = Partner, by.ijk =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_k) names(a_k) 93 + 16 -1 # n = 108 # a_m a_m <- merge(a_k,lm, by.a_k = Partner, by.lm =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_m) names(a_m) 108 + 26 - 1 # n = 133 # a_p a_p <- merge(a_m,nop, by.a_m = Partner, by.nop =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_p) names(a_p) 133 + 20 - 1 # n = 152 # a_r a_r <- merge(a_p,qr, by.a_p = Partner, by.qr =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_r) names(a_r) 152 + 6 -1 # n = 157 # a_s a_s <- merge(a_r,s, by.a_r = Partner, by.s =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_s) names(a_s) 157 + 27 - 1 # n = 183 # a_v a_v <- merge(a_s,tuv, by.a_s = Partner, by.tuv =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_v) names(a_v) 183 + 21 - 1 # n = 203 # a_z (complete data set after this merge) a_z <- merge(a_v,wxyz, by.a_v = Partner, by.wxyz =Partner, all = FALSE, all.x = TRUE, all.y = FALSE) dim(a_z) # n = 211 names(a_z)
No comments:
Post a Comment