Lookup Values in Pandas - 2 search Key

Question

I have 2 tables from google sheets. df1 where it has the users completion of different modules. And df2 that consists of user details.

df1:

df2:

I want to merge tables just like the image below. Currently, I am able to achieve this using array vlookup. Sometimes I also use index match. But it takes forever because in reality, df1 has atleast 50000 rows, that is 40 modules for roughly 5000 users. I am new to python and pandas.

output:

Thank you in advance!

Answer 1

Use pivot to reshape df1, then merge:

df2.merge(df1.pivot(index='User Id', columns='Module', values='Status'), on='User Id')