Combine column in data.frame by the same symbols

Question

I want to combine a list of data.frame by the same symbols in text. Here is my data:

d1 <- data.frame(Name = c("aaa", "bbb", "ccc","ddd","ggg", "eee"), ID = c("123", "456", "789", "101112", "131415", "161718"), stringsAsFactors = FALSE)

d2 <- data.frame(Code = c("123.aR16", "456d245", "14asadf789", "123_dy6r", "202122-fsd", "101112gh"), CupCake = c("a1", "a2", "a3", "a4", "a5", "a6"), stringsAsFactors = FALSE)

If Code contains the same combinations of numbers from ID -> add new value with data from Name.

It looks like copy-paste value from Name.

Expected output:

  Name     ID       Code CupCake
1  aaa    123   123.aR16      a1
2  bbb    456    456d245      a2
3  ccc    789 14asadf789      a3
4  aaa    123   123_dy6r      a4
5   NA     NA 202122-fsd      a5
6  ddd 101112   101112gh      a6

Is this order random or can we expect for row-wise values to correspond to ~ID? — Roman Luštrik, Nov 01 '18 at 19:02
What I mean is, for row 1 where ID=123, can this ID occur in df2 at any point, or is it placed in row one (or not present at all)? Also, where did `202122-fsd` come from? It's not in your example. Please revise your MRE. — Roman Luštrik, Nov 01 '18 at 19:28

djfinnoy · Accepted Answer · 2018-11-01T19:07:18.477

1

Using tidyverse packages:

library(dplyr)
library(stringr)

# Create ID in d2
d2 <- mutate(d2, ID = str_extract(Code, "([0-9]+)"))

# Merge d1 and d2 based on ID
df <- full_join(d1, d2, by= "ID")

# Edit: if you only want one row per ID
df1 <- inner_join(d1, d2, by= "ID")

# Or
df2 <- inner_join(d2, d1, by= "ID")

edited Nov 01 '18 at 19:07

answered Nov 01 '18 at 19:04

djfinnoy

585
3
13

How would you handle cases if ID is not numeric? – Roman Luštrik Nov 01 '18 at 19:07
Not sure I understand what you mean. ID is a string variable in the example data given, and in the solution. – djfinnoy Nov 01 '18 at 19:09

score 0 · Answer 2 · answered Nov 01 '18 at 19:09

0

Another solution.

d2$ID=NA

for(id in d1$ID)
     d2$ID[grep(id, d2[,1])]=id
# if you want to see all data
dplyr::full_join(d1,d2,by="ID")
# if only interested in matched
dplyr::inner_join(d1,d2,by="ID")

answered Nov 01 '18 at 19:09

Bing

1,083
1
10
20

Combine column in data.frame by the same symbols

2 Answers2