LISTSERV mailing list manager LISTSERV 16.0

Help for R-USERS-L Archives


R-USERS-L Archives

R-USERS-L Archives


R-USERS-L@LISTS.UFL.EDU


View:

Message:

[

First

|

Previous

|

Next

|

Last

]

By Topic:

[

First

|

Previous

|

Next

|

Last

]

By Author:

[

First

|

Previous

|

Next

|

Last

]

Font:

Proportional Font

LISTSERV Archives

LISTSERV Archives

R-USERS-L Home

R-USERS-L Home

R-USERS-L  2018

R-USERS-L 2018

Subject:

Re: Merging multiple dataframes

From:

"Grasley-Boy, Nicolette M" <[log in to unmask]>

Reply-To:

UF R Users List <[log in to unmask]>

Date:

Tue, 5 Jun 2018 15:56:35 +0000

Content-Type:

text/plain

Parts/Attachments:

Parts/Attachments

text/plain (139 lines)

Ed's example is pretty similar to how I've been using it.


# read in df's


merged_df <- df1 %>%
  inner_join(df2, by = "id") %>%
  inner_join(df3, by = "id") %>%
  inner_join(df4, by = "id") #repeat for all df's


Inner will only keep the rows with common ids compared to the x df (inner_join(x, y, by =...), so if you happen to have a df with a master set of ids, I would use that as df1 above.


I recently merged some data with incorrect or missing ids and used left_join(df1, df2, by="id") then ran anti_join(df2, df2, by="id") to see which ones were not matched.  I'm sure there are better ways to do it but it did what I wanted :)





Nikki Grasley-Boy, M.Ed., BCBA

Doctoral Student

School of Special Education, School Psychology, and Early Childhood Studies

University of Florida

________________________________
From: UF R Users List <[log in to unmask]> on behalf of El Rouby,Nihal M <[log in to unmask]>
Sent: Tuesday, June 5, 2018 11:42:08 AM
To: [log in to unmask]
Subject: Re: Merging multiple dataframes

Thank you so much Ed.

The inner join can be also used to keep the common Ids across all data frames, correct?

-----Original Message-----
From: UF R Users List [mailto:[log in to unmask]] On Behalf Of Camp, Ed
Sent: Tuesday, June 05, 2018 11:27 AM
To: [log in to unmask]
Subject: Re: Merging multiple dataframes

Quick reproducible if bulky example of how I have used dplyr piping to connect multiple joins when I need to merge more than 2 dataframes.  This is the best way I know (please correct if others know better, I haven't used Reduce per Nihal's suggestion) to merge when a dimension, in this case rows, is ragged.  It would also be useful if only want to retain matching rows (e.g. here I use full join, could use left join or other to filter--the previously suggested dplyr reference website offers a nice explanation.  Perhaps it is obvious, but you could drop the c("year", "x2") in the [] if you want to keep all columns.

rm(list=ls());

#bring up dplyr
require(dplyr)

#create data frames
year <- seq(2000, 2017) #a common variable that will be used to link, via merge or join the data frames

x1 <- rnorm(n=length(year), mean=0, sd=1) #variable 1 for data frames
x2 <- rnorm(n=length(year), mean=1, sd=1) #variable 2 for data frames

df1 <- data.frame(year, x1, x2)                                          #all the years
df2 <- data.frame(year, x1, x2)[1:(length(year)-1),]    #didn't have the last year
df3 <- data.frame(year, x1, x2)[2:length(year),]        #didn't have the first year
df4 <- data.frame(year, x1, x2)
#if the number of rows were identical, you could just cbind them, keeping columns you wanted.
# I use the dplyr joins with piping (%>%) when I need to merge multiple df's that may have ragged dims or if needing to filter only matching rows

#bulky but functional example of piping
merged_df <- df1[,c("year","x2")] %>% full_join(df2[,c("year","x2")], by = "year") %>%  #apparently the %>% symbol functions to "run on" the join (called a "pipe" in dplyr)
        full_join(df3[,c("year","x2")], by = "year") %>%
                full_join(df4[,c("year","x2")], by = "year")
        colnames(merged_df) =c("year", "x2_1", "x2_2", "x2_3", "x2_4") #name them something reasonable merged_df


Hope that is useful.
Ed

-----Original Message-----
From: UF R Users List <[log in to unmask]> On Behalf Of El Rouby,Nihal M
Sent: Tuesday, June 5, 2018 10:41 AM
To: [log in to unmask]
Subject: Re: Merging multiple dataframes

Thank you so much for Max, Young and Nikki for your quick responses.

Nikki, sending the code you have used before would be greatly appreciated

-----Original Message-----
From: UF R Users List [mailto:[log in to unmask]] On Behalf Of Grasley-Boy, Nicolette M
Sent: Tuesday, June 05, 2018 8:41 AM
To: [log in to unmask]
Subject: Re: Merging multiple dataframes

Hi Nihal,

I've been using dplyr's piping to merge multiple dataframes. I'm not at my computer at the moment but I've used this website to guide join type - https://dplyr.tidyverse.org/reference/join.html. Then I just pipe each df into the previous. If you're interested, I can send you more detailed code later. I'm not sure if the other methods folks have sent are more efficient for a lot of dfs. I've only done 7 or 8 with this.

Best,
Nikki


Nikki Grasley-Boy, M.Ed., BCBA
Special Education Doctoral Student
University of Florida


-------- Original message --------
From: "El Rouby,Nihal M" <[log in to unmask]>
Date: 6/5/18 7:50 AM (GMT-05:00)
To: [log in to unmask]
Subject: Merging multiple dataframes

Hello R users,


I'm interested in merging 28 dataframes using R keeping overlapping IDs. Can dplyr inner join do that? or Should I try to figure out a loop?


Thanks,

Nihal

This list strives to be beginner friendly.  However, we still ask that you PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

This list strives to be beginner friendly.  However, we still ask that you PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

This list strives to be beginner friendly.  However, we still ask that you PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

This list strives to be beginner friendly.  However, we still ask that you PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

This list strives to be beginner friendly.  However, we still ask that you
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

This list strives to be beginner friendly.  However, we still ask that you
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Top of Message | Previous Page | Permalink

Advanced Options


Options

Log In

Log In

Get Password

Get Password


Search Archives

Search Archives


Subscribe or Unsubscribe

Subscribe or Unsubscribe


Archives

2019
2018
2017
2016
2015
2014
2013
2012
2011
2010
2009
2008

ATOM RSS1 RSS2



LISTS.UFL.EDU

CataList Email List Search Powered by the LISTSERV Email List Manager