# 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 "result" from df1 and fill into df2 by "Mode" as below format. Note "Mode" has become my column names and the results have been filled into corresponding columns.

also note that "ID" from df2 may not necessary equal to "ID" 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 "pivoting" 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')

• the pivoting idea looks good, but i have trouble to filter. I tried to get the useful_ids from dateframe <pre> idlist = df2 ['ID'].tolist() <code> and do the filter like this <pre> df2 =df2.filter(item=idlist, axis= 'index') <code> and i failed with unexpected keyword argument 'item' Mar 17, 2021 at 23:19
• try "items" with an s instead of "item" Mar 17, 2021 at 23:37
• many thanks, it works Mar 18, 2021 at 0:08