• Home
  • About
  • Contact
  • Privacy
  • Terms
  • DCMA
  • Write For Us / Submit
Tech News, Magazine & Review WordPress Theme 2017
  • Tech
    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    120+ Best Creative Names for Team at Work

    120+ Best Creative Names for Team at Work

    Can You Repair Printed Circuit Board ?

    Mastering PCB Board Repair Comprehensive Guide

    Breaking Barriers Federal Inmate Texting Service

    Breaking Barriers Federal Inmate Texting Service

    transfer whatsapp from android to iphone

    How to Transfer WhatsApp from Android to iPhone!

    Understanding the Role of Humidity Chambers in Climate Testing

    Understanding the Role of Humidity Chambers in Climate Testing

    Everything You Get to Know About Movember Beard Memes

    Everything You Get to Know About Movember Beard Memes

    Best Book Recommendation Apps

    11 Best Book Recommendation Apps

    How to quickly divide or Split PDF files

    How to Quickly Divide or Split PDF Files

  • Gear
    • All
    • Camera
    • Laptop
    • Smartphone
    The New Galaxy Watch Ultra and Galaxy Ring Are Announced by Samsung

    The New Galaxy Watch Ultra and Galaxy Ring Are Announced by Samsung

    Exploring the Innovative Features of Pear Phones

    Exploring the Innovative Features of Pear Phones

    Latest Smart Home Gadgets for a Connected Life

    Latest Smart Home Gadgets for a Connected Life

    Eco-Friendly Products for Students

    Eco-Friendly Products for Students

    Essential Photography Equipment and Gadgets

    Mastering Your Shots: Essential Photography Equipment and Gadgets

    Level Up Your Game: Must-Have Gaming Gear!

    Level Up Your Game: Must-Have Gaming Gear!

    Trending Tags

    • Best iPhone 7 deals
    • Apple Watch 2
    • Nintendo Switch
    • CES 2017
    • Playstation 4 Pro
    • iOS 10
    • iPhone 7
    • Sillicon Valley
  • Gaming
    Master the World of Online Gaming with the Best Gaming VPN

    Master the World of Online Gaming with 3 Best Gaming VPN

    7 Powerful Strategies to Overcome Video Game Addiction and Reclaim Your Life

    7 Powerful Strategies to Overcome Video Game Addiction and Reclaim Your Life

    Powerful Ways Xbox Cloud Gaming is Revolutionizing the Gaming World

    5 Powerful Ways Xbox Cloud Gaming is Revolutionizing the Gaming World

    9 Best Alternative Games Like Kahoot

    9 Best Alternative Games Like Kahoot

    The Top 8 Free Bubble Shooter Games for Endless Entertainment

    The Top 8 Free Bubble Shooter Games for Endless Entertainment

    Cloud Gaming Revolution: How Streaming is Changing the Future of Gaming

    Cloud Gaming Revolution: How Streaming is Changing the Future of Gaming

  • Crypto
    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    120+ Best Creative Names for Team at Work

    120+ Best Creative Names for Team at Work

    Can You Repair Printed Circuit Board ?

    Mastering PCB Board Repair Comprehensive Guide

    Breaking Barriers Federal Inmate Texting Service

    Breaking Barriers Federal Inmate Texting Service

    transfer whatsapp from android to iphone

    How to Transfer WhatsApp from Android to iPhone!

    Understanding the Role of Humidity Chambers in Climate Testing

    Understanding the Role of Humidity Chambers in Climate Testing

    Everything You Get to Know About Movember Beard Memes

    Everything You Get to Know About Movember Beard Memes

    Best Book Recommendation Apps

    11 Best Book Recommendation Apps

    How to quickly divide or Split PDF files

    How to Quickly Divide or Split PDF Files

  • Business
    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    120+ Best Creative Names for Team at Work

    120+ Best Creative Names for Team at Work

    Can You Repair Printed Circuit Board ?

    Mastering PCB Board Repair Comprehensive Guide

    Breaking Barriers Federal Inmate Texting Service

    Breaking Barriers Federal Inmate Texting Service

    transfer whatsapp from android to iphone

    How to Transfer WhatsApp from Android to iPhone!

    Understanding the Role of Humidity Chambers in Climate Testing

    Understanding the Role of Humidity Chambers in Climate Testing

    Everything You Get to Know About Movember Beard Memes

    Everything You Get to Know About Movember Beard Memes

    Best Book Recommendation Apps

    11 Best Book Recommendation Apps

    How to quickly divide or Split PDF files

    How to Quickly Divide or Split PDF Files

