Tips on how to merge information in R utilizing R merge, dplyr, or information.desk

R has quite a few fast, elegant methods to affix information frames by a standard column. I’d like to indicate you three of them:

• base R’s merge() perform
• dplyr’s be part of household of capabilities
• information.desk’s bracket syntax

Get and import the info

For this instance I’ll use one in every of my favourite demo information units—flight delay instances from the U.S. Bureau of Transportation Statistics. If you wish to comply with alongside, head to http://bit.ly/USFlightDelays and obtain information for the timeframe of your selection with the columns Flight Date, Reporting_Airline, Origin, Vacation spot, and DepartureDelayMinutes. Additionally get the lookup desk for Reporting_Airline.

Or, you possibly can obtain these two information units—plus my R code in a single file and a PowerPoint explaining several types of information merges—right here:

To learn within the file with base R, I’d first unzip the flight delay file after which import each flight delay information and the code lookup file with learn.csv(). Should you’re working the code, the delay file you downloaded will probably have a unique identify than within the code beneath. Additionally, word the lookup file’s uncommon .csv_ extension.

unzip("673598238_T_ONTIME_REPORTING.zip")
mydf <- learn.csv("673598238_T_ONTIME_REPORTING.csv", 
sep = ",", quote=""")
mylookup <- learn.csv("L_UNIQUE_CARRIERS.csv_", 
quote=""", sep = "," )

Subsequent, I’ll take a peek at each information with head():

head(mydf)
     FL_DATE OP_UNIQUE_CARRIER ORIGIN DEST DEP_DELAY_NEW  X
1 2019-08-01                DL    ATL  DFW            31 NA
2 2019-08-01                DL    DFW  ATL             0 NA
3 2019-08-01                DL    IAH  ATL            40 NA
4 2019-08-01                DL    PDX  SLC             0 NA
5 2019-08-01                DL    SLC  PDX             0 NA
6 2019-08-01                DL    DTW  ATL            10 NA
head(mylookup)
  Code                                          Description
1  02Q                                        Titan Airways
2  04Q                                   Tradewind Aviation
3  05Q                                  Comlux Aviation, AG
4  06Q                        Grasp Prime Linhas Aereas Ltd.
5  07Q                                  Aptitude Airways Ltd.
6  09Q Swift Air, LLC d/b/a Jap Air Traces d/b/a Jap

Merges with base R

The mydf delay information body solely has airline data by code. I’d like so as to add a column with the airline names from mylookup. One base R means to do that is with the merge() perform, utilizing the essential syntax merge(df1, df2). The order of knowledge body 1 and information body 2 would not matter, however whichever one is first is taken into account x and the second is y. 

If the columns you need to be part of by don’t have the identical identify, it’s essential to inform merge which columns you need to be part of by: by.x for the x information body column identify, and by.y for the y one, comparable to merge(df1, df2, by.x = "df1ColName", by.y = "df2ColName").

It’s also possible to inform merge whether or not you need all rows, together with ones with no match, or simply rows that match, with the arguments all.x and all.y. On this case, I’d like all of the rows from the delay information; if there’s no airline code within the lookup desk, I nonetheless need the data. However I don’t want rows from the lookup desk that aren’t within the delay information (there are some codes for outdated airways that don’t fly anymore in there). So, all.x equals TRUE however all.y equals FALSE. This is the code:

joined_df <- merge(mydf, mylookup, by.x = "OP_UNIQUE_CARRIER", 
by.y = "Code", all.x = TRUE, all.y = FALSE)

The brand new joined information body features a column referred to as Description with the identify of the airline primarily based on the provider code:

head(joined_df)
  OP_UNIQUE_CARRIER    FL_DATE ORIGIN DEST DEP_DELAY_NEW  X       Description
