0

I am trying to comine two data frames such that I match the values in the vectors called vec by their index number. I am looking for an intuitive vlookup function.

    vec=c(-2,-5)
    vec2=c(11,17)
    y1=as.data.frame(cbind(0:(length(c(vec))-1)*2+1,c(vec)))

      V1 V2
    1  1 -2
    2  3 -5

    y2=as.data.frame(cbind(0:(length(c(m$coefficients))-1)*2+2,c(vec2)))

       V1 V2
    1  2 11
    2  4 15

    x=as.data.frame(1:4,names="V1"); names(x)="V1"

      V1
    1  1
    2  2
    3  3
    4  4

What I want is a dataframe to look like this

      V1  V2
    1  1  -2
    2  2  11
    3  3  -5
    4  4  17

So far I was able to do the first merge

c1=merge(x, y1, all.x=TRUE)

  V1 V2
1  1 -2
2  2 NA
3  3 -5
4  4 NA

But unable to do the second with 

c2=merge(c1, y2, all.x=TRUE)
jessica
  • 1,325
  • 2
  • 21
  • 35
  • 1
    First combine your y's then merge: `merge(x, rbind(y1,y2), all.x=TRUE)`. note `m$` isn't defined in your example – MrFlick Mar 30 '20 at 00:51
  • Thank you, I am building out a coefficient, standerror table for my regressions. I have so many regression and needed an automated way to summarize the regression. Thank you! – jessica Mar 30 '20 at 00:55
  • But had I wanted to use this command `c2=merge(c1, y2, all.x=TRUE)` similar to a vlookup. How would I do so? I want to fill the NA's in the data frame `c1` with the values in `y2`. – jessica Mar 30 '20 at 00:56
  • That just wouldn't work in R. R doesn't do vlookups. It does proper merges/joins. It's better to reframe the problem in terms of how the different languages work rather than trying to do literal function translations. It's possible but it just gets messier in R to force it do things the Excel way. see: https://stackoverflow.com/questions/21712384/updating-column-in-one-dataframe-with-value-from-another-dataframe-based-on-matc/40364973 – MrFlick Mar 30 '20 at 01:07

2 Answers2

1

You can rbind y1 and y2 and then merge with x.

merge(x, rbind(y1, y2), by = 'V1')

#  V1 V2
#1  1 -2
#2  2 11
#3  3 -5
#4  4 15

Same logic using dplyr : 

library(dplyr)
bind_rows(y1, y2) %>% inner_join(x, by =  'V1')
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
0

Another option is rbindlist from data.table

library(data.table)
rbindlist(list(y1, y2)[x, on = .(V1)]
akrun
  • 874,273
  • 37
  • 540
  • 662