Matching multiple column and add to dataframe

Question

supposed data set,

df1

df2

 a1     a2   c1      c2
27.0    1   red    apple
131.0   1   blue   banana
2124.0  3   green  apple
1345.0  1   red    orange
346.0   5   blue   grape

I want to compare num1 - a1 & num2 - a2, If both conditions are the same, I want to add the values in c1 and c2 to the list.(assume the beginning is an empty list)

condition

As you see, num1-a1, num2&a2 `s shape is different.
Only the values added to the list are 'c1' and 'c2'

(num1, num2, a1, a2 are just using to compare & match)
The order is jumbled, and the dataframes are different sizes. (different column length)

The Output what I want

above example, 2 matches, (27-1 , 346-5), so

mylist = [red, apple, blue, grape]

How can I do this?

Thank you for reading.

It seems 'blue', 'banana' is also matched.

jezrael
– jezrael

2020-02-03 09:14:56 +00:00
Commented Feb 3, 2020 at 9:14 — jezrael
– jezrael, Commented Feb 3, 2020 at 9:14

jezrael · Accepted Answer · 2020-02-03 09:14:41Z

2

Use Series.str.zfill with DataFrame.assign for add 0 for matched with df2, then DataFrame.merge with defualt join, last use DataFrame.stack for Series and convert to list:

mylist = (df1.assign(num1 = df1['num1'].astype(str).str.zfill(4),
                     num2 = df1['num2'].astype(str).str.zfill(3))
              .merge(df2, left_on=['num1','num2'], right_on=['a1','a2'])[['c1','c2']]
              .stack()
              .tolist())
print (mylist)
['green', 'apple', 'orange', 'melon',
 'blue', 'banana', 'purple', 'peach']

edited Feb 3, 2020 at 9:14

answered Feb 3, 2020 at 9:09

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ybin Over a year ago

Thank you for your efficient code , but now I am reading real data, I modified my question to be a little different from the shape I thought. Can you check again if it`s okay?

jezrael Over a year ago

@ybin - I see your answer and it is correct, only omit .assign(num1 = df1['num1'].astype(str).str.zfill(4), num2 = df1['num2'].astype(str).str.zfill(3))

Collectives™ on Stack Overflow

Matching multiple column and add to dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related