CODEWITHSUNDEEP
rmerge
FrosyFeet456
FrosyFeet456

Reputation: 349

Merging two data.frames with numbers and characters in same column in r

I have two data frames. One is a library of words with a corresponding number. The other is a question, I have 3. My original data has 2 million rows in the library and 1 million questions. As to why I'm using a for loop in the columns. The questions I have is why the first two questions which have numbers sort in the merge command, whereas the questions with only words do not sort. Any reasons why this could be. I have reproducible data, its a lot of code probably but if you run it will make more sense in the data.frames. It should all work without any adjusting. The data.tables are df = questions, df2 = library, output = what I want the output to look like, and DF = is what the actual output is.

words1<-c(1,2,3,"How","did","Quebec")
words2<-c(.24,.25,.66,"Why","does","volicty")
words3<-c("How","do","I","clean","a","car")
library<-c(1,3,.25,.66,"How","did","does","do","I","wash","a","Quebec","car","is")
embedding1<-c(.48,.68,.52,.39,.5,.6,.7,.8,.9,.3,.46,.48,.53,.42)
df <- data.frame(words1,words2,words3)
names(df)<-c("words1","words2","words3")


words1<-c(.48,NA,.68,.5,.6,.48)
words2<-c(NA,.52,.39,NA,.7,NA)
words3<-c(.5,.8,.9,NA,.46,.53)
output<-data.frame(words1,words2,words3)
#--------Upload 2nd dataset-------#
df2 <- data.frame(library,embedding1)
names(df2)<-c("library","embedding1")

#-----Find columns--------#
l=ncol(df)
l
mynames<-colnames(df)
head(mynames)


#------Combine and match libary to training data------#
require(gridExtra)
List = list()
for(name in mynames){
  df1<-df[,name]
  df1<-as.data.frame(df1)
  x_train2<-merge(x= df1, y = df2, 
                  by.x = "df1", by.y = 'library',all.x=T, sort=F)
  new_x_train2<-x_train2[duplicated(x_train2[,2]),]
  x_train2<-x_train2[,-1]
  x_train2<-as.data.frame(x_train2)
  names(x_train2) <- name
  List[[length(List)+1]] = x_train2
}
list<-List

DF  <-  as.data.frame(matrix(unlist(list), nrow=length(unlist(list[1]))))

Upvotes: 0

Views: 810

Answers (2)

Mako212
Mako212

Reputation: 7312

You could do this with tidyverse. Doing it this way leaves more NAs in your columns, but preserves the order, and I think it essentially does what you're looking for:

library(tidyverse)
library(reshape2)

 df %>% melt(id = NULL) %>% 
  inner_join(.,df2,  by = c("value" = "library")) %>% 
  spread(variable, embedding1) %>% 
  select(-value)

Resulting in:

   words1 words2 words3
1      NA   0.52     NA
2      NA   0.39     NA
3    0.48     NA     NA
4    0.68     NA     NA
5      NA     NA   0.46
6      NA     NA   0.53
7    0.60     NA     NA
8      NA     NA   0.80
9      NA   0.70     NA
10   0.50     NA   0.50
11     NA     NA   0.90
12   0.48     NA     NA

Upvotes: 1

Anonymous coward
Anonymous coward

Reputation: 2091

The main reason is because with merge, sorting is done. See ?merge:

The rows are by default lexicographically sorted on the common columns, but for sort = FALSE are in an unspecified order.

If you walk through your loop step-by-step you'll see it in action. Use dplyr::left_join instead, which preserves row-order.

df1 <- df[, "words1"]
df1 <- as.data.frame(df1)

> df1
     df1
1      1
2      2
3      3
4    How
5    did
6 Quebec

merge(x= df1, y = df2, 
      by.x = "df1", by.y = 'library', all.x=T, sort=F)

     df1 embedding1
1      1       0.48
2      3       0.68
3    How       0.50
4    did       0.60
5 Quebec       0.48
6      2         NA

left_join(x = df1, y = df2, by = c("df1" = "library"), all.x = T)

     df1 embedding1
1      1       0.48
2      2         NA
3      3       0.68
4    How       0.50
5    did       0.60
6 Quebec       0.48

Upvotes: 0

Related Questions