R match strings in different rows of a dataframe

Question

I have a dataframe with columns of conditions and their values and I have a dataframe with conditions only, and I would like to extract their values by matching the conditions.

df1:
Name    Style   Price   Style   Price   Style   Price
Gary    A       100     AB      300     B       200
Johnson AB      200     B       700     A       300
Marsha  AC      300     C       500     A       400
Watson  A       400     B       200     AB      500
Emma    C       500     B       100     BC      600 

df1 = structure(list(Name = c("Gary", "Johnson", "Marsha", "Watson", 
"Emma"), Style = c("A", "AB", "AC", "A", "C"), Price = c(100L, 
200L, 300L, 400L, 500L), Style.1 = c("AB", "B", "C", "B", "B"
), Price.1 = c(300L, 700L, 500L, 200L, 100L), Style.2 = c("B", 
"A", "A", "AB", "BC"), Price.2 = c(200L, 300L, 400L, 500L, 600L
)), .Names = c("Name", "Style", "Price", "Style.1", "Price.1", 
"Style.2", "Price.2"), class = "data.frame", row.names = c(NA, 
-5L)) 

df2:
Name    Style
Gary    AB
Johnson A
Marsha  C
Watson  B
Emma    BC

df2 = structure(list(Name = c("Gary", "Johnson", "Marsha", "Watson", 
"Emma"), Style = c("AB", "A", "C", "B", "BC")), .Names = c("Name", 
"Style"), class = "data.frame", row.names = c(NA, -5L))

desired output:
Name    Style   Price
Gary    AB      300
Johnson A       300
Marsha  C       500
Watson  B       200
Emma    BC      600

I don't know how to match them when the conditions are in different columns. Thank you for your help.

score 2 · Accepted Answer · answered Nov 11 '16 at 07:26

2

We can use melt from data.table (which can take multiple measure columsn and then do a join with the second dataset ('df2') on the 'Name' and 'Style' columns.

library(data.table)
melt(setDT(df1), measure = patterns("Style", "Price"), 
  value.name = c("Style", "Price"))[df2, on = c("Name", "Style")][, variable := NULL][]
#      Name Style Price
#1:    Gary    AB   300
#2: Johnson     A   300
#3:  Marsha     C   500
#4:  Watson     B   200
#5:    Emma    BC   600

answered Nov 11 '16 at 07:26

akrun

674,427
24
381
486

sorry what if the name is different like style , pattern , format, is it add all name in the value.name right? – Peter Chung Nov 11 '16 at 07:35
@PeterChung Did you meant `Style`, `style` etc? In that case, `measure = patterns("[Ss]tyle", "Price"),` – akrun Nov 11 '16 at 07:43
I mean the header should be Style Price Pattern Price Format Price, so measure = patterns("Style", "Price", "Pattern", "Format") – Peter Chung Nov 11 '16 at 07:48
@PeterChung If you have 4 pattern of columns, yes you have to specify those – akrun Nov 11 '16 at 07:50
how about the value name then? – Peter Chung Nov 11 '16 at 08:03
@PeterChung If you have column patterns, the `value.name` would also be 4 elements – akrun Nov 11 '16 at 08:04

R match strings in different rows of a dataframe

1 Answers1