CODEWITHSUNDEEP
rdplyr
Laura
Laura

Reputation: 483

Combine data frames picking only columns sharing same name beginnings across data frames

I have this two dataframes that I would like to use to create another one:

df<-as.data.frame(matrix(rexp(200, rate=.1), ncol=10))
colnames(df)<-c("one","two","three","four","five","six","seven","eight","nine","ten")
df


    df.new<-as.data.frame(matrix(rexp(155, rate=.1), ncol=8))
colnames(df.new)<-c("one.two","one.two.new","three.two","three.two.new","five.one","five.one.new","seven.two","seven.two.new")
df.new

My idea is to have a dataframe with these columns:

(one|one.two|one.two.new|three|three.two|three.two.new|five|five.one|five.one.new)

I could do it manually but my dataframes are much bigger than these ones.

Is it possible to do this with dplyr package??

Upvotes: 1

Views: 83

Answers (2)

mlt
mlt

Reputation: 1659

Here is another shorter alternative. I just dislike wide tables...so you got to melt it anyway at some point.

to.pick <- unique(unlist(sapply(colnames(df.new), function(x) {
  Reduce(function(a,b) paste(a, b, sep="."), strsplit(x, '.', fixed=TRUE)[[1]], accumulate=TRUE)
})))
zz <- cbind(df, df.new)
out <- subset(zz, select=to.pick)
colnames(out)
 [1] "one"           "one.two"       "one.two.new"   "three"         "three.two"     "three.two.new" "five"         
 [8] "five.one"      "five.one.new"  "seven"         "seven.two"     "seven.two.new"

Original answer

Use melting/casting for that with data filtered by column name part.

library(tidyr)

Spread stuff into "normal" long representation

df$idx <- 1:nrow(df)
gdf <- gather(df, key, value, -idx)
df.new$idx <- 1:nrow(df.new)
gdf.new <- gather(df.new, key, value, -idx)

Get unique first part

uu <- unique(gdf.new$key)
to.pick <- sapply(uu, function(x) {
  strsplit(x, '.', fixed=TRUE)[[1]][1]
  })

Subset only those from first data frame that we want.

gdf.ss <- subset(gdf, key %in% to.pick)

Combine data still in "normal" long form.

out <- rbind(gdf.ss, gdf.new)

Cast away into "ugly" wide format

out.wide <- spread(out, key, value)
colnames(out.wide)
 [1] "idx"           "five"          "five.one"     
 [4] "five.one.new"  "one"           "one.two"      
 [7] "one.two.new"   "seven"         "seven.two"    
[10] "seven.two.new" "three"         "three.two"    
[13] "three.two.new"

I'll update my answer if you insist on columns sorted not strictly alphabetically.

Upvotes: 1

rgt47
rgt47

Reputation: 336

The columns are clustered in threes, let N=be the number of clusters. 

 N=3 # for the example provided
 foo=seq(1,2*N+1,2) 
 dplyr::bind_cols(df, df.new) %>% dplyr::select(names(.)[c(foo, 
 foo+10, foo+11)])

Upvotes: 0

Related Questions