Hi, I have two dataframes that have some common columns. I would like to join them by the common columns prochi and prescribed_date as there are duplicate prochis but they will be made unique by date. I tried doing an inner join but that just duplicated the columns whereas I would like the information from the test_sql_tsf to fill the NAs in the test_sql_psd common columns.
require(sqldf) test_sql_psd <- http://n4.nabble.com/file/n1593282/test_sql_psd.txt test_sql_psd.txt , header=TRUE, sep="\t", dec=".", na.strings="NA", check.names=TRUE, quote= "\"'") test_sql_tsf <-read.table(file= http://n4.nabble.com/file/n1593282/test_sql_tsf.txt test_sql_tsf.txt , , header=TRUE, sep="\t", dec=".", na.strings="NA", check.names=TRUE, quote= "\"'") test_sql_innerjoin <- sqldf("select * test_sql_psd inner join test_sql_tsf on test_sql_psd.prochi=test_sql_tsf.prochi") colnames(test_sql_psd) [1] "prochi" "prescribed_date" "dataMonth" "item_code" [5] "res_seqno" "quantity" "directions" "no_of_packs" [9] "datasource" "scan_ref_no" "name" "approved_name" [13] "formulation_code" "strength" "measure_code" "bnf_code" [17] "bnf_description" colnames(test_sql_tsf) [1] "prochi" "prescribed_date" "dataMonth" "item_code" [5] "res_seqno" "quantity" "directions" "no_of_packs" [9] "datasource" "scan_ref_no" "name" "formulation_code" [13] "strength" "bnf_code" with the result of: colnames(test_sql_innerjoin) [1] "prochi" "prescribed_date" "dataMonth" "item_code" [5] "res_seqno" "quantity" "directions" "no_of_packs" [9] "datasource" "scan_ref_no" "name" "formulation_code" [13] "strength" "bnf_code" "prochi" "prescribed_date" [17] "dataMonth" "item_code" "res_seqno" "quantity" [21] "directions" "no_of_packs" "datasource" "scan_ref_no" [25] "name" "approved_name" "formulation_code" "strength" [29] "measure_code" "bnf_code" "bnf_description" I'm not sure if I am using the correct sqldf command or if there is an easier way to do this from the start. I also tried test_sql_union<-sqldf("select * test_sql_tsf union select * test_sql_psd") which gave me the same result as inner join. I'm not sure if I am using the correct commands for what I want to do? Thanks for your help. Natalie -- View this message in context: http://n4.nabble.com/inner-join-sqldf-tp1593282p1593282.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.
