r - How to match and divide -
given following data tables:
df1 <- data.table(v1=c("a","c","d","b"),v2=c(0,2,0,2),v3=c(2,0,2,0)) df2 <- data.table(v1=c("a","b","c","d"),v2=c(4,2,4,2)) df1 df2 > df1 v1 v2 v3 1: 0 2 2: c 2 0 3: d 0 2 4: b 2 0 > df2 v1 v2 1: 4 2: b 2 3: c 4 4: d 2
i seek following: every numerical value of df1, divide value corresponding value df2, using v1 key. resulting data table should be:
> df3 v1 v2 v3 1: 0 0.5 2: c 0.5 0 3: d 0 1 4: b 1 0
can please me?
many in advance.
this work examples although isn't extensible more columns. real world usage use 2 tables have same column names?
df3<-merge(df1,df2,"v1")[,list(v2=v2.x/v2.y, v3=v3/v2.y),by=v1]
here's way work more columns, if may or may not have same name in each table. relies on column matched being named v1
otherwise doesn't rely on column names. take whatever first column after v1
divisor if there more 2 columns in df2.
#the first 6 lines change column names incase they're same oldnames1<-names(df1)[!names(df1) %in% "v1"] oldnames2<-names(df2)[!names(df2) %in% "v1"] newnames1<-paste0("x",oldnames1) newnames2<-paste0("y",oldnames2) setnames(df1,oldnames1,newnames1) setnames(df2,oldnames2,newnames2) df3<-merge(df1,df2,by="v1") df3[,(oldnames1):=.sd[,!newnames2,with=false]/get(newnames2),by="v1"] df3[,c(newnames1,newnames2):=null] setnames(df1,newnames1,oldnames1) setnames(df2,newnames2,oldnames2)
Comments
Post a Comment