Below are two sample tables in R
Table1
ID Name
1 a
2 b
3 c
4 a
5 b
6 c
Table2
ID Age
2 20
4 40
6 60
10 100
50 500
I want to create a table as below (By adding a column Age in Table1 and pull the value from Table2 based on ID column:
Output Table
ID Name Age
1 a -
2 b 20
3 c -
4 a 40
5 b -
6 c 60
Could someone help me with this?
you could use sqldf library.
?sqldf
SQL select on data frames
Description
SQL select on data frames
# import the sqldf library.
# if you get "Error in library(sqldf) : there is no package called 'sqldf' you can install it with install.packages('sqldf')
library(sqldf)
# your first df
table1 <- read.csv('/tmp/df1.csv',stringsAsFactors = F)
table2 <- read.csv('/tmp/df2.csv',stringsAsFactors = F)
table2
# express your query in terms of an SQL statement.
# in this case you want ALL the records from the first table and ALL the records from the second one which are 'matching' with the records in the first one
# In terms of SQL, this is a LEFT JOIN statement
sql <- 'select table1.id, name, age from table1 left join table2 on (table1.id = table2.id)'
# run your query
sqldf(sql)
and this is the results:
id name age
1 1 a -
2 2 b 20
3 3 c -
4 4 a 40
5 5 b -
6 6 c 60
NB: if you don't have the matching records for the id 1, 3, 5 in table2, you can change your sql statement from left join to a LEFT OUTER JOIN. Feel free to ask me for further explanations, thanks.
