How to Match Specific Values in two DataFrames and Add Extra Columns in Python Pandas? [duplicate]

Question

How can we use a FOR LOOP to match the color from df_students to the color in df_colors and then fill in the corresponding fruit and corresponding fruit_id for each student in df_students?

import pandas as pd
df_colors = pd.DataFrame({'fruit_id':[101, 102, 103, 104, 105, 106, 107, 108, 109],
                       'fruit':['apple','banana','dragonfruit','kiwi','plum','lime', 'blackberry', 'blueberry', 'guava'],
                       'color':['red', 'yellow', 'magenta', 'brown', 'purple', 'green', 'black', 'blue', 'pink']})

df_students = pd.DataFrame({'student':['Jamie', 'Tao', 'Ingrid', 'Will', 'Boris','Xavier','Nancy', 'Judith', 'Lamar', 'Francis', 'Shawna', 'Carlos', 'Morgan'],
                        'color': ['black', 'red', 'magenta', 'yellow','black', 'magenta', 'brown', 'purple', 'magenta', 'green', 'blue', 'pink', 'pink']})


df_students['fruit'] = ''
df_students['fruit_id'] = ''
for eachstudent in df_students['color']:
    for acolor in df_colors['color']:
        if eachstudent == acolor:
            df_students['fruit'] = df_colors['fruit']
            df_students['fruit_id'] = df_colors['fruit_id']
df_students

This output is incorrect!

Answer 1

import pandas as pd

df_colors = pd.DataFrame({'fruit_id':[101, 102, 103, 104, 105, 106, 107, 108, 109],
                   'fruit':['apple','banana','dragonfruit','kiwi','plum','lime', 'blackberry', 'blueberry', 'guava'],
                   'color':['red', 'yellow', 'magenta', 'brown', 'purple', 'green', 'black', 'blue', 'pink']})

df_students = pd.DataFrame({'student':['Jamie', 'Tao', 'Ingrid', 'Will', 'Boris','Xavier','Nancy', 'Judith', 'Lamar', 'Francis', 'Shawna', 'Carlos', 'Morgan'],
                    'color': ['black', 'red', 'magenta', 'yellow','black', 'magenta', 'brown', 'purple', 'magenta', 'green', 'blue', 'pink', 'pink']})
df_students['fruit'] = ''
df_students['fruit_id'] = ''

for acolor1 in df_colors['color']: 
    df_students.loc[df_students['color']==acolor1,'fruit']= list(df_colors.loc[df_colors['color']==acolor1,'fruit'])[0]
    df_students.loc[df_students['color']==acolor1, 'fruit_id'] = list(df_colors.loc[df_colors['color']==acolor1, 'fruit_id'])[0]
print (df_students)