1                9E 2019-08-12    JFK  SYR             0 NA Endeavor Air Inc.
2                9E 2019-08-12    TYS  DTW             0 NA Endeavor Air Inc.
3                9E 2019-08-12    ORF  LGA             0 NA Endeavor Air Inc.
4                9E 2019-08-13    IAH  MSP             6 NA Endeavor Air Inc.
5                9E 2019-08-12    DTW  JFK            58 NA Endeavor Air Inc.
6                9E 2019-08-12    SYR  JFK             0 NA Endeavor Air Inc.

Joins with dplyr

The dplyr bundle makes use of SQL database syntax for its be part of capabilities. A left be part of means: Embrace all the pieces on the left (what was the x information body in merge()) and all rows that match from the fitting (y) information body. If the be part of columns have the identical identify, all you want is left_join(x, y). In the event that they don’t have the identical identify, you want a by argument, comparable to left_join(x, y, by = c("df1ColName" = "df2ColName")).

Be aware the syntax for by: It’s a named vector, with each the left and proper column names in citation marks.

Replace: Beginning with dplyr model 1.1.0 (on CRAN as of January 29, 2023), dplyr joins have an extra by syntax utilizing join_by():

left_join(x, y, by = join_by(df1ColName == df2ColName))

The brand new join_by() helper perform  makes use of unquoted column names and the ==  boolean operator, which bundle authors say makes extra sense in an R context than c("col1" = "col2"), since = is supposed for assigning a worth to a variable, not testing for equality.

IDG

The code to import and merge each information units utilizing left_join() is beneath. It begins by loading the dplyr and readr packages, after which reads within the two information with read_csv(). When utilizing read_csv(), I don’t must unzip the file first.

library(dplyr)
library(readr)

mytibble <- read_csv("673598238_T_ONTIME_REPORTING.zip")
mylookup_tibble <- read_csv("L_UNIQUE_CARRIERS.csv_")

joined_tibble <- left_join(mytibble, mylookup_tibble,
by = join_by(OP_UNIQUE_CARRIER == Code))

Be aware that dplyr’s older by syntax with out join_by() nonetheless works


joined_tibble <- left_join(mytibble, mylookup_tibble,
by = c("OP_UNIQUE_CARRIER" = "Code"))


read_csv() creates tibbles, that are a kind of knowledge body with some further options. left_join() merges the 2. Check out the syntax: On this case, order issues. left_join() means embody all rows on the left, or first, information set, however solely rows that match from the second. And, as a result of I would like to affix by two otherwise named columns, I included a by argument.

The brand new be part of syntax within the development-only model of dplyr could be:

joined_tibble2 <- left_join(mytibble, mylookup_tibble, 
by = join_by(OP_UNIQUE_CARRIER == Code))

Since most individuals probably have the CRAN model, nevertheless, I’ll use dplyr‘s authentic named-vector syntax in the remainder of this text, till join_by() turns into a part of the CRAN model.

We will take a look at the construction of the end result with dplyr’s glimpse() perform, which is one other approach to see the highest few gadgets of a knowledge body:

glimpse(joined_tibble)
Observations: 658,461
Variables: 7
$ FL_DATE           <date> 2019-08-01, 2019-08-01, 2019-08-01, 2019-08-01, 2019-08-01…
$ OP_UNIQUE_CARRIER <chr> "DL", "DL", "DL", "DL", "DL", "DL", "DL", "DL", "DL", "DL",…
$ ORIGIN            <chr> "ATL", "DFW", "IAH", "PDX", "SLC", "DTW", "ATL", "MSP", "JF…
$ DEST              <chr> "DFW", "ATL", "ATL", "SLC", "PDX", "ATL", "DTW", "JFK", "MS…
$ DEP_DELAY_NEW     <dbl> 31, 0, 40, 0, 0, 10, 0, 22, 0, 0, 0, 17, 5, 2, 0, 0, 8, 0, …
$ X6                <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ Description       <chr> "Delta Air Traces Inc.", "Delta Air Traces Inc.", "Delta Air …

This joined information set now has a brand new column with the identify of the airline. Should you run a model of this code your self, you’ll most likely discover that dplyr is means quicker than base R.

Subsequent, let’s take a look at a super-fast approach to do joins.