Merge two tables and create a column based on the similarity between two columns

I have two tables A/B. I want to join merge table A to B on a loop create.

Input

Table A:            Table B:

name      year      name     subject group
James     one       Jenny    Physics
Jackson   one       Jackson  Maths
Jenny     two       Jenny    PE
Himeth    three     Himeth   Chemistry
Carlos    three     Carlos   Physics
Mendy     one       Mendy    German
James     two       James    Physics
Himeth    one       Himeth   Chemistry

Output

Name    subject    group   year_one year_two year_three
Jenny   PE         Yellow           two     
Jackson Maths      Green   one              
James   Physics    Yellow  one      two     
Himeth  Chemistry  Yellow  one                     three
Carlos  Physics    Green                           three
Mendy   German     Yellow  one      

I want to add year column from table A but create columns for year (one,two,three) as there are duplicates.

years = [“one”, “two”, “three”]

for i in years: 
    df.to_csv("student.csv")
    dfs = pd.merge(df,students,
                on = "Name",
                how = "left")
    dfs[i] = i
    dfs.to_csv("class_setup.csv")

I’m not sure how to loop over the merge for and create a new column.

Answer

Use DataFrame.pivot with DataFrame.join:

df = df2.join(df1.pivot('name','year','year'), on='name').fillna('')
print (df)
      name  one  three  two
0    Jenny              two
1  Jackson  one            
2    Jenny              two
3   Himeth  one  three     
4   Carlos       three     
5    Mendy  one            
6    James  one         two
7   Himeth  one  three     

If order is important:

years = ["one", "two", "three"]
df = df2.join(df1.pivot('name','year','year').reindex(years, axis=1), on='name').fillna('')
print (df)
      name  one  two  three
0    Jenny       two       
1  Jackson  one            
2    Jenny       two       
3   Himeth  one       three
4   Carlos            three
5    Mendy  one            
6    James  one  two       
7   Himeth  one       three