I have two tables A/B. I want to join merge table A to B on a loop create.
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
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.
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