How to join data frames in R (inner, outer, left, right)?

Posted by Dan Goldstein on Stack Overflow See other posts from Stack Overflow or by Dan Goldstein
Published on 2009-08-19T13:18:11Z Indexed on 2010/05/03 15:38 UTC
Read the original article Hit count: 327

Filed under:

r

|

join

|

inner-join

|

outer-join

|

sql

Given two data frames

df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3)))
df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1)))

> df1
  CustomerId Product
           1 Toaster
           2 Toaster
           3 Toaster
           4   Radio
           5   Radio
           6   Radio

> df2
  CustomerId   State
           2 Alabama
           4 Alabama
           6    Ohio

How can I do database style, i.e., sql style, joins? That is, how do I get:

An inner join of df1 and df1
An outer join of df1 and df2
A left outer join of df1 and df2
A right outer join of df1 and df2

P.S. IKT-JARQ (I Know This - Just Adding R Questions)

Extra credit:

How can I do a sql style select statement?

Related posts about join

Improving Partitioned Table Join Performance

as seen on SQL Blog - Search for 'SQL Blog'
The query optimizer does not always choose an optimal strategy when joining partitioned tables. This post looks at an example, showing how a manual rewrite of the query can almost double performance, while reducing the memory grant to almost nothing. Test Data The two tables in this example use… >>> More
MS SQL 2008, join or no join?

as seen on Stack Overflow - Search for 'Stack Overflow'
Just a small question regarding joins. I have a table with around 30 fields and i was thinking about making a second table to store 10 of those fields. Then i would just join them in with the main data. The 10 fields that i was planning to store in a second table does not get queried directly, it's… >>> More
SQL Server 2008, join or no join?

as seen on Stack Overflow - Search for 'Stack Overflow'
Just a small question regarding joins. I have a table with around 30 fields and i was thinking about making a second table to store 10 of those fields. Then i would just join them in with the main data. The 10 fields that i was planning to store in a second table does not get queried directly, it's… >>> More
Simple join gives unnecessary rows,How to get join properly with only matches in both tables

as seen on Stack Overflow - Search for 'Stack Overflow'
Table 1 Field1 Field2 AA 20 AA 20 AB 12 AC 13 Table2 field3 field4 AA 20 AA 20 AC 13 AD 23 AW 21 output required: newfield field2 field4 AA 20 20 AA 20 20 AC 13 13 I used: select field1 as newfield, t1.field2,t2.field4 from table1 t1 join table2 t2 on t1.field1=t2.field3 This… >>> More
Mulitple full joins in Postgres is slow

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a program to use the IMDB database and am having very slow performance on my query. It appears that it doesn't use my where condition until after it materializes everything. I looked around for hints to use but nothing seems to work. Here is my query: SELECT * FROM name as n1 FULL JOIN… >>> More

Developer IT

How to join data frames in R (inner, outer, left, right)? - Developer IT

How to join data frames in R (inner, outer, left, right)?

r

join

inner-join

outer-join

sql

Related posts about r

Related posts about join

Improving Partitioned Table Join Performance

MS SQL 2008, join or no join?

SQL Server 2008, join or no join?

Simple join gives unnecessary rows,How to get join properly with only matches in both tables

Mulitple full joins in Postgres is slow

Categories cloud