No Result
View All Result
Geeky Insider
  • Tech
    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    120+ Best Creative Names for Team at Work

    120+ Best Creative Names for Team at Work

    Can You Repair Printed Circuit Board ?

    Mastering PCB Board Repair Comprehensive Guide

    Breaking Barriers Federal Inmate Texting Service

    Breaking Barriers Federal Inmate Texting Service

    transfer whatsapp from android to iphone

    How to Transfer WhatsApp from Android to iPhone!

    Understanding the Role of Humidity Chambers in Climate Testing

    Understanding the Role of Humidity Chambers in Climate Testing

    Everything You Get to Know About Movember Beard Memes

    Everything You Get to Know About Movember Beard Memes

    Best Book Recommendation Apps

    11 Best Book Recommendation Apps

    How to quickly divide or Split PDF files

    How to Quickly Divide or Split PDF Files

  • Gear
    • All
    • Camera
    • Laptop
    • Smartphone
    The New Galaxy Watch Ultra and Galaxy Ring Are Announced by Samsung

    The New Galaxy Watch Ultra and Galaxy Ring Are Announced by Samsung

    Exploring the Innovative Features of Pear Phones

    Exploring the Innovative Features of Pear Phones

    Latest Smart Home Gadgets for a Connected Life

    Latest Smart Home Gadgets for a Connected Life

    Eco-Friendly Products for Students

    Eco-Friendly Products for Students

    Essential Photography Equipment and Gadgets

    Mastering Your Shots: Essential Photography Equipment and Gadgets

    Level Up Your Game: Must-Have Gaming Gear!

    Level Up Your Game: Must-Have Gaming Gear!

    Trending Tags

    • Best iPhone 7 deals
    • Apple Watch 2
    • Nintendo Switch
    • CES 2017
    • Playstation 4 Pro
    • iOS 10
    • iPhone 7
    • Sillicon Valley
  • Gaming
    Master the World of Online Gaming with the Best Gaming VPN

    Master the World of Online Gaming with 3 Best Gaming VPN

    7 Powerful Strategies to Overcome Video Game Addiction and Reclaim Your Life

    7 Powerful Strategies to Overcome Video Game Addiction and Reclaim Your Life

    Powerful Ways Xbox Cloud Gaming is Revolutionizing the Gaming World

    5 Powerful Ways Xbox Cloud Gaming is Revolutionizing the Gaming World

    9 Best Alternative Games Like Kahoot

    9 Best Alternative Games Like Kahoot

    The Top 8 Free Bubble Shooter Games for Endless Entertainment

    The Top 8 Free Bubble Shooter Games for Endless Entertainment

    Cloud Gaming Revolution: How Streaming is Changing the Future of Gaming

    Cloud Gaming Revolution: How Streaming is Changing the Future of Gaming

  • Crypto
    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    120+ Best Creative Names for Team at Work

    120+ Best Creative Names for Team at Work

    Can You Repair Printed Circuit Board ?

    Mastering PCB Board Repair Comprehensive Guide

    Breaking Barriers Federal Inmate Texting Service

    Breaking Barriers Federal Inmate Texting Service

    transfer whatsapp from android to iphone

    How to Transfer WhatsApp from Android to iPhone!

    Understanding the Role of Humidity Chambers in Climate Testing

    Understanding the Role of Humidity Chambers in Climate Testing

    Everything You Get to Know About Movember Beard Memes

    Everything You Get to Know About Movember Beard Memes

    Best Book Recommendation Apps

    11 Best Book Recommendation Apps

    How to quickly divide or Split PDF files

    How to Quickly Divide or Split PDF Files

  • Business
    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    How Drones and 360-Degree Cameras Are Changing Bridal Photoshoots

    120+ Best Creative Names for Team at Work

    120+ Best Creative Names for Team at Work

    Can You Repair Printed Circuit Board ?

    Mastering PCB Board Repair Comprehensive Guide

    Breaking Barriers Federal Inmate Texting Service

    Breaking Barriers Federal Inmate Texting Service

    transfer whatsapp from android to iphone

    How to Transfer WhatsApp from Android to iPhone!

    Understanding the Role of Humidity Chambers in Climate Testing

    Understanding the Role of Humidity Chambers in Climate Testing

    Everything You Get to Know About Movember Beard Memes

    Everything You Get to Know About Movember Beard Memes

    Best Book Recommendation Apps

    11 Best Book Recommendation Apps

    How to quickly divide or Split PDF files

    How to Quickly Divide or Split PDF Files

