Create new column in a dataframe based on matching result of another dataframe

Question

I have a dataframe df1, which stores the current registered course and the previous course taken.

course	previous_course
CS201	CS101
CS201	CS102
CS201	CS103
CS202	CS101
CS202	CS102
CS202	CS103

Some courses have prerequisite, which are also stored in dataframe df2 (it's one to many relationship, one course could have many prerequisite)

course	prerequiste
CS201	CS101
CS202	CS102
CS202	CS103

I would like to match if the previous_course in df1 is prerequisite or not, and create another column (1 or 0). Could you advise what should I do? Thanks a lot!

jezrael · Accepted Answer · 2021-04-27 10:48:21Z

3

Use indicator parameter in DataFrame.merge, compare by both and cast to integers for map True, False to 1,0:

df = pd.merge(df1, df2, left_on=['course', 'previous_course'],
              right_on=['course', 'prerequiste'],
              how='left', 
              indicator='is_prerequiste')
df['is_prerequiste'] = df['is_prerequiste'].eq('both').astype(int)
print (df)
  course previous_course prerequiste  is_prerequiste
0  CS201           CS101       CS101               1
1  CS201           CS102         NaN               0
2  CS201           CS103         NaN               0
3  CS202           CS101         NaN               0
4  CS202           CS102       CS102               1
5  CS202           CS103       CS103               1

answered Apr 27, 2021 at 10:48

jezrael

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

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

Comments

SeaBean · Accepted Answer · 2021-04-28 18:14:33Z

3

You can do a left .merge() on df1 and df2 and then create the new column based on the joined prerequisite column using np.where(), as follows:

df3 = pd.merge(df1, df2, left_on=['course', 'previous_course'], right_on=['course', 'prerequiste'], how='left')
df3['is_prerequiste'] = np.where(df3['prerequiste'].notna(), 1, 0)


print(df3)

  course previous_course prerequiste  is_prerequiste
0  CS201           CS101       CS101               1
1  CS201           CS102         NaN               0
2  CS201           CS103         NaN               0
3  CS202           CS101         NaN               0
4  CS202           CS102       CS102               1
5  CS202           CS103       CS103               1

edited Apr 28, 2021 at 18:14

answered Apr 27, 2021 at 10:38

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

Collectives™ on Stack Overflow

Create new column in a dataframe based on matching result of another dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related