lookup and fill some value from one dataframe to another I have 2 dataframes, df1,and df2 as below.
df1

and df2

I would like to lookup &quot;result&quot; from df1 and fill into df2 by &quot;Mode&quot; as below format. Note &quot;Mode&quot; has become my column names and the results have been filled into corresponding columns.
also note that &quot;ID&quot; from df2 may not necessary equal to &quot;ID&quot; from df1.For example, I am only interested in 4 IDs (A01,A03,A04 and A05, no A02)
while df1 may contain more IDs

I tried to use below code but it doesn't give me a good result.
merged_df = pd.merge(df2, df1,left_on = 'ID', right_on = 'ID', how='outer')
How can I do this?
I would recommend &quot;pivoting&quot; the first dataframe, then filtering for the IDs you actually care about.
Something like this:
useful_ids = [
    'A01',
    'A03',
    'A04',
    'A05',
]
df2 = df1.pivot(index='ID', columns='Mode')
df2 = df2.filter(items=useful_ids, axis='index')