14) Merging a Data Frame

This Blog entry is from the Loading and Shaping section in Learn R.

Repeat the Blog entries set forth to create a data frame, this time creating a data frame called Descriptions from the table EOD_Descriptions by typing:

Descriptions <- sqlQuery(Connection,"select * from EOD_Desccriptions")
1.png

Run the line of script to console:

2.png

View the Descriptions data frame by typing:

3.png

Run the line of script to console:

4.png

It can be seen that symbol column is common between the AAPL table and the Descriptions table.

The task in this Blog entry is to merge the data frames together on the Symbol identifier, which will then provide a description next to each and every record in the AAPL dataset.  The inner_join() function seeks to bring together all records where the key in one data frame is present in the other. 

To join two data frames in this manner type:

AAPL <- inner_join(AAPL,Descriptions,ID = "Symbol")
5.png

Run the line of script to console:

6.png

Notice that an error relating to levels has been produced, this is owing to there being a disparity in the number of records in one table as opposed to the next.  Inspect the new dataset by typing:

View(AAPl)
7.png

It can be seen that the description field from the Descriptions Data Frame has been duplicated across each record in the AAPL Data Frame, as would be expected of an Inner Join in a database:

8.png