CODEWITHSUNDEEP
r
LoopBloke
LoopBloke

Reputation: 15

How to find a column value anywhere in another data frame and return the column name

As the questions states, I am trying to find a column value(in A$X[i]) anywhere in another data frame(in B) and return the column name(B$[j]).

I have two tables here:

Client Account df
JOHN     KATE    RAM    ABE
2341    K919    Q1234   23445
V454    56783   88872   9009
87874   345GH   63547   8834S

Client details df
Date        ClientNumber   Region   Amount      Insurance 
12/5/2015   9009           EMEA     15236.23    Yes
12/5/2015   K919           EMEA     1889.22     No
12/5/2015   87874          EMEA     152000.0    No
12/5/2015   345GH          EMEA     28877.53    Yes
12/5/2015   9009           EMEA     90000.89    Yes

I am trying to add Client name to Client details table Desired output:

Client details table
Date        ClientNumber   Region   Amount      Insurance  Name
12/5/2015   9009           EMEA     15236.23    Yes        ABE
12/5/2015   K919           EMEA     1889.22     No         KATE
12/5/2015   87874          EMEA     152000.0    No         JOHN
12/5/2015   345GH          EMEA     28877.53    Yes        KATE
12/5/2015   9009           EMEA     90000.89    Yes        ABE

The tables are so big that I would love to make use of vectorization than using a loop.

Upvotes: 1

Views: 87

Answers (2)

Dave Gruenewald
Dave Gruenewald

Reputation: 5689

I'm assuming the first data frame is A and the second is B. You should be able to merge the two using the packages tidyr and dplyr:

library(tidyr)
library(dplyr)

A %>% 
  gather(JOHN:ABE, key = Name, Value = ClientNumber) %>% 
  left_join(B, ., by = "ClientNumber")

The left_join uses the full dataset B and merges with the newly modified dataset A by ClientNumber

Upvotes: 2

Onyambu
Onyambu

Reputation: 79218

it would have been better if you pasted the results of maybe dput(head(data)) here for one to be able to easily answer. Since no loops neither the classes that contain loop in the background such as *apply functions needed, then I guess you will need to do things in a vector format:

 Details$Name <- names(unlist(AccountDF))[match(Details$clientnumber,unlist(AccountDF),nomatch = 0)]

I hope it might help

Upvotes: 0

Related Questions