Submit
Geeky Insider
No Result
View All Result

How to merge data in R using R merge, dplyr, or data.table

by Staff Writer
October 7, 2022
in How To
Reading Time: 5 mins read
“Do More with R” video tutorials
Share on FacebookShare on Twitter

R has a number of quick, elegant ways to join data frames by a common column. I’d like to show you three of them:

  • base R’s merge() function,
  • dplyr’s join family of functions, and
  • data.table’s bracket syntax.

Table of Contents

Toggle
  • Get and import the data
    • READ ALSO
    • How to Activate Iphone With Sim Card
    • How to Quickly Divide or Split PDF Files
  • Merges with base R
  • Joins with dplyr

Get and import the data

For this example I’ll use one of my favorite demo data sets — flight delay times from the U.S. Bureau of Transportation Statistics. If you want to follow along, head to http://bit.ly/USFlightDelays and download data for the time frame of your choice with the columns Flight Date, Reporting_Airline, Origin, Destination, and DepartureDelayMinutes. Also get the lookup table for Reporting_Airline.

READ ALSO

transfer whatsapp from android to iphone

How to Transfer WhatsApp from Android to iPhone!

October 6, 2023
How to prevent phishing attacks

How to Prevent Phishing Attacks: Safeguarding Your Digital Identity

May 14, 2023

Or, download these two data sets — plus my R code in a single file and a PowerPoint explaining different types of data merges — here:

download

Includes several data files, a PowerPoint, and R script to accompany InfoWorld article. Sharon Machlis

 

To read in the file with base R, I’d first unzip the flight delay file and then import both flight delay data and the code lookup file with read.csv(). If you’re running the code, the delay file you downloaded will likely have a different name than in the code below. Also, note the lookup file’s unusual .csv_ extension.

unzip(“673598238_T_ONTIME_REPORTING.zip”)
mydf <- read.csv(“673598238_T_ONTIME_REPORTING.csv”,
sep = “,”, quote=”””)
mylookup <- read.csv(“L_UNIQUE_CARRIERS.csv_”,
quote=”””, sep = “,” )

Next, I’ll take a peek at both files 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 Master Top Linhas Aereas Ltd.
5 07Q Flair Airlines Ltd.
6 09Q Swift Air, LLC d/b/a Eastern Air Lines d/b/a Eastern

Merges with base R

The mydf delay data frame only has airline information by code. I’d like to add a column with the airline names from mylookup. One base R way to do this is with the merge() function, using the basic syntax merge(df1, df2). It doesn’t matter the order of data frame 1 and data frame 2, but whichever one is first is considered x and the second one is y. 

If the columns you want to join by don’t have the same name, you need to tell merge which columns you want to join by: by.x for the x data frame column name, and by.y for the y one, such as merge(df1, df2, by.x = “df1ColName”, by.y = “df2ColName”).

You can also tell merge whether you want all rows, including ones without a match, or just rows that match, with the arguments all.x and all.y. In this case, I’d like all the rows from the delay data; if there’s no airline code in the lookup table, I still want the information. But I don’t need rows from the lookup table that aren’t in the delay data (there are some codes for old airlines that don’t fly anymore in there). So, all.x equals TRUE but all.y equals FALSE. Full code:

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

The new joined data frame includes a column called Description with the name of the airline based on the carrier 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

dplyr uses SQL database syntax for its join functions. A left join means: Include everything on the left (what was the x data frame in merge()) and all rows that match from the right (y) data frame. If the join columns have the same name, all you need is left_join(x, y). If they don’t have the same name, you need a by argument, such as left_join(x, y, by = c(“df1ColName” = “df2ColName”)) .

Note the syntax for by: It’s a named vector, with both the left and right column names in quotation marks.

Update: The development version of dplyr has an additional by syntax: left_join(x, y, by = join_by(df1ColName == df2ColName)). Instead of a named vector with quoted column names, the new join_by() function uses unquoted column names and the ==  boolean operator.

If you’d like to try this out, you can install the dplyr dev version (1.0.99.90 as of this writing) with devtools::install_github(“tidyverse/dplyr”) or remotes`::install_github(“tidyverse/dplyr”). 

left join IDG

A left join keeps all rows in the left data frame and only matching rows from the right data frame.

The code to import and merge both data sets using left_join() is below. It starts by loading the dplyr and readr packages and then reads in the two files with read_csv(). When using read_csv(), I don’t need to 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 = c(“OP_UNIQUE_CARRIER” = “Code”))

read_csv() creates tibbles, which are a type of data frame with some extra features. left_join() merges the two. Take a look at the syntax: In this case, order matters. left_join() means include all rows on the left, or first, data set, but only rows that match from the second one. And, because I need to join by two differently named columns, I included a by argument.

The new join syntax in the development-only version of dplyr would be

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

Since most people likely have the CRAN version, however, I will use dplyr’s original named-vector syntax in the rest of this article, until join_by() becomes part of the CRAN version.

We can look at the structure of the result with dplyr’s glimpse() function, which is another way to see the top few items of a data frame.

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 Lines Inc.”, “Delta Air Lines Inc.”, “Delta Air …

This joined data set now has a new column with the name of the airline. If you run a version of this code yourself, you’ll probably notice that dplyr was way faster than base R.

Next, let’s look at a super-fast way to do joins.

Source by www.infoworld.com

Related Posts

How to Use the Text Command Excel
How To

How to Use the Text Command Excel

November 22, 2023
How to Communicate with Beeper Codes
How To

How to Communicate with Beeper Codes

November 4, 2024
transfer whatsapp from android to iphone
Articles

How to Transfer WhatsApp from Android to iPhone!

October 6, 2023
How to prevent phishing attacks
How To

How to Prevent Phishing Attacks: Safeguarding Your Digital Identity

May 14, 2023

Recommended.

Here’s everything that’s new with the iPhone 14 Pro series that makes it the ‘best iPhone, yet’ (2)

Apple to resolve iPhone 14 Pro and Pro Max camera shake issue by next week; details here- Technology News, Firstpost

September 22, 2022
GeekDad Daily Deal: 2 Lifetime Licenses for Microsoft Office Professional 2021 for Windows

GeekDad Daily Deal: 2 Lifetime Licenses for Microsoft Office Professional 2021 for Windows

October 24, 2022

Trending.

No Content Available
  • Home
  • About
  • Contact
  • Privacy
  • Terms
  • DCMA
  • Write For Us / Submit
Contact us for submission queries. editor[at]geekyinsider.com.
No Result
View All Result
  • Home
  • Review
  • Apple
  • Gaming
  • Gadget and Gear
    • Camera
    • Smartphone
  • Microsoft
  • Security